Home » SQL & PL/SQL » Client Tools » Define - help (merged) (oracle)
Define - help (merged) [message #517438] Tue, 26 July 2011 02:22 Go to next message
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?
Re: Define - help [message #517440 is a reply to message #517438] Tue, 26 July 2011 02:33 Go to previous messageGo to next message
Littlefoot
Messages: 19623
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:\>
define - help [message #517455 is a reply to message #517438] Tue, 26 July 2011 03:03 Go to previous messageGo to next message
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?
Re: define - help [message #517456 is a reply to message #517455] Tue, 26 July 2011 03:06 Go to previous message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: ORA-12505,TNS:listener does not currently know of SID given in connect descriptor
Next Topic: product spy
Goto Forum:
  


Current Time: Fri Sep 19 17:01:58 CDT 2014

Total time taken to generate the page: 0.11048 seconds