Re: sqlplus script in a pl/sql procedure?

Hi Jim

If I understand correctly you are asking to call your sqlplus script from a pl/sql dbms_job. This is a little tricky as sqlplus is a client side product - so calling from the dbms server is not straighforward.

The obvious solution if you want to schedule a client side job would be to use the operating system scheduler rather than the dbms. By the look of your filenames you are on a Windows platform, so look at Accessories/System Tools/Scheduled Tasks.

If you want to do this on the server side then look at the UTL_FILE plsql package.

KJP 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?
Received on Mon Sep 15 2003 - 17:53:24 CDT