1 Reply Latest reply: Mar 18, 2013 8:11 AM by sushaant RSS

    SQL script works in SQL Developer but not when scheduled

    Em
      I have a script that I can run, logged onto my server as a user with full permissions and into my database as SYSDBA, that produces a CSV file on the server when I run it from SQL Developer ON the server. HOWEVER, when I set it up as a scheduled job, using those SAME CREDENTIALS (same Windows/network user; same database user), I get no output. The job indicates that it's running successfully, but no file gets created.

      Any advice is greatly appreciated.

      Here's the script:

      WHENEVER SQLERROR EXIT FAILURE;

           set serveroutput on
           DECLARE
           my_query varchar2(5000);
           
           BEGIN
           my_query := q'[
      SELECT client_id, JOB_NAME, SCHEDULE_TYPE, TO_CHAR(START_DATE,'MM/DD/YYYY HH24:MM') AS START_DATE,
      REPEAT_INTERVAL, ENABLED, STATE, RUN_COUNT,
      TO_CHAR(LAST_START_DATE,'MM/DD/YYYY HH24:MM') AS LAST_START, LAST_RUN_DURATION,
      TO_CHAR(NEXT_RUN_DATE,'MM/DD/YYYY HH24:MM') AS NEXT_RUN
      FROM DBA_SCHEDULER_JOBS
      WHERE instr(client_id,'10.') is not null
      ORDER BY LAST_START_DATE DESC
           ]';
           
           p2k.ccsd_any_query_to_csv('HRISEDB_E_OUTPUT_MK', 'dbserver_job_output.csv',my_query);
           end;

      =================================================================
      Here's the called procedure (I don't really understand it -- I gleaned it from others on the internet):

      --------------------------------------------------------
      -- DDL for Procedure CCSD_ANY_QUERY_TO_CSV
      --------------------------------------------------------
      set define off;

      CREATE OR REPLACE PROCEDURE "CCSD_ANY_QUERY_TO_CSV" (p_dir in varchar2, p_filename in varchar2, p_query in varchar2) AUTHID CURRENT_USER
      is
      l_output utl_file.file_type;
      l_theCursor integer default dbms_sql.open_cursor;
      l_columnValue varchar2(4000);
      l_status integer;
      l_query long;
      l_colCnt number := 0;
      l_separator varchar2(1);
      l_col_desc dbms_sql.desc_tab;
      l_col_type varchar2(30);
      l_datevar varchar2(8);

      BEGIN
      l_query := 'SELECT SYSDATE FROM DUAL; ';
      dbms_sql.parse(l_theCursor, p_query, dbms_sql.native);

      dbms_sql.describe_columns(l_theCursor, l_colCnt, l_col_desc);
      l_output := utl_file.fopen( p_dir, p_filename, 'w' );
      dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

      for i in 1..l_col_desc.count LOOP
      utl_file.put( l_output, l_separator || '"' || l_col_desc(i).col_name || '"' );
      dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
      l_separator := ',';
      end loop;
      utl_file.new_line( l_output );

      l_status := dbms_sql.execute(l_theCursor);

      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
      l_separator := '';
      for i in 1 .. l_colCnt loop
      dbms_sql.column_value( l_theCursor, i, l_columnValue );
      utl_file.put( l_output, l_separator || '"' || l_columnValue || '"');
      l_separator := ',';
      end loop;
      utl_file.new_line( l_output );
      end loop;
      dbms_sql.close_cursor(l_theCursor);
      utl_file.fclose( l_output );
      execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
      exception
      when others then
      execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
      raise;
      end;

      /