Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Creating DB link inside stored procedure

Creating DB link inside stored procedure

From: <kathar.km_at_gmail.com>
Date: Thu, 26 Jul 2007 19:34:19 -0000
Message-ID: <1185478459.224091.160320@w3g2000hsg.googlegroups.com>


I am trying to create DB link from stored procedure. I create a procedure like the following.. but when i executed the query i got error "ORA-01031: insufficient privileges". When i ran the following query,it was working.

create database link TESTING3 connect to user1 identified by "pwd" using 'user1.system.COM'

But when i kept it inside Stored procedure,it was not working. It may be related with single quotes,but i couldn't fix it. Any help on this would be appreciated.

CREATE OR REPLACE PROCEDURE PROC1 IS
BEGIN
execute immediate 'create public database link TESTING5 connect to user1 identified by "pwd" using' ||''''|| 'user1.system.COM'||'''';

   EXCEPTION

     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;

END PROC1; Received on Thu Jul 26 2007 - 14:34:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US