Hello,

I am new to oracle and its syntax for creating stored procedures.
I understand you have to declare an out variable if you want to return a collection of rows to the user. I have a procedure that has compiled succesfully, even though I am not entirely sure it works properly, as I am not able to call the procedure.
How do you do that? How do you pass in a variable that is supposed to hold all of the values that will be returned? I am very confused about this, it doesn´t seem as intuitive as creating procedures with SQL Server.
Here is my store proc:

PROCEDURE SP_SELECT_CHAT_DEV (
OPPID IN NUMBER,
Result_Cursor OUT Result_Set_Pkg.Result_Cursor
)
AS

BEGIN
     INSERT INTO TEMP_CHAT
    (SELECT a.rtcm_company,c.ownerorgid,a.rtcm_message,a.rtcm_ address FROM tn_rtc_chat_message@dblink_dadb_dev a
        LEFT JOIN userlist@dblink_dadb_dev b ON b.usernum=a.rtcm_recipient
        LEFT JOIN tbl_alternativeid@dblink_tpd_dev c ON c.altid=b.userid
            WHERE rtcm_auction=OPPID
                group by a.rtcm_time,a.rtcm_message,a.rtcm_company,c.ownero rgid,a.rtcm_address);


    UPDATE TEMP_CHAT
    SET ownerorgid='Todos'
        WHERE ownerorgid IS NULL;

    OPEN Result_Cursor FOR
    SELECT * FROM TEMP_CHAT;

    COMMIT;

END SP_SELECT_CHAT_DEV;

How can I call this store procedure from inside sqlnavigator?

Thanks,

Daniel Wajnberg

Hi Daniel,

Use ref cursors to return a two dimensional resultset in Oracle. The usage is pretty simple. You need to create a package with the ref cursor declared in it. Please have a look at my test case below. I am fetching all records from a table "t" and displaying it.

---------------------------------------------------------------------
SQL> select * from t;

         X V
---------- ----------
        10 a
        20 b
        30 c
        40 d
        50 e

SQL>
SQL> create or replace package my_pkg as
  2 -- declare a ref cursor which returns records of table t
  3 type cur_temp is ref cursor return t%rowtype;
  4 -- declare your procedure which has this ref cursor as OUT parameter
  5 procedure my_proc ( p_cur_temp out cur_temp ) ;
  6 end my_pkg;
  7 /

Package created.

SQL>
SQL> create or replace package body my_pkg as
  2 procedure my_proc ( p_cur_temp out cur_temp ) as
  3 begin
  4 open p_cur_temp for select x, v from t;
  5 exception
  6 when others then
  7 dbms_output.put_line('Error : '||sqlerrm);
  8 end;
  9 end my_pkg;
 10 /

Package body created.

SQL>
SQL>
SQL> declare
  2 -- declare a variable with rowtype as that of table t
  3 t_rec t%rowtype;
  4 -- declare a ref cursor with type same as the one in the package
  5 v_cur_temp my_pkg.cur_temp;
  6 i number := 1;
  7 begin
  8 -- call the procedure
  9 my_pkg.my_proc(v_cur_temp);
 10 -- now loop thru the ref cursor to display the values returned
 11 loop
 12 fetch v_cur_temp into t_rec;
 13 exit when v_cur_temp%notfound;
 14 dbms_output.put_line('Record '||i||' : x = '||t_rec.x||', v = '||t_rec.v);
 15 i := i + 1;
 16 end loop;
 17 exception
 18 when others then
 19 dbms_output.put_line('Error : '||sqlerrm);
 20 end;
 21 /
Record 1 : x = 10, v = a
Record 2 : x = 20, v = b
Record 3 : x = 30, v = c
Record 4 : x = 40, v = d
Record 5 : x = 50, v = e

PL/SQL procedure successfully completed.

SQL>
SQL> -- try the same from SQL*Plus
SQL> var my_cur refcursor
SQL> exec my_pkg.my_proc(:my_cur);

PL/SQL procedure successfully completed.

SQL> print my_cur

         X V
---------- ----------
        10 a
        20 b
        30 c
        40 d
        50 e

SQL>
---------------------------------------------------------------------

Hope that helps.

Cheers,
Prat