Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Viewing results in output cursor.

    I have several stored procs in packages that take variables as input parameters and that output reference cursors.

    How can I execute these procedures in SQL Plus Worksheet to test them? Including the input parameter is not problem, but how do I view the results that are returned in the cursor?

    Here is an example of one stored proc's header:

    Code:
    TYPE C_EVENTLOG IS REF CURSOR;
    
    PROCEDURE P_S_EVENTLOG
      (V_EVENTDESC      IN VARCHAR2,
        O_EVENTLOGCURSOR OUT C_EVENTLOG);
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If SQL Plus Worksheet is similar to SQL Plus then try this:
    Code:
    var rc refcursor
    exec p_s_eventlog('xxx',:rc)
    print rc

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Thanks Tony! I will try that tomorrow.

    Another suggestion I got was to use "DBMS Output", though I will have to look it up to determine what it is.

    Thanks again for your help (again)!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    DBMS_OUTPUT works like in this example ... see if it suits your needs:
    Code:
    SQL> set serveroutput on
    SQL> DECLARE
      2    CURSOR cur IS SELECT ename FROM EMP WHERE deptno = 10;
      3  BEGIN
      4    FOR cur_r IN cur LOOP
      5      dbms_output.put_line('Employee name: ' || cur_r.ename);
      6    END LOOP;
      7  END;
      8  /
    Employee name: CLARK
    Employee name: KING
    Employee name: MILLER
    
    PL/SQL procedure successfully completed.
    
    SQL>

Posting Permissions

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