altering Database links on the fly

From: errol <yeoerrol_at_yahoo.com>
Date: 4 Sep 2001 18:14:41 -0700
Message-ID: <b392bcd2.0109041714.787a7305_at_posting.google.com>

Hi,

I am having problems creating /altering my database links dynamically. I have attached my code below. Appreciate any inputs or advise.

FYI : I did output the statement in my exception handling and I was able to execute the statement in the sqlplus.

Thanks

---

FUNCTION DBLINK_REFRESH
( DBUser IN varchar2,
  DBPassword in varchar2,
  DBLinkName in varchar2,
  DBLink in varchar2)
RETURN varchar2
is

    v_crsr_for_sql_final pls_Integer;
    column_name varchar2(254);
    vIgnore INTEGER;
    stmnt varchar2(4000);

begin
  DBMS_OUTPUT.ENABLE(1000000);
  --Alter the Database Link
  stmnt := ' CREATE DATABASE LINK "' || DBLinkName || '" CONNECT TO ' || DBUser || ' IDENTIFIED BY ' || DBPassword || ' USING ''' ||DBLink || '''' ;
  v_crsr_for_sql_final := dbms_sql.open_cursor;   dbms_sql.parse (v_crsr_for_sql_final, stmnt, dbms_sql.native); <-- Failed Here
  vIgnore := dbms_sql.execute(v_crsr_for_sql_final);   dbms_sql.close_cursor(v_crsr_for_sql_final);   return 'ESuccess';
exception
  when others then

     dbms_output.put_line(stmnt);
     return 'Failed';

end;

END; Received on Wed Sep 05 2001 - 03:14:41 CEST