|
|
|
Re: failing in Creation of PL/SQL function [message #671095 is a reply to message #671092] |
Wed, 15 August 2018 08:42 |
John Watson
Messages: 8951 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 |
Bill B
Messages: 1971 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 #671103 is a reply to message #671101] |
Wed, 15 August 2018 09:37 |
pablolee
Messages: 2882 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 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
bench wrote on Wed, 15 August 2018 10:27Hi 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 #671131 is a reply to message #671096] |
Thu, 16 August 2018 03:27 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Bill B wrote on Wed, 15 August 2018 14:44Lets 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.
|
|
|
|