Cursor output to sys_refcursor

[TheJon]

Hi,

I'm used to MS T-SQL, and the concept of temporary tables and table variables.

I have an oracle database serving data for MS reporting services, and one procedure I am trying to run does the following:
[ul]
[li]Opens a cursor populated with a list of work requests, and associated data.[/li]
[li]Loops through the cursor passing some of the fields to a procedure on a linked server which returns 3 parameters.[/li]
[li]Currently uses dbms_output.put_line to display the results.[/li]
[/ul]
My procedure works perfectly, but what I want it to do is to populate a sys_refcursor so that the results can be picked up by Reporting Services.

If this was t-sql, I would create a temporary table or a table variable, and insert the results of each loop of the cursor into it. I would then select the results from the temporary table when the cursor is closed.

How can I do this in Oracle? I've looked at collections (1 dimensional) and temporary tables (not temporary) and don't know where to go. Help!

[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]

 

[engineer2100]

Can't just open the cursor

Open cur1 for SELECT....; (cur1 OUT SYS_REFCURSOR)

One question I have - Will MS Reporting Services recognize the SYS_REFCURSOR type? I am not a T-SQL guy!!!

Engi

 

[TheJon]

I have no problem interfacing to MS reporting Services, however it needs a sys_refcursor type as the output parameter to the procedure.

What is confusing me, is that I need to somehow store the results from each line of the cursor loop, then output the lot as a sys_refcursor at the end of the procedure.

Engi, unless I am missing a trick and can call the procedure from the linked server as part of my SQL select, then your suggestion isn't enough.

Here's the code as it works at the moment:

SET serveroutput on
DECLARE
   l_site        VARCHAR2(40);
   l_direction   VARCHAR2(40);
   l_distance    NUMBER(12, 4);
   l_wr_number   VARCHAR2(20);
   l_eastings    NUMBER(12);
   l_northings   NUMBER(12);
-- populate cursor with requested work request types
   CURSOR c1
   IS
      SELECT DISTINCT mr_workrequestnumber_ref, mr_eastings, mr_northings
                 FROM a_work_request
                WHERE mr_classification = 'WK' AND mr_requesttype = 'SM';
BEGIN
   OPEN c1;
   LOOP
      FETCH c1
       INTO l_wr_number, l_eastings, l_northings;
      EXIT WHEN c1%NOTFOUND;
      if l_eastings is not null then
-- call procedure on linked database
      proddbs.search_stw@ocxd_gisd(l_eastings
                                 , l_northings
                                 , l_site
                                 , l_direction
                                 , l_distance
                                  );
      else
        l_site := 'N/A';
        l_direction := 'N/A';
        l_distance := 0;
      end if;
-- for now, output to the console
      DBMS_OUTPUT.put_line(   l_wr_number
                           || ','
                           || l_site
                           || ','
                           || l_direction
                           || ','
                           || l_distance
                          );
   END LOOP;
   CLOSE c1;
END;
SET serveroutput off

[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]

 

[engineer2100]

Is the logic below necessary? if yes, then I am afraid you need to pass individual variables to the procedure (proddbs.search_stw@ocxd_gisd).

If not see below for the modified procedure

DECLARE
   l_site        VARCHAR2(40);
   l_direction   VARCHAR2(40);
   l_distance    NUMBER(12, 4);
   l_wr_number   VARCHAR2(20);
   l_eastings    NUMBER(12);
   l_northings   NUMBER(12);
   
   
   c1		 SYS_REFCURSOR;
-- populate cursor with requested work request types
BEGIN
   OPEN c1 FOR 
    SELECT DISTINCT mr_workrequestnumber_ref, mr_eastings, mr_northings
      FROM a_work_request
     WHERE mr_classification = 'WK' AND 
           mr_requesttype = 'SM';
   
   -- call procedure on linked database
   proddbs.search_stw@ocxd_gisd(c1);
   
   CLOSE c1;
END;

Let me know if this works.

Engi

 

[TheJon]

Engi,

Thanks for your continued help. The procedure on the linked server requires individual parameters to be passed to it. This is why I am using a cursor to call the procedure line by line from the recordset. What I want to be able to do is replace the "DBMS_OUTPUT.put_line" section with an "insert into" section, preferably using a table variable (if such a thing exists) or a temporary table.

Maybe I just need to stop thinking T-SQL, and create a permanent output table which can be truncated at the start of the procedure, inserted into during the cursor loop, then read into a sys_refcursor at the end of the procedure. I could even create the table at the start and drop it at the end. I'm just surprised that something so simple in T-SQL is not an available concept in PL/SQL.


[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]

 

[engineer2100]

Jon,

You can create Temporary tables within Oracle and also tie their existence to either Transaaction or session.

CREATE TEMPORARY TABLE...

Just google this and you'll not be disappointed....