|
DEAR, all. I have problem with creating proxy table in SA12. There is view on oracle 11g and I want to use this view for creating proxy table SA12. But it is not possible to create.. is it impossible to create proxy table using view? CREATE EXISTING TABLE "DBA"."V_UNIST_HR_INFORM" AT 'UNIST_ORACLE:UNIST:V_UNIST_HR_INFORM'; I just want to select(read) the view on oracle 11g... is there any other way to do that for it? Thanks and Best regards, hakwoo KIM |
|
The CREATE EXISTING TABLE statement needs a remote server, which can be created using
CREATE SERVER and an external login CREATE EXTERNLOGIN. Just to add to answer your question: It should not matter whether the proxy table links to a remote table or remote view, bioth should work.
(29 Oct '13, 07:04)
Volker Barth
I have already created SERVER and EXTERNLOGIN. But I did not try to create using sp_remote_table(). I think view created on oracle is not available to being see on SA12. I will let you guys know it after I have further test. Thanks and best regards, hakwoo KIM
(30 Oct '13, 02:37)
hakwoo
Replies hidden
To clarify: You do not need to use sp_remote_tables() to create a proxy table - this system procedure will only help to show which remote tables can be accessed. You might use sp_remote_table with the optional 5th argument to retrieve the table type, something like the following should return all available tables/views etc. on the remote server - you may replace some of the "null" defaults with the according database/schema/table name: call sp_remote_tables('UNIST_ORACLE', null, null, null, 1);
(30 Oct '13, 05:38)
Volker Barth
|
|
I have not used proxy tables with Oracle, however, it seems your proxy location string is unusual: You are using ":" as a delimiter whereas the usual (or required?) delimiter is a dot, and I would assume an Oracle table definition would require four parts: server name, database name, schema name and table/view name - so it would require 3 dots. Cf. this sample location string from the docs:
To clarify the "proxy view" issue: Are you able to create a proxy table to an Oracle table, or does that fail as well? No prolblem to create proxy table using ORACLE TABLE. Actaully, view can not be founded..
(30 Oct '13, 05:44)
hakwoo
|
I have defined Remote Server using GUI call sp_remote_tables('KFF') show VIEW but View is not inside of Proxy table list I guess we are not able to use View unless CREATE SERVER have some option to indicate but what is special on View if we need to do it.