TD-18887: Oracle & TD .NET mode $ in table name fails

[afi]

Dear Support Team,
hi all,

analyzing a migration of a large td 5.2 appliaction to td 6.1 .NET has early raised a severe SQL Problem. The application(s) are running against two big Oracle 11g R2 Databases, each containing some hundred tables. In this tables all primary cand foreign key olumns have a $ sign appended (as a reminder for the developers to handle these columns with caution).

Using .NET Target, each statement accessing a oracle column with a $ sign fails:

SELECT APPL$ FROM CONFAPPL INTO :dfApplication WHERE... - FAIL

Using WIN32 Target (native Oracle router) the same statement works of course.

Unfortunately are all tables in the databases named like <SCHEMA>.TBL$XXXX. A statement like

SELECT <XYZ> FROM conf.tbl$apmn

FAILS too with .NET and works with WIN32.

We've done some experiments with masking the statements (appl\$) and with the SUBSTITUTE entry in [oratagwy], but hadn't any success. Of course there is the workaround of using views with renamed column names, but the databases aren't under our buisiness mamagement and not exclusively used from our applications and it will be a hard thing to constitute the creation of a view for each table in the databases.

The developing workstations are running Windows 7 x64 with Oracle 11gR2 32 Bit Clients.

Solutions ?

Best regards, Afi

[Jeff Luther]

Sounds like '$' didn't make it in as a valid char. in the .NET parser, Afi. I am asking internally to see if our router engineer has a workaround for you other than what you wrote. (I passed your msg. along to him.)

I'll update this forum thread when I hear something back. It might be only that I would report this as a TD .NET defect, but maybe there's a way to get around this too?

[Jeff Luther]

I chatted with our TD/router developer who agrees that it looks like something on the TD/router .NET/WPF end that doesn't like that '$' in the SQL command.

I wrote a small test case and as you report, Win32 version works OK but the WPF version causes a SQL error on this statement:

"create table test_dollarsign$ (id integer)"

The error:

OracleError_00926.png

Let me know if this is not the error you get. Odd.. .that's a CREATE TABLE statement but the error is for a missing VALUES section -- that's part of an INSERT statement, not a CREATE TABLE.

FYI, I've attached both my test cases. I'll get this reported as a new TD defect. New defect: TD-18887

Unfortunately, neither of us can think of a better workaround than what you suggested: a VIEW for each table with a '$' in the name.

[afi]

Yes, Jeff the behavior and the error messages using a table, column, or function call with a $ inside are very strange. A statement "SELECT dollarcollumn$ FROM xyz INTO :df1" raises a "key word FROM not found at excepted position" message. A statement "SELECT SUBSTR (chardollarfield$,1,3) ..." let the application hang.

For the developing oracle instances we can work with the view layers above the underlying tables for some months, expecting the fix latest in TD 6.2, which is the final target of the application (compiled to .NET 64) at the end of the year.