What version of PowerBuilder are you using?
Waht is the platform you are on?
What version of Oracle are you using?

And finally, what is the issue you are experiencing?

PB Version: 2017 R3 build 1915

Platform: Windows 10

Oracle Version: 12.2.0.1.0

Issue:

PowerBuilder is generating the following Oracle data dictionary queries hundreds of times:

SELECT OWNER, SYNONYM_NAME
FROM
SYS.ALL_SYNONYMS WHERE OWNER = 'PUBLIC' AND SYNONYM_NAME = :1;

SELECT TABLE_OWNER, TABLE_NAME
FROM
SYS.ALL_SYNONYMS WHERE OWNER = 'PUBLIC' AND SYNONYM_NAME = :1 AND (DB_LINK
IS NULL OR DB_LINK = '');

From what I can recall, using SyntaxFromSQL on an Oracle connection was expensive (the too many calls to ALL_SYNONYMS that you mention).

When developing my query tool, https://powertothebuilder.com/UltimateSuiteSQL.html, I ended up solving this issue by creating my own SyntaxFromSQL function for Oracle. The solution uses a small C++ DLL to call out to the Oracle API.

I posted the code here: https://powertothebuilder.com/downloads/PBOracleDist.zip

The main object is n_cst_oracle_dw_helper. Try the of_syntaxfromsql in that object to see if it helps. There's a dependency on a string processing object, nv_cppstringx. You'll have to import this as well. The n_cst_oracle_dw_helper object is using a Replace function from nv_cppstringx, but you can replace that with your own implementation if you have one. The nv_cppstringx object came from someone else many many years ago but I can't remember who.

There are two dependencies on dlls, PBOracle.dll and pbstringx.dll (both included in the zip). If you replace the reference to the Replace method in nv_cppstringx, then you won't need pbstringx.dll.

Do you have your table anmes all defined with a PUBLIC SYNONYM?

That's what we do: every table in a schema has a PUBLIC synomym, making it easy for each transaction to find the correct tables.

Unless all of your activity is within a single schema, your code MAY be hunting for the correct schema.tablename for each transaction.


Olan

Hi,

have same problems with SyntaxFromSQL and Oracle 12.

I found out that PB tries to find the table name for each column.

So PB first calls SELECT TABLE_NAME FROM SYS.ALL_TAB_COLUMNS ... for the first possible table in SQL.

If not found PB tries with SELECT SYNONYM_NAME FROM SYS.ALL_SYNONYMS S, SYS.ALL_TAB_COLUMNS T ...

PB retries it for each possible table.

I think, Appeon should find out another way or check for synonyms only after checking in all tables.

To make SyntaxFromSQL faster you can:

• qualify column names in SELECT with table name if you have more than one table in SQL (so PB don't need to search for the table)
  
  
• don't use Oracle keywords like SYSDATE allone in SELECT clause. PB thinks it is a column name (don't know why) and tries to find this column in one of the tables. As workaround you can use the keyword in a function or expression (e. g. SYSDATE + 0)

HTH,

René

When developing my query tool, https://powertothebuilder.com/UltimateSuiteSQL.html, I ended up solving this issue by creating my own SyntaxFromSQL function for Oracle. The solution uses a small C++ DLL to call out to the Oracle API.

Thank your for your own SyntaxFromSQL-Function. This works very well for us and is much faster than the original Powerbuilder-Function.

Now we migrate our program to 64bit and your PBOracle.dll is 32bit. Is there a possibility to get the PBOracle.dll compiled in 64bit?