Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    6

    Unanswered: How to capture EXEC query result into a Cursor?

    I have to coin a Dynamic SQL within PL/SQL and execute a SELECT query against oracle database. How can I capture the resultset and return to the client?

    The code is below: sSql is my variable to hold SQL and work_cursor is my cursor variable.


    But the code "OPEN work_cursor FOR EXEC sSql;" does not work. What method can I use to execute sSql and then capture its resultset in work_cursor?

    Thanks in advance for all suggestions..

    ----------------- CODE BELOW ----------------------------------

    sSql varchar2(1000) := '';
    work_cursor rs_cursor;

    BEGIN

    sSql := 'SELECT cc.requestor, TO_CHAR(cc.create_date,''mm/dd/yyyy hh24:mi:ss'') contact_date,';
    sSql := sSql || 'TO_CHAR(cc.create_date,''mm/dd/yyyy hh24:mi:ss'') AS start_time,';
    sSql := sSql || 'TO_CHAR(cc.create_date + cc.call_duration,''mm/dd/yyyy hh24:mi:ss'') AS end_time,';
    sSql := sSql || 'TO_CHAR(cc.call_duration,''hh24:mi:ss'') AS call_time, cc.notes as contact_comments ';
    sSql := sSql || 'FROM TEST_TABLE cc WHERE ';
    sSql := sSql || v_sbInClause;
    sSql := sSql || ' AND cc.create_date BETWEEN TO_DATE(' || '''' || v_gdBegin || ''',''' || 'MM/DD/YYYY HH24:MIS' || ''') AND TO_DATE(''' || gdEnd || '),''' || 'MM/DD/YYYY HH24:MIS' || ''')';
    sSql := sSql || 'ORDER BY cc.requestor, cc.create_date DESC';

    OPEN work_cursor FOR
    EXEC sSql;

    ret_recordset := work_cursor;

    END;

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Here's an example I got from someone else (can't remember who ... sorry)

    Code:
    SQL> conn scott/tiger
    Connected.
    
    SQL> CREATE OR REPLACE FUNCTION qry_minus   
    2     (p1 IN VARCHAR2, p2 IN VARCHAR2)  
    3     RETURN sys_refcursor  
    4  IS  
    5     return_value sys_refcursor;  
    6  BEGIN  
    7     OPEN return_value FOR p1||' MINUS '||p2;  
    8     RETURN return_value;  
    9  END; 
    10  /
    Function created.
    
    SQL> VAR q refcursor
    SQL> exec :q := qry_minus('SELECT deptno FROM dept', 'SELECT deptno FROM emp')
    PL/SQL procedure successfully completed.
    
    SQL> print q    
        DEPTNO
    ----------        
            40
    SQL> 
    
    -Chuck

Posting Permissions

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