jl66
asked on
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?
Another possibility: create a procedure in the remote database that does everything you need and execute it remotely across the link.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry! It was already covered by Qlemo and I did not read his comments when I posted mine.
ASKER
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?
"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?
ASKER
Very helpful. Thanks a lot.
I've never tried truncate across a link.