DBLINK to SQLSERVER - how to list tables

[citldba]

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 ?

[PAVB]

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.

[citldba]

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!

[PAVB]

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

[citldba]

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

[PAVB]

Yes.

[citldba]

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 ?

[PAVB]

You are not posting your code, you are not posting the actual error stack.

It's very hard to help in such conditions.

[citldba]

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;

[PAVB]

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.