Home » SQL & PL/SQL » SQL & PL/SQL » 2 CURSORS - access 1 cursor for processing???
2 CURSORS - access 1 cursor for processing??? [message #243258] Wed, 06 June 2007 11:13 Go to next message
sarans
Messages: 30
Registered: November 2006
Location: UK
Member
I have 2 cursors with different where clause, and depends on any one given scenario, I need to open up one of the cursors and process it, the processing are same for both of the cursors, but opening and using the cursor depends on the business requirement, now all I want to know is how ot dynamically open up the cursor and process it.

If the above is not clear, please ask me and I will explain.
Re: 2 CURSORS - access 1 cursor for processing??? [message #243305 is a reply to message #243258] Wed, 06 June 2007 16:47 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
one example using ref cursor

CREATE TABLE MY_TAB ( 
  MY_TAB_PK             NUMBER(10)    NOT NULL, 
  DESCRIPTION           VARCHAR2(64)  NOT NULL, 
  CREATE_DATE           DATE          NOT NULL);

-- Some rows !

INSERT INTO MY_TAB
            (my_tab_pk, description, create_date)
     VALUES (991, 'Description 1', SYSDATE);

INSERT INTO MY_TAB
            (my_tab_pk, description, create_date)
     VALUES (992, 'Description 2', SYSDATE - 1);

INSERT INTO MY_TAB
            (my_tab_pk, description, create_date)
     VALUES (993, 'Description 3', SYSDATE - 2);

INSERT INTO MY_TAB
            (my_tab_pk, description, create_date)
     VALUES (994, 'Description 4', SYSDATE - 3);

COMMIT;

CREATE OR REPLACE PACKAGE my_pkg
AS
  TYPE t_test_rec IS RECORD(
   my_tab_pk                     MY_TAB.my_tab_pk%TYPE,
     description                   MY_TAB.description%TYPE,
     create_date                   MY_TAB.create_date%TYPE
  );
   --c1rec       MY_TAB%rowTYPE;

   TYPE t_test_cur IS REF CURSOR
      RETURN t_test_rec;

   FUNCTION my_query (v_test_cv IN OUT t_test_cur)
      RETURN NUMBER;
END my_pkg;
/

CREATE OR REPLACE PACKAGE BODY my_pkg
AS
/*Select all Records from the Table*/

   FUNCTION my_query (v_test_cv IN OUT t_test_cur)
      RETURN NUMBER
   AS
   BEGIN   -- just a silly if condition to demonstrate!
      IF TO_NUMBER (TO_CHAR (SYSDATE, 'ss')) < 30
      THEN   -- Return query 1 !
      DBMS_OUTPUT.put_line ('------- ss < 30, query 1 results ---------');
         OPEN v_test_cv FOR
            SELECT my_tab_pk, description, create_date
              FROM MY_TAB
			  WHERE 10 < 100   -- whatever
             ORDER BY 1 ASC;   -- Sort Ascending!
      ELSE   -- Return query 2 !
      DBMS_OUTPUT.put_line ('------- ss >= 30, query 2 results --------');
         OPEN v_test_cv FOR
            SELECT my_tab_pk, description, create_date
              FROM MY_TAB
			  WHERE 5 > 3       -- whatever
             ORDER BY 1 DESC;   -- Sort Descending!
      END IF;

      RETURN 0;
   END my_query;
END my_pkg;
/


set serveroutput on size 10000 format wrapped 
DECLARE
   retval      NUMBER;

--   TYPE t_test_rec IS RECORD(   -- New Record type defined!
--      my_tab_pk                     MY_TAB.my_tab_pk%TYPE,
--      description                   MY_TAB.description%TYPE,
--      create_date                   MY_TAB.create_date%TYPE
--   );

--   c1rec       t_test_rec;   -- Cursor of New Record type!

   c1rec       MY_TAB%ROWTYPE;   -- Cursor of New Record type!
   v_test_cv   my_pkg.t_test_cur;   -- Cursor Variable passed out of Function!
-- Defined as per ref cursor in Function!
BEGIN
   retval := my_pkg.my_query (v_test_cv);

   LOOP
      FETCH v_test_cv INTO c1rec;
      EXIT WHEN v_test_cv%NOTFOUND;
      DBMS_OUTPUT.put_line (
         c1rec.my_tab_pk ||
         ', ' ||
         c1rec.description ||
         ', ' ||
         c1rec.create_date
      );
   END LOOP;
END;
/
Re: 2 CURSORS - access 1 cursor for processing??? [message #243306 is a reply to message #243305] Wed, 06 June 2007 16:50 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Also see
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279
Re: 2 CURSORS - access 1 cursor for processing??? [message #243435 is a reply to message #243258] Thu, 07 June 2007 05:22 Go to previous messageGo to next message
sarans
Messages: 30
Registered: November 2006
Location: UK
Member
Andrew again - thank you...

Already I have created 2 ref cursors (within functions) and based on the if condition use one of the cursors returned and process....

once again thank you for your valuable suggestions....
Re: 2 CURSORS - access 1 cursor for processing??? [message #251082 is a reply to message #243435] Thu, 12 July 2007 06:38 Go to previous message
darshita
Messages: 5
Registered: July 2007
Location: PA, USA
Junior Member
Thank you so much for your help Andrew.
Previous Topic: droping more than one table using pl/sql
Next Topic: a simple query
Goto Forum:
  


Current Time: Fri Dec 09 15:22:24 CST 2016

Total time taken to generate the page: 0.49959 seconds