Home » RDBMS Server » Server Utilities » create flat file using stored procedure (11.2.0.4, Windows 2008 R2)
create flat file using stored procedure [message #646870] Tue, 12 January 2016 11:17 Go to next message
sant_new1
Messages: 46
Registered: June 2014
Member
PHi friends,

Below is the procedure we are implementing to use to create csv files on a daily basis for application needs in order to import it onto different applications.


CREATE OR REPLACE PROCEDURE DATA_BKP
(in_sql   IN VARCHAR2,
dir_exp  IN VARCHAR2,
f_name   IN VARCHAR2
) is
 
  char_val         CHAR;
  num_val         NUMBER;
  var_val          VARCHAR2(4000);
  long_val         LONG;
  date_val         DATE;
  data_out        VARCHAR2(32767);
  ret_val           NUMBER;
  col_count       INTEGER;
  tabl_cur         NUMBER;
  cur_val          NUMBER;
  c_data          VARCHAR2(4000);
  rec_val         DBMS_SQL.DESC_TAB;
  long_data      INTEGER;
  fl_type           UTL_FILE.FILE_TYPE;
  file_val          BOOLEAN;
 
BEGIN
 
  tabl_cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(tabl_cur, in_sql, DBMS_SQL.NATIVE);
  cur_val := DBMS_SQL.EXECUTE(tabl_cur);
  DBMS_SQL.DESCRIBE_COLUMNS(tabl_cur, col_count, rec_val);
 
  FOR ctr in 1..col_count
  LOOP
      CASE rec_val(ctr).col_type
           WHEN 1  THEN DBMS_SQL.DEFINE_COLUMN(tabl_cur,ctr,var_val,4000);
           WHEN 96 THEN DBMS_SQL.DEFINE_COLUMN(tabl_cur,ctr,char_val,1);
           WHEN 8  THEN DBMS_SQL.DEFINE_COLUMN_LONG(tabl_cur,ctr);
           WHEN 2  THEN DBMS_SQL.DEFINE_COLUMN(tabl_cur,ctr,num_val);
           WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(tabl_cur,ctr,date_val);
      ELSE
           DBMS_SQL.DEFINE_COLUMN(tabl_cur,ctr,var_val,2000);
      END CASE;
  END LOOP;

  fl_type := UTL_FILE.FOPEN(upper(dir_exp),f_name,'w',32767);
  FOR ctr in 1..col_count
  LOOP
            data_out := ltrim(data_out||chr(9)||lower(rec_val(ctr).col_name),chr(9));
  END LOOP;
  UTL_FILE.PUT_LINE(fl_type, data_out);
  IF NOT file_val THEN
            UTL_FILE.FCLOSE(fl_type);
  END IF;
 
   IF NOT file_val THEN
            fl_type := UTL_FILE.FOPEN(upper(dir_exp),c_data,'w',32767);
  END IF;
  LOOP
    ret_val := DBMS_SQL.FETCH_ROWS(tabl_cur);
    EXIT WHEN ret_val = 0;
    data_out := NULL;
    FOR ctr in 1..col_count
     LOOP
          CASE rec_val(ctr).col_type
                WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(tabl_cur,ctr,var_val);
                     data_out := ltrim(data_out||'"'||var_val||'"'||chr(9));
                WHEN 96 THEN DBMS_SQL.COLUMN_VALUE(tabl_cur,ctr,char_val);
                     data_out := ltrim(data_out||'"'||char_val||'"'||chr(9));
                WHEN 8 THEN DBMS_SQL.COLUMN_VALUE_LONG(tabl_cur,ctr,132768,0,long_val,long_data);
                     data_out := ltrim(data_out||'"'||long_val||'"'||chr(9));
                WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(tabl_cur,ctr,num_val);
                    data_out := ltrim(data_out||num_val||chr(9));
                WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(tabl_cur,ctr,date_val);
                    data_out := ltrim(data_out||to_char(date_val,'DD/MM/YYYY HH24:MI:SS')||chr(9));
         ELSE
                 DBMS_SQL.COLUMN_VALUE(tabl_cur,ctr,var_val);
                data_out := ltrim(data_out||'"'||var_val||'"'||chr(9));
         END CASE;
     END LOOP;
   UTL_FILE.PUT_LINE(fl_type, data_out);
  END LOOP;
  UTL_FILE.FCLOSE(fl_type);
  DBMS_SQL.CLOSE_CURSOR(tabl_cur);
END;
/

There is a directory called dir_csv (d:\backup_csv folder on the database server) created in the database.  Procedure is executed as:

exec data_bkp ('select * from table_name','dir_csv','table_name.CSV');



Currently when executing the above procedure it creates the csv files on the database server. But, we would like to input the path where the csv files will be generated into. More likely that we will be executing the procedure from a batch file on a different(not database) server, so would like the csv files to be generated on this batch server rather than the database server. Is this possible to achieve from the below procedure?

Please give your thoughts.. Thanks a lot
Re: create flat file using stored procedure [message #646871 is a reply to message #646870] Tue, 12 January 2016 11:26 Go to previous messageGo to next message
John Watson
Messages: 8293
Registered: January 2010
Location: Global Village
Senior Member
PL/SQL runs in the database, it can't write to a client machine. Why don't you just use SQL*Plus? Spool the query and save it wherever you want. A whole lot simpler.
Re: create flat file using stored procedure [message #646872 is a reply to message #646870] Tue, 12 January 2016 11:34 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As John said,
UTL_FILE will write only to server.
Spool will write to wherever it is been called from.
If you absolutely need it, present a shared filesystem (NFS Mounts?) to your database server.
In old days, Windows was not very happy with this setup and Oracle still could behave funny with UNC thingies.
And it is quite not secure to open-up a filesystem used by database to external users.
I would just let UTL to write to server and then automatically SFTP/whatever (external tools are quite easy) to the target destination.

[Updated on: Tue, 12 January 2016 11:36]

Report message to a moderator

Re: create flat file using stored procedure [message #646875 is a reply to message #646872] Tue, 12 January 2016 12:37 Go to previous messageGo to next message
sant_new1
Messages: 46
Registered: June 2014
Member
Thank you for your replies.. We will probably go with spooling or ftp'ing the files from the db server.
Re: create flat file using stored procedure [message #646876 is a reply to message #646875] Tue, 12 January 2016 13:13 Go to previous message
Michel Cadot
Messages: 67223
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

When you spool, you spool in the client machine, so just execute the script on the target machine.

Previous Topic: exp command ending abruptly without any error messages
Next Topic: if i import database using impdp full import its also change character set and nls_lang
Goto Forum:
  


Current Time: Thu Jul 02 16:41:41 CDT 2020