oracle stored procedure with output parameter

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

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

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

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.

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

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.

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.

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.

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

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.

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.

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!