Re: sqlplus script in a pl/sql procedure?

Brian Peasland wrote:

>Have a look at the DBMS_UTILITY.TABLE_TO_COMMA procedure. It is in the
>Oracle 9i Reference Guide.
>
>HTH,
>Brian
>
>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?
>>
>>

Based on the above TABLE_TO_COMMA should work. But one should always keep in mind that the
largest string it can parse is 30 bytes.

-- 
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)