Using Linux Varibles to call SQL

Swapna173 · 06-06-2008, 01:12 AM

Hi Guys,

I need to validate certain records in sql tables inserted through sql-loader.

for this, i wrote a procedure which takes the oracle user name as parameter.

And i am running this procedure through scripting. but i need to hardcode this username(wherever i user this procedure). so instead of that i hav a file in scripting which initialized all variables.

so how can i use linux variables to call a procedure with that parameter.

Swapna

Uxinn · 06-06-2008, 03:39 AM

What are you using to test the table ? sqlplus ?
What type of script are you using to call your procedure ? bash or ?

If it's sqlplus and bash
then from within a bash script you could do

sqlplus USERNAME/PASSWORD@sid <<EOF
select * from dual;
EOF

So If you need to work with variables you can do that directly.

Swapna173 · 06-09-2008, 01:14 AM

Thanks i will try this out but how can i capture whether procedure executed successfully or not.

Uxinn · 06-10-2008, 05:00 AM

It's probably best to include
"WHENEVER SQLERROR EXIT 1"
in your sql that you are executing

Code:

--run.sql
WHENEVER SQLERROR EXIT 1
exec name_of_my_procedure();

Code:

sqlplus USERNAME/PASSWORD@sid<<EOF
WHENEVER SQLERROR EXIT 1
select * from dual;
EOF
SQLPLUS1=$?
sqlplus USERNAME/PASSWORD@sid<<EOF
WHENEVER SQLERROR EXIT 1
select * from duallllllll;
EOF
SQLPLUS2=$?

echo $SQLPLUS1
echo $SQLPLUS2

Here $SQLPLUS1 returns 0 but $SQLPLUS2 returns 1

Hope this helps,

Uxinn

Swapna173 · 07-29-2008, 02:29 AM

Thanks a lot. I will definately try this out.