Problem with synonym in SqlPLSQLCommand

[ivan.klainic@igea.hr]

Hello!

This is tested against Oracle DB version 9, 10 and 11 with Oracle Clients 9 and 10.

Case:
EOP_ZK_ZK_OPER2 is private synonym in user ZK_USER for package ZK_OPER2 in user EOP_ZK

or in other words:

User EOP_ZK has a PL/SQL package ZK_OPER2 and grants execute privilege for package ZK_OPER2 to ZK_USER.
ZK_USER has synonym for package EOP_ZK.ZK_OPER2 with name EOP_ZK_ZK_OPER2.

Application is connecting to Oracle DB to user ZK_USER.
We tried to migrate runtime 4.0 to 5.2 or 6.2.

Here are results regrading call to PLSQL procedures through synonyms.

TD 2005 version 4.0 (everything is OK):
---------------------------------------

1. SELECT statement :

Call SqlPrepareAndExecute ( hSql,'SELECT EOP_ZK_ZK_OPER2.F001(u.Ulozak_ID) ... ')


2. PL/SQL statement :

If NOT SqlPLSQLCommand ( hSqlUpdate,'EOP_ZK_ZK_OPER2.PF006( nUlozak_ID, lsPlomba )')
Call SalMessageBeep( 0 )
Call SalMessageBox( 'Error message', 'Error', MB_IconAsterisk )



TD 2005 version 5.2 and version 6.2
------------------------------------

1. SELECT statement is OK

2. PL/SQL statement - not OK (screenshot attached)

ORA-04043: object EOP_ZK.EOP_ZK_ZK_OPER2 does not exist



but work with anonymous PL/SQL block:

Set sSQLCommand = '
BEGIN

EOP_ZK_ZK_OPER2.PF006B ( :nUlozak_ID, :lsPlomba);

END;'

Call SqlOraPLSQLPrepare( hSqlUpdate, sSQLCommand )
If NOT SqlOraPLSQLExecute( hSqlUpdate )
Call SalMessageBeep( 0 )
Call SalMessageBox( 'Error message', 'Error', MB_IconAsterisk )



We don't wan't to modify more then 200 SqlPLSQLCommand with anonymous PL/SQL block !!!
During tests, database objects aren't modified. Obvious is that the same code doesn't function same way in diffrent runtimes.

Do you have any suggestions?
Thanks

[markkuvento]

Hi Ivan,

I do not have TD6.2 installed and have no time to follow your ZK-namings

No problems with a private synonym in TD6.1-SP4.

BR,
Markku Vento

[ivan.klainic@igea.hr]

Hi Markku,

Thanks for your effort, but post you mentioned doesn't resolve my problem.

I have installed:
- TD 4.0: no problem
- TD 5.2 SP5 - problem exists
- TD 6.2 (EMP...) - problem exists

I'm still waiting for answer from GUPTA.

BR