13 replies to this topic

[Joseph Serra]

Hi guys

Is it possible for ProIV to access an Oracle Synonym and acess it like a table or view?

Cheers...

Joseph

[Jeff Hon]

Hi Joe!

Yes, you can access an Oracle synonym from Pro-IV. Create your Pro-IV file def and put in the Oracle Synonym name as the Alternate file name. The Alternate field names in the Pro-IV file def must correspond to the Oracle column names obviously. That's it really. Its just like pointing a Pro-IV file def at an Oracle view or table.

Jeff.

[Joseph Serra]

Hi Joe!

Yes, you can access an Oracle synonym from Pro-IV. Create your Pro-IV file def and put in the Oracle Synonym name as the Alternate file name. The Alternate field names in the Pro-IV file def must correspond to the Oracle column names obviously. That's it really. Its just like pointing a Pro-IV file def at an Oracle view or table.

Jeff.

Hi Jeff

Thanks for the quick reply.

I must be doing something wrong with my synonym then. I created a public synonym for a table, but I can't seem to access it from ProIV. Could it be permissions? Do I need to do something else?

Joseph

[Jeff Hon]

Joe,

Firstly, from SQL*Plus, can you list (SELECT *) data from your synonym?

If you can, then maybe try running the following:

SQL> GRANT SELECT ON <sysnonym name> TO <oracle user>;

Jeff.

[Joseph Serra]

Weird, that gives me an error saying 'table or view does not exist'...

But I can do a "select * from <synonym name>".

Do you specifically create public synonyms?

[Jeff Hon]

The GRANT SELECT was a shot in the dark. Sorry...

We use synonyms here. Here's an example of the SQL of a synonym for a view belonging to another schema in a separate tablespace:

-- Create the new synonym
create synonym V_MEMBERS for <oracle_user>.V_MEMBERS@<database_link>;

The Pro-IV file def looks like this:

22/06/07 FILE DEFINITION DEV/JPH/PTS_98

CO- AUTO EXTERNAL FILE KEY RECORD
DIV SEQUENCED RECORD FORMAT TYPE LENGTH LENGTH
FILE NAME: V_MEMBER Y ORA 34 85
ALTERNATE: V_MEMBERS RECORD CODE: MSG:
DESC: File definition for V_MEMBERS ODS view
Delete
-------------------------- R E C O R D L A Y O U T -------------------------
DATA MAX FILL SPECIAL ARRAY
SEQ TYPE VARIABLE NAME LEN CODE DISPLAY-CODE CHECK SIZE
--- ---- -------------------------------- --- ---- ------------ -------- -----
001 AK V_MEMBER_DATABASE_NAME 3
002 AK V_MEMBER_EMPLOYER_ACCOUNT_ID 14
003 AK V_MEMBER_EMPLOYEE_DATABASE_NAME 3
004 AK V_MEMBER_EMPLOYEE_ACCOUNT_ID 14
005 A V_MEMBER_MEMBERSHIP_CATEGORY 40
006 A V_MEMBER_EMPLOYER_ACCESS_AUTH 1
007 N V_MEMBER_EMPLOYMENT_CEASE_DATE 10 DATE4 DATE4

I wrote a dummy Pro-IV update function to just select records from this Pro-IV file def and display in the ARNE in a UMSG and got the results as expected.

Not sure what else you need to do Joe. Any other members know something that I have left out?

Jeff.

Edited by Jeff Hon, 22 June 2007 - 06:55 AM.

[Joseph Serra]

Thanks for your help Jeff.

I think it might have something to do with Friday afternoon. I think I will give up on this today and tackle on monday morning.

[Jeff Hon]

That's a good idea coz I'm going to the pub now anyway!

[Rob Donovan]

Hi Joe,

I'm fairly sure there are Synonyms used in other parts of Super, try a logic search for SYNONYM, possibly the BPOST functions....

Rob.

[Wayne Biggs]

Joe,

Does the schema used by the Pro-IV session have access to the underlying table? I'm assuming you need a public synonym because the table is owned by a different schema in which case you can add the following: -

grant select, update, delete on <synonym> to public ;

or (preferably, from a security perspective)

grant select, update, delete on schema.tablename to proiv_schema ;

are you specifiying public when you create the synonym as follows: -

create public synonym <synonym> for schema.tablename ;

I'm having difficulty typing the word synonym now so i'm off for a coffee.

hth,

Wayne

[Joseph Serra]

Hi Rob

Thanks for the reply. I did do a search, but all I found was synonyms created on the fly and then referenced in embedded SQL.

I was more interested in Synonyms mapped to a ProIV file definition. But from what I gather, it is possible, I'm just having a blond moment...

Joseph

[Joseph Serra]

Wayne!!! Hiya mate...

Thanks for the reply. I created both, public and private. And I fiddled with the grants too.

I then spat the dummy, swore a lot and gave up...

Anyway, thanks for the help, I shall give it another go sometime today...

See you soon buddy...


Joseph

[Jeff Hon]

Hi Joe,

Just out of interest, what error is your Pro-IV function returning to you when you try to access the Oracle synonym?

Jeff.

Edited by Jeff Hon, 26 June 2007 - 12:54 AM.

[Joseph Serra]

Woohoo!!! It works!!!

I don't know what I was doing wrong, but I tried it today and it just works.

I was getting 'ORA-00942: table or view does not exist'. But I have just created a public synonym and created a ProIV logical file def, and then a function to use it, and it works.

Thanks for you help, everyone...

Joseph