Forum Stats

  • 3,770,050 Users
  • 2,253,054 Discussions


Dynamically drop and create database link in other schema from Pl/SQL

pmkr Member Posts: 21 Blue Ribbon
edited Oct 16, 2012 6:40PM in SQL & PL/SQL

Ours is an E-Biz 11i environment on 9i database. Every night we're creating a break fix environment by cloning production. One of the tasks this process does is dropping and re-creating the database links in APPS and other schemas.

We've a post-clone SQL script to do the above, along with the other database tasks such as updating the profiles, cancelling the pending concurrent requests, recreating the database directories etc. Right now, there're a few individual SQLs that do the above. I'm consolidating them all in a single PL/SQL script. Within this, I've written a procedure to drop and re-create the links. It accepts the link name, target user/password it's connecting to and the host name as parameters and drops/re-creates the links.

As long as APPS calls this procedure, it works very well. Since, I can't connect to a different user from within the PL/SQL block, I tried to use 'alter session set current_schema = <username>' and try calling the above procedure, but I'm getting the ORA-1031 error. I tried even using the invoker rights (by defining the procedure with "authid current_user" clause) but still the same error.

Then I came to know about this DBMS_SYS_SQL and thought I got the solution I wanted when I read about the parse_as_user procedure. But still I've no luck. I tried to test the above from a PL/SQL block, as APPS. It works for the APPS schema and when it comes to the other schemas, problem still remains.

I tried to run it as SYS user as well. But again, it works well for the APPS schema and for the other schemas, it throws the same ORA-1031 error. I made sure that the other schemas have the execute privilege on DBMS_SYS_SQL package and it's qualified as "sys.dbms_sys_sql.parse_as_user" when it is called.

This really confuses me. When running this procedure as SYS, it can drop and re-create the links in APPS schema, buy why not in the other schemas ? Do the other schemas need any other privilege ?

Any help is greatly appreciated.

Thanks and regds.


  • spajdy
    spajdy Member Posts: 595 Silver Badge
    In schema where you need to create DB link create this procedure and grant execute privilege on it to schema where you have you big PL/SQL procedure.
    create or replace procedure exec_sql(p_str in varchar2) as
      execute immediate p_str;
    From you big PL/SQL procedure you call schema.exec_sql('create database link ...');
  • pmkr
    pmkr Member Posts: 21 Blue Ribbon
    I tried all that. It only creates in the schema where it's executed.
  • spajdy
    spajdy Member Posts: 595 Silver Badge
    Really ?
    Have schema A and schema B
    In schema A create procedure exec_sql and grant execute on exec_sql to B.
    Now connect to schema B and run
      a.exec_sql('create database link ...');
    Or could you put your test scripts there ?
  • pmkr
    pmkr Member Posts: 21 Blue Ribbon
    Yeah, I know what you're talking about.

    We've a shcema called SDR_PM where I want to create the link. I'm running my Pl/SQL block as APPS schema. I tested creating a procedure under SDR_PM schema to create a db link and granted the execute privilege to APPS schema. While running the procedure as APPS, it only created the link in SDR_PM schema itself. I vaguely remember, even Oracle document talks that creating a database link in another schema is kind of difficult, if not impossible.

    I'm trying to see if there's workaround possible. My intention is to simplify the tasks and run all in one place - meaning, one PL/SQL block - as one schema, without having to connect to a different user outside of the PL/SQL block just to create the database link.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond
    pmkr wrote:

    I'm trying to see if there's workaround possible.
    That requires execute privilege on a very powerful package DBMS_SYS_SQL:
    SQL> select  db_link
      2    from  dba_db_links
      3    where owner = 'U1'
      4  /
    no rows selected
    SQL> declare
      2      v_cur number;
      3      v_userid number;
      4  begin
      5      select  user_id
      6        into  v_userid
      7        from  dba_users
      8        where username = 'U1';
      9      v_cur := dbms_sql.open_cursor;
     10      sys.dbms_sys_sql.parse_as_user(v_cur,'create database link abc using ''def''',dbms_sql.native,v_userid);
     11      dbms_sql.close_cursor(v_cur);
     12  end;
     13  /
    PL/SQL procedure successfully completed.
    SQL> select  db_link
      2    from  dba_db_links
      3    where owner = 'U1'
      4  /
This discussion has been closed.