środa, 11 lutego 2015

Changing database link definition in pl/sql

To change database link definition in Oracle database we have to connect as a owner of this database link. In most cases it's possible by using output from dba_db_links and connect from sqlplus as a owner.

But if we use undocumented function dbms_sys_sql.parse_as_user, then it's possible to change definition from pl/sql:

-- if we use specific password schema
define pass_prefix = 'pre'
define pass_postfix = 'post'

DECLARE
  sqltext  VARCHAR2(1000);
  l_result NUMBER;
  l_cursor INTEGER;
  l_cursors dbms_sql.Number_Table;
BEGIN
  -- modify db_links
  FOR cur IN
  (SELECT u.user_id,
    d.OWNER,
    d.DB_LINK,
    d.USERNAME,
    d.HOST
  FROM dba_db_links d,
    dba_users u
  WHERE d.owner=u.username
  )
  LOOP
    --parse the cursor only if we haven't already
    sqltext:= 'alter database link "'||cur.DB_LINK||'" connect to '||cur.USERNAME||' identified by &&pass_prefix'||cur.USERNAME||'&&pass_postfix';
    IF ( NOT l_cursors.exists(cur.user_id) ) THEN
      l_cursors(cur.user_id):=dbms_sys_sql.open_cursor;
      --parsing anonymous PL/SQL block as a job owner
      dbms_sys_sql.parse_as_user( c => l_cursors(cur.user_id), STATEMENT => sqltext, language_flag => dbms_sql.native, userid => cur.user_id );
    END IF;
    --bind the job number
    --remove the job by executing
    l_result:=sys.dbms_sys_sql.execute(l_cursors(cur.user_id));
  END LOOP;
END;
/

Brak komentarzy:

Prześlij komentarz