Re: Output a variable to screen/file

From: <prochak_at_my-dejanews.com>
Date: Thu, 10 Dec 1998 14:53:15 GMT
Message-ID: <74on8q$d2h$1@nnrp1.dejanews.com>

First, check your news software, you posted that message about 9times.

Now, there's multiple ways to do this. Easiest would be to use the DBMS_OUTPUT package. It gives you functions like PUT_LINE();

Then your script would look like:
DECLARE
    l_var1 VARCHAR2(2000);

BEGIN

       l_var1 := 'This line must be in my file';
      DBMS_OUTPUT.PUTLINE( l_var1);

END; But don't forget to turn the output on before running the script: SET SERVEROUTPUT ON Then there is a method I used frequently. Just return the value via a function then use the function from SQL/PLUS:

spool afile
select show_var() from dual;
spool off

You might want to turn headings off if you want only the data in the file. The function would look like:

CREATE FUNCTION show_var1 RETURN VARCHAR2 AS   l_var1 VARCHAR2(2000);
BEGIN
  l_var1:="This line must be in a file";   RETURN l_var1;
END;
/

Of course not very useful alone, but in a package this can be VERY helpful.

Next would be using the package UTIL_FILE. I haven't used this much, so I cannot help without digging through the manuals. UTIL_FILE may require your DBA to configure the output directory. Note, this creates the file on the server. If you want the file on the client, either of the first two methods will work.

Another way would be to write a host language program using Pro*C, Pro*COBOL or the like and use the file I/O there. PL/SQL is not designed for accessing host files easily. So one question you have to ask yourself is do you really want to write this in PL/SQL?

In article <366D3E15.9D5C6ACE_at_ctp.com>,   Fabian Bakker <fbakke_at_ctp.com> wrote:
> Hello,
>
> Following is a pl / sql script example.
> I need to output the variable l_var1 to a file.
> How can I do that ? (spooling / print / show / print / refcursor??)
>
> Thanx
>
> DECLARE
> l_var1 VARCHAR2(2000);
>
> BEGIN
>
> l_var1 := 'This line must be in my file';
> print l_var1;
> END;
>
>

Finally, not trying to be mean here but I have to wonder about posts like this: is this information not listed in your PL/SQL manuals?

--
Ed Prochak
Magic Interface, Ltd.
440-498-3702

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Dec 10 1998 - 08:53:15 CST