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
Messages: 28
Registered: April 2009
Location: Talavali
Junior Member


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

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
elsif env = cmd then
conn org02/lhjdl@hjdghj
elsif env = wld then
conn org03/lhjdl@hjdghj
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: 63937
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not in SQL*Plus.

Re: Dynamic Connection in SQLPLUS [message #396619 is a reply to message #396592] Tue, 07 April 2009 06:03 Go to previous message
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
Previous Topic: How To reterive the record counts
Next Topic: How to call a procedure in INSERT Trigger
Goto Forum:

Current Time: Thu Oct 27 17:41:26 CDT 2016

Total time taken to generate the page: 0.06473 seconds