stored procedure problem

[vikramarjula]

Hi all,
I have a problem with stored procedure. I have searched the whole forum but I couldn't get an answer. I was trying to use a stored procedure

create or replace procedure code_value_procedure as
code_value number(10);
begin
select code_ID into code_value from claims_transaction where claim_code=0119;
dbms_output.put_line(code_value);
end

when I executed this in oracle I was getting output as 2036(code_value). Iam getting a single column output. Actually I have to bulid my complex stored procedure on these lines. But I was having a problem with this simple procedure. So I couldn't move any further.

I imported this stored procedure into repository using the table definitions... In the colums tab of the stored procedure, I gave a colum name as code_value to capture the code_value value of the stored procedure.

Then I used the ODBC stage and checked the "stored procedure " , selected code_value_procedure, loaded the columns into it. I do not have any parameter to be passed since I don't have in arguments in my procedure.
I used ODBC -> seqfile. When I run this program my job is aborted and the error log Iam getting is "sql statement resulted in more number of required columns".
Can some one help me out

[roy]

Hi,
I prefer to avoid SPL when I can, so if you can build the same logic with DS your better off doing it IMHO.
(after saying my 0.02 worth)
As for your problem I think there is nothing I can add on what was already said in this forum, it was quite extensive.
is this the exact error message you get?
it sounds like you actually get more columns then you have in the table definition attached to the output link.
I hope you'll get another reply soon from someone more experianced with Oracel SPL usage.

Good Luck,

[wdudek]

I'm not familiar with the ODBC stage, but if you are trying to get the value back, wouldn't you need a function to do this in Oracle, not a procedure? From what I see you are populating a variable local to the procedure and then doing nothing with it besides outputting it in a sql plus window?

i.e.

create or replace function code_value_procedure return number
as
code_value number(10);
begin
select code_ID into code_value from claims_transaction where claim_code=0119;

return code_value;

end;

[jreddy]

I thought you can never return a value from a procedure into DataStage. You can return values from user-defined sql queries, but thats the limit. In ODBC Stage, the columns tab would refer to only the input parameters that the procedure can accept, but you cant specify columns to accept return values from procedures.

[jreddy]

so, since all you need is a return value from that SELECT query, forget about procedure, use an ODBC stage, and select user-defined sql query option. Then in ViewSQL tab, write that SELECT query in either reference input or primary input depending on the kind of link you have used to connect it to the transformer or any receiving stage. And in columns tab, give a column name which will receive your return_code.