Re: Newbie question

In article <890h14$mhd$1_at_tilde.csc.ti.com>,   "Dave Hedgehog" <K.Ratcliff_at_myteethbtinternet.com> wrote:
> Hope somebody can help me out with this....
>
> We have a EDI package which uses an Oracle 8 database.
> I have been asked to write an PL/SQL stored procedure to access this
> database & extract some information
> for reporting purposes (possibly using UTL_FILE ?).
> I don't have a problem writing & executing any SQL through SQL*PLUS in
> windows but how would I go about
> executing the stored procedure in a live environment through a UNIX
> automated script ? Is this possible ?
> Thanks in advance for any help.
> Keith
>
> --
> ---------------------ANTI SPAM -------------------
> Please remove myteeth before replying
>
>

Sure it is possible. There are wany ways to extract data from oracle db. Here are two:

1. Write a sql script file (on unix):

spool /user/local/your_db_name/output.txt

• your sql script to select data:

select * from table1;
select * from table2;
....

spool off;
/
exit;
/

• end of script.

Then run it on unix:

sqlplus your_username/your_password_at_your_db_name @your_script_file

2. Write a store procedure (package will be better), load it into oracle db. Then call it
begin
 Package_name.your_store_procedure_name (parameters such as the export file name, etc);
end;

In your store procedure, you will call Oracle's UTL_FILE package (make sure in your database's init.ora file to specify the directory you are going to write to).

You can find the syntax to write package in any oracle PL/SQL programming book.

Hope this helps.

Guang

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Feb 23 2000 - 11:49:13 CST