Home » SQL & PL/SQL » SQL & PL/SQL » Ref Cursor - How to append records into ref cursor?
Ref Cursor - How to append records into ref cursor? [message #211388] Thu, 28 December 2006 06:19 Go to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Hi,

Is it possible to append ref cursor?

Iam having a procedure which accepts 1 string as input
parameter. That string will have list of ID delimited by comma.

I want to extract & match every ID with some tables.

My problem is for first ID i would get 10 records
and for 2nd ID i 'l get other 20 records. But while returning
i need to send the same(10 + 20 records) as ref cursor(OUT parameter).
But in below given code i could send only last 20 records. first
10 records are not append/updated into ref cursor.

How to append 2nd 20 records with 1st 10 records? so that i can
send all the 30 records.

Here goes my code...

*******************************************************************************

CREATE OR REPLACE PROCEDURE g...
(
in_groupId IN VARCHAR2,
out_g... OUT TYPES.DATASET
)
IS
v_temp VARCHAR2(500) := in_groupId ||',';
v_temp_split VARCHAR2(500);
v_pos1 NUMBER := 0;
v_pos2 NUMBER := 1;
v_pos3 NUMBER := 0;
v_extract_char VARCHAR(1) := NULL;
v_comma_cnt NUMBER := 0;

BEGIN
-- check in for null input parameters
IF ( in_groupId IS NOT NULL ) THEN
-- loop to count no of in_groupId
FOR j IN 1..LENGTH(v_temp)
LOOP
v_extract_char := SUBSTR(v_temp,j,1);
IF (v_extract_char = ',') THEN
v_comma_cnt := v_comma_cnt + 1;
END IF;
END LOOP;
-- loop to extract in_group Id
FOR i IN 1..v_comma_cnt
LOOP
v_pos1 := instr(v_temp,',',(v_pos1 + 1));
v_pos3 := ((v_pos1-1) - v_pos2 )+ 1;
v_temp_split := SUBSTR(v_temp,v_pos2,v_pos3);
v_pos2 := v_pos1 + 1;
-- query to return dataset
OPEN out_g... FOR
SELECT
DISTINCT ...
FROM
g... gcs
WHERE ...
AND ( gcs.l... = v_temp_split );
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('INTERNAL ERROR');
END g...;

*******************************************************************************

in this v_temp_split will have extracted id & iam opening
ref cursor for each & every ID extracted from list.

2) How to handle no_data_found exception for this ref cursor?

Please help me....

-thiyagarajan.

[Updated on: Sun, 03 June 2007 09:10] by Moderator

Report message to a moderator

Re: Ref Cursor - How to append records into ref cursor? [message #211433 is a reply to message #211388] Thu, 28 December 2006 11:45 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Try a google on "cursor expressions"

icon10.gif  Re: Ref Cursor - How to append records into ref cursor? [message #211737 is a reply to message #211433] Tue, 02 January 2007 00:44 Go to previous messageGo to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

i tried with cursor expression too...still im having problem yaar...
Re: Ref Cursor - How to append records into ref cursor? [message #212790 is a reply to message #211388] Mon, 08 January 2007 06:45 Go to previous messageGo to next message
Giraffe
Messages: 10
Registered: January 2007
Location: Suffolk, UK
Junior Member
Whilst you could rewrite your SELECT to populate a collection and then open your cursor after the loop by SELECTing from the collection, it would make sense to instead have one SELECT which can take multiple ids going in. Collections is again the key, however this time the collection would consist solely of the id, and wouldn't involve multiple SELECT statements.

First off, let's create two types: a record to represent the id and a table (collection) of those records.
CREATE OR REPLACE TYPE recstring IS OBJECT ( val VARCHAR2 ( 500 ) );
CREATE OR REPLACE TYPE tabstring IS TABLE OF recstring;


These have to be declared as database types to enable you to use them in SQL queries - just declaring them in a PL/SQL package won't work here.

Next, I suggest you refactor your string splitting code out of the procedure into its own function. Instead of processing the SELECT in the loop, we're going to just populate the collection.
CREATE OR REPLACE FUNCTION commasplit ( in_groupId IN VARCHAR2 )
RETURN tabstring
IS
  v_result tabstring;
  v_temp VARCHAR2(500) := in_groupId || ',';
  v_temp_split VARCHAR2(500);
  v_pos1 NUMBER := 0;
  v_pos2 NUMBER := 1;
  v_pos3 NUMBER := 0;
  v_extract_char VARCHAR(1) := NULL;
  v_comma_cnt NUMBER := 0;
BEGIN
  v_result := tabstring();

  IF in_groupId IS NOT NULL THEN
    -- loop to count no of in_groupId
    FOR j IN 1 .. LENGTH ( v_temp )
    LOOP
      v_extract_char := SUBSTR ( v_temp, j, 1 );
      IF v_extract_char = ',' THEN
        v_comma_cnt := v_comma_cnt + 1;
      END IF;
    END LOOP;

    FOR i IN 1 .. v_comma_cnt
    LOOP
      v_pos1 := INSTR ( v_temp, ',', ( v_pos1 + 1 ) );
      v_pos3 := ( ( v_pos1 - 1 ) - v_pos2 ) + 1;
      v_temp_split := SUBSTR ( v_temp, v_pos2, v_pos3 );
      v_pos2 := v_pos1 + 1;

      -- Instead of processing here, we add it to a collection
      v_result.EXTEND;
      v_result ( v_result.LAST ) := recstring ( v_temp_split );
    END LOOP;
  END IF;

  RETURN v_result;
END commasplit;


And a quick test, to make sure I didn't break anything in the refactoring:

SQL> SET SERVEROUTPUT ON
SQL> 
SQL> DECLARE
  2    v_result tabstring;
  3  BEGIN
  4    v_result := commasplit ( in_groupId => 'abc,def,ghi' );
  5  
  6    FOR i IN 1 .. v_result.LAST
  7    LOOP
  8      DBMS_OUTPUT.put_line ( v_result(i).val );
  9    END LOOP;
 10  END;
 11  /

abc
def
ghi

PL/SQL procedure successfully completed


Now, instead of looping through this collection of Ids, we can simply join to the collection as though it were a table, so you can change your procedure to look more like this:

CREATE OR REPLACE PROCEDURE g... ( in_groupId IN VARCHAR2
                                                       , out_g OUT SYS_REFCURSOR
                                                       )
IS
  v_ids tabstring;  
BEGIN
  -- Convert input string to collection of string ids
  v_ids := commasplit ( in_groupId );
  
  -- query to return dataset
  OPEN out_g FOR
    SELECT DISTINCT
           ...
    FROM   g... gcs
    JOIN   TABLE ( v_ids ) ids
    ON     gcs.l... = ids.val;

/* I didn't populate these fields, so I took them out of the query */
...
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('INTERNAL ERROR');
END g...;


NO_DATA_FOUND does not need to be handled here, as you are simply returning a cursor. If no data is found, it returns a cursor with no rows.

[Updated on: Sun, 03 June 2007 09:11] by Moderator

Report message to a moderator

Re: Ref Cursor - How to append records into ref cursor? [message #212947 is a reply to message #212790] Mon, 08 January 2007 22:10 Go to previous message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Hi Giraffe,

Thanx a lot Giraffe...gr8 work done.....

- thiyagarajan palanisamy
Previous Topic: help with TRIM
Next Topic: New to Oracle (merged)
Goto Forum:
  


Current Time: Wed Dec 07 08:42:27 CST 2016

Total time taken to generate the page: 0.40721 seconds