Can IRD return a variable of type Cursor ?

[Mohammed_Adel]

I have a stored procedure returns an output variable of Type ref cursor , is it supported in IRD to retrieve such types ?

Your help will be appreciated

[cavagnaro]

What for?? If you check only types are String and Numeric for attached data and even SP execution can't handle those.

[bublepaw]

You can create another stored procedure which interates cursor and retrives whatever is need it by strategy and returns it as a string or integer type. As an alternative You can use webservice which will do this or You can use Composer to play with cursor in more native form in java or .net

[Mohammed_Adel]

thanks alot 

[smile]

hello All,

just one stupid question related to this topic: how to correctly define and execute SP on oracle? I'm wasting time with it ;(
i need: sp with one input parameter (ani). on the out it should return 1 row with several columns (int & string).
the question are:
1. what style of definition should i use? function or procedure and what it should return (cursor or just output variables)?
2. how to execute it from ird? i understand how to put input parameters, but how to get it from sp?

the thing is that procedures in ms sql and in oracle are very different...

[cavagnaro]

Hey smile,
I always use Store Procedure with output variables.
The SP put @out and then use/select the attach output function to select the correct Interaction variables with equivalent values.

Good idea is to check URS/DBServer logs so you can see what is going on

[smile]

well, from log file perspective sp execution is fine, but i'm not familar with oracle, so i don't know what's right/what's wrong ;(
could you please give a some sample oracle sp?
for example how do the same task using ms sql:
1. define sp via:
create procedure test (@ani varchar)
as
begin
select a,b,c from ...
end

2. sp accept 1 (or several) input parameters and return 1 row with several columns-results.
3. i can execute it in ird using 'procedure' way and put input variable as parameters.
4. on the last tab in ird db wizard i can map a,b,c into variables or attached data

to run this sp: exec test @ani=123. i don't worry about all other output parameters, isn't it?
this is ok.

in case of oracle afaik this doesn't work, because in oracle db sp can only return either ref cursor or put values into variables. Even if i know how to execute such sp in sql developer i can't understand how to do this in IRD...
The trouble is that: procedure definition in oracle implies that it will be executed with all parameters, i.e.:

CREATE PROCEDURE sp_test_proc (ani IN VARCHAR2,b OUT NUMBER,c out number)
begin
select ... into b,c from ....
end;

thus to run this procedure i must pass all 3 parameters to it (1 input and 2 output)
sp_test_proc (a,b,c), where a - input, b,c-output. Should i pass to sp execution in IRD all 3 parameters?
what's about typing conversion, i mean if sp return 'number', should integer variable in ird work?

[René]

Hi,

IRD can handle stored procedure with single output parameter only. If you'd like to return multiple value then you must format these into key-value pair list.

Sample
------------------------------------------------------------------
1/ Let's have sp test defined using code below

[code]Create procedure test (IN_ANI varchar, OUT_RESULT varchar)
as
declare
a varchar(10);
b varchar(10);
c varchar(10);
begin
select a,b,c into a,b,c from ... where ani = IN_ANI

OUT_RESULT := 'A:' || a || '|B:' || b || '|C:' || c;
end;[/code]

2/ Use IRD Database object to execute the test sp
    Name: test
    Parameters: IN_ANI = vANI
                        OUT_RESULT = @out
    Assign output - choose 2nd option (output format: <key1>:<value1>|<key2>:<value2>|...
    Select maping between returned keys and IRD variables on last page of DB wizard
------------------------------------------------------------------

Please ensure in code of stored procedure that none of returned parameter that you include in OUT_RESULT has 'null' value. If even one exists with 'null' value then IRD won't be able to process returned string.

R.

[smile]

oh yeah, thanks, Rene! such solution should work
i forgot about this limitation, cause i used to create sp in sql server and ird/urs can correctly parse more than one column in reply.

[smile]

works good, Rene, thank you