Trying to create a DML file of the owner inserts Oracle

Question

I am trying to create a DML file that contains all the inserts to a database using only a script and asking only for the owner name, I found some documentation about the creation of files in Oracle and some other about how to get the insert statements. This is the query that gets the inserts

SELECT /*insert*/ * FROM ALL_TAB_COLUMNS WHERE OWNER = 'OwnerName';

And this is what I`m trying to do in order to create the file with the selected rows from the query

DECLARE
    F1 UTL_FILE.FILE_TYPE;
    
    CURSOR C_TABLAS IS
        SELECT /*insert*/ * FROM ALL_TAB_COLUMNS WHERE OWNER = 'BETA';
        
    V_INSERT VARCHAR2(32767);
BEGIN
    OPEN C_TABLAS;
    LOOP
        FETCH C_TABLAS INTO V_INSERT;
        EXIT WHEN C_TABLAS%NOTFOUND;
        
        F1 := UTL_FILE.FOPEN('D:\Desktop\CENFOTEC\4 Cuatrimestre\Programación de Bases de Datos\Proyecto\FileTests','TestUno.dml','W');
        UTL_FILE.PUT_LINE(F1, V_INSERT);
        UTL_FILE.FCLOSE (F1);
    END LOOP;
    CLOSE C_TABLAS;
END;

I'm having trouble with the fetch, I'm getting this error: wrong number of values in the INTO list of a FETCH statement

I know that it is a basic one, but I can't figure out how many columns I am getting from the query above

Although I'm trying this way i wouldn't mind changing it, I need to create a DML file of all the inserts needed to replicate the database of the given user. Thanks a lot

Answer 1

In SQL Developer, when you use:

SELECT /*insert*/ * FROM ALL_TAB_COLUMNS WHERE OWNER = 'OwnerName';

Then the /*insert*/ hint is processed by SQL Developer on the client-side and converts the returned result set into DML statements.

To quote @ThatJeffSmith in his answer where he gave the above solution:

here is a SQL Developer-specific solution

That behaviour is specific to the SQL Developer client application.

---

In the Oracle database, when you use:

SELECT /*insert*/ * FROM ALL_TAB_COLUMNS WHERE OWNER = 'OwnerName';

Then /*insert*/ is an inline comment and it is IGNORED and has zero effect on the output of the query.

Therefore, when you do:

DECLARE
    F1 UTL_FILE.FILE_TYPE;
    
    CURSOR C_TABLAS IS
        SELECT /*insert*/ * FROM ALL_TAB_COLUMNS WHERE OWNER = 'BETA';
        
    V_INSERT VARCHAR2(32767);
BEGIN
    OPEN C_TABLAS;
    LOOP
        FETCH C_TABLAS INTO V_INSERT;
        EXIT WHEN C_TABLAS%NOTFOUND;
        
        F1 := UTL_FILE.FOPEN('D:\Desktop\CENFOTEC\4 Cuatrimestre\Programación de Bases de Datos\Proyecto\FileTests','TestUno.dml','W');
        UTL_FILE.PUT_LINE(F1, V_INSERT);
        UTL_FILE.FCLOSE (F1);
    END LOOP;
    CLOSE C_TABLAS;
END;
/

The PL/SQL anonymous block will be processed by the database's PL/SQL engine on the server-side and it will context-switch and pass the cursor's SQL to the database's SQL engine where it will be run and the /*insert*/ comment is ignored and it will return all the columns.

I can't figure out how many columns I am getting from the query above.

One column for every column in the ALL_TABS_COLUMNS table. You can use:

SELECT * FROM all_tabs_columns FETCH FIRST ROW ONLY

And then count the columns. I made it 37 columns (but might have miscounted).

---

However

Trying to generate INSERT statements that correspond to all the rows in the ALL_TAB_COLUMNS table so that you can recreate the database is WRONG. You need to generate the DDL statements for each table and not generate DML statements to try to modify a data dictionary table (which, likely as not, if you try to modify data dictionary tables will leave your database in an unusable state).

If you want to recreate the database then use the answers in this question or backup the database and then restore it to the new database.