• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Urgent need help with PL/SQL function

 
Ranch Hand
Posts: 82
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Could anyone tell me, what's wrong with my function.

[error]
Warning: Function created with compilation errors.
[/error]
 
Ranch Hand
Posts: 254
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks a lot. It works fine now.
 
Beksy Kurian
Ranch Hand
Posts: 254
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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

 
Looky! I'm being abducted by space aliens! Me and this tiny ad!
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic