ddl over dblink. dblink must be dynamic

in Oracle Database; i am looking for 1 sample program which contains any DDL operation using dblink and the dblink must take input ...
+ Post Reply + Post New Topic
Results 1 to 5 of 5
  1. #1
    shivu is offline Junior Member
    Points: 70, Level: 1
    Join Date
    16 Sep 2009
    Posts
    2
    Points
    70

    ddl over dblink. dblink must be dynamic

    i am looking for 1 sample program which contains any DDL operation using dblink and the dblink must take input from variable (which varies during runtime).
    i have a sample where dblink is fixed but i want change during runtime.

    DECLARE
    n_cursor INTEGER;
    num_rows_processed integer;
    p_stmt VARCHAR2(200) := 'create table temp (key1 number, key2 varchar2(1))';
    BEGIN
    n_cursor := dbms_sql.open_cursor@mylink;
    dbms_sql.parse@ mylink(n_cursor, p_stmt, dbms_sql.native) ;
    num_rows_processed := dbms_sql.execute@mylink(n_cursor) ;
    dbms_sql.close_cursor@ mylink(n_cursor) ;
    END;
    Last edited by shivu; 01-18-2010 at 11:59 AM.


  2. #2
    zargon's Avatar
    zargon is offline Forum Guru
    Points: 26,400, Level: 97
    Join Date
    22 Oct 2008
    Location
    Aurora, CO
    Posts
    2,025
    Points
    26,400

    Re: ddl over dblink. dblink must be dynamic

    Quote Originally Posted by shivu View Post
    i am looking for 1 sample program which contains any DDL operation using dblink and the dblink must take input from variable (which varies during runtime).
    i have a sample where dblink is fixed but i want change during runtime.

    DECLARE
    n_cursor INTEGER;
    num_rows_processed integer;
    p_stmt VARCHAR2(200) := 'create table temp (key1 number, key2 varchar2(1))';
    BEGIN
    n_cursor := dbms_sql.open_cursor@mylink;
    dbms_sql.parse@ mylink(n_cursor, p_stmt, dbms_sql.native) ;
    num_rows_processed := dbms_sql.execute@mylink(n_cursor) ;
    dbms_sql.close_cursor@ mylink(n_cursor) ;
    END;
    There are none. The dbms_sql.open_cursor is not a dynamic SQL call (the cursor has to be able to be opened through a valid dblink); the link has to be resolved at compile time and your dynamic dblink 'requirement' cannot be. Also your dynamic link specification runs the risk of being improperly submitted which would generate runtime failures.

    What you ask is simply not possible.

  3. #3
    shivu is offline Junior Member
    Points: 70, Level: 1
    Join Date
    16 Sep 2009
    Posts
    2
    Points
    70

    Question Re: ddl over dblink. dblink must be dynamic

    open_cursor work dynamically
    execute immediate 'select dbms_sql.open_cursor'||'@mylink'||' from dual' into n_cursor;
    but i couldn't use above method for dbms_sql.parse.

    Is there any other alternative method to solve my problem.
    Because i want to drop tables from different Databases using only DBLINKS

  4. #4
    zargon's Avatar
    zargon is offline Forum Guru
    Points: 26,400, Level: 97
    Join Date
    22 Oct 2008
    Location
    Aurora, CO
    Posts
    2,025
    Points
    26,400

    Re: ddl over dblink. dblink must be dynamic

    Quote Originally Posted by shivu View Post
    open_cursor work dynamically
    execute immediate 'select dbms_sql.open_cursor'||'@mylink'||' from dual' into n_cursor;
    but i couldn't use above method for dbms_sql.parse.
    That statement doesn't open a dynamically assigned cursor. To construct a statement to use a dynamic cursor you'd need to do this

    execute immediate 'select dbms_sql.open_cursor@'||p_mylink||' from dual' into n_cursor;

    I have doubts such a process will work. Remember,. too, that execute immediate opens a new context and as such the cursor is not open for use after the execute immediate completes which is why the parse and execute portions of your dbms_sql attempt aren't successful. My comments still stand as dbms_sql.open_cursor is not a dynamic operation that can use variables in place of an actual dblink name.

    Quote Originally Posted by shivu View Post
    Is there any other alternative method to solve my problem.
    Because i want to drop tables from different Databases using only DBLINKS
    Other than coding for all of your possible dblinks and using an if-then statement to select the proper code based upon a passed dblink name I don't see any way of doing what you want.

  5. #5
    SteveS is offline Junior Member
    Points: 120, Level: 2
    Join Date
    26 Jan 2010
    Location
    Stroudsburg, PA USA
    Posts
    7
    Points
    120

    Re: ddl over dblink. dblink must be dynamic

    You might want to consider using synonyms. Dynamically recreate the dblink then change the synonym to use this new link. Just keep using the same synonym.

    create or replace synonym my_table_remotedb for owner.mytable@somedb;

    Only refer to my_table_remotedb in your procedure.

Other Solutions
  1. Hook into Dynamic Converter
    By OracleNovice in forum Oracle Webcenter Suite (formerly Oracle ECM)
    Replies: 0
    Last Post: 03-22-2010, 10:12 PM
  2. placeholder in dynamic SQL ?
    By lubna in forum SQL PL/SQL
    Replies: 1
    Last Post: 03-08-2010, 07:38 AM
  3. Dynamic query
    By lberkefeld in forum Oracle Webcenter Suite (formerly Oracle ECM)
    Replies: 0
    Last Post: 09-17-2009, 07:04 AM
  4. Create dblink to sql server
    By srinath in forum Server Administration and Options
    Replies: 3
    Last Post: 08-11-2009, 01:31 PM
  5. How to implement Dynamic Server Pages in Stellent ??
    By Vijay Thagulla in forum Oracle Webcenter Suite (formerly Oracle ECM)
    Replies: 1
    Last Post: 02-18-2009, 09:23 AM