cancel
Showing results for 
Search instead for 
Did you mean: 

PB .net 12.5.2, datawindow retrieves choke on Oracle TO_CHAR function

Former Member
0 Kudos

Oracle 11g, we've got at least a couple of these datawindows. Just another PB .net glitch?

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Todd;

  yes, sounds like a PB.Net specific issue if iy works OK from a PB Classic compiled application. I would recommend that you report this to SAP technical support.

  BTW: what Oracle connectivity mechanism are you using?

Regards ... Chris

Former Member
0 Kudos

Hi Chris thanks, we are connecting via 32 bit ODBC

Former Member
0 Kudos

Have you run a trace to see what PB is sending to Oracle?

What happens if using the native ORA driver? 

Former Member
0 Kudos

Terry,

Not sure I know off hand how to run this trace you speak of, or how to utilize the native ORA driver. I may follow up on those when/if I get the time.The problem shouldn't be in the ODBC connection, as the SQL works fine from an ISQL session in the database painter with the same ODBC connection. I turned on the PFC SQL debug service, that didn't give any more good info, it added an additional informational window that turned out to be misleading and irrelevant.

It looks like the to_char problem may only be when converting a date to a string, seems to work if trying to convert a # to a string.

I ended up removing the to_char from the dw sql, and converting the dates to formatted string using computed fields.

Regards,

Todd Oesterreich

Former Member
0 Kudos

To trace, simply add trace to the dbms setting:

sqlca.dbms = "trace odb"

The native driver is the ORA driver.  It works much better than ODBC.

sqlca.dbms = "ora"

Former Member
0 Kudos

Terry, here is the trace output, it essentially reflects the contents of the original error messagebox,

Regards, Todd

(ce01ac8): OPEN CURSOR: (0.000 MS / 309.854 MS)

(ce0e138): DUMMY CURSOR CONNECTION :

(ce0e138): USERID=xxxxxxx

(ce0e138): SERVER=XXXXXXXX

(ce0e138): DBPARM=ConnectString='DSN=XXXXXXXX; UID=xxxxxxx; PWD=<******>; SERVER=xxxxxxxx.xxxx.xxxxxxxxx.com' (0.000 MS / 0.000 MS)

(ce0e138): PREPARE:

(ce0e138):  (0.017 MS / 0.017 MS)

(ce0e138): *** ERROR 999 ***(rc -1) : Syntax Error: No SQL Command

(ce0e138): CANCEL: (0.001 MS / 0.018 MS)

(ce0e138): DISCONNECT: (0.003 MS / 0.021 MS)

(ce0e138): SHUTDOWN DATABASE INTERFACE: (0.000 MS / 0.021 MS)

(ce01ac8): COMMIT: (1.019 MS / 310.873 MS)

(ce01ac8): CANCEL: (0.000 MS / 310.873 MS)

(ce01ac8): DISCONNECT: (3.374 MS / 314.247 MS)

(ce01ac8): SHUTDOWN DATABASE INTERFACE: (0.000 MS / 314.247 MS)

former_member190719
Active Contributor
0 Kudos

You can't used database specific commands (e.g., TO_CHAR) in a SQL statement using an ODBC driver   What you have to do is specify the ODBC equivalent than then let the ODBC driver convert it for you.  For example, if you do:

WHERE {fn CONVERT(EMPNO,SQL_CHAR)}

Then what the ODBC driver should pass when it's talking to Oracle is:

WHERE to_char(EMPNO)

Former Member
0 Kudos

It's been working in PB Classic all along, still works in PB Classic 12.5.2, same ODBC connection. Doesn't work in PB .net 12.5.2

This is the identical trace in PB Classic 12.5.2

Well, for proprietary reasons I'm not going to show that, suffice to say that the trace shows some "preparation" being done to the SQL with the TO_CHAR, then executes it and fetches a few hundred rows.

Former Member
0 Kudos

I'll add that the to_char works correctly in both PB 12.5.2 Classic and also as a PB 12.5.2 winform app.

Former Member
0 Kudos

I'll also add that we see this both at runtime, and retrieving in the IDE/datawindow painter. Paste the SQL in the ISQL Session tab, no problems.