oracle stored procedure with output parameter

[anagarur]

Hi! I am trying to do the following stored procedure in oracle 8. The proc. is:

CREATE OR REPLACE PROCEDURE INSERTBLKHEADER(
v_fkey IN callblockheader.pk_sourcefile%type,
v_bSNum IN callblockheader.blockserialnumber%type,
v_bCount IN callblockheader.blockcount%type,
v_fOffset IN callblockheader.fileoffset%type,
v_id OUT NUMBER)
AS

BEGIN
INSERT INTO CallBlockHeader (pk_blockheader,
pk_sourceFile, blockSerialNumber, blockCount,
fileOffset)
VALUES(callblockheader_seq.nextval, v_fkey,
v_bSNum, v_bCount, v_fOffset);
COMMIT;

SELECT pk_blockheader into v_id
FROM callblockheader
WHERE pk_blockheader =
CallBlockHeader_Seq.currval;

END;
/

My problem:

1. I compile the above code and it compiles!
2. In SQL * PLUS, I say:
execute insertblkheader(10,10,10,10); I get the foll. error msg. (Note: The input parameters are all number data types (number(10) - to be specific)


BEGIN insertblkheader(10,10,10,10); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'INSERTBLKHEADER'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Can someone please point where I am going wrong - am i executing it wrong at SQL PLUS prompt?
This stored procedure is being accessed from Visual C++ and the program is expecting to get the value of the v_id parameter.

Any help will be much appreciated.
Thanks,
Aruna

[Highlander]

Try to hard-code the variable types (varchar2(2000), number(2,4)....etc) and see if you get the same problem.

[anagarur]

Yes, I am still getting the same error even after hardcoding the variables to the number data type. The table has all number data type columns only.

Cheers,
Aruna

[carp]

As the error message says, you are providing the wrong number of arguments. Your procedure has five arguments; you are only passing four! You must provide a variable for v_id.

[anagarur]

If i do as you say then, I get this msg. its b'cos v_id is an OUT type parameter.

SQL> execute insertblkheader(2,2,2,2,2);
BEGIN insertblkheader(2,2,2,2,2); END;

*
ERROR at line 1:
ORA-06550: line 1, column 31:
PLS-00363: expression '2' cannot be used as an assignment target
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

[carp]

That is because you are trying to pass a value to an out argument (as the error message states). As I said - you must pass in a VARIABLE.

[anagarur]

Hi!

Okay, I get your point.... Thanks for correcting me. Let me ask you this. I'm sort of new and so can use some advise.

Basically, I am converting a SQL Server stored proc. to oracle pl/sql. Here is the sql server one:

CREATE PROCEDURE InsertBlkHeader(@fkey int,@bSNum int,@bCount int,@fOffset int, @id int OUTPUT) AS
INSERT CallBlockHeader (pk_sourceFile, blockSerialNumber, blockCount,fileOffset)
VALUES(@fkey, @bSNum, @bCount, @fOffset)
Select @id = @@IDENTITY

In the above, when they do "select @id = @@identity", they are returning the value to the calling front-end program in Visual C++. I am trying to do the same thing in oracle. The @id is an output parameter.
Am i doing the right stuff in my stored proc. in PL/SQL or am i totally messing up?
Please advise.

[carp]

Yes, I think you are right on course. It was just a matter of working out how PL/SQL handles passing arguments in and out of a procedure.

[GARYM]

Try this you need all parameters passed:


SET SERVEROUTPUT ON ;
VARIABLE P1 varchar2(50);
VARIABLE P2 varchar2(50);
VARIABLE P3 VARCHAR2(50);
VARIABLE P4 varchar2(50);
VARIABLE P5 varchar2(50);
VARIABLE P6 varchar2(50);
VARIABLE P7 varchar2(50);
VARIABLE P8 varchar2(50);
VARIABLE P9 varchar2(50);
VARIABLE P10 varchar2(50);
VARIABLE P11 varchar2(50);
VARIABLE P12 varchar2(50);
VARIABLE P13 varchar2(50);
VARIABLE P14 varchar2(50);
VARIABLE P15 varchar2(50);
VARIABLE P16 varchar2(50);
VARIABLE P17 number;
VARIABLE P18 varchar2(50);
VARIABLE P19 number;
VARIABLE P20 varchar2(50);
VARIABLE P21 varchar2(50);
VARIABLE P22 varchar2(50);
VARIABLE P23 varchar2(50);
VARIABLE P24 varchar2(50);
VARIABLE P25 varchar2(50);
VARIABLE P26 varchar2(50);
VARIABLE P27 number;





EXECUTE :P27:=990025;

EXECUTE SP_GET_FUNCOMP_DETAIL(:P1,:P2,:P3,:P4,:P5,:P6,:P7,:P8,:P9,:P10,:P11,:P12,:P13,:P14,:P15,:P16,:P17,:P 18,:P19,:P20,:P21,:P22,:P23,:P24,:P25,:P26,:P27);


Replace types with your types and tyhe procedure namae with yours then yopu can print the result.


Gary

[Pinakin]

Hi Aruna,

I havent read any of the replies. Just read your prob and giving the possible solution.

Try running the following piece of code in SQL*PLUS :
(Set ServerOutPut ON)

DECLARE
OptValue NUMBER ;
BEGIN
Exec insertblkheader(10,10,10,10, OptValue);
DBMS_OUTPUT.PUT_LINE('Value Returned Is : '||OptValue) ;
END ;

This should work. Your procedure has 4 input parameters and 1 output parameter. So total 5 parameters. You need to specify the 5th output parameter, the parameter in which your output value will be stored.


Good Luck,

Pinakin.

[mber]

Is the procedure working? I think it is not, because in Ur "select ......." statement, U didn't specified any variable to hold that query result, I guess it is a problem, but I'm not pretty sure about that.

[carp]

It looks OK to me:

SELECT pk_blockheader into v_id
FROM callblockheader
WHERE pk_blockheader =
CallBlockHeader_Seq.currval;

Apparently the compiler thought so too!