Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Khalid.

Asked: June 04, 2008 - 12:30 pm UTC

Last updated: June 05, 2008 - 9:43 am UTC

Version: 10g

Viewed 1000+ times

You Asked

How can I use Sql*plus command through the pl/sql block, for example :

declare 
x number(3);
begin
select st_no into x from student;

<b>connect scott/tiger@db</b> -- did not work!

create table temp (col number(3));

insert into temp (col) values (x);

end;

and Tom said...

of course not.

sqlplus is a command line scripting environment that can run a plsql block of code in the server.

sqlplus is sqlplus - sqlplus is the user interface. sqlplus runs sqlplus commands (like connect, set, spool) - plsql cannot, plsql runs elsewhere in the network, in the server.

in order to run plsql, you already have to be connected - think about that...

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

thanks

Khalid, June 05, 2008 - 1:51 am UTC

Thanks a lot for your reply. But I'm already connected and the objective is changing the connection during the pl/sql code.
Tom Kyte
June 05, 2008 - 9:43 am UTC

you cannot, that would be like changing brains in the middle of a thought. Think about this please.


where is plsql running?
In the database

how did it get running?
a client connected to the database and ran it

what would happen if the connection was terminated?
plsql would be killed



for plsql to access another database, you use a DATABASE LINK and perform a distributed operation.

Just an idea

Martin Vajsar, June 05, 2008 - 4:18 am UTC

You state that you're already connected to the DB, so I assume that you wouldn't be tryting to connect to a different database. Maybe you could try to use the command
execute immediate 'alter session set current_schema=<i>scott</i>';
(You would need to use execute immediate for the DDL commands you have in the script anyway.)

This allows you to set another schema as "default", that is unqualified names of tables and other objects will be resolved in the schema you set. Of course you would still need the privileges to DML and even DDL commands in that schema, so it seems to me that you're trying to do something weird. Besides, wanting to set another schema in PL/SQL block in the middle of processing something looks pretty suspicious.

I'm writing this because I myself didn't know and missed the "alter session set current_schema" command really badly after turning to Oracle from MS SQL server for quite a long time.

WHAT?!?!

Duke Ganote, June 05, 2008 - 10:12 am UTC

@Khalid: What are you trying to do? Are you trying to copy data from one database into another and create tables? I can't tell from your example; it doesn't make sense to me.

I'm sure there's a solution, but I'm totally missing *what* you're trying to achieve.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.