oracle stored procedure with output parameter
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: oracle stored procedure with output parameter

  1. #1
    Join Date
    Oct 2000
    Posts
    16
    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

  2. #2
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    Try to hard-code the variable types (varchar2(2000), number(2,4)....etc) and see if you get the same problem.

  3. #3
    Join Date
    Oct 2000
    Posts
    16

    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

  4. #4
    Join Date
    Nov 1999
    Location
    Elbert, Colorado, USA
    Posts
    81
    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.

  5. #5
    Join Date
    Oct 2000
    Posts
    16

    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

  6. #6
    Join Date
    Nov 1999
    Location
    Elbert, Colorado, USA
    Posts
    81
    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.

  7. #7
    Join Date
    Oct 2000
    Posts
    16

    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.


  8. #8
    Join Date
    Nov 1999
    Location
    Elbert, Colorado, USA
    Posts
    81
    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.

  9. #9
    Join Date
    Aug 2000
    Location
    Straham NH
    Posts
    73
    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

  10. #10
    Join Date
    Sep 2000
    Posts
    47

    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
  •  


Click Here to Expand Forum to Full Width