How do I create a variable from a query to pass to a cursor for a loop?

I am working on a project to clean up some development environment databases.  I have the ddl, but there are tables they want DML for so that they contain the standardized data expected to be there.  I have a script (courtesy of Tim Hall at Oracle-Base) which will output the table data as dml, but some of my schemas have a large number of tables where I will need to run said script.  I have been trying to modify the script so that it will generate the table list in a cursor before running the dml creation through a loop so that it can output the dml as an individual script per table.  I need to be able to pass a variable in to "v_table_name" and the last spool so that I can run the dml creation script against each table individually, and against the spool so that it creates individualized scripts.  Here is the script from Tim with some modifications:

SET LINESIZE 1000
SET SERVEROUTPUT ON
SET FEEDBACK OFF
SET PAGESIZE 0
SET VERIFY OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET HEADING OFF

ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

SPOOL C:\DIRECTORY\TEMP.SQL

DECLARE

  CURSOR c_columns (p_table_name  IN  VARCHAR2,
                    p_owner       IN  VARCHAR2) IS
    SELECT Lower(a.column_name) column_name,
           a.data_type
    FROM   all_tab_columns a
    WHERE  a.table_name = p_table_name
    AND    a.owner      = p_owner
    AND    a.data_type  IN ('CHAR','VARCHAR2','DATE','NUMBER','INTEGER');
    
  v_table_name  VARCHAR2(30) := Upper( %NEED_A_VARIABLE_HERE% );
  v_owner       VARCHAR2(30) := Upper('DPS');
  
  
  FUNCTION Format_Col(p_column    IN  VARCHAR2,
                      p_datatype  IN  VARCHAR2) 
    RETURN VARCHAR2 IS
  BEGIN
    IF p_datatype IN ('CHAR','VARCHAR2','DATE') THEN
      RETURN ''' || Decode(' || p_column || ',NULL,''NULL'','''''''' || ' || p_column || ' || '''''''') || ''';
    ELSE 
      RETURN ''' || Decode(' || p_column || ',NULL,''NULL'',' || p_column || ') || ''';
    END IF;
  END;
    
BEGIN

  Dbms_Output.Disable;
  Dbms_Output.Enable(1000000);
  
  Dbms_Output.Put_Line('SELECT ''INSERT INTO ' || Lower(v_owner) || '.' || Lower(v_table_name));
  Dbms_Output.Put_Line('(');
  << Columns_Loop >>
  FOR cur_rec IN c_columns (v_table_name, v_owner) LOOP
    IF c_columns%ROWCOUNT != 1 THEN
      Dbms_Output.Put_Line(',');
    END IF;
    Dbms_Output.Put(cur_rec.column_name);
  END LOOP Columns_Loop;
  Dbms_Output.New_Line;
  Dbms_Output.Put_Line(')');
  Dbms_Output.Put_Line('VALUES');
  Dbms_Output.Put_Line('(');
  
  << Data_Loop >>
  FOR cur_rec IN c_columns (v_table_name, v_owner) LOOP
    IF c_columns%ROWCOUNT != 1 THEN
      Dbms_Output.Put_Line(',');
    END IF;
    Dbms_Output.Put(Format_Col(cur_rec.column_name, cur_rec.data_type));
  END LOOP Data_Loop;
  Dbms_Output.New_Line;
  Dbms_Output.Put_Line(');''');
  Dbms_Output.Put_Line('FROM ' || Lower(v_owner) || '.' || Lower(v_table_name) );
  Dbms_Output.Put_Line('/');

END;
/

SPOOL OFF

SET LINESIZE 1000
SPOOL C:\DIRECTORY\DATA_DML_%VARIABLE_HERE_TOO%.SQL

@C:\DIRECTORY\TEMP.SQL

SPOOL OFF

SET PAGESIZE 14
SET FEEDBACK ON

Open in new window


Again, thanks to Tim Hall.  Link to the script: ORACLE-BASE
LVL 1
Vince MabarySystems and Database AdministratorAsked:
Who is Participating?
 
flow01Connect With a Mentor Commented:
And to continue the use of sqlplus scripts  (and adding another 2 loops)

you can create  another script  @dump_more_tables.sql  yourtableselection
to create such script

change the path in the call to dump_single_table  to the location you are using
example of calling
 @C:\Users\Hub\Documents\expert_exchange\dump_more_tables TEMP% NAW
= dump all tables with tablename like TEMP%  and owner like NAW
dump_single_table.sql
dump_more_tables.sql
1
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
First:  Are you sure you only have tables with the following data types: 'CHAR','VARCHAR2','DATE','NUMBER','INTEGER'

If you have others, the script you found won't get those columns.

Back to the question being asked:
You cannot mix PL/SQL code and SQL commands.  You cannot create a loop that will dynamically 'spool'.

You can follow the script's idea and have SQL generate SQL that will generate SQL but that seems overly complex.  In other words:  write some SQL that basically generates the code above once for each table to want.  Then you can have a spool command for each table.  Then execute that script to generate the individual INSERT statements.

You can also use UTL_FILE and move it all into PL/SQL and not use SPOOL.  Then you can create whatever files you want.  Issue there is the files will be generated on the database server.

Do you need the actual INSERT statements?  It is pretty simple using SQL Developer or SQLCl to generate CSV values from a table's data that can be easily used with SQL*Loader to load back up.  This method is less prone to errors as it isn't constrained by a fixed list of data types.
1
 
Vince MabarySystems and Database AdministratorAuthor Commented:
I see what you are saying.  All of the tables I will be working with contain those data types without exception.  Regrettably, for the project I am working on, I do need the insert for the sake of making a build package.  I see what you mean about the spool/loop issue.  I was mostly looking into trying that approach so that when I get to larger schemas, I would be able to set some filters (table name and owner), then let the script do the rest of the work.

I will look into your suggestion regarding UTL_FILE and the CI/Loader options.  They may be the answer to my problems.  Will update soon.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>you can create  another script  @dump_more_tables.sql  yourtableselection
to create such script

This is the SQL to generate SQL to generate SQL.  The original script was SQL to generate SQL.  The easiest is to add one more SQL generation.

Personally, I wouldn't do this.  For "Master" tables, I would have a stock build script that was stored in version control to build by baselines.  Generate those brute force and never run the generating scripts ever again?

If that is what you are after then use what flow01 posted and forget everything I mentioned about UTL_FILE.

If you have baseline tables, EXPORT them in a baseline DMP file.  Part of the build is to import the baseline data back in.


The problem is if the base tables ever change.  Then you have to mess with a lot of things no matter what.
1
 
Vince MabarySystems and Database AdministratorAuthor Commented:
I am actually building the baseline right now.  Or rebuilding it I should say.  Their previous baseline is a few years out of date, and includes what were at the time (and still are) junk tables.  I don't complain, because it gives me a perfect opportunity to learn all of the db objects, and their interactions.  I am working on some modifications to the db's, and the dev standards so that future versions are actually in version control, and the conventions are standardized enough to permit an export and go.  Thank you both!
0
 
Vince MabarySystems and Database AdministratorAuthor Commented:
Thank you flow01 for providing the solution I was looking for, and special thank you to slightwv for helping me learn some tricks for future aspects of my projects.  Much appreciated, both of you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.