DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: creating table on a remote database

  1. #1
    Join Date
    Jan 2003
    Posts
    38

    creating table on a remote database

    Hello all,

    I have created a link between 2 databases. On database A , I am logged in as user system and created a link as follows

    create database link connect to new-user identified by new-user using ''

    On the database B I have created a user named new-user and granted connect,resource,dba to this user. Now from the machine with database A if I run the following

    SQL> create table new-user.test@ as select * from users;

    it gives me the following error
    ERROR at line 1:
    ORA-02021: DDL operations are not allowed on a remote database
    I have already given dba role to the user new-user on the remote database . What should I have to do to be able to create tables on remote database under the user new-user from the tables on the local database ( P.S. I am system user on the local database )

    Thanks a lot for your help

    -K

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    well oracle doesnt allow you to do DDl over a database link..DDL is not allowed..

    dbms_job.submit@remote-database( :job_number,
    'execute immediate ''create table ....'';' );
    commit;

    regards
    Hrishy

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    I just discovered that through pl/sql engine we can DO ddl on database links..in oracle8.1.7 i am not sure about 9i though

    CREATE OR REPLACE PROCEDURE
    create_Remote_Table(p_table_name VARCHAR2) AS
    l_sql_error_code PLS_INTEGER;
    l_sql_error_message VARCHAR2(512);
    BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE ' || p_table_name ||' (c1 number)' ;
    EXCEPTION
    WHEN OTHERS THEN
    l_sql_error_code := SQLCODE;
    l_sql_error_message := SQLERRM(l_sql_error_code);
    DBMS_OUTPUT.ENABLE(5000);
    DBMS_OUTPUT.PUT_LINE('OTHER ERROR');
    DBMS_OUTPUT.PUT_LINE(l_sql_error_message);
    END create_Remote_Table;


    BEGIN
    create_Remote_Table@db_link(‘my_remote_table’);
    END;

    regards
    Hrishy

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by hrishy
    Hi

    I just discovered that through pl/sql engine we can DO ddl on database links..in oracle8.1.7 i am not sure about 9i though

    CREATE OR REPLACE PROCEDURE
    create_Remote_Table(p_table_name VARCHAR2) AS
    l_sql_error_code PLS_INTEGER;
    l_sql_error_message VARCHAR2(512);
    BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE ' || p_table_name ||' (c1 number)' ;
    EXCEPTION
    WHEN OTHERS THEN
    l_sql_error_code := SQLCODE;
    l_sql_error_message := SQLERRM(l_sql_error_code);
    DBMS_OUTPUT.ENABLE(5000);
    DBMS_OUTPUT.PUT_LINE('OTHER ERROR');
    DBMS_OUTPUT.PUT_LINE(l_sql_error_message);
    END create_Remote_Table;


    BEGIN
    create_Remote_Table@db_link(‘my_remote_table’);
    END;

    regards
    Hrishy

    Hrishy :

    it cannot create on remote db if u dont have user same as that on curr db for the SP u created........

    am using 9i....its giving error....can u post wat u did.....


    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Hrishy :

    first of all u need to have the procedure on remote DB.....again u will be in fix as u cant perform ddl on remote db thru dblink.....

    abhay
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    Hi abhay

    Yes my proceedure is on the remote DB and i just execute the proceedure from the local Db ..i am sorry i should have mentioned that..

    regards
    Hrishy

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    conclusion is that u cant perform ddl directly via DB Link..


    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Feb 2007
    Posts
    1

    Cool DDL on Linked Server

    hai..

    will u try this? i found it work

    .....
    cur INTEGER;
    sSQL VARCHAR2(32767);
    .....

    sSQL := 'CREATE TABLE MY_TABLE (c1 number) ';

    cur := BMS_SQL.OPEN_CURSOR@DB_LINK;

    DBMS_SQL.PARSE@DB_LINK(cur, sSQL, DBMS_SQL.NATIVE);

    DBMS_SQL.CLOSE_CURSOR@DB_LINK(cur);


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width