This discussion is archived
9 Replies Latest reply: Mar 2, 2012 5:10 PM by Dave Rabone RSS

Sqlplus and passing system parameters &1 is not getting the value passed

809498 Newbie
Currently Being Moderated
We are having an issue with &1 (actual value) being passed to a sql file. I am assuming this is an installation issue, but cant find the answer.

Below I have example 1 where it works on my PC

In a test.sql file I have:
select &1, &2 from dual;
exit;

When sqlplus is called passing 2 parameters on my computer it works as expected by receiving the 2 numbers in the sql file.
C:\>sqlplus scott/tiger @test.sql 1 2

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Oct 30 10:33:10 2010

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

old 1: select &1, &2 from dual
new 1: select 1, 2 from dual

1 2
---------- ----------
1 2

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64
bit Production
With the Partitioning, Data Mining and Real Application Testing options

C:\>

In example 2: Using the same test.sql file that should receive 1 and 2 and select that from dual, on a different computer we are having thiis issue where parameter 1 (&1) is not actually receiving the parameter, but instead receiving glogin.sql as the first parameter???


C:\>sqlplus scott/tiger @test.sql 1 2

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Oct 30 11:09:10 2010

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

old 1: select &1, &2 from dual
new 1: select Files\oracle\product\10.2.0\client_1\sqlplus\admin\glogin.sql, 2
from dual
select Files\oracle\product\10.2.0\client_1\sqlplus\admin\glogin.sql, 2 from dua
l
*
ERROR at line 1:
ORA-00911: invalid character


Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64
bit Production
With the Partitioning, Data Mining and Real Application Testing options

C:\>select &1, &2 from dual;
'select' is not recognized as an internal or external command,
operable program or batch file.
'1' is not recognized as an internal or external command,
operable program or batch file.
'2' is not recognized as an internal or external command,
operable program or batch file.

C:\>

