Change user during a procedure

[Kioutie]

Hi!

I need to change user during a procedure.
I tried this way:
FOR I IN EXECUTIONS.FIRST .. EXECUTIONS.LAST LOOP
IF UPPER(EXECUTIONS(I).EX_COMPTE) <> UPPER(COMPTE_COUR)
OR COMPTE_COUR IS NULL THEN
REQ_EXEC := 'CONNECT :1/:2@UNIF';
EXECUTE IMMEDIATE REQ_EXEC
USING EXECUTIONS(I).EX_COMPTE, EXECUTIONS(I).EX_PASSWD ;
END IF;
REQ_EXEC := 'EXECUTE ' || EXECUTIONS(I).EX_MAPPING || '.MAIN()';
EXECUTE IMMEDIATE REQ_EXEC;
END LOOP;

But it doesn't work. Seems like he dont want to do a CONNECT in procedure..

Do you know hoe to make it?

Thanks..

Desperate Fanny

 

[sem]

You can not make connections from within pl/sql. Try to create database link(s) instead. EXECUTE is also sql*plus-specific command, so also can not be called. Can you explain what you're trying to do?

Regards, Dima

 

[Kioutie]

Hi,

Ihave to execute packages, their names are in an Oracle table, and they are in three different schemas. So, in my table, i have the name of the owner (schema), the password and the name of the package. If i am not in this schema, i have to connect to it..

 

[SantaMufasa]

Kioutie,

I am puzzled...why must you connect to a schema to execute that schema's package(s)? Why won't a &quot;GRANT EXECUTE ON <package_name> TO <user>;&quot; relieve you from connecting to the package-owning user?

Dave

 

[Kioutie]

I just can't .. the DBA's don't want. And anyway, i've tried this way, but i can't do an EXECUTE statement in a plsql procedure.. so i think that i will return to school and do haircuts rest of my life.. .. joking..
I'll try to find another way, i think its impossible to do it this way..

 

[SantaMufasa]

Kioutie,

Hold on ! Don't give up so easily...(why cut hair when you can have so much fun doing this work? <smile>)

Don't allow your DBAs to prevent work by simply saying, &quot;I don't want to.&quot; That is a totally unacceptable answer. The only acceptable method for a DBA to reject a request like this (to GRANT EXECUTE on a specific procedure to a specific user) is to show that the request disobeys a company-audit policy or that the request introduces unmanageable risk to the business. If your DBAs say that &quot;GRANT EXECUTE...&quot; is an audit exception, then what on earth do they think it is to do multiple &quot;CONNECT user/password&quot; commands from within a procedure?

The proper, conventional-wisdom solution to your problem is to &quot;GRANT EXECUTE...&quot;. If your DBAs don't like that, then escalate the issue to your DBAs' management (perhaps with a copy of this thread). If that doesn't work, keep escalting until you find an audience willing to listen.

Here is a principle that I have used that ALWAYS leads to a resolution of such a problem (whether the problem is in IT or with any customer-service issue). If the person with whom you are negotiating says, &quot;I'm sorry, Kioutie, but that is not possible,&quot; just point out that you know is is possible, but it requires 1) DESIRE to resolve the problem, 2) AUTHORITY to resolve the problem, and 3) MEANS to resolve the problem. (I call this the &quot;DAM&quot; principle.) Whichever &quot;DAM&quot; component they are missing to resolve the problem, ask to speak with someone who has the DESIRE to resolve the problem and the AUTHORITY to approve the resolution of the problem or the acquisition of the MEANS to resolve it.

So much for my lesson on &quot;The Politics of IT Problem Solving&quot; for today.

Let us know what you decide,

Dave