Home » Infrastructure » Unix » How to pass parameter from file to sqlplus in UNIX? (aix)
How to pass parameter from file to sqlplus in UNIX? [message #626929] Wed, 05 November 2014 06:50 Go to next message
pallvi
Messages: 3
Registered: November 2014
Junior Member
Hi,

I have one variable in which i am putting file content and in file account number are there, i have put quotes in file and putting that file in d variable.

d='cat u.txt'

and file content are

"'32432432432','32432432432','2343243223432'"

sqlplus -s user/password@servername @file.sql $d >> file1.txt

and file.sql have below code

define acct = &1;
select * from account where acct in ( &acct);
exit;


however this is not working, i have put set echo on in .sql, seems lile variable is not showing in .sql file.

Please help me how i can achieve this.


Re: How to pass parameter from file to sqlplus in UNIX? [message #626934 is a reply to message #626929] Wed, 05 November 2014 07:45 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3171
Registered: May 2013
Location: World Wide on the Web
Senior Member
Welcome to the forum!

Please use code tags to separate the code part from description of issue.

If I correctly understand, you want to SPOOL the SQL*Plus result into a file. All you need to do is use SPOOL <filename> inside the sqlplus session.
Re: How to pass parameter from file to sqlplus in UNIX? [message #626935 is a reply to message #626934] Wed, 05 November 2014 07:51 Go to previous messageGo to next message
pallvi
Messages: 3
Registered: November 2014
Junior Member
Hi lalit,

i will use code tag , but please let me know how i can edit this post, i am new in this fourm

No i want my variable is pass to sqlplus i also used spool on in .sql file, but seems like in sqlplus variable is not coming from unix.
Re: How to pass parameter from file to sqlplus in UNIX? [message #626936 is a reply to message #626934] Wed, 05 November 2014 07:52 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Also, depending on what the SQL script expects in the variable, your might have to use backticks instead of single quotes when asigning the variable. See this example:

/tmp $ echo "Test" >tmp.file
/tmp $ d='cat tmp.file'
/tmp $ echo $d
cat tmp.file
/tmp $ d=`cat tmp.file`
/tmp $ echo $d
Test


Re: How to pass parameter from file to sqlplus in UNIX? [message #626939 is a reply to message #626936] Wed, 05 November 2014 08:04 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3171
Registered: May 2013
Location: World Wide on the Web
Senior Member
@OP, does this help http://www.orafaq.com/forum/t/79149/2
Re: How to pass parameter from file to sqlplus in UNIX? [message #626940 is a reply to message #626939] Wed, 05 November 2014 08:06 Go to previous messageGo to next message
pallvi
Messages: 3
Registered: November 2014
Junior Member
I replied @ also, that also not worked
Re: How to pass parameter from file to sqlplus in UNIX? [message #626943 is a reply to message #626940] Wed, 05 November 2014 08:12 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well "not work" is pretty much useless as an error description. You have to show us what really happens, like this for example:

/tmp $ cat test.sql
define acct = &1;
select &acct from dual;
exit;

/tmp $ sqlplus user/pass @test.sql 999

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 5 15:10:21 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

old   1: select &acct from dual
new   1: select 999 from dual

       999
----------
       999

Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
/tmp $

Re: How to pass parameter from file to sqlplus in UNIX? [message #626944 is a reply to message #626940] Wed, 05 November 2014 08:13 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3171
Registered: May 2013
Location: World Wide on the Web
Senior Member
Sorry, but "not worked" doesn't help to understand the issue. Could you please elaborate and I would like to see a test case. Show us what are you doing using copy paste.
Re: How to pass parameter from file to sqlplus in UNIX? [message #626945 is a reply to message #626940] Wed, 05 November 2014 08:13 Go to previous message
Michel Cadot
Messages: 67135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"Not worked" does not help in any way to know how it does not work.
Copy and paste what you have, do and get as Thomas did.

Previous Topic: SP2-0670: Internal number conversion failed even if I return a number
Next Topic: file check in a directory
Goto Forum:
  


Current Time: Fri May 29 02:33:05 CDT 2020