OraFAQ Forum: Client Tools » Define

Home » Other » Client Tools » Define - help (merged) (oracle)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Define - help (merged) [message #517438]

Tue, 26 July 2011 02:22

sathik
Messages: 196
Registered: August 2009
Location: INDIA

Senior Member

Hi All,

To add application name in a spool file, I am using the below way.

sqlplus username/pwd@tns @xyz.sql APP0115

SQL> define appname="'&1'"
Enter value for 1: APP0115
SQL> prompt &appname
'APP0115'
SQL> spool &appname._html_jobs.csv;
SP2-0768: Illegal SPOOL command
Usage: SPOOL { <file> | OFF | OUT }
where <file> is file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]

But I am getting the above error in the spool file clause because of single quote printing infront of the spool file. But the method of defining a character is "'&1'". So I cannot avoid this single quote in the define clause.

'APP0115'

can anybody help me to print the appname like APP0115 instead 'APP0115'.then only I can use this in the spool file clause?

Report message to a moderator

Re: Define - help [message #517440 is a reply to message #517438]

Tue, 26 July 2011 02:33

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Remove single quotes.

M:\>type xyz.sql
define appname = &1
prompt &appname

spool &appname._html_jobs.csv;
select * From dept;
spool off;
M:\>
M:\>
M:\>sqlplus scott/tiger@ora10 @xyz.sql app0115

SQL*Plus: Release 10.2.0.1.0 - Production on Uto Srp 26 09:32:36 2011

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


Session altered.


Session altered.

app0115

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

M:\>type *.csv

app0115_html_jobs.csv



    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON



M:\>

Report message to a moderator

define - help [message #517455 is a reply to message #517438]

Tue, 26 July 2011 03:03

sathik
Messages: 196
Registered: August 2009
Location: INDIA

Senior Member

To add application name in a spool file, I am using the below way.

sqlplus username/pwd@tns @xyz.sql APP0115

SQL> define appname="'&1'"
Enter value for 1: APP0115
SQL> prompt &appname
'APP0115'
SQL> spool &appname._html_jobs.csv;
SP2-0768: Illegal SPOOL command
Usage: SPOOL { <file> | OFF | OUT }
where <file> is file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]

But I am getting the above error in the spool file clause because of single quote printing infront of the spool file. I know the method of defining a character is "'&1'". So If I avoid this single quote in the define clause, I am getting the below error.

SQL> define appname="&1"
Enter value for 1: APP0115
SQL> prompt &appname
APP0115
SQL> spool &appname._html_jobs.csv;
SQL> select &appname from dual;
old   1: select &appname from dual
new   1: select APP0115 from dual
select APP0115 from dual
       *
ERROR at line 1:
ORA-00904: "APP0115": invalid identifier


SQL> spool off;

can anybody help me to print the appname like APP0115 instead 'APP0115'.then only I can use this in the spool file clause?

Report message to a moderator

Re: define - help [message #517456 is a reply to message #517455]

Tue, 26 July 2011 03:06

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

select '&appname' from dual;

It is a follow-up to your previous question, so please post it and your next questions on the subject in the previous topic which is in "Client Tools" section.

Regards
Michel

Report message to a moderator

Previous Topic:	ORA-12505,TNS:listener does not currently know of SID given in connect descriptor
Next Topic:	product spy

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 00:02:06 CST 2025