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

asked 28 Oct '13, 22:08

hakwoo's gravatar image

hakwoo
81448
accept rate: 0%

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.

(29 Oct '13, 21:49) Ukraine

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

permanent link

answered 29 Oct '13, 06:45

Reimer%20Pods's gravatar image

Reimer Pods
4.2k334481
accept rate: 12%

edited 29 Oct '13, 08:04

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:

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?

permanent link

answered 30 Oct '13, 05:40

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659
accept rate: 32%

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

(30 Oct '13, 05:44) hakwoo
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×62
×27

question asked: 28 Oct '13, 22:08

question was seen: 1,072 times

last updated: 30 Oct '13, 05:44