Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Connection in SQLPLUS (Oracle 10G)
Dynamic Connection in SQLPLUS [message #396592] Tue, 07 April 2009 05:21 Go to next message
bholeuday
Messages: 28
Registered: April 2009
Location: Talavali
Junior Member

Hi,

My requirement is like this....

I have get_env.sql like

col ts new_value ts
col ss new_value ss
prompt Please Enter Environment Details Options are
prompt ricdb
prompt cfgcent
prompt cmd
prompt wld
select tab_tablespace ts, IND_TABLESPACE ss from settings_ud where env_name = '&Env';
conn invctl_doc/*****@dcs1d
@P:\061008\Release_Management_Process\06_04_09\run.sql


Which fires SELECT on settings_ud now i want to develope something like below logic in sqlplus (Only in SQL Statement)

If env = ricdb then
conn org01/lhjdl@hjdghj
@P:\061008\Release_Management_Process\06_04_09\run.sql
elsif env = cmd then
conn org02/lhjdl@hjdghj
@P:\061008\Release_Management_Process\06_04_09\run.sql
elsif env = wld then
conn org03/lhjdl@hjdghj
@P:\061008\Release_Management_Process\06_04_09\run.sql
end if;

Is this possible ??

Thanks in Advance.
Re: Dynamic Connection in SQLPLUS [message #396608 is a reply to message #396592] Tue, 07 April 2009 05:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not in SQL*Plus.

Regards
Michel
Re: Dynamic Connection in SQLPLUS [message #396619 is a reply to message #396592] Tue, 07 April 2009 06:03 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It is, but it's a pain to code, and future generations of developers will burn small models of you in effigy for lumbering them with maintaining it.

You can use the NEW_VALUE command in SQL*Plus to populate a substitution variable with a value from a SELECT statement.
You can then use this substitution variable as the name of a SQL Script to execute - this gives you a rudimentary IF-THEN-ELSE functionality.

Each of the scripts you could optionally run would contain code like
conn org02/lhjdl@hjdghj
 @P:\061008\Release_Management_Process\06_04_09\run.sql
Previous Topic: How To reterive the record counts
Next Topic: How to call a procedure in INSERT Trigger
Goto Forum:
  


Current Time: Mon Dec 05 02:36:12 CST 2016

Total time taken to generate the page: 0.04485 seconds