Urgent need help with PL/SQL function

[bobby, morkos]

Could anyone tell me, what's wrong with my function.

SQL> CREATE OR REPLACE Function SHOW_CUSTOMERS(v_id IN NUMBER)
  2   RETURN types.ref_cursor
  3    AS
  4   user_cursor  types.ref_cursor ;
  5      BEGIN                                          
  6        OPEN  user_cursor FOR
  7      SELECT CUST_NAME, CUST_PHONE  
  8      FROM customers
  9      WHERE CUST_ID=v_id; 
 10    RETURN user_cursor 
 11  END;
 12  /

[error]
Warning: Function created with compilation errors.
[/error]

[Beksy Kurian]

It is missing a ; after the return statement.
Regards
Beksy

[bobby, morkos]

I get this error, when I try to run it in SQL+

SQL>CREATE OR REPLACE Function SHOW_CUSTOMERS(v_id IN NUMBER)  
  2     RETURN types.ref_cursor;  
  3      AS  
  4     user_cursor  types.ref_cursor;  
  5        BEGIN                                            
  6          OPEN  user_cursor FOR  
  7        SELECT CUST_NAME, CUST_PHONE    
  8        FROM customers  
  9        WHERE CUST_ID=v_id;  
 10        RETURN user_cursor;  
 11  END; 
 12  /

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]

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]

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]

Here you go the package with some corrections and the java code...I haven't tested the java code...

 create or replace package customer_info IS
 type ref_cursor is ref cursor;
 function show_customer(v_id IN NUMBER) return ref_cursor
end;
/
CREATE OR REPLACE package body customer_info Is
 Function SHOW_CUSTOMER(v_id IN NUMBER) RETURN ref_cursor  IS 
 user_cursor ref_cursor;
 BEGIN
  OPEN user_cursor FOR
  SELECT CUST_NAME, CUST_PHONE
  FROM customers
  WHERE CUST_ID=v_id;
 RETURN user_cursor;
END;
/
and in JAVA-----
Connection conn;
CallableStatement cstmt=conn.prepareCall("{? = call customer_info.show_customer("+v_id+") }");
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.execute();
ResultSet rs=(ResultSet)cstmt.getObject(1);
while(rs.next())
{
System.out.println(rs.getString(1)+" "+rs.getString(2));
}
cstmt.close();
 

Regards
Beksy
[ April 11, 2002: Message edited by: Beksy Kurian ]

[bobby, morkos]

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]

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

create or replace package customer_info IS 
 type ref_cursor is ref cursor; 
 function show_customer(v_id IN NUMBER)     return  ref_cursor;
end;
/
CREATE OR REPLACE package body customer_info Is   Function SHOW_CUSTOMER(v_id IN NUMBER) RETURN ref_cursor  IS  user_cursor ref_cursor; 
 BEGIN  OPEN user_cursor 
   FOR  SELECT CUST_NAME, CUST_PHONE  
   FROM customers  
   WHERE CUST_ID=v_id; 
  RETURN user_cursor;
 END;
END;
/
  

[ April 11, 2002: Message edited by: Beksy Kurian ]
[ April 11, 2002: Message edited by: Beksy Kurian ]

[bobby, morkos]

Thanks a lot. It works fine now.

[Beksy Kurian]

glad to help! I have edited the java code a little. I hope that works too!
Regards
Beksy

[bobby, morkos]

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