Home » SQL & PL/SQL » Client Tools » sqlplus &1 not working??? (10.2)
sqlplus &1 not working??? [message #399939] Fri, 24 April 2009 10:54 Go to next message
hrothgarv
Messages: 4
Registered: April 2009
Junior Member
Hi,

I'm going crazy over this issue in running sqlplus from the command-line with substitution variables. I google'd the problem, saw one similar incident but no resolution. here's the problem: I'm running windows sqlplus (version 10.2):

test.sql
--------
select '&1' from dual;

from the commandline, i execute
sqlplus <usr/pwd@instance> test.sql 'blahblahblah'

blahblahblah is not used to substitute &1 in test.sql, rather it uses my full-path glogin.sql location, thus the result is:

old 1: select '&1' from dual
new 1: select 'Files\Oracle\oracle10g\10.2.0.2\sqlplus\admin\glogin.sql' from dual

any help???

Thanks
Re: sqlplus &1 not working??? [message #399944 is a reply to message #399939] Fri, 24 April 2009 11:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read SQL*PlusĀ® User's Guide and Reference to know how to call a script from command line.

Regards
Michel
Re: sqlplus &1 not working??? [message #399946 is a reply to message #399939] Fri, 24 April 2009 11:06 Go to previous messageGo to next message
hrothgarv
Messages: 4
Registered: April 2009
Junior Member
hi,

thanks. i've already looked at that... i never had any problems using sqlplus via command line, except for this one, and i'm stumped.

maybe i'm mind is so foucsed on the wrong that i decided to ask for help...
Re: sqlplus &1 not working??? [message #399950 is a reply to message #399946] Fri, 24 April 2009 11:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You wrongly use the syntax maybe till now you were lucky.

Regards
Michel
Re: sqlplus &1 not working??? [message #399970 is a reply to message #399939] Fri, 24 April 2009 13:15 Go to previous messageGo to next message
hrothgarv
Messages: 4
Registered: April 2009
Junior Member
hi,

i know i'm just new here, but your posts are not really helping. there's nothing wrong with the syntax I use. here's link to the same issue that i am experiencing.

http://bytes.com/groups/oracle/65782-sql-plus-parameters

i run the script inside sqlplus without any problems. it's just when i do everything from the command-line that this issue appears.

if you know why this is happening, then provide the answer. if not let the ones who REALLY know reply.
Re: sqlplus &1 not working??? [message #399978 is a reply to message #399970] Fri, 24 April 2009 13:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I just wanted to reply you a "helpful" answer but your insult let me with no voice.
Be polite or wait for long before getting a "helpful" answer.

Regards
Michel
Re: sqlplus &1 not working??? [message #399979 is a reply to message #399939] Fri, 24 April 2009 14:11 Go to previous messageGo to next message
BlackSwan
Messages: 22927
Registered: January 2009
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.


Re: sqlplus &1 not working??? [message #399982 is a reply to message #399939] Fri, 24 April 2009 14:50 Go to previous messageGo to next message
hrothgarv
Messages: 4
Registered: April 2009
Junior Member
hi,

apologies for any uproars i might have caused. i'm also just replying to the post replies - never intended to insult nor offend anyone.

i'm still investigating what's causing the issue regarding my original post, and if i ever i find the cause, i'd be posting it here to help those who experience the same.

thanks,
Re: sqlplus &1 not working??? [message #399983 is a reply to message #399939] Fri, 24 April 2009 14:51 Go to previous messageGo to next message
BlackSwan
Messages: 22927
Registered: January 2009
Senior Member
Your command line contains incorrect syntax.
Re: sqlplus &1 not working??? [message #401148 is a reply to message #399982] Fri, 01 May 2009 17:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7997
Registered: November 2002
Location: California, USA
Senior Member
You DO have syntax errors in what you posted. You are missing the @ or start and the < and > do not belong there. You may not be running what you posted. This is one of the reasons that we ask you to post a copy and paste of an actual run, so we can see what you actually ran.

Also, you have not made it clear whether you are trying to execute from the SQL*Plus command line or the Windows operating system command prompt. Compare what I have posted below to what you have posted.

Also, if your login.sql or glogin.sql script has an &1 in it for which a value has been assigned or if a value is assigned in some other way such as "define 1 = 'some_path'", then you have a conflict there. Make sure that your test.sql file is not using any of the same variables. You may need to pass a dummy value for that, followed by another value for your test.sql, like:

@test.sql 'dummy', 'blahblahblah'

If you can't figure it out, then post the contents of your login.sql and glogin.sql.

-- from SQL*Plus command line:
SCOTT@orcl_11g> @test.sql 'blahblahblah'
SCOTT@orcl_11g> select '&1' from dual;
old   1: select '&1' from dual
new   1: select 'blahblahblah' from dual

'BLAHBLAHBLA
------------
blahblahblah

SCOTT@orcl_11g>


-- from windows command prompt:
c:\app\Barbara\product\11.1.0\db_1\BIN>sqlplus.exe scott/tiger@orcl @c:\oracle11g\test.sql 'blahblahblah'

SQL*Plus: Release 11.1.0.6.0 - Production on Fri May 1 15:21:28 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

old   1: select '&1' from dual
new   1: select 'blahblahblah' from dual

'BLAHBLAHBLA
------------
blahblahblah

SQL>

[Updated on: Fri, 01 May 2009 17:37]

Report message to a moderator

Re: sqlplus &1 not working??? [message #418043 is a reply to message #401148] Wed, 12 August 2009 12:52 Go to previous message
jecdba
Messages: 1
Registered: August 2009
Location: California
Junior Member
This is a Windows problem with Oracle. I suspect your Windows registry shows Oracle Home as "C:\Program Files\Oracle\" etc.

In attempting to execute your glogin.sql script, it is trying to run "C:\Program" with a parameter of "Files\Oracle\...\glogin.sql".

I suggest you edit your Windows registry (Start/run: regedit), find "ORACLE_HOME" and change the "C:\Program Files" part of the path to "C:\PROGRA~1". (On the off chance it is not "PROGRA~1", you might want to run "dir /x C:\" to verify and/or find out what it is).

To be safe I changed the ORACLE_HOME/bin entry in my PATH as well.

The other possibility is to reinstall with "PROGRA~1" instead of "PROGRAM FILES" as the high order install directory.

Previous Topic: How to enable editing in Toad
Next Topic: SQL Script to check status of last command
Goto Forum:
  


Current Time: Fri Oct 31 22:30:05 CDT 2014

Total time taken to generate the page: 0.10070 seconds