Originally posted by: smcasey

We have an Oracle stored procedure call which involves many parameters (IN and OUT).
We have the SQL statement formatted for Oracle adapter, no problems here.
There are no issues and TX executes fine except when two of the parameters have an "%" in
the parameter.

Here is pseudo code of the procedure call via adapter in the map:
DBQUERY (SQL Statement, "xyz.mdq", "DB1")

The SQL Statement looks similar to this:
="call XYZ.STOREDPROCNAME( ?/'" + FN1 POFields:CPData +
"', ?/'" + FN2 POFields:CPData +
"', '" + FN3 POFields:CPData +
"', '" + FN4 POFields:CPData + ....
.... "', '" + FNx POFields:CPData +"', ?, ?)"

We have read the April 2006 forum post on DBQUERY with Oracle LIKE condition.

Question: Is there a switch on the maps to "disable" resource registry lookup for a certain maps?
We would like to disable resource registry lookup so we can assure the parameters get
passed to end database without issues. I cannot find this.

What are folks doing when they run into TX attempting to resolve this as a resource registry?

For each parameter are you substituting a different char and then using the
REPLACE (similar to April 2006 post but for each parameter)?

Using WTX 8.1 with provided Oracle adapter.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: janhess

There's no switch to disable resource registry procesing. However, my experience is that if it doesn't find a reference in the RR it leaves the original value.
If that isn't the case then substitute is probably your only answer.
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

Ok. For anyone else out there who may have to insert a % sign into more than one column, here's how I've been successful.

The problem is when the SQL Statement is built, it could look like:
"call XYZ.STOREDPROCNAME('Value 1', 'Value % 2', 'Other Value', 'Final % Parm')"

Because DBQUERY can resolve Resource Registry, the statement going to Oracle would be:
"call XYZ.STOREDPROCNAME('Value 1', 'Value Parm')"

WTX would try to find a Resource Registry with the name " 2', 'Other Value', 'Final ", which was everything between the percent signs of the original statement. Since there's no entry by that name, WTX put a NONE in its place.

So, I made a Resource Registry entry with the name PERCENTSIGN and assigned it the value %. And on the SQL Statement I used the SUBSTITUTE function to replace each individual % with %PERCENTSIGN%. So the SQL Statement is now:
"call XYZ.STOREDPROCNAME('Value 1', 'Value %PERCENTSIGN% 2', 'Other Value', 'Final %PERCENTSIGN% Parm')"

The DBQUERY resolves each individual 'reference' to the Resource Registry, and passes % to the database as intended.
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender