ORACLE: Build 'INSERT INTO' statements from 'SELECT *' results

Question

I am trying to build a script that takes the results from a table "ADMIN.ACCT_HRS" and creates INSERT INTO statements for each row, so that I could feasibly rebuild the table on another server via a DML statement. The search parameter for ACCT_HRS is the "WID", which I am pulling from the "ADMIN.INVENTORY" table using a user-inputted value, "pknum". This script is still in its infancy so I am only targeting one table; but eventually, I plan to adapt it to generate the INSERT INTO statements for six different tables with only one user-inputted value, all with constantly changing numbers of rows and columns. I could only figure out how to hardcode the column names that are being fetched (the following script works). I'm asking how to make this script dynamic for number of columns and column names.

**No, I cannot use external clients such as SQLcl **No, I cannot use a database link because the DML is being sent to a remote server that I have zero visibility on. **Yes, it has to be a PL/SQL that can be executed in Oracle to produce an output. **Yes, I realize there are easier ways and this is a pain in the @$$.

So far, my script looks like this:

DECLARE
    v_pknum VARCHAR2(16);
    v_sqlOutput VARCHAR2(4000);
    v_col_val VARCHAR2(4000);

    -- Variables for ACCT_HRS columns
    v_wid ADMIN.ACCT_HRS.WID%TYPE;
    v_hours_type_cd ADMIN.ACCT_HRS.HOURS_TYPE_CD%TYPE;
    v_wrk_dt ADMIN.ACCT_HRS.WRK_DT%TYPE;
    v_tech_id ADMIN.ACCT_HRS.TECH_ID%TYPE;
    v_tech_type ADMIN.ACCT_HRS.TECH_TYPE%TYPE;
    v_tech_class ADMIN.ACCT_HRS.TECH_CLASS%TYPE;
    v_man_hrs ADMIN.ACCT_HRS.MAN_HRS%TYPE;
    v_job_order ADMIN.ACCT_HRS.JOB_ORDER%TYPE;
    v_create_dt ADMIN.ACCT_HRS.CREATE_DT%TYPE;
    v_euser_id ADMIN.ACCT_HRS.EUSER_ID%TYPE;
    v_man_hrs_seq_no ADMIN.ACCT_HRS.MAN_HRS_SEQ_NO%TYPE;

    -- Cursor to fetch data
    CURSOR c_ACCT_HRS IS SELECT * FROM ADMIN.ACCT_HRS WHERE WID IN (SELECT WID FROM ADMIN.INVENTORY WHERE pknum = v_pknum);

BEGIN
    -- Prompt for pknum value
    DBMS_OUTPUT.PUT_LINE('Enter pknum:');
    v_pknum := '&pknum';

    -- Open the cursor
    OPEN c_ACCT_HRS;

    -- Loop to fetch each row
    LOOP
        -- Fetch the next row from the cursor into variables
        FETCH c_ACCT_HRS INTO v_wid, v_hours_type_cd, v_wrk_dt,
                               v_tech_id, v_tech_type, v_tech_class,
                               v_man_hrs, v_job_order, v_create_dt,
                               v_euser_id, v_man_hrs_seq_no;

        -- Exit the loop if no more rows
        EXIT WHEN c_ACCT_HRS%NOTFOUND;

        -- Initialize the output statement for each iteration
        v_sqlOutput := 'INSERT INTO ACCT_HRS (WID, HOURS_TYPE_CD, WRK_DT, TECH_ID, TECH_TYPE, TECH_CLASS, MAN_HRS, JOB_ORDER, CREATE_DT, EUSER_ID, MAN_HRS_SEQ_NO) VALUES (';

        -- Handle NULL values and construct the corresponding part of the INSERT INTO statement
        v_col_val := '';

        IF v_wid IS NOT NULL THEN v_col_val := v_col_val || '''' || v_wid || ''', ';
        ELSE v_col_val := v_col_val || 'NULL, ';
        END IF;

        IF v_hours_type_cd IS NOT NULL THEN v_col_val := v_col_val || '''' || v_hours_type_cd || ''', ';
        ELSE v_col_val := v_col_val || 'NULL, ';
        END IF;

        IF v_wrk_dt IS NOT NULL THEN v_col_val := v_col_val || '''' || v_wrk_dt || ''', ';
        ELSE v_col_val := v_col_val || 'NULL, ';
        END IF;

        IF v_tech_id IS NOT NULL THEN v_col_val := v_col_val || '''' || v_tech_id || ''', ';
        ELSE v_col_val := v_col_val || 'NULL, ';
        END IF;

        IF v_tech_type IS NOT NULL THEN v_col_val := v_col_val || '''' || v_tech_type || ''', ';
        ELSE v_col_val := v_col_val || 'NULL, ';
        END IF;

        IF v_tech_class IS NOT NULL THEN v_col_val := v_col_val || '''' || v_tech_class || ''', ';
        ELSE v_col_val := v_col_val || 'NULL, ';
        END IF;

        IF v_man_hrs IS NOT NULL THEN v_col_val := v_col_val || '''' || v_man_hrs || ''', ';
        ELSE v_col_val := v_col_val || 'NULL, ';
        END IF;

        IF v_job_order IS NOT NULL THEN v_col_val := v_col_val || '''' || v_job_order || ''', ';
        ELSE v_col_val := v_col_val || 'NULL, ';
        END IF;

        IF v_create_dt IS NOT NULL THEN v_col_val := v_col_val || '''' || v_create_dt || ''', ';
        ELSE v_col_val := v_col_val || 'NULL, ';
        END IF;

        IF v_euser_id IS NOT NULL THEN v_col_val := v_col_val || '''' || v_euser_id || ''', ';
        ELSE v_col_val := v_col_val || 'NULL, ';
        END IF;

        IF v_man_hrs_seq_no IS NOT NULL THEN v_col_val := v_col_val || '''' || v_man_hrs_seq_no || ''', ';
        ELSE v_col_val := v_col_val || 'NULL, ';
        END IF;

        -- Trim the trailing comma and add to the INSERT INTO statement
        v_sqlOutput := v_sqlOutput || RTRIM(v_col_val, ', ') || ');';

        -- Print the INSERT INTO statement for each row
        DBMS_OUTPUT.PUT_LINE(v_sqlOutput);

    END LOOP;

    -- Close the cursor
    CLOSE c_ACCT_HRS;

END;
/

Answer 1

... so that I could feasibly rebuild the table on another server via a DML statement

If that's so, then - in my opinion - you're choosing a wrong tool to do that. Oracle already has utility which is designed for such a purpose (moving data around), and it is called Data Pump. What does it do? It exports stuff here and imports it there. You will need DBA's assistance (unless you're the one) because you have to have access to Oracle directory.

On the other hand, there are still the original export and import utilities which work with .dmp file located on your own PC, i.e. you don't need Oracle directory.

Why is that approach better than yours? Because - as long as there's no problem in exporting simple datatypes (such as NUMBER or VARCHAR2) - there are other datatypes which aren't that simple to handle (DATE is the first one; not to mention BLOBs etc.).

Furthermore, export/import are fast; your row-by-row insert is slow (I wouldn't want to insert millions of rows that way).

---

I'm asking how to make this script dynamic for number of columns and column names.

Just as you said, it'll have to be dynamic. You'll probably have to query (in a loop) user_tables joined to user_tab_columns to get column names and their datatypes. Then compose statement you'll execute immediate.

It is kind of unclear what is desired result; you're printing result to the screen, which means that tool you use supports it (SQL*Plus, SQL Developer, TOAD and similar); then what? Copy/paste it elsewhere (into some file)? If so, why not spooling it?

---

I don't see any benefit in doing it your way and suggest you think it over and discard the idea altogether. Use export/import instead.

---

[EDIT] After reading your comment, it seems that you're using GUI tool for that purpose ("right click ... wizard ... copy/paste ...").

Furthermore, it seems that you didn't put any effort in reading documentation I posted. It exists for a reason. If you did that, you'd actually see that exp is a command line utility. It offers quite a few options you might want to use; one of them is tables parameter which lets you specify ALL tables you want to export. In a single command, without clicking, following the wizard, ....

For example:

M:\>exp scott/tiger@orcl tables=(emp, dept) file=scott.dmp

Export: Release 18.0.0.0.0 - Production on Sri O×u 27 07:05:08 2024
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Export done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                            EMP         14 rows exported
. . exporting table                           DEPT          4 rows exported
Export terminated successfully without warnings.

M:\>

Now that you have .dmp file, you can import it into target schema using similar imp command.

What's even better with that? You can put these commands into a batch file and schedule it (in MS Windows, it is Task Manager; on other operating systems, you'd use what they have for such a purpose) so that .dmp file either waits for you in the morning (ready to be imported) or - if possible - do it all for you (both export and import).

Someone said (I'm just repeating it): those who live by the GUI, die by the GUI. It helps, helps a lot - but only if you know how to properly use it.

Also, if the only tool you have is a hammer, every problem looks like a nail (but you wouldn't dig a hole using a hammer, would you?). That's why there are different tools for different problems.

Therefore, I still can't see why you think that export/import isn't the right choice for you, but OK - I won't try to stop you doing it your way. Good luck!

Answer 2

Reinventing your own way to move data around is likely to be painful and unnecessary. Data pump is a very useful tool, and as mentioned on your previous question some clients can generate insert statements if you really want those instead, but you seem to have ruled out existing tools.

So while I would avoid doing this, you can do this with dynamic SQL. Probably... mostly... This example handles some data types, but not LOBs (it could be extended to do that, but it might not be needed; BLOBs in particular will be a pain), and you can add handling for more if you need to.

declare
  -- your target table name and query, could be a procedure arguments later
  l_table_name all_tables.table_name%type := dbms_assert.simple_sql_name('ACCT_HRS');
  l_statement varchar2(4000) := 'select * from acct_hrs';
  -- for initial parse and execute
  l_refcursor sys_refcursor;
  -- variables for dbms_sql use
  l_c pls_integer;
  l_col_cnt pls_integer;
  l_desc_t dbms_sql.desc_tab4;
  -- local variables to hold column info as required
  l_column_name all_tab_cols.column_name%type;
  l_data_type varchar2(20);
  l_varchar varchar2(4000);
begin
  -- For now set NLS to something useful so we can default all the results to strings
  execute immediate q'^alter session set nls_numeric_characters='.,'^';
  execute immediate q'^alter session set nls_date_format='SYYYY-MM-DD HH24:MI:SS'^';
  execute immediate q'^alter session set nls_timestamp_format='SYYYY-MM-DD HH24:MI:SS.FF9'^';
  execute immediate q'^alter session set nls_timestamp_tz_format='SYYYY-MM-DD HH24:MI:SS.FF9 TZR'^';

  -- open ref cursor for the statement
  open l_refcursor for l_statement;

  -- convert ref cursor to dbms_sql cursor
  l_c := dbms_sql.to_cursor_number(l_refcursor);

  dbms_sql.describe_columns3(c=>l_c, col_cnt=>l_col_cnt, desc_t=>l_desc_t);

  -- define all columns as strings; this will end up with implicit conversion
  -- of dates etc. using NLS settings (hence settng above), and won't handle LOBs;
  -- so should be finessed based on actual data types really...
  for i in 1..l_col_cnt loop
    dbms_sql.define_column(c=>l_c, position=>i, column=>l_varchar, column_size=>4000);
  end loop;

  -- fetch each row in turn
  while dbms_sql.fetch_rows(c=>l_c) > 0 loop
    -- generate the insert statement and column list
    dbms_output.put('insert into ' || l_table_name || '(');
    for i in 1..l_col_cnt loop
      if i > 1 then
        dbms_output.put(',');
      end if;
      dbms_output.put('"' || l_desc_t(i).col_name || '"');
    end loop;
    -- and start the values clause
    dbms_output.put(') values (');
  
    for i in 1..l_col_cnt loop
      -- get the column value for this row (again, as string...)
      dbms_sql.column_value(l_c, i, l_varchar);
      -- write out with explicit conversion, with delimiter after first column
      if i > 1 then
        dbms_output.put(',');
      end if;
      case
        when l_varchar is null then
          dbms_output.put('null');
        when l_desc_t(i).col_type = 2 then
          dbms_output.put(l_varchar);
        when l_desc_t(i).col_type in (12, 180, 181, 231) then
          -- date type 12 will be implicitly cast; can do that explicitly
          dbms_output.put(q'^timestamp '^' || l_varchar || q'^'^');
        when l_desc_t(i).col_type = 182 then
          dbms_output.put(q'^interval '^' || l_varchar || q'^' year to month^');
        when l_desc_t(i).col_type = 183 then
          dbms_output.put(q'^interval '^' || l_varchar || q'^' day to second^');
        -- any other explcit handling if you have other data types
        -- ...
        else
          dbms_output.put(q'^'^' || replace(l_varchar, q'^'^', q'^''^') || q'^'^');
      end case;
    end loop;
    dbms_output.put_line(');');
  end loop;

  dbms_sql.close_cursor(l_c);
end;
/

With a dummy table with a few of your column names and then others I've made up for different data types, that produces:

insert into ACCT_HRS("WID","HOURS_TYPE_CD","WRK_DATE","TECH_ID","TECH_TYPE","SOME_TS","Some_TsTz","SOME_INTERVALYM","SOME_INTERVALDS") values (1,2,timestamp ' 2024-03-26 19:43:49',42,'ABC',timestamp ' 2024-03-26 19:43:49.276568000',timestamp ' 2024-03-26 19:43:49.276568000 +00:00',interval '+05-00' year to month,interval '+01 02:03:00.000000' day to second);
insert into ACCT_HRS("WID","HOURS_TYPE_CD","WRK_DATE","TECH_ID","TECH_TYPE","SOME_TS","Some_TsTz","SOME_INTERVALYM","SOME_INTERVALDS") values (1.5,-2.75,timestamp ' 2024-03-26 19:43:49',42,'ABC',timestamp ' 2024-03-26 19:43:49.276568000',timestamp ' 2024-03-26 19:43:49.276568000 +00:00',interval '+00-02' year to month,interval '+00 00:00:02.000000' day to second);

fiddle showing the dummy table and that code running, and also then manually running the generated inserts to check they actually work.

Again, this isn't a great idea, and this is a starting point for you to build on if you really want to go down this route.

Answer 3

Assuming that you have the table:

CREATE TABLE ADMIN.ACCT_HRS(
  WID            NUMBER,
  HOURS_TYPE_CD  NUMBER,
  WRK_DT         DATE,
  TECH_ID        NUMBER,
  TECH_TYPE      VARCHAR2(20),
  TECH_CLASS     VARCHAR2(200),
  MAN_HRS        NUMBER,
  JOB_ORDER      NUMBER,
  CREATE_DT      DATE,
  EUSER_ID       NUMBER,
  MAN_HRS_SEQ_NO NUMBER
)

Then you do not need PL/SQL and can generate the queries from a query:

SELECT 'INSERT INTO ADMIN.ACCT_HRS (WID, HOURS_TYPE_CD, WRK_DT, TECH_ID, TECH_TYPE, TECH_CLASS, MAN_HRS, JOB_ORDER, CREATE_DT, EUSER_ID, MAN_HRS_SEQ_NO) VALUES ('
    || COALESCE( TO_CHAR(wid), 'NULL') || ','
    || COALESCE( TO_CHAR(hours_type_cd), 'NULL') || ','
    || CASE
       WHEN wrk_dt IS NULL THEN 'NULL'
       ELSE 'TO_DATE(''' || TO_CHAR(wrk_dt, 'SYYYY-MM-DD HH24:MI:SS') || ''', ''SYYYY-MM-DD HH24:MI:SS'')'
       END || ','
    || COALESCE( TO_CHAR(tech_id), 'NULL') || ','
    || CASE WHEN tech_type IS NULL THEN 'NULL' ELSE '''' || tech_type || '''' END || ','
    || CASE WHEN tech_class IS NULL THEN 'NULL' ELSE '''' || tech_class || '''' END || ','
    || COALESCE( TO_CHAR(man_hrs), 'NULL') || ','
    || COALESCE( TO_CHAR(job_order), 'NULL') || ','
    || CASE
       WHEN create_dt IS NULL THEN 'NULL'
       ELSE 'TO_DATE(''' || TO_CHAR(create_dt, 'SYYYY-MM-DD HH24:MI:SS') || ''', ''SYYYY-MM-DD HH24:MI:SS'')'
       END || ','
    || COALESCE( TO_CHAR(euser_id), 'NULL') || ','
    || COALESCE( TO_CHAR(man_hrs_seq_no), 'NULL')
    || ')' AS sql
FROM   ADMIN.ACCT_HRS
WHERE  WID IN ( SELECT WID
                FROM   ADMIN.INVENTORY
                WHERE  pknum = '&pknum' );

fiddle

---

If you are using a client that supports it then you could simplify it all to:

ACCEPT pknum CHAR PROMPT 'Enter pknum: '

SET HEADING OFF
SET LINESIZE 400
SPOOL filename.dmp

SELECT /*INSERT*/ *
FROM   admin.acct_hrs
WHERE  WID IN ( SELECT WID
                FROM   ADMIN.INVENTORY
                WHERE  pknum = '&pknum' );