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?

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é

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

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.

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 = '');

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?