RE: sql-plus variable error?

From: Larry G. Elkins <elkinsl_at_flash.net>
Date: Fri, 28 Apr 2000 07:16:51 -0500
Message-Id: <10481.104394@fatcity.com>

Quang,

Try two periods, &SCHEMAOWNERTO..HpxCatalog, after your substitution variable:

  1* select * from &1..emp
SQL> /
Enter value for 1: scott
old 1: select * from &1..emp
new 1: select * from scott.emp

The explanation from the SQL*Plus manual:

<<< Start Manual Quote >>>
If you wish to append characters immediately after a substitution variable, use a period to separate the variable from the character. For example:

SQL> SELECT * FROM EMP WHERE EMPNO='&X.01'; Enter value for X: 123

is interpreted as

SQL> SELECT * FROM EMP WHERE EMPNO='12301'; <<< End Manual Quote >>>

In your case, the period you are using in the owner.object notation is being interpreted by SQL*Plus as a command to append to the variable every thing *following* the period, thus you lose the period. The double period, "..", gets you around this.

Regards,

Larry G. Elkins
The Elkins Organization Inc.
elkinsl_at_flash.net
214.954.1781

-----Original Message-----

From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Guang Mei Sent: Thursday, April 27, 2000 9:10 PM
To: Multiple recipients of list ORACLE-L Subject: sql-plus variable error?

I have the following script in a file:

--

set verify off
set feedback off

accept SCHEMAOWNERFROM char prompt 'Enter Owner of the Schema you wish to move data FROM:'
accept SCHEMAOWNERTO char prompt 'Enter Owner of the Schema you wish to move data TO:'

select 'insert into HpxCatalog:' from dual;

INSERT INTO &SCHEMAOWNERTO.HpxCatalog(

       CatalogID,
	 CatalogName,
	 Description,
	 Active )
Values (
	 &SCHEMAOWNERTO.HPXSEQ_Catalog.nextval,
	 'Catalog_Name1',     -- hardcoded
	 '',
	 'T' );

--

When I run this file in sql-plus, and I enter the two variables (two schema names)at prompt, say 'testfrom' and 'testto', I got the following error in sql-plus:

'INSERTINTOHPXCATALOG:'

---

insert into HpxCatalog:
INSERT INTO testtoHpxCatalog(

            *
ERROR at line 1:
ORA-00942: table or view does not exist

I thought I would get "INSERT INTO testto.HpxCatalog(" stuff with a dot "." in it. Any idea what I did wrong?

Thanks.

Guang

---

Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

--

Author: Guang Mei
  INET: zlmei_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may Received on Fri Apr 28 2000 - 07:16:51 CDT