Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
| Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 6th, 2003, 01:20 PM
Authorized User
 
Join Date: Aug 2003
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default CALLING ORACLE SELECT PROCEDURE

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

 
Old November 20th, 2003, 09:36 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Calling Oracle Stored Procedure using Linked Serve Twinklestar SQL Server 2000 4 September 23rd, 2009 04:02 AM
calling stored procedure jomet JSP Basics 0 November 23rd, 2007 08:06 AM
calling an oracle function from VB6 kalyansarkar108 VB Databases Basics 14 April 3rd, 2007 12:02 PM
calling an oracle procedure from a jsp page jasonteaboy Pro JSP 0 November 20th, 2006 03:06 PM
Calling an Oracle Stored Procedure booksnore2 BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 2 October 1st, 2004 09:35 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.