Function in user defined SQL

SimonB · Post by **SimonB** » Thu Jun 26, 2003 11:02 am

Hi
Apologies if this has been covered elsewhere - I have searched for it, but to no avail [:I]

I need to return a string from Oracle DB (using ODBC stage) that is calculated via a user defined SQL statement that includes a function (using the key values as parameters).

e.g:
SELECT col1, col2, calculate(col1,col2) as col3 FROM dual;

The problem arises at runtime as DS aborts due to "SQL statement has incorrect number of result columns.". This doesn't seem to make sense, as the ODBC stage has 3 result columns (col1 (key), col2(key) and col3 (not key)).

Can anyone advise me on where I am going wrong?!?

Many thanks

Simon

kduke · Post by **kduke** » Thu Jun 26, 2003 3:30 pm

Simon

When you select from dual then you do not need to define keys because DataStage wants to do a where clause. You do not need a where clause but you need to feed the stored procedure the two fields. You also do not need the two columns in the select because you already know their values.

There is a way to call a stored procedure. This is what you need to research.

Thanks Kim.

Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com

SimonB · Post by **SimonB** » Fri Jun 27, 2003 3:58 am

Thanks for the feedback Kim.

The reason why I was doing a select from Dual is because I am unable to import the stored procedure definition. Whenever I try to import it, I get the following error:

DSR.MetaGeta(GET.PROC.COLUMNS)(DSSQLProcedureColumns("","SIEBEL","CRMLOOKUPBROCHURECODES")): BCI Error:Error calling SQLFetch -
SQLSTATE = 22003, NativeError = 0, ErrorMsg = [Oracle][ODBC]Numeric value out of range.

What numeric range? I have tried several workarounds, but the error persists.
As a result (and because I need a quick fix), I figured it would be quicker to embed the function into a SQL statement. But, as I have found out, this has its own set of challenges!

If I was to stick with the SELECT FROM Dual option, how do I pass through the values to the function without defining them as Key values?

Cheers

Simon

nvkravi · Post by **nvkravi** » Fri Jun 27, 2003 5:06 am

Hi All,
Me also faced same problem when I importing Stored Procedure from Oracle.Here I have used a small Stored Procedure with 3 IN Columns and 1 OUT column. I already referred about this Stored Procedure topic by using Manuals. But I could not rectify this error. Pls help me out from this problem. Thanx in Advance.

Regards
Ravi

kduke · Post by **kduke** » Fri Jun 27, 2003 8:25 am

SimonB

I have never got the import on a stored procedure to work either but I knew it is available. I was hoping someone could explain it. Usually I call it in the primary select.

Thanks Kim.

Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com

inter5566 · Post by **inter5566** » Fri Jun 27, 2003 9:24 am

Simon,

Have you tried to manually enter the derivation for the third column using your stored procedure? Maybe DS doesn't like the as clause.

Steve

SimonB · Post by **SimonB** » Fri Jun 27, 2003 10:01 am

Hi all
I have tried manually entering the column derivations, however the parameters to be passed in (col1 and col2) can't be hard-coded (as they are read in from seq files) and I can't figure out how to pass them through as bind variables.

It is interesting that there are widespread problems with importing procedure definitions. Is this a bug, or are we all doing it wrong?!?

Cheers for all your interest/advice on this

Simon

trobinson · Post by **trobinson** » Fri Jun 27, 2003 12:45 pm

I've never been able to import a Stored Procedure either but then the whole Oracle table Definition import is not good anyway (synonynms) so it's par for the course. You can, of course, manually type in the stored procedure. I think this thread has gotten outta hand. Use the function call to dual since I assume it is a reference look-up you're doing. Here's how;
User-defined SQL example:
SELECT calculate(:1,:2)FROM dual;

Col1 = :1
Col2 = :2

Both cols are defined as Primary Keys on the columns tab.

The result of the function will be put into Col1 for use downstream.

I only use procedures when I want WRITE something to Oracle. I always use functions when I do complex SELECTS. I only use the vanilla lookup when it is simple. Also functions are nice when using the Fegley Technique. This technique does ALL reference lookups in a single call to the database as a single SELECT with multiple function calls. It is can be more efficient albeit marginally harder to understand and therefore maintain.

ray.wurlod · Post by **ray.wurlod** » Fri Jun 27, 2003 6:21 pm

This is probably not a useful answer, since I have never tried to use stored procedures with Oracle. However, I can report success using them with SQL Server. The only glitch I found is the documented restriction that in/out parameters are not supported (so we had to create a different SP with separate in and out parameters for the same value).

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518