Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    148

    Unanswered: How to print the sql from a stored proc

    Hi all. I have a stored proc that just isn't giving me the right results when compared to the static sql statement i use.... I was just wondering how I could print the sql out of this stored proc.

    check it out:
    Code:
    CREATE OR REPLACE PACKAGE CountREP
    AS
    	TYPE ref_cur         IS      	REF CURSOR;
    	PROCEDURE CountREP
    	(
    		
    		FRM				IN		date,
    		TODATE				IN		date,
    		REG					IN		VARCHAR2,
    		REP				IN		VARCHAR2,
    		RC1             		IN  OUT 	REF_CUR
    	);
    END;
    /
    CREATE OR REPLACE PACKAGE BODY CountREP
    	PROCEDURE CountREP
    	(	
    
    		FRM				IN		date,
    		TODATE				IN		date,
    		REG					IN		VARCHAR2,
    		REP				IN		VARCHAR2,
    		RC1                     IN  OUT 	REF_CUR
    	)
    	AS
    	BEGIN 
    		
    	OPEN RC1 FOR 
    	SELECT count (*) from tbl_Requests 
    	WHERE cRegion = reg  
    	AND cReqID = REP 		
    	AND dSubmit BETWEEN  frm  AND todate;
    END;
    
    
    END;
    /

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Would help if you point out WHAT contains your SQL. A simple dbms_output.put_line should help inside the sp itself.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    If you mean the source code, try:
    Code:
    SELECT TEXT FROM USER_SOURCE 
     WHERE NAME= 'COUNTREP';

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    If you have 9i then use the DBMS_METADATA package. It is making DDL extraction a snap:

    Code:
    set pagesize 0
    set long 90000
    
    execute DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
    
    SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
        FROM USER_ALL_TABLES u
    WHERE u.nested='NO' 
             AND (u.iot_type is null or u.iot_type='IOT');
    
    execute DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');

    Hope that helps,

    clio_usa - OCP DBA 8/8i/9i

Posting Permissions

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