Problem with synonym in SqlPLSQLCommand

General discussion forum about all databases other than SqlBase.
ivan.klainic@igea.hr

Problem with synonym in SqlPLSQLCommand

Post by ivan.klainic@igea.hr » 04 Nov 2013, 12:52

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
You do not have the required permissions to view the files attached to this post.

markkuvento

Re: Problem with synonym in SqlPLSQLCommand

Post by markkuvento » 11 Dec 2013, 14:19

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

Re: Problem with synonym in SqlPLSQLCommand

Post by ivan.klainic@igea.hr » 12 Dec 2013, 12:56

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

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot], [Google] and 0 guests