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 [EMAIL PROTECTED] +49 (0) 540 / 550 5177 Avermann Maschinenfabrik GmbH & Co. KG http://www.avermann.de ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED] > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Sergey V Dolgov > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
