Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> calling sql script from pl/sql

calling sql script from pl/sql

From: John Hume <jdhume_at_earthlink.net>
Date: 19 Apr 2002 18:47:02 -0700
Message-ID: <749ff512.0204191747.2bf912c6@posting.google.com>


Hi,

I wrote a sql script that takes a table name and output filename as parameters, and generates a "CREATE TABLE" statement for the table. I can call the script from SQL*Plus using the following syntax:

@c:\GenerateDDL.sql MY_TABLE output.sql

This works just fine, and it spools an output file that contains the DDL statement for the specified table. But now I want to create DDL statements for a bunch of tables automatically by querying user_tables and user_tab_columns. I tried writing a separate PL/SQL block where the loop looks like:

FETCH table_cur INTO current_table;
WHILE table_cur%FOUND LOOP

   @c:\GenerateDDL.sql current_table current_table;    FETCH table_cur INTO current_table;
END LOOP; This did not work because PL/SQL doesn't understand the "@" call. Is there a way to call GenerateDDL.sql (exactly as written) using SQL*Plus from within the PL/SQL block? I cannot use UTL_FILE to create the DDL files because the init.ora file on our server is not set up to support it (and changing it is not an option). DBMS_PIPE will not work because the user I'm using to execute the script does not have permission to that object (and granting permission is not an option either).

Any help is much appreciated!

-John Received on Fri Apr 19 2002 - 20:47:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US