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

BTW the links that I used to get here are: https://stackoverflow.com/questions/44026532/generate-inserts-in-oracle-sql-developer and https://docs.oracle.com/database/121/ARPLS/u_file.htm#ARPLS70906 — Alexander García, Apr 06 '22 at 12:18
`ALL_TAB_COLUMNS` is a data dictionary file. Trying to `INSERT` directly into data dictionary tables is **WRONG** and is likely to leave your database in an unusable state. If you want to recreate the DDL statements to create tables then use the [method you linked](https://stackoverflow.com/questions/10886450/how-to-generate-entire-ddl-of-an-oracle-schema-scriptable) to [in your previous question](https://stackoverflow.com/q/71748295/1509264). — MT0, Apr 06 '22 at 12:24

MT0 · Answer 1 · 2022-04-06T12:48:56.520

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.

Thank you for your help. The problem is that I need to generate a DDL file to all the Create Table and a DML file to all the inserts in those tables. It asked that way for a project — Alexander García, Apr 06 '22 at 13:04
@AlexanderGarcía Which is fine but you are querying the `ALL_TABS_COLUMNS` table which is wrong and you are trying to apply an SQL Developer specific solution to a non-SQL Developer situation. This is an answer to this question which asks what is wrong with your current query (unfortunately, what is wrong is both the underlying concept and the implementation). — MT0, Apr 06 '22 at 13:13

Trying to create a DML file of the owner inserts Oracle

1 Answers1

However