Re: Calling Oracle Stored Function in ODBC

In article <8tc809$p53$1_at_nnrp1.deja.com>,   Dan Kraiman <kraimand_at_dynmeridian.com> wrote:
> I wrote the following oracle stored function:
>
> CREATE OR REPLACE function auth_user(UID IN VARCHAR2, PW IN VARCHAR2)
> RETURN rsCursor IS
> TYPE rsCursor IS REF CURSOR;
> L_USERID VARCHAR2(50);
> L_PASSWORD VARCHAR2(50);
> r1 rsCursor;
> BEGIN
> L_USERID := UPPER(UID);
> L_PASSWORD := UPPER(PW);
> OPEN R1 FOR SELECT * FROM UIDPASSWORD WHERE UPPER(USERID) =
> L_USERID AND UPPER(PASSWORD) = L_PASSWORD;
> RETURN R1;
> END;
>
> It compiles fine. How do I execute it using ODBC? I am using a third
> party product which requires an odbc string as input and when executed
> returns the result set from the select statement.

Generally, the ODBC syntax one uses is:
{call auth_user( 'user', 'pwd' )}

Assuming you have the Oracle ODBC driver, you could also declare the function as a procedure with two IN varchar2 parameters and one OUT ref cursor parameter. When you'd then execute

{call auth_user( 'user', 'pwd' )}

the result set would be returned.

--
Justin Cave - Oracle ODBC Development

Opinions expressed herein are my own and may not reflect those of
Oracle Corporation.


Sent via Deja.com http://www.deja.com/
Before you buy.