jim agans wrote:
>hello ...
>
>I would like to generate a text file to a network drive from a Oracle
>table.
>The reason I would like to do it (generate a text file from pl/sql)is
>it can be set to run 1 time a month that way thru submit_job.
>this script generates the file from a sqlplus session:
>
>
>CLEAR BUFFER;
>set lines 300;
>set pagesize 50000;
>set colsep ""
>set feed off
>set head off
>--
>col SS_NUMBER for a9
>col ENTRY_CODE FOR a3
>col PROCESSING_CODE FOR a3
>col LAST_NAME FOR a13
>col FIRST_NAME FOR a10
>col MIDDLE_NAME FOR a7
>col DATE_OF_BIRTH FOR a8
>col SEX_CODE FOR a1
>col DATE_REPORT_PREPARED FOR a8
>col SUBJECTS_DRIVERS_LIC FOR a20
>col SUBJECTS_DRIVERS_LIC_STATE FOR a2
>col DATE_WARRANT_ISSUED FOR a8
>col BLANKS1 FOR a3
>col NCIC FOR a10
>col BLANKS2 FOR a3
>col ORI_NUMBER FOR a9
>col WARRANT_ISSUING_STATE FOR a2
>col BLANKS3 FOR a3
>col OCA_NUMBER FOR a20
>col OFFENSE_CODE FOR a4
>col REPORTER_ID_CODE FOR a4
>col WARRANT_ISSUING_AGENCY_NAME FOR a30
>col WARRANT_NUMBER FOR a20
>col SUBJECTS_RACE FOR a1
>col WARRANT_ISSUING_AGENCY_PHONE FOR a10
>col BLANKS4 FOR a30
>col FBI_NUMBER FOR a12
>col OFFENSE_TYPE FOR a20
>col OFFENSE_CHRG_SYMBOL FOR a1
>col BLANKS5 FOR a26
>spool C:\EVSFUFEL;
>select * from arcfiles.EVSFUFEL;
>spool off;
>
>
>Can you call a script from pl/sql?
>by the way...what is the sqlplus comand to not show the commands that
>run in a script?
>
>
You are approaching this backward. Put your code in a stored procedure
using UTL_FILE and execute it from DBMS_JOB.
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Mon Sep 15 2003 - 20:56:17 CDT
