Home » SQL & PL/SQL » SQL & PL/SQL » Executing the run time file at SQLPLUS (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Executing the run time file at SQLPLUS [message #669446] Mon, 23 April 2018 05:07 Go to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All,

I want to execute the different files at the run time in the SQL PLUS

Ex: I have two different files like ABC.SQL, XYZ.SQL,

I had declared one bind variable to hold file_name .

var file_name VARCHAR2(200);
DECLARE
  sr VARCHAR2(20) :='ABC';
BEGIN
  IF sr         ='ABC' THEN
    :file_name :='ABC.SQL';
  ELSE
    :file_name :='XYZ.SQL';
  END IF;
END;
/
PRINT file_name 

Now i want to execute the file which is there in :file_name bind variable

SQL>@:file_name

Please help to resolve the issue

Thanks
SaiPradyumn

Re: Executing the run time file at SQLPLUS [message #669447 is a reply to message #669446] Mon, 23 April 2018 05:23 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
That's something I'd handle in the OS personally.
Re: Executing the run time file at SQLPLUS [message #669448 is a reply to message #669447] Mon, 23 April 2018 05:37 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Sorry I didn't get you .
How to handle from OS , We are using LINUX machine.
Re: Executing the run time file at SQLPLUS [message #669449 is a reply to message #669446] Mon, 23 April 2018 05:40 Go to previous messageGo to next message
John Watson
Messages: 8120
Registered: January 2010
Location: Global Village
Senior Member
If I understand you correctly, what you want to do is assign a PL/SQL bind variable to a SQL*Plus substitution variable. Example here,
https://blogs.oracle.com/opal/sqlplus-101-substitution-variables#3_2
Re: Executing the run time file at SQLPLUS [message #669451 is a reply to message #669446] Mon, 23 April 2018 08:01 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2908
Registered: January 2010
Location: Connecticut, USA
Senior Member
SQL> var file_name VARCHAR2(200);
SQL> DECLARE
  2    sr VARCHAR2(20) :='ABC';
  3  BEGIN
  4    IF sr         ='ABC' THEN
  5      :file_name :='ABC.SQL';
  6    ELSE
  7      :file_name :='XYZ.SQL';
  8    END IF;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> column file_name new_value file_name noprint
SQL> select  :file_name file_name
  2    from  dual
  3  /




SQL> @&file_name
SP2-0310: unable to open file "ABC.SQL"
SQL> 

SY.
Re: Executing the run time file at SQLPLUS [message #669452 is a reply to message #669451] Mon, 23 April 2018 08:28 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member

Thank you very much Solomon


I tried the same.But following statement did the magic to execute my fie

 column file_name new_value file_name noprint

Otherwise its always asking for the &Quote:
Enter the Value for

Thanks a lot the forum
Re: Executing the run time file at SQLPLUS [message #669453 is a reply to message #669452] Mon, 23 April 2018 08:46 Go to previous messageGo to next message
BlackSwan
Messages: 26648
Registered: January 2009
Location: SoCal
Senior Member
are you using sqlplus as your client software?
Re: Executing the run time file at SQLPLUS [message #669454 is a reply to message #669453] Mon, 23 April 2018 09:27 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2908
Registered: January 2010
Location: Connecticut, USA
Senior Member
BlackSwan wrote on Mon, 23 April 2018 09:46
are you using sqlplus as your client software?
That's what OP stated in original post "I want to execute the different files at the run time in the SQL PLUS".

SY.
Re: Executing the run time file at SQLPLUS [message #669465 is a reply to message #669454] Tue, 24 April 2018 03:04 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Yes Solomon,

In the development environment we use SQL Developer tool to connect to the database,
But while giving the releases to UAT, we will execute all those scripts through SQLPLUS (from linux System)


Once again Thanks a lot

Thanks
SaiPradyumn
Re: Executing the run time file at SQLPLUS [message #669466 is a reply to message #669465] Tue, 24 April 2018 03:11 Go to previous messageGo to next message
cookiemonster
Messages: 13777
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since there are differences in how sql developer and sqlplus works (there's various sqlplus commands sql developer doesn't like for starters) you really should test the script with sqlplus in dev.
Re: Executing the run time file at SQLPLUS [message #669470 is a reply to message #669466] Tue, 24 April 2018 05:06 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Yes cookiemonster

Before giving the release to UAT we are doing the same through the SQLPLUS also

Thanks
SaiPradyumn
Re: Executing the run time file at SQLPLUS [message #669472 is a reply to message #669470] Tue, 24 April 2018 07:24 Go to previous messageGo to next message
Bill B
Messages: 1926
Registered: December 2004
Senior Member
If this is something you want to do everytime you startup sqlplus you put the commands in one of two files. They should be in the oracle bin directory in your installation. They are

glogin.sql - Run when you anyone logs into oracle using sqlplus.
login.sql - same as glogin.sql but is individual to the specific login and is found in the users home directory.
Re: Executing the run time file at SQLPLUS [message #669473 is a reply to message #669446] Tue, 24 April 2018 07:45 Go to previous messageGo to next message
EdStevens
Messages: 1158
Registered: September 2013
Senior Member
And how are you populating the value of SR to begin with?
Re: Executing the run time file at SQLPLUS [message #669482 is a reply to message #669473] Tue, 24 April 2018 13:58 Go to previous messageGo to next message
Michel Cadot
Messages: 66776
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

EdStevens wrote on Tue, 24 April 2018 14:45
And how are you populating the value of SR to begin with?
This is the key point, without this all discussions are pointless.

Re: Executing the run time file at SQLPLUS [message #669488 is a reply to message #669482] Wed, 25 April 2018 03:15 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All

I had written the following LOAD.SQL & one function get_file_name_function to satisfy my requirement .


var file_name varchar2(200);

begin
  select get_file_name_function into :file_namefrom dual;   
end;
/
print :file_name;
column file_name new_value file_name noprint;
print :file_name;
@&file_name;

Before that loading the all required files. Preparing the files with same naming convention
which is returned by get_file_name_function.

We are not keeping these files glogin.sql,login.sql .
Why because we have different DML statements for every release.
We will include all these scripts in the release.

Its working fine.Thanks for you support .


Thanks
Sai Pradyumn
Re: Executing the run time file at SQLPLUS [message #669518 is a reply to message #669488] Thu, 26 April 2018 14:44 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2908
Registered: January 2010
Location: Connecticut, USA
Senior Member
Very inefficient. All you need is:

column file_name new_value file_name noprint
select  get_file_name_function file_name
  from  dual
/
@&file_name

SY.
Re: Executing the run time file at SQLPLUS [message #669519 is a reply to message #669518] Thu, 26 April 2018 14:47 Go to previous message
Bill B
Messages: 1926
Registered: December 2004
Senior Member
Why not pass the script name as an argument to his routine and then just run it using execute immediate?

[Updated on: Thu, 26 April 2018 14:47]

Report message to a moderator

Previous Topic: Trap Constraint Errors in PL/SQL?
Next Topic: roles granted to a role
Goto Forum:
  


Current Time: Sun Jan 19 21:57:38 CST 2020