Unanswered: Need strong PL/SQL buff to solve this problem..
I have been trying to create a procedure that will spool the results of a record set in a global temporary table to a file. If ran separately, the statements work, but if ran as a procedure I get the error: "Problem encoutered with CREATE statement".
I have never tried to spool a recordset inside of a cursor, so I hope that is not causing part of the problem. Thanks to any gurus that know the answer. Also, I am running Oracle 220.127.116.11.0
Here is the code I am using:
CREATE OR REPLACE PROCEDURE TAX.DATA_ARCHIVE
/*SET FEEDBACK OFF;
SET HEADING ON;
SET VERIFY OFF;
SET PAGESIZE 0;
SET LINESIZE 136;
SET TERMOUT OFF;
Cursor tax_cursor IS
Select distinct company, report_year, report_month from test_watlas;
2) Use the DBMS_UTILITY.exec_ddl_statement() procedure. This accepts the DDL as a parameter in the same way as execute immediate.
However, the problem with both of these methods is that any reference to the global temp table will also need to be created dynamically because the procedure will not compile if the table doesn't already exist. So you'll need to wrap the insert statement up in one of the above methods also.
The other show stopper is that the SPOOL command is a SQL*Plus command and will not work. You need to make use of the UTL_FILE package or a java procedure if you want to output to an OS file from within a stored procedure.
Thanks for the reply. I was afraid that the SPOOL command could not be used. I have never tried to output to an OS file system from Oracle, so this helps alot. I will incorporate these changes to the procedure.
--I am a SQL Server 2000 DBA, so most of the nuances in Oracle are still left to me found-- In SQL Server, creating a temp table in a proc is not a problem and and their export utility (BCP) works well inside of procs once you figure out the snytax. But as with most microsoft products it has its drawbacks too.