Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    17

    Unanswered: Parameter to the Stored Procedure

    Hi,

    Which is the best way to pass the parameter to stored procedure?

    1. create procedure sp_test(P_name table.column%type)

    or

    2. 1. create procedure sp_test(P_name varchar2)


    I am troubled to which option to my strored procedure.

    please let me know which is betters and why?



    Thanking in advance,
    ssutar

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    whatever works. you would want an IN clause I believe.
    create procedure sp_test( p_name IN varchar2)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Not necessarily; documentation says that
    If you omit IN, OUT, and IN OUT, then the argument defaults to IN.

  4. #4
    Join Date
    Sep 2009
    Posts
    5
    Quote Originally Posted by ssutar
    Hi,

    Which is the best way to pass the parameter to stored procedure?

    1. create procedure sp_test(P_name table.column%type)

    or

    2. 1. create procedure sp_test(P_name varchar2)


    I am troubled to which option to my strored procedure.

    please let me know which is betters and why?



    Thanking in advance,
    ssutar
    I think the first option is better, because if you later change the column's type (eg. column's size), you don't need to bother with changing the procedure. You are always assured that parameter and column have the same data type.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Makes sense, but not in this case. Arguments do not accept size. Back to the documentation:
    Datatypes cannot specify length, precision, or scale. For example, VARCHAR2(10) is not valid, but VARCHAR2 is valid. Oracle Database derives the length, precision, and scale of an argument from the environment from which the procedure is called.
    This is OK:
    Code:
    SQL> create or replace procedure prc_test (par_dname varchar2) is
      2  begin
      3    null;
      4  end;
      5  /
    
    Procedure created.
    This, on the other hand, is not: argument's size (VARCHAR2(20)) is not accepted:
    Code:
    SQL> create or replace procedure prc_test (par_dname varchar2(20)) is
      2  begin
      3    null;
      4  end;
      5  /
    
    Warning: Procedure created with compilation errors.
    
    SQL> show err
    Errors for PROCEDURE PRC_TEST:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    1/39     PLS-00103: Encountered the symbol "(" when expecting one of the
             following:
             := . ) , @ % default character
             The symbol ":=" was substituted for "(" to continue.
    
    SQL>

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by Littlefoot
    Not necessarily; documentation says that:
    If you omit IN, OUT, and IN OUT, then the argument defaults to IN.
    Welp, that is good to know.
    I suppose from a 3rd party's standpoint it is easier to understand the code
    knowing that it is intended as an IN parameter.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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