How can I create an empty file.

[tpbjr]

Hi All,

Is there a way to create an empty file from with in PL/SQL?

I want to create an empty file from with in PL/SQL if child records are found in a table.

Then when PL/SQL returns to the UNIX script I will check for the existance of that file and if it exists I will send an email. I know how to do the UNIX script portion but not the PL/SQL portion. Is it possible or is there an easier way?



Thank you for all your help

http://www.besware.com

Tom

 

[LKBrwnDBA]

If you only want to check a result, it's possible to set a return code (something like this):

#!/bin/ksh
#...etc...
sqlplus uid/pw <<EOF
var rc number;
exec :rc:=0;
Declare cnt number:=0;
Begin
  Select count(*) Into Cnt From child_tab;
  If Cnt > 0 Then
    :rc=1;
  End If;
End;
Exit :rc;
EOF
if [ $? -ne 0 ]
then
  mailx -s'Children exist' me@domain.com
fi

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[tpbjr]

That looks cool but it will not work for what I am doing.
My PL/SQL process must continue to run even when a child record is found. I just want a flag it so when I return to the UNIX script I can check the status.

Basically, my IF statement for checking the count has to be inside the PL/SQL. That is where I thought I could create an empty file then check for it when I get back to the UNIX script.

Maybe there is some way to force PL/SQL to return a specific error code then I could check for that value in UNIX. That would work for me too.

Does this information help you to help me?

Thank you for all your help

http://www.besware.com

Tom

 

[SantaMufasa]

Tom,

Here is a proof of concept to do what you want. (For my example, I used a SQL*Plus "ACCEPT...PROMPT" to contrive a condition to simulate your IF statement. Since I used "ACCEPT...PROMPT", I must run the code from a SQL*Plus script. I called my script "tt_337.sql".):

set verify off
accept my_response prompt "Enter 'Y' to create a file ('tom.txt') and 'N' to remove that file: "
declare
    file_handle utl_file.file_type;
    path     varchar2(500) := 'd:\dhunt\sqldba\';
    filename varchar2(200) := 'tom.txt';
begin
    if upper('&my_response') = 'Y' then
        file_handle := utl_file.fopen(
            location => path,
            filename => filename,
            open_mode => 'W',
            max_linesize => 32767);
        utl_file.put_line(file_handle,'place holder');
        utl_file.fclose(file_handle);
    else
        utl_file.fremove (path, filename); 
    end if;
end;
/

SQL> @tt_337
Enter 'Y' to create a file ('tom.txt') and 'N' to remove that file: y

PL/SQL procedure successfully completed.

SQL> host

D:\dhunt\sqldba>type tom.txt
place holder

D:\dhunt\sqldba> exit

SQL> SQL> @tt_337
Enter 'Y' to create a file ('tom.txt') and 'N' to remove that file: n

PL/SQL procedure successfully completed.

D:\dhunt\sqldba>type tom.txt
place holder

D:\dhunt\sqldba>
************************************************************************************************

Let us know if this satisfies your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

Tom,

The code, above, is correct...my copy-and-paste into Tek-Tips was, however, a bit flakey. The portion of the proof-of-concept that reads:

...
SQL> @tt_337
Enter 'Y' to create a file ('tom.txt') and 'N' to remove that file: y

PL/SQL procedure successfully completed.

SQL> host

D:\dhunt\sqldba>type tom.txt
place holder

D:\dhunt\sqldba> exit

SQL> SQL> @tt_337
Enter 'Y' to create a file ('tom.txt') and 'N' to remove that file: n

PL/SQL procedure successfully completed.

D:\dhunt\sqldba>type tom.txt
place holder [B][I](<-- This was the flakey copy-and-paste.)[/I][/B]

D:\dhunt\sqldba>

...in actuality, (correctly) read:

SQL> @tt_337
Enter 'Y' to create a file ('tom.txt') and 'N' to remove that file: y
SQL> host

D:\dhunt\sqldba>type tom.txt
place holder

D:\dhunt\sqldba>exit

SQL> @tt_337
Enter 'Y' to create a file ('tom.txt') and 'N' to remove that file: n
SQL> host

D:\dhunt\sqldba>type tom.txt
[B][I]The system cannot find the file specified.[/I][/B]

D:\dhunt\sqldba>

I hope this clarifies things.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[tpbjr]

I utilized most of you suggestion. The bottom, you got me to where I needed to be.

Thank you so much.

Thank you for all your help

http://www.besware.com

Tom