return a variable value in a cursor

[Shay1975]

I am trying to return a variable value in a cursor below but I get the following error. I am not very familiar with PL/SQL so any help would be great. It compiles fine

System.Data.OleDb.OleDbException: ORA-00900: invalid SQL statement

SQL call is as below

Dim sql As String = "{call LOGIN_APPLICATION_INTERFACE.CHECK_USERUNIT(" & unitID & _
", '" & unit & "', {resultset 0, IO_CURSOR})}"

Here is the procedure body...

PROCEDURE CHECK_USERUNIT(UNITID IN INTEGER, UNIT IN VARCHAR2, IO_CURSOR OUT T_CURSOR) IS
UNIT_COUNT INTEGER;
RETURNVAL VARCHAR2 (1);
BEGIN

IF UPPER(UNIT) = 'RISK' THEN
SELECT COUNT(*) INTO UNIT_COUNT
FROM LOGIN_UNIT
WHERE UNIT_DESC = 'Risk Management' and UNIT_ID = UNITID;

IF UNIT_COUNT > 0 THEN
RETURNVAL := 'Y';
ELSE
RETURNVAL := 'N';
END IF;

OPEN IO_CURSOR FOR
RETURNVAL;

ELSIF UPPER(UNIT) = 'FRONTOFFICE' THEN
SELECT COUNT(*) INTO UNIT_COUNT
FROM LOGIN_UNIT
WHERE unit_desc='BZWBK' OR unit_desc='Wholesale Treasury'
OR unit_desc='New York' AND UNIT_ID = UNITID;

IF UNIT_COUNT > 0 THEN
RETURNVAL := 'Y';
ELSE
RETURNVAL := 'N';
END IF;

OPEN IO_CURSOR FOR
RETURNVAL;

END IF;
END;

Thanks in advance

[Saii]

What are you trying to do? Open cursor for Y and N? why do u need a cursor to return a Y or N value?

[Shay1975]

What are you trying to do? Open cursor for Y and N? why do u need a cursor to return a Y or N value?

I don't have to do it this way, but I want to know is it possible and if so, how?

I am mostly from a SQL server background and it is very easy to do in SQL server.

Thanks

[frozenmist]

Hi,
You have to give create or replace procedure.
And could you give an input on what T_CURSOR is?

Cheers

[Shay1975]

T_CURSOR is declared in Package spec as below

TYPE T_CURSOR IS REF CURSOR;

I have no problems creating a new proc, I just need to know the syntax and if possible to return a variable in a cursor?

Thanks

[frozenmist]

Hi Shay,

It is possible to return.
Though I am not sure of the syntax.
May be some of the other Experts may be able to help you with the syntax

Cheers

[Saii]

hi,

Cursors/Ref cursors in Pl/SQL are used to handle resultsets(multiple records dataset). Say for example, you want to pass output of "select * from emp" to some outer code/ application, then you can simply use open cursor for select * from emp and pass the cursor as OUT parameter.
For passing variables like Y and N in your case, you can simply have an OUT varchar2 type parameter.
Hope that helps!!!