Re: Perform Subscript from File in PL/SQL

rainer.hirthammer_at_software-objects.com (Rainer Hirthammer) wrote in message news:<3d9aac27.767543_at_news.btx.dtag.de>...
> Thank you. My intention is to embed update-scripts in an enclosing
> body, where I first check the conditions for update and finally I
> evaluate the results.
> I would be very pleased if I could accomplish to read a textfile into
> a varchar and execute immediate it (at client-side, no UTL_FILE (?)),
> is there a way to?
>
> On 1 Oct 2002 11:18:06 -0700, Mark.Powell_at_eds.com (Mark D Powell)
> wrote:
> >rainer.hirthammer_at_software-objects.com (Rainer Hirthammer) wrote in message news:<3d99b782.8900027_at_news.btx.dtag.de>...
> >> How can I do this:
> >> ...
> >> BEGIN
> >> @subscript.sql;
> >> EXCEPTION
> >> ...
> >Rainer, in general you don't. Instead you would write a sqlplus
> >script that executed several plsql scripts.
> >
> >If you need to run scripts based on information determined in the
> >plsql scripts then you can potentially use a sqlplus variable and have
> >the plsql scripts or sql statements set the variable value and then
> >execute a script where the variable is the script name: start
> >variablescript..sql
> >
> >You can also sometimes substitute dynamic sql, execute immediate or
> >dbms_sql, for the script.
> >
> >Also you can use the spool command to create sql files that you
> >populate via select as in select 'select '||col1||... from table_a
> >where ... and then execute these.
> >
> >Alternately, you have to resort to using extenal procedures or the
> >interprocess communication routines: dbms_alert and dbms_pipe to send
> >commands/information between sessions.
> >
> >Some combination of the above methods will generally allow you to do
> >what you need. The problem is selecting the best one for the task.

Your ability to read a text file at the client and pass the contents to Oracle will depend on what your client software is! Oracle Forms has a text_io function that is similar to utlfile. And if you write in Pro*C you can read using/write using the normal C IO functions. We have several VB applications that read data files and insert into Oracle.

There are many options depending on what you really want to do and how often it will be done and by who (DBA, developer, end-user), and a very important consideration is if you need to pull sets of data back.

Topics to research: stored procedures, packages, reference cursors

HTH -- Mark D Powell -- Received on Wed Oct 02 2002 - 14:04:27 CDT