DBLINK to SQLSERVER - how to list tables
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: DBLINK to SQLSERVER - how to list tables

  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    56

    DBLINK to SQLSERVER - how to list tables

    I've got a couple of questions about how to use a DBLINK from Oracle (9i) to SQLSERVER2005. The link is created and as long as I know the name of a table in SQLSERVER I can read it from Oracle, but I would like to be able to see what tables exist in SQLSERVER from Oracle. I want to run something like :

    select * from all_tables@dblinkname

    but whatever I try always comes back empty. Is there anyway I can find all the SQLSERVER tables from Oracle ?

    Next question is that my application actually runs on a second Oracle instance. So it news to access the first instance through a link and then access SQLSERVER through another link so something like this :

    select * from tablename@firstoraclelink@sqlserverlink

    but that doesn't seem valid. Is there a way to use a DBLINK on a remote database ?

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    As per your second question, I would point to a view on the second database that is relying on a DBLink pointing to the third database.
    Ugly, very ugly but it should work.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Feb 2004
    Location
    UK
    Posts
    56
    Yes good plan. The only thing is that I need to access all tables that come back from the select from all_tables, so I can't create a view because I don't know what I want to access until it is time to access it!

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    If that's the case mon cher ami you are in troubles.

    I would change the strategy, that three tier strategy is complicated and also both performance challenged and unscalable. Experience says that if it looks too complicated and you go for it anyway you are going to bring increasingly bad things to your plate. Just MHO
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Feb 2004
    Location
    UK
    Posts
    56
    I think I might have found a way to do what I need (but I'll have to hard code the table names) - but one more question, can I execute a stored procedure remotely ?

    E.g. EXECUTE my_proc@remotedb

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Yes.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Feb 2004
    Location
    UK
    Posts
    56
    Ok thanks got that now. I'm a bit new to stored procedures. I've set a string equal to my generated SQL statement, and then used Execute Immediate stringname; to run it, but it complains if the the sql statement/string is over 255 characters. Is there anyway I can execute a generated statement that is longer than 255 characters in the procedure ?

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You are not posting your code, you are not posting the actual error stack.

    It's very hard to help in such conditions.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Feb 2004
    Location
    UK
    Posts
    56
    Fair point. The error I am getting is

    ORA-20000: ORU-10028:line length overflow, limit of 255 chars per line
    ORA-06512: at "myproc", line 24


    The procedure code is below. I've highlighted the relevant portions. If I make V_string less than 255 then it isn't long enough for my query :

    CREATE OR REPLACE PROCEDURE myproc IS
    v_string varchar2(2000);
    cursor get_file_date is

    select to_char(trunc(sysdate) - rownum,'YYYYMMDD') file_date
    From all_objects
    Where rownum between 1 and 31;


    BEGIN
    For A in get_file_date
    Loop
    V_String:='insert into localtable Select * from '||
    '"remotetable'||a.file_date||'"'||'@remotedb b '||
    'where not exists '||
    '(select * from localtable a '||
    'where a.fielda = b."fielda" '||chr(10)||
    'and a.fielda = b."fieldb" '||
    'and a.fieldc = b."fieldc" '||
    'and (a.fieldd = b."fieldd" '||
    'and (a.fielde = b."fielde" '||
    'and (a.fieldf = b."fieldf" ';

    dbms_output.put_line(' V String is '||V_string);

    Execute Immediate v_string;
    Commit;
    End Loop;


    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    WHEN OTHERS THEN
    -- Consider logging the error and then re-raise
    RAISE;
    END myproc;

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Oracle is not complaining about your sql statement being larger than 255 characters.

    You are getting the error at line 24; dbms_output.put_line cannot print more than 255 characters in a single line and that's a hard coded limit.
    You can always do it by yourself perhaps resorting to dbms_output.new_line function.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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