Result set to REF CURSOR
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Result set to REF CURSOR

  1. #1
    Join Date
    Jun 2005
    Posts
    11

    Result set to REF CURSOR

    Hello All,

    I have a stored procedure in which I construct a SQL statement on fly and execute it (dynamic SQL). Now, I want to take the result set returned by this dynamic SQL (Column list is always the same) into an output variable of the stored procedure.

    Can someone tell me give a sample piece of code to achieve this? Your help is greatly appreciated.

    Thanks,
    Desi Rookie

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jun 2005
    Posts
    11
    Here is my problem in detail. Any help will be very much appreciated.

    Thanks,
    Desi Rookie

    CREATE OR REPLACE PROCEDURE Test_SP
    (
    pi_Emp_Name IN VARCHAR2
    , po_Emp_Details OUT {How do I declare an output paramter to hold multiple rows & columns???} )
    )

    AS
    v_SQL_String VARCHAR2(2000);
    v_Dummy integer;

    BEGIN
    v_SQL_String := 'SELECT Employee_ID, Employee_Name, Emp_Address1, Emp_Address2, Emp_City, Emp_State, Emp_Zip FROM Employee WHERE UPPER (Employee_Name) LIKE ''%' || UPPER (pi_Emp_Name) || '%''';

    -- Now I need to execute the above SQL string and take the output of it into po_Emp_Details (Please refer to the output parameter of the proc)
    OPEN po_Emp_Details FOR
    EXECUTE v_SQL_String;

    END; -- Test_SP;

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    did you even read the article I suggested? It's all laid out right there...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width