Doubt in Oracle connecting unix - Very urgent


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Doubt in Oracle connecting unix - Very urgent
# 1  
Old 02-25-2009
Doubt in Oracle connecting unix - Very urgent

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.
# 2  
Old 02-25-2009
quote problems:

Just remove the triple quotes from around NA.
Should only be: 'NA'
# 3  
Old 02-26-2009
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.
# 4  
Old 02-26-2009
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.
# 5  
Old 02-26-2009
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..
# 6  
Old 02-26-2009
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.
# 7  
Old 02-26-2009
Try this...

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
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Clarify doubt ... plz Urgent

Hi friends, I am new to UNIX. I going to transfer files using SFTP. I am writing a script and using mget . If i am using mget * means, if all the files and their sub directories are transferred or not? If suppose , the local system dose not have the sub directory then what will... (1 Reply)
Discussion started by: punitha
1 Replies

2. Shell Programming and Scripting

Help need Connecting to Oracle DB from Unix

I have a table called CLARIFICATION in one of my schema in Oracle Data Base. I have to connect to oracle Data Base from Unix and compare the one of the column(Exec_Date) in CLARIFICATION table with Sysdate.If Exec_Date = Sysdate go head and run rest of the script otherwise exit out with out... (2 Replies)
Discussion started by: veeru
2 Replies

3. UNIX for Dummies Questions & Answers

Connecting to Unix server through Oracle

Hi, I need some help regarding oracle with unix script Actually i have a shell script and i want that it gets triggered through a oracle Db.I dont have any idea how is it done, even whether it is possible....pls help.....!!!! (3 Replies)
Discussion started by: fidelis
3 Replies

4. Shell Programming and Scripting

Connecting to Oracle by using ODBC on UNIX

Hi All, I have a requirement where I need to connect to Oracle database which is installed on WINDOWS machine. I dont have Oracle client installed on my AIX so i can't use SQLPLUS command. But ODBC to connect to Oracle is configured on my UNIX server. Can any one help me with the steps of... (1 Reply)
Discussion started by: Raamc
1 Replies

5. Shell Programming and Scripting

problem with connecting sftp server(urgent please)

Hi all, I have command to connect gateway server iam in home directory/> ssh root@mrp-gateway root@mrp-gateway:/root> sftp -v msgGoogle@126.132.45.123 sftp/>dir upload --> folder sftp/upload/ls ------------- 8990.txt kittu.txt 8989.txt i have an requirement to print files list which... (2 Replies)
Discussion started by: kittusri9
2 Replies

6. Shell Programming and Scripting

Connecting to Oracle Database

if; sql="select username from dba_users where username = '$FromUser';" check_FromUser=`ExecSql "$sql"` I want to connect to Oracle database & check the users in the database, store the value in check_FromUser. First how to connect to Oracle database from the shell script ? Appreciate your... (4 Replies)
Discussion started by: dreams5617
4 Replies

7. Shell Programming and Scripting

Awk Doubt _ Urgent

Guys I have the file TEST.csv generated after a join of two different files with the same columns: key,string,data,number,key,string,data,number abc,test,020202,3,abc,test,010305,4 abc,level,070202,9,abc,tool,010203,7 def,tool,010101,7,,,, ghi,,,ghi,test,010203,8 I have to generate a... (2 Replies)
Discussion started by: Rafael.Buria
2 Replies

8. UNIX for Advanced & Expert Users

Connecting to Oracle through unix shell scripts

Hi, Can some one help me in connecting to oracle through unix shell scripts with examples. Regards Narayana Gupta (1 Reply)
Discussion started by: guptan
1 Replies

9. Shell Programming and Scripting

doubt???? urgent

How to embed a shell script into a C++ program...do kinbdly help me by giving an example.... Thanking you... (1 Reply)
Discussion started by: vas_dba
1 Replies

10. UNIX for Dummies Questions & Answers

Unix + oracle doubt....involving shell script

....does the dbms_output.put_line work inside unix shell script? i mean this is to be inside the sqlplus connection as follows!! sqlplus -s $UP <<EOJ .. .. .. dbms_output.put_line ('Insertion procedure failed for UPC BC : ' || wk_key_value || ' Sqlcode: ' || SQLCODE || ' Error... (2 Replies)
Discussion started by: mexx_freedom
2 Replies
Login or Register to Ask a Question

Featured Tech Videos