Using synonym for reference Object type

[Supriyo]

How can I use synonyms for reference of object type in table?
I am using an object type in my table. I want to access the table from different user using a synonym.

For example,

I have one table EMP (empid number, audit_trail audit_trail_obj) where audit_trail_obj is an Oracle object type embedded in EMP table.

Now if I want to access the same table from different user thru a synonym, what should be the process to do so?

Thanx in advance,
Supriyo

[Tarry]

What was the problem?
You can just create a synonym as
create synonym xyz for EMP;

[TimHall]

I don't know all the names of your objects and schemas so it's difficult to explain so here's an example:

The object and table owner is DEV and the other schema that will access the table and object is SCOTT:

Connect as DEV:

create type test1_type as object (id number(10), description varchar2(50));
/

create table test1_tab (
id NUMBER(10),
test1_col test1_type);

grant select, insert, update, delete on test1_tab to scott;
grant execute on test1_type to scott;

Connect as SCOTT:

create synonym test1_tab for dev.test1_tab;
create synonym test1_type for dev.test1_type;

insert into test1_tab(id, test1_col) values (1, test1_type(1, 'ONE'));

This should explain how you need to approach it. If lots of schemas are going to need access to this table you may want to substitue the synonym for a public synonym. This saves you defining it for each referencing schema.

Cheers

[Supriyo]

It does not work.
When insert the record in the table from scott, Oracle gives the error - Invalid column name.

[TimHall]

I've just tried again. If I cut and paste my example as I said it works perfectly. I think you must be typing something wrong. Make sure you grant execute on the type or you will not be able to use the type constructor in the insert.

Cheers

[Supriyo]

I've just tried again. But same problem again occur.

For example..

SQL> connect bank/bank@hclinf011
Connected.

SQL> create type test1_type as object (id number(10), description varchar2(50));
2 /

Type created.

SQL> create table test1_tab (
2 id NUMBER(10),
3 test1_col test1_type);

Table created.

SQL> grant select, insert, update, delete on test1_tab to bank_app;

Grant succeeded.

SQL> grant execute on test1_type to bank_app;

Grant succeeded.

SQL> connect bank_app/bank_app@hclinf011
Connected.

SQL> create synonym test1_tab for bank.test1_tab;

Synonym created.

SQL> create synonym test1_type for bank.test1_type;

Synonym created.

SQL> insert into test1_tab(id, test1_col) values (1, test1_type(1, 'ONE'));
insert into test1_tab(id, test1_col) values (1, test1_type(1, 'ONE'))
*
ERROR at line 1:
ORA-00904: invalid column name

[TimHall]

I'm assuming the * was originally under the test1_type(1, 'ONE') reference in the insert statement. I'd try a couple of things next:

1) Try the insert in the owners schema to check it works OK.

2) Try the insert in the app schema without using the synonym. Instead use the schema to prefix it :bank.test1_type(1, 'ONE').

What version of Oracle are you using?

Cheers

[Supriyo]

As per your two suggetion, it will work perfectly.
But I want to access the table(Oracle object type embedded) from different user without using schema name.
It is possible....

Oracle Version - 8.1.7.0.0
Thanks..

[TimHall]

Well it definitely works in 9i, and I'm sure I've seen it work OK in 8i also, although I'm not able to prove this at the moment. I wonder if there is some problem with synonyms on objects in your version.

You should raise a TAR on metalink and check if there is a problem with your version. You may need to apply a patch to fix it. You should consider going to 8.1.7.4 anyway.

When you raise the TAR use this example as it will allow support to assess the issue alot quicker.

Cheers