Oracle PLSQL Write output query on a file

Question

I m trying to generate the Data Insert statments of a table uding this program.

When I execute the program, I have this statement in the myfile.txt, while I expect to get the list of insert statments

SELECT
    'INSERT INTO TAB_PARAMS (ID,CODE,VALUE,ENV,COMMENT) VALUES ('
    || id
    || ','
    || ''''
    || code
    || ''''
    || ','
    || ''''
    || value
    || ''''
    || ','
    || ''''
    || env
    || ''''
    || ','
    || ''''
    || comment
    || ''''
    || ');' AS "insert.sql"
FROM
    tab_params;

The programm I use is:

SET SERVEROUTPUT ON;
DECLARE

CURSOR column_names_cur
IS
SELECT column_name
FROM user_tab_columns
WHERE table_name = UPPER('&&1')
ORDER BY column_id ;

CURSOR col_select_cur
IS
SELECT DECODE(data_type ,
                       'DATE' ,    ''''||'TO_DATE('||''''||'||'||''''||''''||''''||''''||'||'||'TO_CHAR(' ,
                       'NUMBER',    NULL,
                       'VARCHAR2', ''''''''''||'||' ,
                       'XMLTYPE', ''''||'xmltype('||''''||'||'||''''||''''||''''||''''||'||') 

   ||column_name
   ||DECODE (data_type ,
                     'DATE' , ','||''''||'dd-mon-yyyy hh24:mi:ss'||''''||')'||'||'||''''||''''||''''||''''||'||'||''''||','||''''||'||'||''''||''''||''''||''''||'||'||''''||'dd-mon-yyyy hh24:mi:ss'||''''||'||'||''''||''''||''''||''''||'||'||''''||')'||'''' ,
                     'NUMBER',   NULL ,
                     'VARCHAR2' , '||'||''''||''''||''''||'''' ,
                     'XMLTYPE', '||'||''''||''''||''''||''''||'||'||''''||')'||'''' ) sel
FROM user_tab_columns
WHERE table_name = UPPER('&&1')
ORDER BY column_id ;  


w_sql_start               VARCHAR2(4000) ;
w_sql                     VARCHAR2(4000) ;
w_ok                      BOOLEAN ; 
w_file_handle             utl_file.file_type ;
w_err_text                VARCHAR2(500) ; 
w_col_sep                 VARCHAR2(400) := '||'',''||' ;

BEGIN

  -- obtain the start of the sql statement 
  FOR column_names_rec IN column_names_cur
  LOOP

     w_sql_start := w_sql_start ||column_names_rec.column_name  || ',' ;
  DBMS_OUTPUT.put_line('00'||w_sql_start);
  END LOOP ;
DBMS_OUTPUT.put_line('01'||w_sql_start);

w_sql_start := 'SELECT ''INSERT INTO &&1 ('|| RTRIM (w_sql_start,',') ||') VALUES (' ||''''||'||';  
DBMS_OUTPUT.put_line('02'||w_sql_start);

-- obtain individual columns

  FOR col_select_rec IN col_select_cur
  LOOP

     w_sql := w_sql || col_select_rec.sel ||w_col_sep ;        
     DBMS_OUTPUT.put_line('A0'||w_sql);
  END LOOP ;

  IF w_sql IS NOT NULL
  THEN
     w_sql := SUBSTR(w_sql, 1, LENGTH(w_sql)-7) ;      
     w_sql := w_sql_start || w_sql ||'||'||''''||');'||''''||' AS "insert.sql" FROM &&1 ;' ;
     DBMS_OUTPUT.put_line('A1'||w_sql);
  ELSE
     w_sql := 'SELECT ''Table &&1 not found.'' AS "insert.sql" FROM DUAL; ' ;
  END IF ;

-- write the select statement which will genearte the insert statement to a file on the unix box

--w_file_handle := utl_file.fopen ('/app/webrep/webreports', -- directory ,
w_file_handle := utl_file.fopen ('ALERT_DIR', -- directory ,
                                'myfile.txt' , -- filename ,
                                'W') ;

utl_file.putf(w_file_handle ,
             '%s' ,
             w_sql) ;

utl_file.fclose(w_file_handle) ;

END ;
/

I m on oracle 11gR2

So what is the issue you faced.? Whats your problem >

XING
– XING

2017-10-18 11:15:20 +00:00
Commented Oct 18, 2017 at 11:15 — XING
– XING, Commented Oct 18, 2017 at 11:15

Kaushik Nayak · Accepted Answer · 2017-10-18 11:02:34Z

1

You are writing the SELECT statement into the file instead of the values returned from select.

So, I suggest you do the following.

Include the following in your DECLARE block.

ref_cur sys_refcursor; 
v_insert_query VARCHAR2(1000);

And instead of simply writing this,

utl_file.putf(w_file_handle ,
             '%s' ,
             w_sql) ;

Fetch the inserts within w_sql in a loop using REF CURSOR

OPEN ref_cur FOR w_sql;

LOOP;

      FETCH ref_cur INTO v_insert_query;
      EXIT WHEN ref_cur%NOTFOUND;
      utl_file.putf(w_file_handle ,
             '%s' ,
             v_insert_query) ;

END LOOP;

answered Oct 18, 2017 at 11:02

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle PLSQL Write output query on a file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related