Can you assist on why this is happening?
  • 1. Re: Sqlplus and passing system parameters &1 is not getting the value passed
    726548 Newbie
    Currently Being Moderated
    Seems variable 1 gets another value.
    The 'select' is not recognized as an internal or external command failure is caused by entering select on de c:\ prompt after the 'exit' of your script

    To test :
    1) remove the exit from the script
    2) run the script
    you wil get the same failure but probably stay within sqplus
    3) enter the command DEFINE
    SQL> DEFINE
    you wil probably see
    DEFINE 1 = "Files\oracle\product\10.2.0\client_1\sqlplus\admin\glogin.sql"
    DEFINE 2 = "2"
    enter
    SQL> DEFINE 1=my1
    enter DEFINE
    you should see
    DEFINE 1 = "my1"
    DEFINE 2 = "2"
    enter connect scott/tiger
    enter DEFINE
    if the "Files... value is back
    there is a DEFINE command in your login.sql or glogin.sql file on this PC
    The login.sql and glogin.sql scripts are executed each time you connect in sqlplus in this release : in prior release they were only executed at starting SQLPLUS
  • 2. Re: Sqlplus and passing system parameters &1 is not getting the value passed
    809498 Newbie
    Currently Being Moderated
    Thank you. I removed the exit and did as you suggested and yes define 1 flipped back to Files\...

    I searched the HD of this computer for login.sql and glogin.sql and only found the glogin.sql and did not find a DEFINE statement within. Is there someother .profile or similar on a windows based OS that could be changing this?

    Is there away to set something in a login.sql script that would run and override what is happening to the first parameter passed? Or to put something within the test.sql script to change this?
  • 3. Re: Sqlplus and passing system parameters &1 is not getting the value passed
    726548 Newbie
    Currently Being Moderated
    Of couse you could use create a dummy parameter 1 and use 2 en 3 instead but thats no proper solution.
    What is in the glogin.sql ?
  • 4. Re: Sqlplus and passing system parameters &1 is not getting the value passed
    809498 Newbie
    Currently Being Moderated
    I thought of the dummy parm, but that would cause a quite a bit of re-write. It is very strange that it is acting this way. Below is the glogin.sql. Also I did a login of sqlplus user/pass@db and just did a define and saw 1 is being set. I am not sure if the login.sql is in a location that I cant find?

    I dont believe the glogin.sql is different than the packaged version.

    --
    -- Copyright (c) 1988, 2004, Oracle Corporation. All Rights Reserved.
    --
    -- NAME
    -- glogin.sql
    --
    -- DESCRIPTION
    -- SQL*Plus global login "site profile" file
    --
    -- Add any SQL*Plus commands here that are to be executed when a
    -- user starts SQL*Plus, or uses the SQL*Plus CONNECT command
    --
    -- USAGE
    -- This script is automatically run
    --

    -- Used by Trusted Oracle
    COLUMN ROWLABEL FORMAT A15

    -- Used for the SHOW ERRORS command
    COLUMN LINE/COL FORMAT A8
    COLUMN ERROR FORMAT A65 WORD_WRAPPED

    -- Used for the SHOW SGA command
    COLUMN name_col_plus_show_sga FORMAT a24
    COLUMN units_col_plus_show_sga FORMAT a15
    -- Defaults for SHOW PARAMETERS
    COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME
    COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE

    -- Defaults for SHOW RECYCLEBIN
    COLUMN origname_plus_show_recyc FORMAT a16 HEADING 'ORIGINAL NAME'
    COLUMN objectname_plus_show_recyc FORMAT a30 HEADING 'RECYCLEBIN NAME'
    COLUMN objtype_plus_show_recyc FORMAT a12 HEADING 'OBJECT TYPE'
    COLUMN droptime_plus_show_recyc FORMAT a19 HEADING 'DROP TIME'

    -- Defaults for SET AUTOTRACE EXPLAIN report
    -- These column definitions are only used when SQL*Plus
    -- is connected to Oracle 9.2 or earlier.
    COLUMN id_plus_exp FORMAT 990 HEADING i
    COLUMN parent_id_plus_exp FORMAT 990 HEADING p
    COLUMN plan_plus_exp FORMAT a60
    COLUMN object_node_plus_exp FORMAT a8
    COLUMN other_tag_plus_exp FORMAT a29
    COLUMN other_plus_exp FORMAT a44

    -- Default for XQUERY
    COLUMN result_plus_xquery HEADING 'Result Sequence'
  • 5. Re: Sqlplus and passing system parameters &1 is not getting the value passed
    726548 Newbie
    Currently Being Moderated
    You can try determining the actual working directory when executing the sqlplus-command and/or search the registry of windows (regedit) for an sqlpath-entry ,
    the glogin.sql and login.sql are found following these rules : maybe there is a reference to a network directory.
  • 6. Re: Sqlplus and passing system parameters &1 is not getting the value passed
    809498 Newbie
    Currently Being Moderated
    We were able to get past this by running an install product for oracle that brings up the GUI that has list of installed products, unistall the client and then we performed an install of the client after this was performed the issue was resolved. We do not know how the DEFINE 1 was getting set but thankful we got passed it.
  • 7. Re: Sqlplus and passing system parameters &1 is not getting the value passed
    921512 Newbie
    Currently Being Moderated
    I was able to resolve this by changing my ORACLE_HOME variable to use shorten path.

    Old Home: Q:\Program Files\Oracle

    New Home: Q:\PROGRA~1\Oracle

    There are several PATHs in the regustry (Windows 2003 Server) with the path define with "Program Files" in the path. I would think the best option would be to re-install Oracle client and use shorten path or just install to a path without a space.
  • 8. Re: Sqlplus and passing system parameters &1 is not getting the value passed
    Dave Rabone Journeyer
    Currently Being Moderated
    and that is your answer ... don't install into a folder with spaces anywhere in the (full) pathname.

    It's been a problem in some older versions which has been fixed at some point (not quite sure when)i.

    Then we get 64 bit windows which introduces Program Files (x64) as a name, which breaks all this stuff again.

    So, avoid the pain and stick with simple file and folder names.
  • 9. Re: Sqlplus and passing system parameters &1 is not getting the value passed
    sb92075 Guru
    Currently Being Moderated
    Dave Rabone wrote:
    So, avoid the pain and stick with simple file and folder names.
    avoid the pain & don't do Windoze & don't respond to clueless newbies who resurrect dead threads!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points