This script exports data from tables as insert statements. Useful for exporting data from static dictionary tables. #oracle #sqlplus

data_as_inserts.sql

set heading off
set linesize 1000
rem set pagesize 50000
set echo off
set serveroutput on
SET FEEDBACK OFF
SPOOL out.sql

DECLARE
  cur SYS_REFCURSOR;
  curid NUMBER;
  desctab DBMS_SQL.desc_tab;
  colcnt NUMBER;
  namevar VARCHAR2(4000);
  numvar NUMBER;
  datevar DATE;

  out_columns varchar2(10000);
  out_values varchar2(10000);
  
BEGIN
  FOR rec IN (SELECT table_name 
                FROM user_tables 
               WHERE table_name LIKE '%LOV'
               ORDER BY table_name)
  LOOP
    OPEN cur FOR 'SELECT * FROM '||rec.table_name||' ORDER BY 1';

    curid := DBMS_SQL.to_cursor_number(cur);
    DBMS_SQL.describe_columns(curid, colcnt, desctab);

    out_columns := 'INSERT INTO '||rec.table_name||'(';

    FOR indx IN 1 .. colcnt LOOP
      out_columns := out_columns||desctab(indx).col_name||',';
      IF desctab (indx).col_type = 2 
      THEN
        DBMS_SQL.define_column (curid, indx, numvar); 
      ELSIF desctab (indx).col_type = 12
      THEN
        DBMS_SQL.define_column (curid, indx, datevar); 
      ELSE
        DBMS_SQL.define_column (curid, indx, namevar, 4000); 
      END IF;
    END LOOP;

    out_columns := rtrim(out_columns,',')||') VALUES (';

    WHILE DBMS_SQL.fetch_rows (curid) > 0 
    LOOP
      out_values := '';
      FOR indx IN 1 .. colcnt 
      LOOP
        IF (desctab (indx).col_type = 1) 
        THEN
          DBMS_SQL.COLUMN_VALUE (curid, indx, namevar);
          out_values := out_values||''''||namevar||''',';
        ELSIF (desctab (indx).col_type = 2)
        THEN
          DBMS_SQL.COLUMN_VALUE (curid, indx, numvar);
          out_values := out_values||numvar||',';
        ELSIF (desctab (indx).col_type = 12)
        THEN
          DBMS_SQL.COLUMN_VALUE (curid, indx, datevar);
          out_values := out_values||
            'to_date('''||to_char(datevar,'DD.MM.YYYY HH24:MI:SS')||
             ''',''DD.MM.YYYY HH24:MI:SS''),';
        END IF;
      END LOOP; 
      dbms_output.put_line(out_columns||rtrim(out_values,',')||');');

    END LOOP;

    DBMS_SQL.close_cursor (curid);

  END LOOP;
  DBMS_OUTPUT.put_line('COMMIT;');
END;
/
SPOOL OFF
EXIT

Hi there,

I'm trying to use this solution as it was intended to, exporting data as insert statements but I have run into a problem. The static data seems to have blobs in them and so I get an error stating:

ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
ORA-06512: at "SYS.DBMS_SQL", line 1830
ORA-06512: at line 41
00932. 00000 - "inconsistent datatypes: expected %s got %s"

Line 41 for me is line 49 here.

Have you come across a similar issue before? How do I get oracle to export blobs nicely as insert statements?

You have to add a declare a BLOB variable, then add DBMS_SQL.DEFINE_COLUMN referencing to the BLOB variable and then add to the if clause with DBMS_SQL.COLUMN_VALUE the one to evaluate de BLOB data that will be processed.
Something to know is the column type number of the BLOB. You can find it here https://community.toadworld.com/platforms/oracle/w/wiki/3328.dbms-sql-describe-columns.