Using synonym for reference Object type
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Using synonym for reference Object type

  1. #1
    Join Date
    May 2002
    Posts
    6
    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

  2. #2
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    What was the problem?
    You can just create a synonym as
    create synonym xyz for EMP;
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  3. #3
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  4. #4
    Join Date
    May 2002
    Posts
    6
    It does not work.
    When insert the record in the table from scott, Oracle gives the error - Invalid column name.

  5. #5
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  6. #6
    Join Date
    May 2002
    Posts
    6
    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


  7. #7
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  8. #8
    Join Date
    May 2002
    Posts
    6
    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..


  9. #9
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width