RE: Re[2]: change a database connection in a stored procedure?

Hi Alexandre,

        I am writing a procedure to create a user, grant privilleges to the user nad then connect as that user and create schema in that user. I want this whole process to be automated. That is why I want to change a database connection. Anyway I have done it using batch file.
Thanks for your response
Rgds
Kranti
-----Original Message-----
Sent: Tuesday, May 07, 2002 3:49 PM
To: Multiple recipients of list ORACLE-L

Hi Kranti,

First of all, think about what you wanna do. This looks like logical mistake if you need session change in PL/SQL. When you change the session, what happens with the first one? Session is establised by client requesting the server, so you can only do it from client.

If you just want to perform some action as other user, then you may run procedure from that user scheme declared with AUTHID DEFINER (which is by default). Another solution may be creating database link to itself as another user but that doesn't make much sense. Note that you will make new session with database link from server (as client) to itself as server.

Alexandre Gorbatchev
Oracle DBA/Developer, OCP
Alexandre.Gorbatchev_at_avermann.de
+49 (0) 540 / 550 5177
Avermann Maschinenfabrik GmbH & Co. KG
http://www.avermann.de

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, May 07, 2002 10:08 AM

> Hello kranti,
>
> I made a mistake. You can't change your database connection at all
> from stored procedure
> (there is no such SQL command "connect" it's a sqlplus directive).
> When I gave the answer I thought about database link.
> You can drop and create it using dynamic SQL.
>
> Tuesday, May 07, 2002, 2:08:27 PM, you wrote:
>
> kp> Hi Sergey,
> kp> I am using dynamic SQL but it is returing error for connect statement.
Can u
> kp> give me some example code.
>
> kp> Rgds
> kp> Kranti
> kp> -----Original Message-----
> kp> Sent: Tuesday, May 07, 2002 7:38 AM
> kp> To: Multiple recipients of list ORACLE-L
>
>
> kp> Hello kranti,
>
> kp> Use dynamic SQL.
>
> kp> Monday, May 06, 2002, 8:23:29 PM, you wrote:
>
> kp>> Hi List,
> kp>> Can someone tell me is it possible to change a database
> kp> connection
> kp>> in a stored procedure? if so how?
> kp>> TIA
> kp>> Kranti
>
>
>
>
>
>
> --
> Best regards,
> Sergey mailto:dsv_at_pptus.oilnet.ru
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Sergey V Dolgov
> INET: dsv_at_pptus.oilnet.ru
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Alexandre Gorbatchev
  INET: alexandre.gorbatchev_at_avermann.de

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: kranti pushkarna
  INET: kranti_pushkarna_at_staarship.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).