|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
February 14th, 2005, 10:45 AM
|
|||
|
|||
Helllp - Functions
Need Help, the following procedure/function is supposed to execute a query and return a resultset object to the calling application, however, when i try to create it in oracle i get the errors shown below!
CREATE OR REPLACE FUNCTION fu_checkAlert ( PID_in IN INTEGER ) RETURN "rs" AS LANGUAGE JAVA NAME 'ResultSet'; BEGIN SELECT * FROM Alert WHERE PID = PID_in ORDER BY alertdate; END; / show err; Warning: Function created with compilation errors. Errors for FUNCTION FU_CHECKALERT: LINE/COL ERROR -------- ----------------------------------------------------------------- 6/1 PLS-00103: Encountered the symbol "SELECT" PLEASE HELLLP ME!!!!
|
|
#2
February 14th, 2005, 10:51 AM
|
|||
|
|||
|
I know nothing about java interfaces but the comma after resultset is wrong
Code:
CREATE OR REPLACE FUNCTION fu_checkAlert ( PID_in IN INTEGER ) RETURN "rs" AS LANGUAGE JAVA NAME 'ResultSet' IS BEGIN SELECT * FROM Alert WHERE PID = PID_in ORDER BY alertdate; return rs; END; / rs should be created in the function. Don't you have another function similar this to copy? One that returns a java result set?
|
|
#3
February 14th, 2005, 02:30 PM
|
|||
|
|||
|
Quote:
Unfortunately i dont have any other functions to copy, i'm a complete newbie to oracle and i'm having difficulty getting to grips with it! I can execute the query directly from my java code and get the resultset that way but it's not how i want to do it, because i have 2 or 3 apps calling it, was trying to do a stored proc too, but that wouldn't work either!
|
|
#4
February 20th, 2005, 10:59 PM
|
|||
|
|||
|
It looks like you are trying to mix 2 different things... Calling PL/SQL stored procedures from Java, and calling Java from Oracle. I believe you want the first... In that case, your procedure would just be...
Code:
CREATE OR REPLACE FUNCTION fu_checkAlert
(
PID_in IN INTEGER
)
RETURN sys_refcursor
IS
v_rs sys_refcursor;
BEGIN
open v_rs for '
SELECT * FROM Alert WHERE PID = '||PID_in||' ORDER BY alertdate';
return v_rs;
END;
The "as Language Java Name '......'" syntax is for creating procedures/functions that allow you to call Java methods that have been loaded into Oracle. Hope this helps. -wb-
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > Helllp - Functions |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
