Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Posts
    2

    Question 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 8.1.6.3.0
    Here is the code I am using:



    -----begin SQL-----

    CREATE OR REPLACE PROCEDURE TAX.DATA_ARCHIVE
    AS
    /*SET FEEDBACK OFF;
    SET HEADING ON;
    SET VERIFY OFF;
    SET PAGESIZE 0;
    SET LINESIZE 136;
    SET TERMOUT OFF;
    */
    --declare
    filename1 varchar2(7);
    fullpath varchar2(20);
    filepath varchar2(20);
    reportyear varchar2(20);
    reportmonth varchar2(20);
    curcompany varchar2(20);
    Cursor tax_cursor IS
    Select distinct company, report_year, report_month from test_watlas;
    tax_vars tax_cursor%ROWTYPE;


    CREATE GLOBAL TEMPORARY TABLE WATLAS_TEMP
    (
    TRAN_ID NUMBER(28),
    REPORT_STATE VARCHAR2(2),
    REPORT_YEAR NUMBER,
    REPORT_MONTH NUMBER,
    COMPANY VARCHAR2 (10),
    SCHEDULE_CODE VARCHAR2 (4),
    CARRIER_NAME VARCHAR2 (35),
    CARRIER_FEIN VARCHAR2 (15),
    CARRIER_CONTROL_NAME VARCHAR2 (4),
    MODE_CODE VARCHAR2 (3),
    ORIGIN_NAME VARCHAR2 (35),
    ORIGIN_CITY VARCHAR2 (35),
    ORIGIN_STATE VARCHAR2 (2),
    ORIGIN_CODE VARCHAR2 (15),
    DESTINATION_NAME VARCHAR2 (35),
    DESTINATION_CITY VARCHAR2 (35),
    DESTINATION_STATE VARCHAR2 (2),
    DESTINATION_CODE VARCHAR2 (15),
    CONSIGNOR_NAME VARCHAR2 (35),
    CONSIGNOR_FEIN VARCHAR2 (15),
    CONS_CONTROL_NAME VARCHAR2 (4),
    MANIFEST_DATE DATE,
    MANIFEST VARCHAR2 (15),
    NET NUMBER (28),
    GROSS NUMBER (28),
    BILLED NUMBER (28),
    PRODUCT VARCHAR2 (3),
    POSITION_HOLDER VARCHAR2 (35),
    POSITION_FEIN VARCHAR2 (15),
    ORIGIN_OPTION CHAR (1),
    CONS_QUALIFIER VARCHAR2 (2),
    CARRIER_QUALIFIER VARCHAR2 (2),
    DEST_OPTION CHAR (1),
    POSITION_QUALIFIER VARCHAR2 (2),
    POSITION_CONTROL_NAME VARCHAR2 (4),
    POSITION_637 VARCHAR2 (15),
    EXCHANGE_FLAG CHAR (1),
    CDE_TRANS_TYPE VARCHAR2 (4),
    DTM_UPD DATE)
    ON COMMIT DELETE ROWS;

    begin
    open tax_cursor;
    loop
    fetch tax_cursor into tax_vars;
    exit when tax_cursor%NOTFOUND;

    filepath := 'c:\';

    fullpath := filepath || tax_vars.company || To_char(tax_vars.report_month) || to_char(tax_vars.report_year);

    INSERT INTO watlas_temp
    (
    TRAN_ID,
    REPORT_STATE,
    REPORT_YEAR,
    REPORT_MONTH,
    COMPANY,
    SCHEDULE_CODE,
    CARRIER_NAME,
    CARRIER_FEIN,
    CARRIER_CONTROL_NAME,
    MODE_CODE,
    ORIGIN_NAME,
    ORIGIN_CITY,
    ORIGIN_STATE,
    ORIGIN_CODE,
    DESTINATION_NAME,
    DESTINATION_CITY,
    DESTINATION_STATE,
    DESTINATION_CODE,
    CONSIGNOR_NAME,
    CONSIGNOR_FEIN,
    CONS_CONTROL_NAME,
    MANIFEST_DATE,
    MANIFEST,
    NET,
    GROSS,
    PRODUCT,
    POSITION_HOLDER,
    POSITION_FEIN,
    ORIGIN_OPTION,
    CONS_QUALIFIER,
    CARRIER_QUALIFIER,
    DEST_OPTION,
    POSITION_QUALIFIER,
    POSITION_CONTROL_NAME,
    POSITION_637,
    EXCHANGE_FLAG ,
    CDE_TRANS_TYPE,
    DTM_UPD,
    BILLED)
    Select
    TRAN_ID,
    REPORT_STATE,
    REPORT_YEAR,
    REPORT_MONTH,
    COMPANY,
    SCHEDULE_CODE,
    CARRIER_NAME,
    CARRIER_FEIN,
    CARRIER_CONTROL_NAME,
    MODE_CODE,
    ORIGIN_NAME,
    ORIGIN_CITY,
    ORIGIN_STATE,
    ORIGIN_CODE,
    DESTINATION_NAME,
    DESTINATION_CITY,
    DESTINATION_STATE,
    DESTINATION_CODE,
    CONSIGNOR_NAME,
    CONSIGNOR_FEIN,
    CONS_CONTROL_NAME,
    MANIFEST_DATE,
    MANIFEST,
    NET,
    GROSS,
    PRODUCT,
    POSITION_HOLDER,
    POSITION_FEIN,
    ORIGIN_OPTION,
    CONS_QUALIFIER,
    CARRIER_QUALIFIER,
    DEST_OPTION,
    POSITION_QUALIFIER,
    POSITION_CONTROL_NAME,
    POSITION_637,
    EXCHANGE_FLAG ,
    CDE_TRANS_TYPE,
    DTM_UPD,
    BILLED
    From test_watlas
    Where Report_month = tax_vars.report_month AND
    Report_year = tax_vars.report_year AND
    Company = tax_vars.company;

    -- dbms_output.put_line('Test');
    -- dbms_output.put_line(fullpath);

    SPOOL fullpath
    Select
    TRAN_ID || ';' ||
    REPORT_STATE || ';' ||
    REPORT_YEAR || ';' ||
    REPORT_MONTH || ';' ||
    COMPANY || ';' ||
    CHEDULE_CODE || ';' ||
    CARRIER_NAME || ';' ||
    CARRIER_FEIN || ';' ||
    CARRIER_CONTROL_NAME || ';' ||
    MODE_CODE || ';' ||
    ORIGIN_NAME || ';' ||
    ORIGIN_CITY || ';' ||
    ORIGIN_STATE || ';' ||
    ORIGIN_CODE || ';' ||
    DESTINATION_NAME || ';' ||
    DESTINATION_CITY || ';' ||
    DESTINATION_STATE || ';' ||
    DESTINATION_CODE || ';' ||
    CONSIGNOR_NAME || ';' ||
    CONSIGNOR_FEIN || ';' ||
    CONS_CONTROL_NAME || ';' ||
    MANIFEST_DATE || ';' ||
    MANIFEST || ';' ||
    NET || ';' ||
    GROSS || ';' ||
    PRODUCT || ';' ||
    POSITION_HOLDER || ';' ||
    POSITION_FEIN || ';' ||
    ORIGIN_OPTION || ';' ||
    CONS_QUALIFIER || ';' ||
    CARRIER_QUALIFIER || ';' ||
    DEST_OPTION || ';' ||
    POSITION_QUALIFIER || ';' ||
    POSITION_CONTROL_NAME || ';' ||
    POSITION_637 || ';' ||
    EXCHANGE_FLAG || ';' ||
    CDE_TRANS_TYPE || ';' ||
    DTM_UPD || ';' ||
    BILLED
    FROM WATLAS_TEMP
    WHERE
    Report_month = tax_vars.report_month AND
    Report_year = tax_vars.report_year AND
    Company = tax_vars.company;

    SPOOL OFF;

    COMMIT;


    end loop;
    close tax_cursor;
    end;

  2. #2
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    There are several things wrong with this.

    First, the CREATE statement is a DDL statement. You can't run DDL from within a stored procedure.

    To solve this problem, two options spring to mind.

    1) Wrap the create statement in 'EXECUTE IMMEDIATE' ie.
    Code:
    execute immediate ('
      CREATE GLOBAL TEMPORARY TABLE WATLAS_TEMP 
       (TRAN_ID NUMBER(28), 
        REPORT_STATE VARCHAR2(2), 
        REPORT_YEAR NUMBER, 
        REPORT_MONTH NUMBER, 
        COMPANY VARCHAR2 (10), 
        ....
        ....
        POSITION_QUALIFIER VARCHAR2 (2), 
        POSITION_CONTROL_NAME VARCHAR2 (4), 
        POSITION_637 VARCHAR2 (15), 
        EXCHANGE_FLAG CHAR (1), 
        CDE_TRANS_TYPE VARCHAR2 (4), 
        DTM_UPD DATE) 
        ON COMMIT DELETE ROWS
      ');
    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.

  3. #3
    Join Date
    Feb 2002
    Posts
    2

    Thumbs up

    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.

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •