Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2012
    Posts
    4

    Question Unanswered: procedure using bind variable

    Hi,

    when i execute the following query

    create or replace procedure sp2 (a number,b number,c out number)
    as
    c:= a+b;
    end;
    /

    after compiling this the following warning message came

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    3/2 PLS-00103: Encountered the symbol "=" when expecting one of the
    following:
    constant exception <an identifier>
    <a double-quoted delimited-identifier> table LONG_ double ref
    char time timestamp interval date binary national character
    nchar



    i have declared 'C' as a bind variable by using below method

    var c number(5);
    exec sp2(1,2,:c);

    but it shows below error

    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00905: object SYSTEM.SP2 is invalid
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    can anyone guide me whether the written code is correct?

  2. #2
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    There are two problems:

    1.) An error in your code
    2.) Your understanding of a "bind variable"

    ad 1.) insert a "begin" at the begin of your procedure.

    Code:
    SQL> create or replace procedure sp2 (a number,b number,c out number)
      2  as
      3  begin
      4  c:= a+b;
      5  end;
      6  /
    
    Procedure created.
    
    SQL>
    ad 2.) read up on binding varaibles, an example using your procedure would be:

    Code:
    SQL> -- lets define a binding variable called "result"
    SQL> variable result number;
    
    SQL> -- now use this variable with your procedure
    SQL> execute sp2(5,7,:result);
    PL/SQL procedure successfully completed.
    
    SQL> -- now show the binding variable
    SQL> print result;
    
        RESULT
    ----------
            12
    
    SQL>
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >create or replace procedure sp2 (a number,b number,c out number)
    >as
    >c:= a+b;
    >end;
    >/

    I was always taught that BEGIN & END must exist as pairs.

    You can not execute any stored procedure that has syntax errors.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by OP
    PLS-00905: object SYSTEM.SP2 is invalid
    You are looking for trouble when creating objects in SYS/SYSTEM schemas. Create your own user and practice SQL skills connected as you; leave SYS & SYSTEM alone.

  5. #5
    Join Date
    Apr 2012
    Posts
    4

    Smile procedure using bind variable

    Thanks for your timely reply- magicwand!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •