Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE
UTL_FILE [message #398688] Fri, 17 April 2009 02:34 Go to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> select banner from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production


SQL> sho parameter utl_file_dir

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      C:\CSV_FILES
SQL> sho user
USER is "SYS"
SQL> ed
Wrote file afiedt.buf

  1* CREATE OR REPLACE DIRECTORY CSV_FILES AS 'c:\CSV_FILES'
SQL> /

Directory created.

SQL> sho parameter utl_file_dir

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      C:\CSV_FILES
SQL> GRANT READ, WRITE ON DIRECTORY CSV_FILES TO SCOTT;

Grant succeeded.

SQL> GRANT EXECUTE ON UTL_FILE TO SCOTT;

Grant succeeded.



create or replace
function  dump_csv( p_query        in varchar2,
                       p_separator in varchar2 default ',',
                       p_dir       in varchar2 ,
                       p_filename  in varchar2 )
   return number
   is
       l_output        utl_file.file_type;
       l_theCursor     integer default dbms_sql.open_cursor;
       l_columnValue   varchar2(2000);
       l_status        integer;
       l_colCnt        number default 0;
       l_separator     varchar2(10) default '';
       l_cnt           number default 0;
   begin
       l_output := utl_file.fopen( p_dir, p_filename, 'w' );
   
       dbms_sql.parse(  l_theCursor,  p_query,
                                            dbms_sql.native );
   
       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;
   
       dbms_sql.define_column( l_theCursor, 1,
                               l_columnValue, 2000 );
   
       l_status := dbms_sql.execute(l_theCursor);
   
       loop
           exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
           l_separator := '';
           for i in 1 .. l_colCnt loop
               dbms_sql.column_value( l_theCursor, i,
                                      l_columnValue );
               utl_file.put( l_output,
                             l_separator || l_columnValue );
               l_separator := p_separator;
           end loop;
           utl_file.new_line( l_output );
           l_cnt := l_cnt+1;
       end loop;
       dbms_sql.close_cursor(l_theCursor);
   
       utl_file.fclose( l_output );
       return l_cnt;
   end dump_csv;
   /
Function created.


SQL> CREATE or replace FUNCTION CONCAT_LIST( cur SYS_REFCURSOR )
  2    RETURN  VARCHAR2 IS
  3      ret VARCHAR2(32000);
  4      tmp VARCHAR2(4000);
  5  BEGIN
  6      loop
  7          fetch cur into tmp;
  8          exit when cur%NOTFOUND;
  9              ret := ret || ',' || tmp;
 10      end loop;
 11      RETURN ret;
 12  END;
 13  /

Function created.



SQL> declare
  2         l_rows    number;
  3         l_title varchar2(100);
  4     begin
  5  SELECT
  6         substr(CONCAT_LIST(CURSOR(select cname from col
  7  where tname like 'EMP'
  8      )),2)  into l_title 
  9  FROM dual;
 10  dbms_output.put_line(l_title);
 11         l_rows := dump_csv( 'select * from emp@india',
 12                             ',',
 13                             'C:\CSV_FILES',
 14                             'test.csv' );
 15         dbms_output.put_line( to_char(l_rows) ||
 16                               ' rows extracted to ascii file' );
 17     end;
 18  /

PL/SQL procedure successfully completed.


File Created.

But Here is my Problem,

SQL> declare
  2         l_rows    number;
  3         l_title varchar2(100);
  4     begin
  5  SELECT
  6         substr(CONCAT_LIST(CURSOR(select cname from col
  7  where tname like 'EMP'
  8      )),2)  into l_title 
  9  FROM dual;
 10  dbms_output.put_line(l_title);
 11         l_rows := dump_csv( 'SELECT a.org_id, substr(signup_name,1,30), substr(internal_process_
indicator,1,20)
 12  from org_profile@india a, org_profile_ext@india b
 13  where a.org_id = b.org_id
 14  and org_status <> 'Terminated'
 15  and a.org_id not in (select org_id from org_management_information)
 16  order by a.org_id',
 17                             ',',
 18                             'C:\CSV_FILES',
 19                             'test.csv' );
 20         dbms_output.put_line( to_char(l_rows) ||
 21                               ' rows extracted to ascii file' );
 22     end;
 23  /
and org_status <> 'Terminated'
                   *
ERROR at line 14:
ORA-06550: line 14, column 20:
PLS-00103: Encountered the symbol "TERMINATED" when expecting one of the
following:
. ( ) , * @ % & | = - + < / > at in is mod not range rem =>
.. <an exponent (**)> <> or != or ~= >= <= <> and or like as
between from using ||
The symbol ". was inserted before "TERMINATED" to continue.


Please let me know to the exact error to correct my code

Thanks & regards

Sriram
Re: UTL_FILE [message #398691 is a reply to message #398688] Fri, 17 April 2009 02:38 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
isn't it very obvious that dump_csv first parameter is string that is used as query while building dynamice sql? you therefore need to pass 'terminated' as ''Terminated''
Re: UTL_FILE [message #398696 is a reply to message #398691] Fri, 17 April 2009 02:44 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Thanks bonker

It`s working now.

Sriram.
Previous Topic: SQL use, one column row string value combine
Next Topic: List of Months between range (merged 3)
Goto Forum:
  


Current Time: Sun Dec 04 02:51:27 CST 2016

Total time taken to generate the page: 0.07962 seconds