Home » SQL & PL/SQL » SQL & PL/SQL » failing in Creation of PL/SQL function (PL SQL ORACLE)
icon5.gif  failing in Creation of PL/SQL function [message #671092] Wed, 15 August 2018 08:34 Go to next message
bench
Messages: 4
Registered: August 2018
Junior Member
Hi there,

I will be thankful to have your suggestion here Smile

im trying to create the bellow function but its created with warnings and im not able to execute it - ORA-06575,
do you have any suggestion? Confused

--------------
Warning: compiled but with compilation errors
Commit complete.

ORA-06575: Package or function CHECK_EVENT_ID is in an invalid state
------------

SQL query:

CREATE OR REPLACE FUNCTION CHECK_EVENT_ID (EVENT_ID_IN NUMBER (18))
RETURN BOOLEAN
IS
COUNTER NUMBER ;
RETURN_VALUE BOOLEAN;
BEGIN
COUNTER := 0;
RETURN_VALUE := 1;
SELECT COUNT (*) INTO COUNTER FROM APE1_RATED_EVENT a where EVENT_ID_IN=a.EVENT_ID;
IF COUNTER=0 THEN RETURN_VALUE:= 0;
END IF;
RETURN RETURN_VALUE;
END CHECK_EVENT_ID;

COMMIT;
Re: failing in Creation of PL/SQL function [message #671093 is a reply to message #671092] Wed, 15 August 2018 08:36 Go to previous messageGo to next message
BlackSwan
Messages: 26629
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: failing in Creation of PL/SQL function [message #671094 is a reply to message #671092] Wed, 15 August 2018 08:38 Go to previous messageGo to next message
BlackSwan
Messages: 26629
Registered: January 2009
Location: SoCal
Senior Member
bench wrote on Wed, 15 August 2018 06:34
Hi there,

I will be thankful to have your suggestion here Smile

im trying to create the bellow function but its created with warnings and im not able to execute it - ORA-06575,
do you have any suggestion? Confused

--------------
Warning: compiled but with compilation errors
Commit complete.

ORA-06575: Package or function CHECK_EVENT_ID is in an invalid state
------------

SQL query:

CREATE OR REPLACE FUNCTION CHECK_EVENT_ID (EVENT_ID_IN NUMBER (18))
RETURN BOOLEAN
IS
COUNTER NUMBER ;
RETURN_VALUE BOOLEAN;
BEGIN
COUNTER := 0;
RETURN_VALUE := 1;
SELECT COUNT (*) INTO COUNTER FROM APE1_RATED_EVENT a where EVENT_ID_IN=a.EVENT_ID;
IF COUNTER=0 THEN RETURN_VALUE:= 0;
END IF;
RETURN RETURN_VALUE;
END CHECK_EVENT_ID;

COMMIT;
RETURN_VALUE is datatype BOOLEAN, so assigning a zero is not correct.
Is 0 TRUE or FALSE?
Re: failing in Creation of PL/SQL function [message #671095 is a reply to message #671092] Wed, 15 August 2018 08:42 Go to previous messageGo to next message
John Watson
Messages: 8060
Registered: January 2010
Location: Global Village
Senior Member
Your code is full of bugs, starting with the very first line:
orclx>
orclx> CREATE OR REPLACE FUNCTION CHECK_EVENT_ID (EVENT_ID_IN NUMBER (18))
  2  RETURN BOOLEAN
  3  IS
  4  COUNTER NUMBER ;
  5  RETURN_VALUE BOOLEAN;
  6  BEGIN
  7  COUNTER := 0;
  8  RETURN_VALUE := 1;
  9  SELECT COUNT (*) INTO COUNTER FROM APE1_RATED_EVENT a where EVENT_ID_IN=a.EVENT_ID;
 10  IF COUNTER=0 THEN RETURN_VALUE:= 0;
 11  END IF;
 12  RETURN RETURN_VALUE;
 13  END CHECK_EVENT_ID;
 14  /

Warning: Function created with compilation errors.

orclx> show errors
Errors for FUNCTION CHECK_EVENT_ID:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/45     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         := . ) , @ % default character
         The symbol ":=" was substituted for "(" to continue.

orclx>
You cannot constrain a numeric argument like that, just declare it as NUMBER.
Re: failing in Creation of PL/SQL function [message #671096 is a reply to message #671093] Wed, 15 August 2018 08:44 Go to previous messageGo to next message
Bill B
Messages: 1910
Registered: December 2004
Senior Member
Lets go over your code line by line

CREATE OR REPLACE FUNCTION CHECK_EVENT_ID (EVENT_ID_IN NUMBER (18))
RETURN BOOLEAN <--- You may not return a boolean value in an oracle function
IS
COUNTER NUMBER ;
RETURN_VALUE BOOLEAN;
BEGIN
COUNTER := 0;
RETURN_VALUE := 1; <--- Even if you could return boolean this needs to be set to TRUE or FALSE
SELECT COUNT (*) INTO COUNTER FROM APE1_RATED_EVENT a where EVENT_ID_IN=a.EVENT_ID;
IF COUNTER=0 THEN RETURN_VALUE:= 0; <-- use FALSE, not 0
END IF;
RETURN RETURN_VALUE;
END CHECK_EVENT_ID;

here is how to do it (at least one method)
CREATE OR REPLACE FUNCTION Check_event_id(Event_id_in NUMBER(18))
    RETURN VARCHAR2
IS
    Counter        NUMBER;
    Return_value   VARCHAR2(5);
BEGIN
    SELECT COUNT(*)
    INTO Counter
    FROM Ape1_rated_event A
    WHERE Event_id_in = A.Event_id;

    IF Counter = 0 THEN
        Return_value := 'FALSE';
    ELSE
        Return_value := 'TRUE';
    END IF;

    RETURN Return_value;
END Check_event_id;
Re: failing in Creation of PL/SQL function [message #671098 is a reply to message #671096] Wed, 15 August 2018 09:11 Go to previous messageGo to next message
bench
Messages: 4
Registered: August 2018
Junior Member
Hi again,

Thanks for all the comments and suggestions Smile,
i tried to create the bellow corrected function but its still failing as earlier


----------

Warning: compiled but with compilation errors
ORA-06575 - Package or function string is in an invalid state

----------

CREATE OR REPLACE FUNCTION Check_event_id(Event_id_in NUMBER)
RETURN VARCHAR2
IS
Counter NUMBER;
Return_value VARCHAR2(5);
BEGIN
SELECT COUNT(*)
INTO Counter
FROM Ape1_rated_event A
WHERE Event_id_in = A.Event_id;

IF Counter = 0 THEN
Return_value := FALSE;
ELSE
Return_value := TRUE;
END IF;

RETURN Return_value;
END Check_event_id;
Re: failing in Creation of PL/SQL function [message #671099 is a reply to message #671098] Wed, 15 August 2018 09:13 Go to previous messageGo to next message
pablolee
Messages: 2881
Registered: May 2007
Location: Scotland
Senior Member
You are telling the function to return a string, but then returning a Boolean value.
Re: failing in Creation of PL/SQL function [message #671100 is a reply to message #671098] Wed, 15 August 2018 09:19 Go to previous messageGo to next message
BlackSwan
Messages: 26629
Registered: January 2009
Location: SoCal
Senior Member
bench wrote on Wed, 15 August 2018 07:11
Hi again,

Thanks for all the comments and suggestions Smile,
i tried to create the bellow corrected function but its still failing as earlier


----------

Warning: compiled but with compilation errors
ORA-06575 - Package or function string is in an invalid state

----------

CREATE OR REPLACE FUNCTION Check_event_id(Event_id_in NUMBER)
RETURN VARCHAR2
IS
Counter NUMBER;
Return_value VARCHAR2(5);
BEGIN
SELECT COUNT(*)
INTO Counter
FROM Ape1_rated_event A
WHERE Event_id_in = A.Event_id;

IF Counter = 0 THEN
Return_value := FALSE;
ELSE
Return_value := TRUE;
END IF;

RETURN Return_value;
END Check_event_id;
>Return_value VARCHAR2(5);
string assignment require use of single quote marks.
Below is invalid syntax.
>Return_value := TRUE;
Re: failing in Creation of PL/SQL function [message #671101 is a reply to message #671100] Wed, 15 August 2018 09:27 Go to previous messageGo to next message
bench
Messages: 4
Registered: August 2018
Junior Member
Hi BlackSwan,

it still failing same same with the bellow

IF Counter = 0 THEN
Return_value := 'FALSE';
ELSE
Return_value := 'TRUE';
END IF;

Re: failing in Creation of PL/SQL function [message #671102 is a reply to message #671101] Wed, 15 August 2018 09:31 Go to previous messageGo to next message
BlackSwan
Messages: 26629
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

You need to provide us with CREATE TABLE statement & INSERT statements so we can have working test case to reproduce what you report.
You need to use COPY & PASTE to show us whole session so we can completely see what you do & how Oracle responds.
Re: failing in Creation of PL/SQL function [message #671103 is a reply to message #671101] Wed, 15 August 2018 09:37 Go to previous messageGo to next message
pablolee
Messages: 2881
Registered: May 2007
Location: Scotland
Senior Member
Assuming that your table exists and is visible to the procedure and the column in that table matches the column name that you coded and the only change you made was to put apostrophes around the true and false values, the code should work.
Which makes me believe that the table is not 'visible' to your function.
Does that table (Ape1_rated_event) exist in the same schema as the function?
Re: failing in Creation of PL/SQL function [message #671114 is a reply to message #671101] Wed, 15 August 2018 12:47 Go to previous messageGo to next message
joy_division
Messages: 4947
Registered: February 2005
Location: East Coast USA
Senior Member
bench wrote on Wed, 15 August 2018 10:27
Hi BlackSwan,

it still failing same same with the bellow

IF Counter = 0 THEN
Return_value := 'FALSE';
ELSE
Return_value := 'TRUE';
END IF;

Do you see how John posted his actual session so you can see what the error is and where it is? "Failing" is an unhelpful error message and thankfully Oracle does not report an error message like that.
Re: failing in Creation of PL/SQL function [message #671120 is a reply to message #671103] Wed, 15 August 2018 17:17 Go to previous messageGo to next message
bench
Messages: 4
Registered: August 2018
Junior Member
thank you, issue solved
function and table were belong to different schemes
Re: failing in Creation of PL/SQL function [message #671131 is a reply to message #671096] Thu, 16 August 2018 03:27 Go to previous messageGo to next message
cookiemonster
Messages: 13726
Registered: September 2008
Location: Rainy Manchester
Senior Member
Bill B wrote on Wed, 15 August 2018 14:44
Lets go over your code line by line

CREATE OR REPLACE FUNCTION CHECK_EVENT_ID (EVENT_ID_IN NUMBER (18))
RETURN BOOLEAN <--- You may not return a boolean value in an oracle function
[/code]
Yes you can. Functions can return any PL/SQL datatype.
However if you return a PL/SQL datatype, as opposed to a SQL datatype then you can't use that function in SQL statements.
You can still use it in PL/SQL.
Re: failing in Creation of PL/SQL function [message #671142 is a reply to message #671131] Thu, 16 August 2018 07:44 Go to previous message
Bill B
Messages: 1910
Registered: December 2004
Senior Member
Your correct. I was assuming that the function would be used in sql code as opposed to pl/sql. Thanks you for fixing my mistake.
Previous Topic: Last 7 days report
Next Topic: Put data from one field to another.
Goto Forum:
  


Current Time: Fri Nov 22 20:03:33 CST 2019