pl/sql + text file

[jbpelletier]

hi,

im trying to write some data to a text file without succes.(looks like im attempting to write on the server instead of my cpu, not sure) I whant to know how would u do to write in a text file located "on" our cpu in pl/sql.

lets say how to write "hello" in C:\test.text.

any help appreciated,
jb

 

[SantaMufasa]

JB,

I saved the following code to a script called, "tt_277.sql". Then I ran the script from the SQL*Plus prompt. (Cut and paste will not work since the "set echo off" command works only to suppress scripted code.)

set echo off
spool JB.txt
set serveroutput on format wrap
begin
    dbms_output.enable(1000000);
    dbms_output.put_line('Hello');
end;
/

Here are the invocation and results of running "tt_277.sql":

SQL> @tt_277
Hello

PL/SQL procedure successfully completed.

SQL> host
C:\> type JB.txt
Hello

The "set serveroutput on format wrap" opens a connection for the "dbms_output.put_line" packaged procedure to output to your screen. The "dbms_output.enable(1000000);" command instructs the package to create a million-byte output buffer (the default is 2000 bytes).

The "spool" command writes to your client-side disk; Oracle PL/SQL's "utl_file" flat-file-manipulation package works against the server-side disks only (which probably explains your earlier problem).

Let us know if you have any follow-up questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[jbpelletier]

this "could " prolly works with my need. Do there is a way to do all "inside" the package..

cause those lines looks like SQL*Plus parameters and not PL/SQL...

set echo off
spool JB.txt
set serveroutput on format wrap

 

[SantaMufasa]

Sorry, JB, I didn't notice in your original specs that you wanted to do this from inside a package...Ooops...it's not in your original specs. <grin>

You can certainly use the "DBMS_OUTPUT.PUT_LINE" in a PL/SQL package, but whatever your outside calling environment is, it must be ready to accept the output to the screen and be able to spool that output to a file.

Otherwise, I cannot think of a way to instruct PL/SQL to output to a client-side file (especially when invoked from a PL/SQL package). If someone else can think of a way to write to a client-side file (besides via some SPOOL-like command), then please post here.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.