Home » SQL & PL/SQL » SQL & PL/SQL » Accessing a Table contains array in Remote DB (oracle 9i)
Accessing a Table contains array in Remote DB [message #383784] Fri, 30 January 2009 03:36 Go to next message
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.
Re: Accessing a Table contains array in Remote DB [message #383795 is a reply to message #383784] Fri, 30 January 2009 04:39 Go to previous messageGo to next message
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.
Re: Accessing a Table contains array in Remote DB [message #383803 is a reply to message #383784] Fri, 30 January 2009 05:51 Go to previous messageGo to next message
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
Re: Accessing a Table contains array in Remote DB [message #383811 is a reply to message #383803] Fri, 30 January 2009 06:18 Go to previous messageGo to next message
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?
Re: Accessing a Table contains array in Remote DB [message #383812 is a reply to message #383811] Fri, 30 January 2009 06:33 Go to previous messageGo to next message
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 208 times)
Re: Accessing a Table contains array in Remote DB [message #383822 is a reply to message #383812] Fri, 30 January 2009 07:17 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Accessing a Table contains array in Remote DB [message #383878 is a reply to message #383784] Fri, 30 January 2009 22:24 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>ORA-21700: object does not exist or is marked for delete
prove object exists.
Re: Accessing a Table contains array in Remote DB [message #383888 is a reply to message #383877] Sat, 31 January 2009 00:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Accessing a Table contains array in Remote DB [message #384036 is a reply to message #383878] Sun, 01 February 2009 22:59 Go to previous messageGo to next message
vboppa
Messages: 22
Registered: November 2008
Junior Member
How can i prove? Is there any way?
Re: Accessing a Table contains array in Remote DB [message #384037 is a reply to message #383784] Sun, 01 February 2009 23:05 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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.

Re: Accessing a Table contains array in Remote DB [message #384040 is a reply to message #384037] Sun, 01 February 2009 23:31 Go to previous messageGo to next message
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
Re: Accessing a Table contains array in Remote DB [message #384042 is a reply to message #383784] Sun, 01 February 2009 23:38 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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.
Re: Accessing a Table contains array in Remote DB [message #384049 is a reply to message #384042] Mon, 02 February 2009 00:16 Go to previous messageGo to next message
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
Re: Accessing a Table contains array in Remote DB [message #384051 is a reply to message #383784] Mon, 02 February 2009 00:23 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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."!

Re: Accessing a Table contains array in Remote DB [message #384062 is a reply to message #384051] Mon, 02 February 2009 01:08 Go to previous messageGo to next message
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

Re: Accessing a Table contains array in Remote DB [message #384074 is a reply to message #384062] Mon, 02 February 2009 02:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
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.
Re: Accessing a Table contains array in Remote DB [message #384089 is a reply to message #384062] Mon, 02 February 2009 02:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
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.

Re: Accessing a Table contains array in Remote DB [message #384091 is a reply to message #384074] Mon, 02 February 2009 02:57 Go to previous messageGo to next message
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.
Re: Accessing a Table contains array in Remote DB [message #384096 is a reply to message #384062] Mon, 02 February 2009 03:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
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
Re: Accessing a Table contains array in Remote DB [message #384102 is a reply to message #384091] Mon, 02 February 2009 03:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
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
Re: Accessing a Table contains array in Remote DB [message #384123 is a reply to message #384102] Mon, 02 February 2009 03:48 Go to previous messageGo to next message
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.
Re: Accessing a Table contains array in Remote DB [message #384127 is a reply to message #384123] Mon, 02 February 2009 03:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
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.

Re: Accessing a Table contains array in Remote DB [message #384147 is a reply to message #384127] Mon, 02 February 2009 06:04 Go to previous messageGo to next message
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.
Re: Accessing a Table contains array in Remote DB [message #463160 is a reply to message #383784] Tue, 29 June 2010 22:26 Go to previous messageGo to next message
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.

Re: Accessing a Table contains array in Remote DB [message #463163 is a reply to message #463160] Tue, 29 June 2010 22:38 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
I doubt OP will be back with solution after 16 MONTHS
Re: Accessing a Table contains array in Remote DB [message #463174 is a reply to message #463160] Wed, 30 June 2010 00:29 Go to previous messageGo to next message
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 Go to previous message
dpandhi
Messages: 4
Registered: July 2009
Junior Member
Thanks Vasudev for reply
Previous Topic: Count column with column value as 'UNDEFINED'
Next Topic: how to execute the statement exist in clob column
Goto Forum:
  


Current Time: Thu Dec 08 02:09:10 CST 2016

Total time taken to generate the page: 0.08740 seconds