OraFAQ Forum: SQL & PL/SQL » Accessing a Table contains array in Remote DB

Home » SQL & PL/SQL » SQL & PL/SQL » Accessing a Table contains array in Remote DB (oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Accessing a Table contains array in Remote DB [message #383784]

Fri, 30 January 2009 03:36

vboppa
Messages: 22
Registered: November 2008

Junior Member

Dear Forum,

How to access (create Synonym and Materialized View) a Table contains an Array of type object which is on Remote Database connecting through DBLink.

Thanks.

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #383795 is a reply to message #383784]

Fri, 30 January 2009 04:39

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Exactl y the same way that you'd access it locally, but add @<db link name> after the table name.

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #383803 is a reply to message #383784]

Fri, 30 January 2009 05:51

vboppa
Messages: 22
Registered: November 2008

Junior Member

I have tried with the following statement, but it's not working

SELECT clo1,clo2,col3 x.arr_col1,x.arr_col2,x.arr_col3,x.arr_col4
FROM prod_history@dblink a, table(a.Array_Info ) x

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #383811 is a reply to message #383803]

Fri, 30 January 2009 06:18

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

I had a look in the 9i Error message lookup, and I can't find ' it's not working' in there - any chance of more details?

Can you provide us with a create table script so we can try to reproduce the problem?

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #383812 is a reply to message #383811]

Fri, 30 January 2009 06:33

vboppa
Messages: 22
Registered: November 2008

Junior Member

I have attached script file here with for object, varray, table creation and select statement.

Attachment: Script.txt
(Size: 1.68KB, Downloaded 943 times)

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #383822 is a reply to message #383812]

Fri, 30 January 2009 07:17

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

It works for me:

-- Object Creation

CREATE TYPE MTR_BAL_INFO_OBJ  AS OBJECT
(BALANCE_ID         NUMBER,
 BALANCE_CURRENT    NUMBER(18,2),
 BALANCE_CHANGE     NUMBER(18,2),
 BALANCE_EXPIRY     DATE);
 /

-- VArray Creation

CREATE TYPE MTR_BAL_INFO_ARRAY
AS VARRAY(20) OF MTR_BAL_INFO_OBJ;
/

-- Table Creation

CREATE TABLE MTR_HISTORY
(
  LOGIN_NAME         VARCHAR2(30)               NOT NULL,
  UNIT_TYPE_ID       NUMBER,
  BALANCE_INFO       MTR_BAL_INFO_ARRAY
);
/

insert into mtr_history values ('Me',2,mtr_bal_info_array(mtr_bal_info_obj(1,1,1,sysdate),mtr_bal_info_obj(2,2,2,sysdate+1)));
insert into mtr_history values ('Me',1,mtr_bal_info_array(mtr_bal_info_obj(3,3,3,sysdate+2),mtr_bal_info_obj(4,4,4,sysdate+3)));

select
LOGIN_NAME,
UNIT_TYPE_ID,
x.BALANCE_ID,
x.BALANCE_CHANGE,
x.BALANCE_EXPIRY,
x.BALANCE_CURRENT from mtr_history@loopback a, table(a.balance_info ) x;

LOGIN_NAME                     UNIT_TYPE_ID BALANCE_ID BALANCE_CHANGE BALANCE_E BALANCE_CURRENT
------------------------------ ------------ ---------- -------------- --------- ---------------
Me                                        2          1              1 30-JAN-09               1
Me                                        2          2              2 31-JAN-09               2
Me                                        1          3              3 01-FEB-09               3
Me                                        1          4              4 02-FEB-09               4

Loopback is simple datbase link that connect back to the schema I'm doing this in.

What do you get when you run it?
What version of Oracle are you running - I'm using 10gr2?

[Updated on: Fri, 30 January 2009 07:18]

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #383877 is a reply to message #383822]

Fri, 30 January 2009 21:55

vboppa
Messages: 22
Registered: November 2008

Junior Member

SQL> select
LOGIN_NAME,
UNIT_TYPE_ID,
x.BALANCE_ID,
x.BALANCE_CHANGE,
x.BALANCE_EXPIRY,
x.BALANCE_CURRENT from mtr_history@REPO_CC a,
table(a.balance_info ) x;
x.BALANCE_CURRENT from mtr_history@REPO_CC a,
table(a.balance_info ) x
*
ERROR at line 7:
ORA-21700: object does not exist or is marked for delete

I am getting the above error.

Remote database version is 9i
and My database is 10gR2

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #383878 is a reply to message #383784]

Fri, 30 January 2009 22:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>ORA-21700: object does not exist or is marked for delete
prove object exists.

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #383888 is a reply to message #383877]

Sat, 31 January 2009 00:16

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle versions with 4 decimals.

Regards
Michel

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #384036 is a reply to message #383878]

Sun, 01 February 2009 22:59

vboppa
Messages: 22
Registered: November 2008

Junior Member

How can i prove? Is there any way?

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #384037 is a reply to message #383784]

Sun, 01 February 2009 23:05

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you

While using SQL execute SELECT so details about object are displayed.
use CUT & PASTE from whole session showing the SELECT & returned result set.

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #384040 is a reply to message #384037]

Sun, 01 February 2009 23:31

vboppa
Messages: 22
Registered: November 2008

Junior Member

Hi,

The following is the select statement and th result.

SQL> SELECT
a.login_name, a.unit_type_id, x.balance_id, x.balance_change,
x.balance_expiry, x.balance_current
FROM mtr_history@testdb10.regress.rdbms.dev.us.oracle.com a,
2 TABLE(a.balance_info ) x; 3 4 5
TABLE(a.balance_info ) x
*
ERROR at line 5:
ORA-21700: object does not exist or is marked for delete

Kindly tell me what may be the problem? It's working on same DB from different users.

Thanks

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #384042 is a reply to message #383784]

Sun, 01 February 2009 23:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Kindly tell me what may be the problem?
The problem is:
ERROR at line 5:
ORA-21700: object does not exist or is marked for delete

>It's working on same DB from different users.
For the user where it is "not working" TABLE(a.balance_info ) x does not exist.

Since "It's working on same DB from different users." then login as 1 of those users.

Realize TABLE_A exists for USER_X, but does not exist for USER_Y.
SELECT COUNT(*) FROM TABLE_A;
works for USER_X & fails for USER_Y.

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #384049 is a reply to message #384042]

Mon, 02 February 2009 00:16

vboppa
Messages: 22
Registered: November 2008

Junior Member

Dear Sir,

>It's working on same DB from different users.
>For the user where it is "not working" TABLE(a.balance_info ) x does not exist.

The schema is on same DB and conatains select table and execute on object of VARRAY previliges.

My requirement is the same table whicha has VARRAY of type object needs to access from other database. For this simply i have created a Database link and issued same query. It's giving error.

Is there any seperate query to select a table which contains VARRAY of object from Remote Database.

I would appreciate your valuable time to resolve my issue.

Thanks

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #384051 is a reply to message #383784]

Mon, 02 February 2009 00:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you

Do NOT describe/explain/enumerate your problem.

use CUT & PASTE to show exactly what you are doing & how Oracle responds.

If we can not replicate your problem, we can not provide a solution.

I really, really, really do not KNOW what EXACTLY your problem is.

Please use CUT & PASTE to show us what you are doing & seeing!

>It's giving error.
Worthless statement.

You give me solution to "It's giving error."!

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #384062 is a reply to message #384051]

Mon, 02 February 2009 01:08

vboppa
Messages: 22
Registered: November 2008

Junior Member

I hope the following statements can be useful to simulate the prioblem

Statements executed in X Schema of XYZ DB
SQL> CREATE TYPE MTR_BAL_INFO_OBJ AS OBJECT
(BALANCE_ID NUMBER,
BALANCE_CURRENT NUMBER(18,2),
BALANCE_CHANGE NUMBER(18,2),
BALANCE_EXPIRY DATE);

Type created.

SQL> CREATE TYPE MTR_BAL_INFO_ARRAY
AS VARRAY(20) OF MTR_BAL_INFO_OBJ;

Type created.

SQL> CREATE TABLE MTR_HISTORY
( LOGIN_NAME VARCHAR2(30),
UNIT_TYPE_ID NUMBER,
BALANCE_INFO MTR_BAL_INFO_ARRAY
);

Table created.

SQL> insert into mtr_history values ('Me',2,mtr_bal_info_array(mtr_bal_info_obj(1,1,1,sysdate),mtr_bal_info_obj(2,2,2,sysdate+1)));

1 row created.

SQL> insert into mtr_history values ('Me',1,mtr_bal_info_array(mtr_bal_info_obj(3,3,3,sysdate+2),mtr_bal_info_obj(4,4,4,sysdate+3)));

1 row created.

SQL> grant select on MTR_HISTORY to Y;

Grant succeeded.

SQL> grant execute on MTR_BAL_INFO_OBJ to Y;

Grant succeeded.

Statements executed in Y Schema of XYZ DB
SQL> create database link test connect to X identified by X using 'XYZ';

Database link created.

SQL> select a.LOGIN_NAME,a.UNIT_TYPE_ID,x.BALANCE_ID,x.BALANCE_CHANGE,x.BALANCE_EXPIRY,
x.BALANCE_CURRENT from mtr_history@test a, table(a.balance_info ) x;

LOGIN_NAME UNIT_TYPE_ID BALANCE_ID BALANCE_CHANGE BALANCE_E
------------------------------ ------------ ---------- -------------- ---------
BALANCE_CURRENT
---------------
Me 2 1 1 02-FEB-09
1

Me 2 2 2 03-FEB-09
2

Me 1 3 3 04-FEB-09
3

LOGIN_NAME UNIT_TYPE_ID BALANCE_ID BALANCE_CHANGE BALANCE_E
------------------------------ ------------ ---------- -------------- ---------
BALANCE_CURRENT
---------------
Me 1 4 4 05-FEB-09
4

Statements executed in Y Schema of XYZ DB
SQL> create database link test connect to X identified by X using 'XYZ';

Database link created.

SQL> select a.LOGIN_NAME,a.UNIT_TYPE_ID,x.BALANCE_ID,x.BALANCE_CHANGE,x.BALANCE_EXPIRY,
x.BALANCE_CURRENT from mtr_history@test a, table(a.balance_info ) x;

LOGIN_NAME UNIT_TYPE_ID BALANCE_ID BALANCE_CHANGE BALANCE_E
------------------------------ ------------ ---------- -------------- ---------
BALANCE_CURRENT
---------------
Me 2 1 1 02-FEB-09
1

Me 2 2 2 03-FEB-09
2

Me 1 3 3 04-FEB-09
3

LOGIN_NAME UNIT_TYPE_ID BALANCE_ID BALANCE_CHANGE BALANCE_E
------------------------------ ------------ ---------- -------------- ---------
BALANCE_CURRENT
---------------
Me 1 4 4 05-FEB-09
4

Statements executed in A Schema of ABC DB
SQL> create database link test connect to X identified by X using 'XYZ';

Database link created.

-- Without VARRAY of type object
SQL> select a.LOGIN_NAME, a.UNIT_TYPE_ID from mtr_history@test a;

LOGIN_NAME UNIT_TYPE_ID
------------------------------ ------------
Me 2
Me 1

-- With VARRAY of type object

SQL> select a.LOGIN_NAME,a.UNIT_TYPE_ID,x.BALANCE_ID,x.BALANCE_CHANGE,x.BALANCE_EXPIRY,
x.BALANCE_CURRENT from mtr_history@test a, table(a.balance_info ) x;
x.BALANCE_CURRENT from mtr_history@test a, table(a.balance_info ) x
*
ERROR at line 2:
ORA-21700: object does not exist or is marked for delete

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #384074 is a reply to message #384062]

Mon, 02 February 2009 02:16

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

The difference is that user a does not have the same privileges as user y. When there are insufficient privileges such that Oracle cannot see an object, it returns an error that the object does not exist. Try granting the same privileges to a as y.

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #384089 is a reply to message #384062]

Mon, 02 February 2009 02:52

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

Apparently there may be some restrictions on what types can be passed from one database to another that are not simulated by a loopback database link. You might try experimenting with writing something on the local database that converts things to a different format that for example does not include a table function, then access that from the remote db.

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #384091 is a reply to message #384074]

Mon, 02 February 2009 02:57

vboppa
Messages: 22
Registered: November 2008

Junior Member

Hi,

Schema "A" is not on Same DB. It's on other database accessing objects through DB Link.

Thanks.

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #384096 is a reply to message #384062]

Mon, 02 February 2009 03:07

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

The following section confirms the restriction on remote objects via database link and suggests a workaround using types with matching oid's in both databases.

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28371/adobjbas.htm#CIHBIBEA

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #384102 is a reply to message #384091]

Mon, 02 February 2009 03:12

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

Here is an example:

http://stackoverflow.com/questions/378299/referencing-oracle-user-defined-types-over-dblink#380453

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #384123 is a reply to message #384102]

Mon, 02 February 2009 03:48

vboppa
Messages: 22
Registered: November 2008

Junior Member

Hi,

I have tried with that example, I have not came right. Could you apply to my code.

Thanks.

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #384127 is a reply to message #384123]

Mon, 02 February 2009 03:56

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

I only have one database, so I cannot reproduce your problem. All I can do is what JRowbottom already did with a loopback database link. Please post a copy and paste of what you tried with the oid and the results. You might try just a simple test first, like using the exact example to see if that works for you, then try adapting it to your current problem.

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #384147 is a reply to message #384127]

Mon, 02 February 2009 06:04

vboppa
Messages: 22
Registered: November 2008

Junior Member

Thanks a lot Barbara Boehmer.

I have Resolved the issue with Object Identifier.

Both database sides needs to be create the Same objects with Same Object Identifiers.

Thanks.

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #463160 is a reply to message #383784]

Tue, 29 June 2010 22:26

dpandhi
Messages: 4
Registered: July 2009

Junior Member

Hi Vasudev,

Could you please share the code.

Thanks
Deepika

As you said.................................

I have Resolved the issue with Object Identifier.

Both database sides needs to be create the Same objects with Same Object Identifiers.

Thanks.

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #463163 is a reply to message #463160]

Tue, 29 June 2010 22:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I doubt OP will be back with solution after 16 MONTHS

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #463174 is a reply to message #463160]

Wed, 30 June 2010 00:29

vboppa
Messages: 22
Registered: November 2008

Junior Member

Dear Deepika,

The code is in the following.

SOURCE DATABASE
----------------

SQL> SELECT SYS_OP_GUID() FROM DUAL; 
 
SYS_OP_GUID()
--------------------------------
8A3920795594A9E1E040582B24437453


CREATE TYPE MTR_BAL_INFO_OBJ OID '8A3920795594A9E1E040582B24437453' AS OBJECT
(BALANCE_ID NUMBER,
BALANCE_CURRENT NUMBER(18,2),
BALANCE_CHANGE NUMBER(18,2),
BALANCE_EXPIRY DATE);

Type created.

SQL> SELECT SYS_OP_GUID() FROM DUAL; 
 
SYS_OP_GUID()
--------------------------------
8A392079559CA9E1E040582B24437453

CREATE TYPE MTR_BAL_INFO_ARRAY OID '8A392079559CA9E1E040582B24437453'
AS VARRAY(20) OF MTR_BAL_INFO_OBJ;

Type created.

SQL> CREATE TABLE MTR_HISTORY
( LOGIN_NAME VARCHAR2(30),
UNIT_TYPE_ID NUMBER,
BALANCE_INFO MTR_BAL_INFO_ARRAY
);

Table created.

SQL> insert into mtr_history values ('Me',2,mtr_bal_info_array(mtr_bal_info_obj(1,1,1,sysdate),mtr_bal_info_obj(2,2,2,sysdate+1)));

1 row created.

SQL> insert into mtr_history values ('Me',1,mtr_bal_info_array(mtr_bal_info_obj(3,3,3,sysdate+2),mtr_bal_info_obj(4,4,4,sysdate+3)));

1 row created.


SQL> COMMIT;
 
Commit complete

REMOTE DATABASE
---------------

SQL> CREATE TYPE MTR_BAL_INFO_OBJ OID '8A3920795594A9E1E040582B24437453' AS OBJECT
     (BALANCE_ID NUMBER,
     BALANCE_CURRENT NUMBER(18,2),
     BALANCE_CHANGE NUMBER(18,2),
     BALANCE_EXPIRY DATE);
     /
 
Type created

SQL> CREATE TYPE MTR_BAL_INFO_ARRAY OID '8A392079559CA9E1E040582B24437453'
     AS VARRAY(20) OF MTR_BAL_INFO_OBJ;
     /
 
Type created

SQL> select a.LOGIN_NAME,a.UNIT_TYPE_ID,x.BALANCE_ID,x.BALANCE_CHANGE,x.BALANCE_EXPIRY,
  2  x.BALANCE_CURRENT from [email]mtr_history@test[/email] a, table(a.balance_info ) x;
 
LOGIN_NAME                     UNIT_TYPE_ID BALANCE_ID       BALANCE_CHANGE BALANCE_EXPIRY      BALANCE_CURRENT
------------------------------ ------------ ---------- -------------------- -------------- --------------------
Me                                        2          1                 1.00 6/30/2010 10:4                 1.00
Me                                        2          2                 2.00 7/1/2010 10:45                 2.00
Me                                        1          3                 3.00 7/2/2010 10:46                 3.00
Me                                        1          4                 4.00 7/3/2010 10:46                 4.00
 
SQL>

Regards
Vasudev Boppa
+91-9740311464

[Updated on: Wed, 30 June 2010 00:57] by Moderator

Report message to a moderator

Re: Accessing a Table contains array in Remote DB [message #463336 is a reply to message #463163]

Wed, 30 June 2010 16:06

dpandhi
Messages: 4
Registered: July 2009

Junior Member

Thanks Vasudev for reply

Report message to a moderator

Previous Topic:	Count column with column value as 'UNDEFINED'
Next Topic:	how to execute the statement exist in clob column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 06 22:41:09 CST 2024