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;

View 5 Replies


ADVERTISEMENT

PL/SQL :: Existing State Of Package Has Been Discarded

Nov 2, 2012

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?

Re: When should be package invalidated?

View 4 Replies View Related

Count Number Of Combinations Of Country / State Invalid For Driving Licenses

May 21, 2012

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?

View 1 Replies View Related

SQL & PL/SQL :: Difference Between Stand Alone Function And Function Declared In A Package?

Mar 11, 2010

What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.

View 2 Replies View Related

SQL & PL/SQL :: Cause Of Invalid Package?

Mar 8, 2012

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.

ORA-00600: internal error code, arguments: [kkxprpic8], [], [], [], [], [], [], []

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?

View 6 Replies View Related

SQL & PL/SQL :: Package Error - Invalid Identifier?

Mar 4, 2012

way give error in package below

SQL> CREATE OR REPLACE PACKAGE DATA_BKG AS
2
3 TYPE OBJ_DEPT IS RECORD
4 (
5 DEPT_ID NUMBER(10),

[code]...

View 7 Replies View Related

Executing Function In A Package?

Mar 24, 2008

I have created one function in the package.

function : Tier_wh
package : Order_DESC

Function defined as

function TIER_WH( message in out Xorder_desc)
...
...
end;

Xorder_desc is defined as

create or replace type Xorder_desc type
(order number(10),
location number(10),
wh varchar2(20)
);

how to execute this function which is defined in the package .

View 1 Replies View Related

SQL & PL/SQL :: Can Use Htp Package In Oracle Function

May 14, 2010

can we use htp package in function?

something like

if event_id = p_event_id THEN
htp.tableRowOpen;
htp.tableData(htf.bold('Evnt'), 'RIGHT', cattributes=>'CLASS=bptext');
htp.tableRowClose;
end if;

View 4 Replies View Related

SQL & PL/SQL :: How To Use Ref Cursor In A Package Or In A Function

Sep 20, 2012

what is ref cursor?

How to use ref cursor in a package or in a function?

View 7 Replies View Related

SQL & PL/SQL :: Calling A Function In Package

Mar 9, 2010

I have this following pakg

CREATE OR REPLACE PACKAGE pkg_test AS

-- 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;

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Create Pipeline Function In Package

Feb 13, 2013

Can we create a Pipelined function in A Package ? I know we can create it standalone function.

View 11 Replies View Related

PL/SQL :: How To Test Procedure / Function / Package

Sep 16, 2012

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 .

View 6 Replies View Related

PL/SQL :: Get A Called Procedure / Function Name Within Package?

Jan 11, 2013

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:

11.1.2013 09:00:01    START.*TEST_PACKAGE.proc_2*
11.1.2013 09:00:01    START.*TEST_PACKAGE.proc_1*
11.1.2013 09:00:01    END.*TEST_PACKAGE.proc_1*
11.1.2013 09:00:01    END.*TEST_PACKAGE.proc_2*

I tried to use "dbms_utility.format_call_stack" but it did not return the name of procedure/function.

View 7 Replies View Related

Export / Import Of Procedure - Function / Package

Jun 9, 2012

Can we Export & Import of Procedure, Function & Package selection by name, as we can export & import of one or more table by name

View 2 Replies View Related

Client Tools :: How To Add Function To Existing Package

Nov 11, 2010

I want to add my function to the existing package.

View 8 Replies View Related

SQL & PL/SQL :: Providing Execute Privileges To Function In A Package?

Jun 6, 2013

I want to provide execute privilege to a function in a package to other schema.

Owner wedb
Package MASTER
function in a a package is convert_function

GRANT EXECUTE ON wedb.MASTER.convert_function to HRDB;

I got the blow error.

ORA-00905: missing keyword

View 2 Replies View Related

PL/SQL :: Providing Execute Privileges To Function In A Package

Jun 6, 2013

I want to provide execute privilege to a function in a package to other schema.

Owner wedb
Package MASTER
function in a a package is convert_function

GRANT EXECUTE ON wedb.MASTER.convert_function to HRDB;I got the blow error.

ORA-00905: missing keyword

View 5 Replies View Related

PL/SQL :: Call VB Function From A Package Stored Procedure

Apr 30, 2013

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.

View 5 Replies View Related

How To Audit Execute On Specified Procedure Or Function In Package

Oct 4, 2012

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 ???

View 1 Replies View Related

PL/SQL :: Unescape Data Of Table Column Using Package Function

Feb 8, 2013

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].......   

View 17 Replies View Related

PL/SQL :: Table Out From A Function / Invalid Datatype Error

May 9, 2013

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

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Function Returning CLOB - Invalid Format

Jan 22, 2011

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.

View 2 Replies View Related

SQL & PL/SQL :: Function That Returns A Table Type Inside A Package Body

Jul 26, 2011

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?

View 10 Replies View Related

PL/SQL :: Find Out In Database Package Where Package Is Installed

May 28, 2013

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').

What I would need is something like $$PLSQL_UNIT

View 8 Replies View Related

SQL & PL/SQL :: Global Exception In Package Available Outside Package?

Jan 15, 2012

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:

e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);

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?

View 4 Replies View Related

Index Is In Unusable State

Sep 1, 2010

I can see the error in alert log like

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.

View 9 Replies View Related

SQL & PL/SQL :: Index Unused State

May 7, 2013

I made an Index Unused while doing some update by using sql developer right click 'Make unusealbe'. Now I need make it as useable.

marking as Usable. I have checked in all_indexes state is show as 'UNUSABLE'.

View 2 Replies View Related

PL/SQL :: Get CITY And STATE From Address

Jul 9, 2013

I have got a table where the site address is stored as follows - 

SOUTH PARK ROAD #822,,,,
HOLLYWOOD,FL,33021,,
USPO BOX 1111,,,,
PHILADELPHIA,PA,19178-3478,,
USNORTH AVE EAST,
P.O. BOX 1234,,,ELIZABETH,NJ,07207-6031,,
US12345 PARK SORRENTO SUITE 222,,,,
CALABASAS,CA,91302,,US 

I have got 2 fields to be populated from this - CITYSTATE How can I pick up the 5th and 6th field separated by the comma.

For example: -

Following output should be shown for the above recordsCITY  
 
STATEHOLLYWOOD       FLPHILADELPHIA        PAELIZABETH       NJCALABASAS       CA

View 3 Replies View Related

Session Inactive State?

Jul 1, 2012

want to know the following

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 ?

View 6 Replies View Related

SQL & PL/SQL :: RESTRICT_REFERENCE Changing A Database State

Mar 24, 2010

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).

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved