Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > Oracle
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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

  #2 (permalink)  
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
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 01:31 AM.


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