UNIX for Dummies Questions & Answers

I am using a oracle query from unix..

flag=`sqlplus -s <<EOF
SELECT 'Y' FROM table_name
WHERE cond1 = '${table_name}'
AND DECODE('${var_a}','''NA''',own,'${var_b0}')= own
exception
when no_data_found then
dbms_output.put_line(NVL(l_owner_flag,'X'));
end;
EOF`

Its not validating the value for NA.

quote problems:

Just remove the triple quotes from around NA.
Should only be: 'NA'

I tried with 'NA' also. But when i give the input value as 'NA' it is not getting validated. Its returning the value as null.

Here is a sample code for connecting to Oracle from unix...
Try modifying your script accordingly. Problem could be because you have not used SET commands inside the script

flag=`sqlplus -s<<-EOsqlplus
$Login/$psswd@$con_strng
WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT FAILURE
SET TERM OFF
SET HEAD OFF
SET VERIFY OFF
SET FEEDBACK OFF
select to_char(sysdate,'yyyymmdd') from dual;
EXIT;
EOsqlplus`

Change the above select query and give yours.

Thanks for ur reply mahish, what i want is how to validate the string ie by using the decode function. If i give input value as na....it should validate with the owner..but its not taking it up..

Well, before we try running the thing blind again....

Can we do this:

Code:

cat << EOF
SELECT 'Y' FROM table_name
WHERE cond1 = '${table_name}'
AND DECODE('${var_a}','''NA''',own,'${var_b0}')= own
exception
when no_data_found then
dbms_output.put_line(NVL(l_owner_flag,'X'));
end;
EOF

... so we can see how this SQL gets expanded?

We're running a little blind here. For all we know,
the variables could be mucking up the SQL.

Plus, i don't know about that "own exception" line...

I recommend cobbling together the SQL without UNIX.
UNIX isn't the problem here.

One can pass operating system variables to sqlplus using this syntax:
sqlplus username/password @cmdfile.sql var1 var2 var3
Parameter var1 will be mapped to SQL*Plus variable &1, var2 to &2, etc. Look at this example:
sqlplus scott/tiger @x.sql '"test parameter"' dual
Where x.sql consists of:
select '&1' from &2;
exit 5;...

Regards
w020637