call ORACLE Stored procedure with MBS SQL plugin

Add-Ons
1

Hi

I’m trying to get data from an ORACLE stored Procedure using MBS SQL plugin with SQLConnectionMBS… this is the stored procedure:

CREATE OR REPLACE PROCEDURE LISTA_UNIDADES (v_unidad in number, CURSOR_RESULTADO OUT SYS_REFCURSOR) IS BEGIN OPEN CURSOR_RESULTADO FOR select id, nombre FROM UNIDAD WHERE estado = 'A' AND (id = v_unidad or v_unidad = 0) ORDER BY id; END;

Here is the code I’m using to call it, but I got the ORA-06550 error

dim con as SQLConnectionMBS 
dim cmd as SQLCommandMBS

con = new SQLConnectionMBS
con.SetFileOption con.kOptionLibraryOracle, GetFolderItem("c:\\xxx\\oci.dll")

dim d as string = "xxxxxxxx:1521/RTD"
dim u as string = "XXXXX"
dim p as string = "xxxx"

con.connect(d, u, p, SQLConnectionMBS.kOracleClient)

cmd = new SQLCommandMBS(con, "BEGIN LISTA_UNIDADES(1); END;")

cmd.Execute

I guess the problem are the parameters, but I do not know how to pass them… am I correct? is there any way to pass the parameters?
any example would be appreciated…

Regards

2

Could you provide whole error message?

This stored procedure is valid and works in other oracle app?

3

A stored procedure is normally just called by it’s name.
And than you bind the parameters
Maybe like this:

dim cmd as new SQLCommandMBS(con, "LISTA_UNIDADES") cmd.ParamByIndex(0) = 1 cmd.Execute

4

Hi Christian:

yes the procedure works fine in TOAD…
the message I get:

[quote]An exception of class SQLErrorExceptionMBS was not handled. The application must shut down.
Exception Message: ORA-06550: Line 1 Column 7:
PLS-00306: wrong number or types of arguments in call to “LISTA_UNIDADES”
ORA-06550: Line 1, column 1
PL/SQL: Statement ignored

Exception Error Number:6550[/quote]

Thanks in advance

5

did you try with just the function name and let the plugin check parameters?

7

dim cmd as new SQLCommandMBS(con, "LISTA_UNIDADES") cmd.ParamByIndex(0).setAsLong 1 cmd.Execute

Sorry. isst the Set call.

8

Thanks Christian… but now Im getting this error:

ORA-24374: define not done before fetch or execute and fetch.

9

How can I do that?

10 
dim cmd as new SQLCommandMBS(con, "LISTA_UNIDADES")

When plugin sees this, it should detect that it is a stored procedure.
What does ParamCount say here?

cmd.ParamByIndex(0).setAsLong 1 cmd.Execute

We may need to set output parameter.

11

Well, I can’t test myself right now and as far as I see, it should detect input and output parameter itself and just work.
So I am not sure why this may fail.

12

the result is 0

I think that is the problem… is there any way to set the output parameter?

13

I sent you some C++ code which does work as far as I know.
Maybe you spot a difference.

14

Thanks Christian!!! I will take a look

15

I will also add the AsCursor method to the plugin, so you actually can access the output parameter with cursor.

16

Perfect!!! could you send me the plugin when you have done?

17

Did you fix the other error?
Because the AsCursor function is used when the execute succeeds.

18

I have not been able to solve it yet…