Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Urgent need help with PL/SQL function

 
bobby, morkos
Ranch Hand
Posts: 82
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Could anyone tell me, what's wrong with my function.

[error]
Warning: Function created with compilation errors.
[/error]
 
Beksy Kurian
Ranch Hand
Posts: 254
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It is missing a ; after the return statement.
Regards
Beksy
 
bobby, morkos
Ranch Hand
Posts: 82
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 254
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 82
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 254
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 82
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 254
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 82
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot. It works fine now.
 
Beksy Kurian
Ranch Hand
Posts: 254
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
glad to help! I have edited the java code a little. I hope that works too!
Regards
Beksy
 
bobby, morkos
Ranch Hand
Posts: 82
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic