Problem passsing a condition to SQL script 1

[anuktac]

hi,
I am having a problem regarding passing a flag/value to an SQL script.
I am writing a tool that creates a DAT file from a table by selecting all its data. If a CTL(control file) exists, then it executes a UNIX shell script (unload.sh). If a CTL file does not exist then it executes an SQL script(unload.sql).
Unload.sql contains some formatting and a spool command, example..

set tab off
set heading off heading off feedback off echo off verify off space 1 pagesize 0 linesize 120
spool table_name.DAT
[
set of SQL scripts
]
spool off

My problem is how to make the program understand whether CTL file exists or not. I tried inserting a flag in a table,if that flag is null then execute unload.sql.
I want to write something like :

select ctl_flag into v_ctl_flag from tmp_table;
if v_ctl_flag is null then
execute unload.sql
else
exit;
end if;

But this I can write only within a declare begin end block and cannot have a spool command in it.

I can also insert a line into a file, that will say whether CTL file exists or not...but SQL cannot read from a file (I think).

Can anyone help me with this problem?
Is there any other approach to this?
I cannot hardcode the user-id password since I have to keep the program user-id independent.
Will be glad of any help.
-Anukta

 

[Turkbear]

Hi,
One way may be to write a shell script that checks for the CTL file and if found takes one action ( calls unload.sh) and if not takes another ( calls unload.sql)..

 

[anuktac]

I tried that, but the problem is how to call unload.sql from shell script (I cannot hardcode user-id & password).

 

[Turkbear]

What do you use in unload.sh to connect?

The Sqlscript can contain a connect statement and with feedback off you would need access to the directory where the script was stored to read it..

Maybe I'm missing something.

 

[sem]

You may use /nolog switch to start sqlplus unconnected and connect from sql script using
CONNect use/password@db .

 

[anuktac]

I think I got around the problem, by prompting for user-id, passwd and database.

if ctl_file_not_found
then
echo "enter oracle user-id:"
read ora_uid
echo "enter oracle passwd:"
read ora_pwd
echo "enter database identifier:"
read ora_dbase
sqlplus $ora_uid/$ora_pwd@ora_dbase @unload.sql
fi

-Anukta

 

[anuktac]

I missed the '$' in the syntax.
it should read:
sqlplus $ora_uid/$ora_pwd@$ora_dbase @unload.sql
-Anukta

 

[sem]

You may also ask for username/password within sql script:

accept username prompt 'Enter Name '
accept password prompt 'Enter Password ' hide
accept database prompt 'Enter DB '
connect &&username/&&password@&&database