Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 923
  • Last Modified:

Can I log in another database in Oracle PL/SQL block?

Have a need to log in another database to do something like truncate a table, etc, then come back to continue process in a PL/SQL procedure. Is that possible and how?
0
jl66
Asked:
jl66
  • 3
  • 3
  • 2
  • +1
3 Solutions
 
slightwv (䄆 Netminder) Commented:
I'm not sure if you can truncate or not an am on mobile right now so I can't test it but you should be able to as least delete using database links.

I've never tried truncate across a link.
0
 
slightwv (䄆 Netminder) Commented:
Another possibility: create a procedure in the remote database that does everything you need and execute it remotely across the link.
0
 
QlemoC++ DeveloperCommented:
You can't do that (change connection) in midst of a block. You can do that in SQL*Plus, e.g. by using the SQL*Plus CONNECT command - but that will not "return", you will have to disconnect and connect back to the original connection. Or spawn another sqlplus with the host command, doing whatever you want to do remotely. Returning from that sqlplus will continue execution in the original connection.

TRUNCATE is considered as being a DDL command, and those cannot be executed via dblink.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
jl66Author Commented:
Thanks for Gurus' inputs. Have to truncate table. Delete would take a few hours.
Could slightwv pls give a a bit detail on how to do it via a remote procedure? For example, invoke the remote procedure via a dblink. Is it possible?
 
0
 
slightwv (䄆 Netminder) Commented:
Still on mobile so cannot test.

Create a procedure in the remote database using dynamic sql something like:

Create or replace procedure myproc
Is
Begin
Execute immediate 'truncate table mytab';
End;
/

Then across the link should go like(on mobile and can't test):
Begin
 Remote_proc@remotedb(param);
End;
/
0
 
sventhanCommented:

Hmmm.... interesting...

I do not think truncate is allowed via DB link.

02021, 00000, "DDL operations are not allowed on a remote database"
// *Cause: An attempt was made to use a DDL operation on a remote database.
// For example, "CREATE TABLE tablename@remotedbname ...".
// *Action: To alter the remote database structure, you must connect to the
// remote database with the appropriate privileges.
0
 
sventhanCommented:
Sorry! It was already covered by Qlemo and I did not read his comments when I posted mine.
0
 
jl66Author Commented:
Actually I got the following error when I tried to execute the truncate statement.:

"ORA-00942: table or view does not exist"
The reason for that is that when executing "truncate table table_name", the pl/sql engine expects table_name is not a variable. Can any trick overcome it?
0
 
jl66Author Commented:
Very helpful. Thanks a lot.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now