1. The Forum has been upgraded to a modern and advanced system. Please read the announcement about this update.
  2. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ddl over dblink. dblink must be dynamic

Discussion in 'SQL PL/SQL' started by shivu, Jan 18, 2010.

  1. shivu

    shivu Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    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;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,142
    Likes Received:
    308
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    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. shivu

    shivu Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    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. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,142
    Likes Received:
    308
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    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.

    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. SteveS

    SteveS Active Member

    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Stroudsburg, PA USA
    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.