Home » SQL & PL/SQL » SQL & PL/SQL » How to get count of substition variables in SQLPLUS
How to get count of substition variables in SQLPLUS [message #271764] Tue, 02 October 2007 17:25 Go to next message
AL_JAY
Messages: 2
Registered: October 2007
Junior Member
I am working on a project that is scheduled and runs behind the scenes. The SQL script accepts a single parameter. (below)

declare LOG_FILES varchar(255);
BEGIN
LOG_FILES := '&&1';
IF LOG_FILES = '' OR LOG_FILES IS NULL THEN
DBMS_OUTPUT.put_line('No value passed to CPMS.SQL');
ELSE
DBMS_OUTPUT.put_line('IMPORTING DATA FROM ' || LOG_FILES);
Load_cpms_data.LOAD_TEXT_FILES(LOG_FILES,'160');
-- cms_log.CPMS_MAIL;
END IF;
END;
/
exit;
/

What I am trying to do is stop SQLPLUS from pausing with "Enter value for 1:" because the value will never be entered.

Any help would be greatly appreciated!

AJ
Re: How to get count of substition variables in SQLPLUS [message #271765 is a reply to message #271764] Tue, 02 October 2007 17:58 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
The wrapper script should be written to throw error & NOT invoke sqlplus when no argument is present.
Re: How to get count of substition variables in SQLPLUS [message #271786 is a reply to message #271765] Tue, 02 October 2007 22:42 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Agree completely with anacedent.

For completeness though, the WRONG (but plausible) way to do it would be to add statments to your login.sql or glogin.sql to the effect:
DEFINE 1=""
DEFINE 2=""
...

Ross Leishman
Re: How to get count of substition variables in SQLPLUS [message #272364 is a reply to message #271764] Thu, 04 October 2007 13:45 Go to previous message
AL_JAY
Messages: 2
Registered: October 2007
Junior Member
I've been out of Oracle for a while. The wrapper script does check for parameters. What I was trying to do is allow the SQL script to be executed from another batch file that another developer may create, or to be inadvertantly executed by another user and not pause waiting for input.

The scheduler and batch files are running on MS Windows XP. I have the timeout set so it automatically terminates. I guess that's the best we can do.

Thanks very much for your help!!

AJ

[Updated on: Thu, 04 October 2007 13:47]

Report message to a moderator

Previous Topic: Can you join these 2 queries into 1?
Next Topic: Help with max and min query
Goto Forum:
  


Current Time: Sat Dec 10 03:13:56 CST 2016

Total time taken to generate the page: 0.09728 seconds