SQL & PL/SQL :: ORA-06575 Package Or Function In Invalid State
Feb 15, 2012
The function is supposed to tell whether numbers stored as a string value evaluates to a date.I copied into an SQL windown in PL/SQL Developer and typed "commit;" after and then tried to view the results as I posted below to make sure the function worked.
However, I get an ora-06575 Package or Function GETDATE is in an invalid state.So would like to know first, if the code is correct? And if it is, do I need to do something else to make Developer take the code? Here is the code that gave me the error I used to try to test the function:
select GetDate ('010312', 'dd-mm-yyyy')
from dual
Here is the code I used to create the function:
create or replace function GetDate (vDate varchar2, vMask varchar2)
return varchar2
is
vReturnDate date;
begin
vReturnDate = to_date(vDate, vMask);
return vReturnDate;
exception
when value_error then
return 0;
end GetDate;
commit;
I am getting the following error only for the ""first execution"", even the package is in "valid" state. If the execute the same package again "second execution", it runs ok.
usually we complie all the "invalid objects" in the schema when we move the code to TEST or PRE-PROD env.
but i am getting the following error, even if the package is in "valid state".
ERROR at line 1: ORA-04068: existing state of packages has been discarded ORA-04061: existing state of package "TEST_OWNER.TEST_PKG" has been invalidated ORA-04065: not executed, altered or dropped package "TEST_OWNER.TEST_PKG" ORA-06508: PL/SQL: could not find program unit being called: "TEST_OWNER.TEST_PKG" ORA-06512: at line 2I have gone through the following thread, blu explained the cause of this error, but do we get this error even when the package is in "valid" state too?
I'm trying to do a count on the number of combinations of country/state codes that are invalid (in Australia) for driving licenses.
select COUNTRY_CODE, STATE_CODE, count(*) from (select COUNTRY_CODE, STATE_CODE from CUSTOMER_TABLE where DRIVING_LICENCE is not null and not (COUNTRY_CODE in ('AUST') AND STATE_CODE in ('VIC', 'NSW', 'SA', 'QLD', 'NT', 'TAS', 'WA', 'ACT'))) group by COUNTRY_CODE, STATE_CODE
The output is okay...for example I get these results:
INTINT NZSI NZINT AUSTINT NZNSW NZ <null>
However, what I am missing is the combination of "AUST" & <null> for country/state respectively. Am I writing the code correctly?
There are 4 packages got invalid 2 days back. when I analyze the database I came to know that there are 5 tables got truncated and 2 tables got altered during the issue period through the code. Those truncated tables have the indirect relationship with these 4 packages.but there is no any relation between these packages and altered table.
Also during that time I got the below error in my alert log.I am sure the cause this error is the invalid packages.
I know if any alteration happens in a table, the refrence package will be getting as invalid. Apart from this, is there anyother cause to bring the package into invalid status? How to proceed further to find the root cause of thses invalid package?
-- Create a table type of the table you want TYPE tbl_test IS varray(100) of VARCHAR2(30);
-- Function that will return the table type FUNCTION fnc_test RETURN tbl_test;
-- End package END;
CREATE OR REPLACE PACKAGE BODY pkg_test AS FUNCTION fnc_test RETURN tbl_test IS -- type table_name_va is varray(100) of VARCHAR2(30); -- Variable of the type tbl_test
[code]...
But i am having problem calling this to test it.
declare TYPE tbl_test IS varray(100) of VARCHAR2(30); var_tbl_test tbl_test; begin var_tbl_test:= pkg_test.fnc_test;
How can i test my Procedure /Function/Package, i mean how to unit & integration test my Procedure /Function/Package. Is there any tool available in the market, or we have to test in manually .
is it possible to obtain a called procedure/function name within package?
For a measuring and tracing purpose, I would like to store an info at the beginning of each procedure/function in package with timestamp + additional details if needed.
For example:
CREATE OR REPLACE PACKAGE BODY "TEST_PACKAGE" IS PROCEDURE proc_1 IS BEGIN
[Code]....
I would like to replace "???????" with a function which would return a name of called procedure, so result of trace data after calling TEST_PACKAGE.proc_2 would be:
I need to call the VB function below from a Procedure's PL/SQL code and capture the returned variable into a varchar2 variable.I looked at the several means and nothing seems to work.
My purpose is to audit the execution of a specified procedure, function in a package. So I try this audit option audit execute on dbms_java.longname Althought I'm using SYS, it leads to this error:
SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" But when I try audit execute on dbms_java It's ok and it audit every statement that using that package dbms_java. But thing I want is audit the specified procedure on this package, not all of this package.
why DBA_OBJ_AUDIT_OPTS show DBMS_JAVA package object type is procedure ???
I'm trying to un-escape the data of a table column using the package function - UTL_URL.Unescape . But the problem is my column is a CLOB and length of my data exceeds that acceptable by the function.
So, i'm just trying to simulate that function behavior normally. My logic is to scan all characters with pattern '%yy' (HEX) and then convert it into DECIMAL using TO_CHAR(,'XX') . But got stuck in this regexp-replace.
with xx as( select '<p>%3Cp%3Ewefwef%3C/p%3E</p>' col from dual ) select UTL_URL.unescape(col) x1, regexp_replace(col,'%([a-zA-Z0-9]){2}','2|.') x2 from [code].......
I am getting an ORA-00902: invalid datatype error when I am trying call the below function from a select statement. Here I am trying to get a table out from a function.
create or replace package pkg10 as type tabletype1 is table of table1%rowtype index by binary_integer; function func1 return tabletype1; end pkg10;create or replace package body pkg10 as
I have a problem when trying to create a PLSQL function based on an XML extraction query.
I have three dummy tables:
SQL> get create_address 1 create table ADDRESS 2 ( 3 id NUMBER not null, 4 house_number NUMBER, 5 house_name VARCHAR2(20), 6 street_name VARCHAR2(30),
[code]....
And the following dummy data for these:
1 insert all 2 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET) 3 values (1, 1, '', 'Tube Street', 'Norwich', 'Norfolk', 'NF12', '2DF') 4 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET) 5 values (2, 5, '', 'Dave Street', 'Edlington', 'Kent', 'CT34', '8GH') 6 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET)
[code]....
So far so good then. But, what I want to create is a function where I can pass in an id value and return the corresponding XML CLOB.
So I try, very simply, this:
SQL> get get_xml_data 1 create or replace function get_xml_data(p_id in number) return clob is 2 Result clob; 3 begin 4 select xmlroot(xmlelement("HomeData",
[code]....
And, alas, i'm greeted by this:
SQL> / Warning: Function created with compilation errors. SQL> sho err Errors for FUNCTION GET_XML_DATA: LINE/COL ERROR -------- ----------------------------------------------------------------- 4/5 PL/SQL: SQL Statement ignored 24/63 PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got - SQL>
I've tried to redo the query in several different ways but so far nothing.
CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT ( TEST_ID NUMBER(9), TEST_DESC VARCHAR(30) ) / CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE / [code]....
I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?
I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?
I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').
I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
2437312:ORA-12801: error signaled in parallel query server P009 2437313:ORA-01502: index 'POS.XIETBK_POS_FACT_TRAN_DATE' or partition of such index is in unusable state
and tried to rebuild the index and i got following error.
ORA-14086: a partitioned index may not be rebuilt as a whole.
The table size for the index is large. we need to rebuild the hole index.
1. when the oracle session changed from active to inactive? 2. what is the time for active session? 3. session is changed to inactive from active. but it is still showing in v$session. 4. in v$session, can i see the ipaddress of client machine ?
When i am writing a function (Stand alone or inside a Package) i know what i am writing, i know weather i am changing a Package state or weather i am changing a Database State. So what is the use for giving a PRAGMA RESTRICT_REFERENCE?
as for the other PRAGMAs EXECPTION_INIT is needed to Specify an error name to a specifiec error number so that i can use the error name to handle the exception. AUTONOMOUS_TRANSACTION is used to execute the SQL Operation inside a Block as a child Transaction.SERIALLY_REUSABLE states that a package variable doesnot persists throughout the session.
RESTRICT_REFERENCE states that the code should not do the following RNPS : Read no package state, WNPS : Write no package state RNDS : Read no database state WNDS : Write no database state
what is there to state as i know what my code is doing.
without specifing EXECPTION_INIT, AUTONOMOUS_TRANSACTION or SERIALLY_REUSABLE i cannot get a handler for an exception which doesnot have a handler, i cannot execute SQL Operations from inside the CODE autonomously or i cannot reuse the package variable, but without the RESTRICT_ REFERENCE is can ensure that my code in not doing (RNPS,WNPS,RNDS,WNDS).