Home » SQL & PL/SQL » SQL & PL/SQL » RE: Cursor variable output
RE: Cursor variable output [message #223649] Sat, 10 March 2007 01:22 Go to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

is there anyway by which the output of a cursor variable be written to a file(.csv format).i want the result to be pivoted and then that output written to a file.

sample create table and insert statements :
CREATE TABLE TEST
(
ID NUMBER,
m_type varchar2(1),
AMT NUMBER
)
/
begin
INSERT INTO TEST VALUES(1,'A',200); 
INSERT INTO TEST VALUES(1,'B',200); 
INSERT INTO TEST VALUES(2,'A',200); 
INSERT INTO TEST VALUES(3,'A',200); 
INSERT INTO TEST VALUES(3,'B',200); 
INSERT INTO TEST VALUES(3,'C',200);
INSERT INTO TEST VALUES(1,'A',200); 
END;


CREATE OR REPLACE PROCEDURE TEST_PROC(CUR_VAR OUT SYS_REFCURSOR) IS
SQL_STAT VARCHAR2(32767);
BEGIN
SQL_STAT :='SELECT ID ';
FOR X IN (SELECT DISTINCT M_TYPE FROM TEST)
LOOP
SQL_STAT :=SQL_STAT||',SUM(DECODE(M_TYPE,'''||X.M_TYPE||''',AMT))"'||
X.M_TYPE||'"' ;
END LOOP;
SQL_STAT :=SQL_STAT ||' FROM TEST GROUP BY ID ';
OPEN CUR_VAR FOR SQL_STAT;
END;


till here its no problem.but , now how do i write the output to a file.can anyone please guide me .


regards,
Re: RE: Cursor variable output [message #223677 is a reply to message #223649] Sat, 10 March 2007 04:18 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
utl_file package can help you

Have a look at : utl_file
Re: RE: Cursor variable output [message #223679 is a reply to message #223677] Sat, 10 March 2007 04:45 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


thanks martijn.i know about utl_file package.but how can i incorporate the procedure in the utl_file package.that's where i am stuck.

regards,
Re: RE: Cursor variable output [message #223694 is a reply to message #223679] Sat, 10 March 2007 08:53 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Do you mean you need an example of using UTL_FILE?
Re: RE: Cursor variable output [message #223840 is a reply to message #223694] Mon, 12 March 2007 00:53 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


thanx William for your reply.i have been using utl_file and i know how to use it.but what i was confused was how to incorporate the proc.
declare
file_output utl_file.file_type :=utl_file.fopen('MY_DIR','TEST_OUTPUT.CSV','W');
BEGIN
UTL_FILE.PUT_LINE(FILE_OUTPUT,--this is where i am confused 
as to how to use the proc.if you can suggest/recommend 
something on this i would appreciate that.
hope that i have explained what exactly 
i am looking for.



regards,




[Updated on: Mon, 12 March 2007 00:55]

Report message to a moderator

Re: RE: Cursor variable output [message #224068 is a reply to message #223840] Mon, 12 March 2007 17:54 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Here's a demo:

CREATE OR REPLACE PROCEDURE log_dbms_output
        ( p_filename VARCHAR2 )
AS
        k_dir CONSTANT all_directories.directory_name%TYPE := 'WILLIAM_DATA';
        v_dbmsoutputlines DBMS_OUTPUT.CHARARR;
        v_file UTL_FILE.FILE_TYPE;
        v_pointless_dummy INTEGER := 1e6;
BEGIN
        v_file := UTL_FILE.FOPEN(k_dir,p_filename,'A');
        DBMS_OUTPUT.GET_LINES(v_dbmsoutputlines, v_pointless_dummy);

        IF v_dbmsoutputlines.COUNT > 0
        THEN
                UTL_FILE.PUT_LINE
                ( v_file,'LOG_DBMS_OUTPUT called on ' ||
                  TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') || ' by ' || user );

                FOR i IN 1..v_dbmsoutputlines.LAST LOOP
                        UTL_FILE.PUT_LINE(v_file,v_dbmsoutputlines(i));
                END LOOP;

                UTL_FILE.PUT_LINE
                ( v_file,'LOG_DBMS_OUTPUT completed on ' ||
                  TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') || ' user ' || user );
        END IF;

        UTL_FILE.FCLOSE(v_file);
        DBMS_OUTPUT.PUT_LINE
        ( 'Logged ' || v_dbmsoutputlines.COUNT ||
          ' DBMS_OUTPUT lines to file ' || k_dir || '/' || p_filename );
END;
/
Re: RE: Cursor variable output [message #224405 is a reply to message #224068] Wed, 14 March 2007 03:19 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


thanx William for your reply.But William its just an demo of using UTL_File,this is not i was looking for.

SQL> VAR X REFCURSOR
SQL> EXEC TEST_PROC(:X)

PL/SQL procedure successfully completed.

SQL> PRINT X

        ID          A          B          C                                     
---------- ---------- ---------- ----------                                     
         1        400        200                                                
         2        200                                                           
         3        200        200        200           


this is what i was looking for .this output to be written in a csv format.


regards,
Re: RE: Cursor variable output [message #224431 is a reply to message #224405] Wed, 14 March 2007 04:56 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
So the problem you are having is to do with formatting text strings and not to do with UTL_FILE?
Re: RE: Cursor variable output [message #224435 is a reply to message #224431] Wed, 14 March 2007 05:12 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


formatting???? i didn't get what you meant by formatting.i didn't have problem using UTL_file.i just posted the output to give a fair idea of how i wanted the output in the file.i was trying to do the same using a cursor variable and transferring the out to a file.can you please suggest on this


regards,

[Updated on: Wed, 14 March 2007 05:15]

Report message to a moderator

Re: RE: Cursor variable output [message #224444 is a reply to message #224435] Wed, 14 March 2007 05:32 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
So given data in columns like this

         1        400        200
         2        200
         3        200        200        200

you want to change the format to this:

1,400,200
2,200
3,200,200,200

and write this to a file.

However, you are already familiar with writing to files with UTL_FILE, so it's just about the format.

However, it's apparently not about the format either.
Re: RE: Cursor variable output [message #224448 is a reply to message #224444] Wed, 14 March 2007 05:40 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

yes and there's where i am stuck.i don't know how much columns will be generated from the cursor variable.and above all how do i refer the columns from the cursor variable.

below is what i was trying to do
utl_file.put_line(file_output,test_proc(cur_var_name));

this is the part from where i am not able to proceed forward.any suggestions on this.



regards,

[Updated on: Wed, 14 March 2007 05:40]

Report message to a moderator

Re: RE: Cursor variable output [message #224683 is a reply to message #224448] Thu, 15 March 2007 02:32 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
OK, I think we finally got there.

How do you write a procedure that accepts a cursor variable as a parameter and writes the rows out to a file in CSV format (or does anything else with them, for that matter), when you don't know in advance what columns will exist in the cursor?

That I agree is a tricky one.
Re: RE: Cursor variable output [message #224789 is a reply to message #224683] Thu, 15 March 2007 08:24 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I think this is what you are after.
This version accepts tablename as input and not a refcursor.

Find the basic version of dump_csv code.

create or replace procedure dump_csv(p_TableName varchar2) AUTHID current_USER is
    l_theCursor     integer default dbms_sql.open_cursor;
    l_desc_tab                      dbms_sql.desc_tab;
    l_columnValue   varchar2(2000);
    l_status        integer;
    l_colCnt        number default 0;
    l_cnt           number default 0;
    p_Query         varchar2(512);
begin
      p_query := 'select * from ' || p_TableName;
      dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
      dbms_sql.describe_columns(l_theCursor, l_colcnt, l_desc_tab);

    for i in 1 .. 255 loop
        begin
            dbms_sql.define_column( l_theCursor, i, 
                                    l_columnValue, 2000 );
            l_colCnt := i;
        exception
            when others then
                if ( sqlcode = -1007 ) then exit;
                else
                    raise;
                end if;
        end;
    end loop;

    l_status := dbms_sql.execute(l_theCursor);
    
    loop
        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, 
                                   l_columnValue );
            dbms_output.put_line(l_desc_tab(i).col_name || '=' || trim(l_columnValue)||';');
        end loop;
        
    end loop;
    dbms_sql.close_cursor(l_theCursor);
end dump_csv;
/



Output :

scott@ORCL> set serveroutput on size 100000
scott@ORCL> exec dump_Csv('DEPT');
DEPTNO=10;
DNAME=ACCOUNTING;
LOC=NEW YORK;
DEPTNO=20;
DNAME=RESEARCH;
LOC=DALLAS;
DEPTNO=30;
DNAME=SALES;
LOC=CHICAGO;
DEPTNO=40;
DNAME=OPERATIONS;
LOC=BOSTON;

Customize it for your needs.

HTH

Courtesy Asktom.oracle.com.

[Updated on: Thu, 15 March 2007 08:34]

Report message to a moderator

Re: RE: Cursor variable output [message #224955 is a reply to message #224789] Fri, 16 March 2007 02:30 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

Exactly William that's what i was trying to explain.Is there any way of achieving it.

Rajaram thanks for the efforts taken.


regards,
Re: RE: Cursor variable output [message #224958 is a reply to message #224955] Fri, 16 March 2007 02:40 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
This is not possible within PL/SQL, although I think an enhancement to allow this is on the list of new features rumoured for 11g later this year. In the meantime I've seen it done in Java. If I find some code I'll post it here.
Re: RE: Cursor variable output [message #225077 is a reply to message #224958] Sat, 17 March 2007 00:57 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
I think S.Rajarm was on the right track. Please see the demonstration below.

-- test table and data:
SCOTT@10gXE> CREATE TABLE test
  2    (id	NUMBER,
  3  	m_type	VARCHAR2 (1),
  4  	AMT	NUMBER)
  5  /

Table created.

SCOTT@10gXE> BEGIN
  2    INSERT INTO test VALUES (1, 'A', 200);
  3    INSERT INTO test VALUES (1, 'B', 200);
  4    INSERT INTO test VALUES (2, 'A', 200);
  5    INSERT INTO test VALUES (3, 'A', 200);
  6    INSERT INTO test VALUES (3, 'B', 200);
  7    INSERT INTO test VALUES (3, 'C', 200);
  8    INSERT INTO test VALUES (1, 'A', 200);
  9  END;
 10  /

PL/SQL procedure successfully completed.


-- original unmodified dump_csv function by Tom Kyte:
SCOTT@10gXE> create or replace function  dump_csv( p_query     in varchar2,
  2  					   p_separator in varchar2
  3  							 default ',',
  4  					   p_dir       in varchar2 ,
  5  					   p_filename  in varchar2 )
  6  return number
  7  AUTHID CURRENT_USER
  8  is
  9  	 l_output	 utl_file.file_type;
 10  	 l_theCursor	 integer default dbms_sql.open_cursor;
 11  	 l_columnValue	 varchar2(2000);
 12  	 l_status	 integer;
 13  	 l_colCnt	 number default 0;
 14  	 l_separator	 varchar2(10) default '';
 15  	 l_cnt		 number default 0;
 16  begin
 17  	 l_output := utl_file.fopen( p_dir, p_filename, 'w' );
 18  
 19  	 dbms_sql.parse(  l_theCursor,	p_query, dbms_sql.native );
 20  
 21  	 for i in 1 .. 255 loop
 22  	     begin
 23  		 dbms_sql.define_column( l_theCursor, i,
 24  					 l_columnValue, 2000 );
 25  		 l_colCnt := i;
 26  	     exception
 27  		 when others then
 28  		     if ( sqlcode = -1007 ) then exit;
 29  		     else
 30  			 raise;
 31  		     end if;
 32  	     end;
 33  	 end loop;
 34  
 35  	 dbms_sql.define_column( l_theCursor, 1, l_columnValue,
 36  				 2000 );
 37  
 38  	 l_status := dbms_sql.execute(l_theCursor);
 39  
 40  	 loop
 41  	     exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
 42  	     l_separator := '';
 43  	     for i in 1 .. l_colCnt loop
 44  		 dbms_sql.column_value( l_theCursor, i,
 45  					l_columnValue );
 46  		 utl_file.put( l_output, l_separator ||
 47  					 l_columnValue );
 48  		 l_separator := p_separator;
 49  	     end loop;
 50  	     utl_file.new_line( l_output );
 51  	     l_cnt := l_cnt+1;
 52  	 end loop;
 53  	 dbms_sql.close_cursor(l_theCursor);
 54  
 55  	 utl_file.fclose( l_output );
 56  	 return l_cnt;
 57  end dump_csv;
 58  /

Function created.

SCOTT@10gXE> SHOW ERRORS
No errors.


-- directory object, procedure, and execution:
SCOTT@10gXE> CREATE OR REPLACE DIRECTORY my_dir AS 'd:\oracle2'
  2  /

Directory created.

SCOTT@10gXE> CREATE OR REPLACE PROCEDURE test_proc
  2  AS
  3    v_sql_stat VARCHAR2 (32767);
  4    v_rows	  NUMBER;
  5  BEGIN
  6    v_sql_stat := 'SELECT ID';
  7    FOR r IN (SELECT DISTINCT m_type FROM test)
  8    LOOP
  9  	 v_sql_stat := v_sql_stat
 10  	 || ',SUM(DECODE(m_type,''' || r.m_type || ''',AMT))"' || r.m_type || '"' ;
 11    END LOOP;
 12    v_sql_stat := v_sql_stat || ' FROM test GROUP BY id';
 13    v_rows := dump_csv (v_sql_stat, ',', 'MY_DIR', 'test.dat');
 14  END test_proc;
 15  /

Procedure created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> EXEC test_proc

PL/SQL procedure successfully completed.


-- resulting contents of d:\oracle2\test.dat:
1,400,200,
2,200,,
3,200,200,200

Re: RE: Cursor variable output [message #225188 is a reply to message #225077] Mon, 19 March 2007 00:41 Go to previous message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


I really appreciate the inputs/examples provided by all of you.Thanks for all the efforts.



regards,
Previous Topic: hi guys help me out for these problem on procedures
Next Topic: CALL Store Procedure with Output variable from Another Procedure
Goto Forum:
  


Current Time: Thu Dec 08 19:53:49 CST 2016

Total time taken to generate the page: 0.07812 seconds