File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes Urgent need help with PL/SQL function Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Urgent need help with PL/SQL function" Watch "Urgent need help with PL/SQL function" New topic
Author

Urgent need help with PL/SQL function

bobby, morkos
Ranch Hand

Joined: Jan 04, 2002
Posts: 82
Could anyone tell me, what's wrong with my function.

[error]
Warning: Function created with compilation errors.
[/error]
Beksy Kurian
Ranch Hand

Joined: Jul 11, 2001
Posts: 254
It is missing a ; after the return statement.
Regards
Beksy
bobby, morkos
Ranch Hand

Joined: Jan 04, 2002
Posts: 82
I get this error, when I try to run it in SQL+


Warning: Function created with compilation errors.

Originally posted by Beksy Kurian:
It is missing a ; after the return statement.
Regards
Beksy

[ April 11, 2002: Message edited by: bobby, morkos ]
[ April 11, 2002: Message edited by: bobby, morkos ]
Beksy Kurian
Ranch Hand

Joined: Jul 11, 2001
Posts: 254
oops! I should've ben more clear. You don't need a ; after the first return statement. You needed a ; in the return statement just before the end statement.
anyway,ref cusrors and pl/sql tables should be declared in package before passing as parameters.
Try this:
create or replace package types IS
type ref_cursor is ref cursor;
end;
/
and then----
CREATE OR REPLACE Function SHOW_CUSTOMER(v_id IN NUMBER)
RETURN types.ref_cursor
IS user_cursor types.ref_cursor;
BEGIN
OPEN user_cursor FOR
SELECT CUST_NAME, CUST_PHONE
FROM customers
WHERE CUST_ID=v_id;
RETURN user_cursor;
END;
/
And also, after compiling functions or procedures, if you are getting an error, type show errors. You will get a more detailed error.
Regards
Beksy
[ April 11, 2002: Message edited by: Beksy Kurian ]
bobby, morkos
Ranch Hand

Joined: Jan 04, 2002
Posts: 82
Thanks a lot. It works, now how do I run it in sql+ and call it from a java program.

Originally posted by Beksy Kurian:
oops! I should've ben more clear. You don't need a ; after the first return statement. You needed a ; in the return statement just before the end statement.
anyway,ref cusrors and pl/sql tables should be declared in package before passing as parameters.
Try this:
create or replace package types IS
type ref_cursor is ref cursor;
end;
/
and then----
CREATE OR REPLACE Function SHOW_CUSTOMER(v_id IN NUMBER)
RETURN types.ref_cursor
IS user_cursor types.ref_cursor;
BEGIN
OPEN user_cursor FOR
SELECT CUST_NAME, CUST_PHONE
FROM customers
WHERE CUST_ID=v_id;
RETURN user_cursor;
END;
/
And also, after compiling functions or procedures, if you are getting an error, type show errors. You will get a more detailed error.
Regards
Beksy
[ April 11, 2002: Message edited by: Beksy Kurian ]
Beksy Kurian
Ranch Hand

Joined: Jul 11, 2001
Posts: 254
Here you go the package with some corrections and the java code...I haven't tested the java code...


Regards
Beksy
[ April 11, 2002: Message edited by: Beksy Kurian ]
bobby, morkos
Ranch Hand

Joined: Jan 04, 2002
Posts: 82
SQL> create or replace package customer_info IS
2 type ref_cursor is ref cursor;
3 function show_customer(v_id IN NUMBER) return ref_cursor
4 end;
5 /
Warning: Package created with compilation errors.
SQL> CREATE OR REPLACE package body customer_info Is
2 Function SHOW_CUSTOMER(v_id IN NUMBER) RETURN ref_cursor IS
3 user_cursor ref_cursor;
4 BEGIN
5 OPEN user_cursor FOR
6 SELECT CUST_NAME, CUST_PHONE
7 FROM customers
8 WHERE CUST_ID=v_id;
9 RETURN user_cursor;
10 END;
11 /
Warning: Package Body created with compilation errors.
Beksy Kurian
Ranch Hand

Joined: Jul 11, 2001
Posts: 254
hum!! two small mistakes!!
package header needs a ; after the last return statement.
package body needs one more end statement.
Try it. It worked for me.
Beksy

[ April 11, 2002: Message edited by: Beksy Kurian ]
[ April 11, 2002: Message edited by: Beksy Kurian ]
bobby, morkos
Ranch Hand

Joined: Jan 04, 2002
Posts: 82
Thanks a lot. It works fine now.
Beksy Kurian
Ranch Hand

Joined: Jul 11, 2001
Posts: 254
glad to help! I have edited the java code a little. I hope that works too!
Regards
Beksy
bobby, morkos
Ranch Hand

Joined: Jan 04, 2002
Posts: 82
Could I return with the same procedure to return many results like select CUST_NAME, CUST_PHONE FROM customers.
Originally posted by Beksy Kurian:
glad to help! I have edited the java code a little. I hope that works too!
Regards
Beksy
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Urgent need help with PL/SQL function