Home » SQL & PL/SQL » SQL & PL/SQL » Connect to other database in PL/SQL
Connect to other database in PL/SQL [message #185702] Thu, 03 August 2006 03:16 Go to next message
pham
Messages: 17
Registered: August 2006
Junior Member
Hello,

there are two oracle schema A and B:
a working procedure in A has to start some sql-DOS-files (for example: my_file.sql) in B (not in A).
The oracle user account for B is acquaint.

Do you have a (some) idee?

many thanks


Re: Connect to other database in PL/SQL [message #185711 is a reply to message #185702] Thu, 03 August 2006 03:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, clarification time.

Are we talking about two databases, or two schemas within one database?

Are you running pl/sql procedures here, or SQL scripts?

Anyway, if you are connected to schema A, and would like all unqualified SQL references to refer to schema B, then you can issue the command:
ALTER SESSION SET current_schema = B;
Re: Connect to other database in PL/SQL [message #185713 is a reply to message #185711] Thu, 03 August 2006 04:18 Go to previous messageGo to next message
pham
Messages: 17
Registered: August 2006
Junior Member
there are two server and two database.

I can call a procedure with remote_procedure@db_link_to_schema_B, but I can't start a DOS-file from this remote_procedure.

It don't work with ALTER SESSION SET current_schema = B;

Why I need to call a DOS-file.sql, I want to use SQLPLUS (to create table, etc.)



Re: Connect to other database in PL/SQL [message #185714 is a reply to message #185711] Thu, 03 August 2006 04:20 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As you're talking about PL/SQL, maybe AUTHID might mean something to you. Here are a few words about this concept:
Google

Oracle8i Database introduced the AUTHID clause for procedures, functions and packages. When set to AUTHID DEFINER (the default), then your program runs under "definer rights." This means that any references to data objects (such as tables and views) are resolved at compile time, based on the directly granted privileges of the definer or owner of the program. Roles are ignored.

If, on the other hand, you set the clause to AUTHID CURRENT_USER, then any references to data objects are resolved at run time, based on the privileges of the currently-connected schema. And (the DBAs have got to love this) role-based privileges are now applied.
Re: Connect to other database in PL/SQL [message #185719 is a reply to message #185714] Thu, 03 August 2006 04:38 Go to previous messageGo to next message
pham
Messages: 17
Registered: August 2006
Junior Member
thank you for fast answer,

user A has in schema A procedure A
user A has a DB-link: db_link_to_schema_B
user B has in schema B procedure remote_procedure_B

I start a procedure A in schema A with user A.
The procedure A call: remote_procedure_B@db_link_to_schema_B.
Now: user B start the remote_procedure_B in schema B.

All things are fine.

And I want: start a DOS-file.sql from remote_procedure_B in schema B with user B.

And have no idee
May be, the solution is not on this way??

Re: Connect to other database in PL/SQL [message #185721 is a reply to message #185719] Thu, 03 August 2006 04:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The reason why you can't run a SQL file from a procedure at the remote database is the same as the reason that you can't run a SQL file from a procedure locally.

That reason is: You can't do it.

You can host out to the OS, fire up sql*plus and pass it the script to run. Is that what you're trying to do?

If so, can you show us the code and the errors you get when you run it. (you know - the stuff that the posting guidelines ask you to provide)
Re: Connect to other database in PL/SQL [message #185724 is a reply to message #185721] Thu, 03 August 2006 05:18 Go to previous message
pham
Messages: 17
Registered: August 2006
Junior Member
You are right JRowbottom,

That reason is: I can't do it.
My hope for a tricky solution is wasted

I make only brain storming just moment, ther are no code to see.

Why I do that? we have a ERP-software and have to call DOS-files.sql from other software / database / company. It's the easy way to do that, what I written bevor, but it can't work.

Now I have to use the common way to do that: connect to the server B , database B and user B. It's a very difficult way for me to propose, etc.

thank a lot








Previous Topic: Oracle8i and UTL_HTTP
Next Topic: Dynamic Select fields
Goto Forum:
  


Current Time: Fri Dec 02 18:38:31 CST 2016

Total time taken to generate the page: 0.50349 seconds