-
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.
-
For highlander
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.
-
for CARP
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.
-
for CARP
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,:P18,: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
-
Specify Output Parameter ....
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|