is it possible to create proxy table using view on remote server?

[Former Member]

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

[Former Member]

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.

[reimer_pods]

The CREATE EXISTING TABLE statement needs a remote server, which can be created using CREATE SERVER and an external login CREATE EXTERNLOGIN.

If you've already defined a remote server and login, please show us the statements. You might try calling sp_remote_tables() to list the tables SA "sees" on the remote server.

See this related question Create Proxy Table from Oracle/SAP to SQLA

[VolkerBarth]

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.

[Former Member]

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

[VolkerBarth]

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);

[VolkerBarth]

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:

CREATE EXISTING TABLE employees AT 'myora.database.owner.employees';

---

To clarify the "proxy view" issue: Are you able to create a proxy table to an Oracle table, or does that fail as well?

[Former Member]

No prolblem to create proxy table using ORACLE TABLE. Actaully, view can not be founded..