This discussion is archived
11 Replies Latest reply: Aug 4, 2013 6:45 PM by Ariean RSS

Passing Varchar Input Parameters To a Procedure

Ariean Newbie
Currently Being Moderated

Hello,

I need a clarification on the thread which i marked it as Answered.

I was asked to use single quotes for PROVIDER='6' instead of PROVIDER=6 as mentioned in the thread below for better performance.

But how do i pass the varchar input parameters values to a procedure with single quotes from an application or its not required as Oracle might prefix it by default?

I am thinking of inputting it from application itself but not sure if there is any better approach.

 

https://forums.oracle.com/message/11131804#11131804

 

Thanks,

  • 1. Re: Passing Varchar Input Parameters To a Procedure
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    It's unclear what you're asking.

     

    If you have a procedure, proc_x, that expects a VARCHAR2 argument, you can call it like this:

     

    proc_x ('6');

     

    If you don't use the correct data type, Oracle may do an implicit conversion, which is never a good idea.  Sometimes implicit conversions make a big difference, sometimes the difference is so small you can't notice, but there's no reason to take a chance.  if a column is defined as VARCHAR2, use VARCHAR2s, such as '6' when dealing with that column, and not NUMBERs, such as 6.

     

    How are you calling the procedure now?  What is the rpocedure?  If you could post a compelte (simplified) working example, then people could help you much more.

  • 2. Re: Passing Varchar Input Parameters To a Procedure
    Lalit Kumar B Explorer
    Currently Being Moderated

    If you are calling the procedure from frontend application, and if you are passing an IN parameter of data type VARCHAR then-

    1. For static values, when hard coded, should be in single quotes. Example -

    p_test('6')

     

    2. For dynamic values, when specified as variable, just mention the variable name. Example -

    p_test(var_name)

     

     

    I looked at the other link posted by you. in that case PROVIDER is the predicate in the query. If PROVIDER data type is VARCHAR2 then specify as '6', else if data type is NUMBER, then specify as 6(without quotes). Oracle might also go for implicit conversion if required.

  • 3. Re: Passing Varchar Input Parameters To a Procedure
    rp0428 Guru
    Currently Being Moderated

    I was asked to use single quotes for PROVIDER='6' instead of PROVIDER=6 as mentioned in the thread below for better performance.

    But how do i pass the varchar input parameters values to a procedure with single quotes from an application or its not required as Oracle might prefix it by default?

    You were asked to use single quotes because in that thread all you provided was a hard-coded query.

     

    If your query is in a procedure and uses a bind variable to supply the value of 'PROVIDER' your parameter datatype should be

    p_provider LOAD.PROVIDER%TYPE

    That will define the parameter type to be the same as the table column.

  • 4. Re: Passing Varchar Input Parameters To a Procedure
    Ariean Newbie
    Currently Being Moderated

    yes i am using the binding variables

     

    Table Defintion:

    create or replace loan
    (
    provider varchar2(5),
    ver_num number,
    extract_dt varchar2(20),
    uninum varchar2(20)
    )

     

     

    Procedure fcs_stage_data_delete (p_provider in varchar2, p_ver_num in integer, p_extract_dt in varchar2, p_uninum in varchar2)
    as
    begin
    DELETE /*+ INDEX(L IDX_LOAN_02) */ FROM loan L 
    WHERE L.provider=p_provider
    AND L.ver_num=p_ver_num
    AND L.EXTRACT_DT=p_extract_dt
    AND L.UNINUM=p_uninum
    end;


    I substituted the binding parameters with actual values for performance tuning and what i found is that if i put '6' instead of 6 in my query the performance is much better, but while passing the input parameters or invoking it from Informatica tool i am passing the parameters like below. Now i am confused if i pass the variable like below with out quotes my execution plan wouldn't be same. Kindly advise further.


    fcs_stage_data_delete(6,3,2012-12-31,92500)


    Thank you.

  • 5. Re: Passing Varchar Input Parameters To a Procedure
    Etbin Guru
    Currently Being Moderated

    Having to deal with other tools the safest approach seems to be having all parameters defined as varchar2 datatype and doing explicit conversions within the procedure

     

    procedure fcs_stage_data_delete(p_provider in varchar2,

                                    p_ver_num in varchar2,

                                    p_extract_dt in varchar2,

                                    p_uninum in varchar2

                                   ) as 

    begin 

      delete /*+ index(l idx_loan_02) */

        from loan 

       where provider   = p_provider                       /* provider assumed varchar2 */

         and ver_num    = to_number(p_ver_num)             /* ver_num assumed number */

         and extract_dt = to_date(p_extract_dt,'yyyymmdd') /* extract_dt assumed date */

         and uninum     = to_number(p_uninum)              /* uninum assumed number */

    end;

     

    and calling the procedure as fcs_stage_data_delete('6','3','20121231','92500') 

     

    Regards

     

    Etbin

  • 6. Re: Passing Varchar Input Parameters To a Procedure
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    If the procedure is expecting a VARCHAR2, a NUMBER, and 2 more VARCHAR2s (in that order), then pass it a VARCHAR2, a NUMBER, and 2 more VARCHAR2s.

     

    Ariean wrote:

     

    ...

    1. fcs_stage_data_delete(6,3,2012-12-31,92500) 

     

    That's 4 NUMBERs, the 3rd of which is equivalent to 1969 (2012 - 12 = 2000, then subtract 31 from that).

    A correct way to call the procedure as posted would be

     

    fcs_stage_data_delete ('6', 3, '2012-12-31', '92500')

     

    If loan.extract_dt repressents a date, then the column should be defined as a DATE, not a VARCHAR2, and you should only compare it to other DATEs, so the argument p_extract_dt should also be a DATE, not a VARCHAR2.

  • 7. Re: Passing Varchar Input Parameters To a Procedure
    rp0428 Guru
    Currently Being Moderated

    Ariean wrote:

     

    yes i am using the binding variables

     

    1. Procedure fcs_stage_data_delete (p_provider in varchar2, p_ver_num in integer, p_extract_dt in varchar2, p_uninum in varchar2)  
    2. as 
    3. begin 
    4. DELETE /*+ INDEX(L IDX_LOAN_02) */ FROM loan L   
    5. WHERE L.provider=p_provider  
    6. AND L.ver_num=p_ver_num  
    7. AND L.EXTRACT_DT=p_extract_dt  
    8. AND L.UNINUM=p_uninum  
    9. end


    I substituted the binding parameters with actual values for performance tuning and what i found is that if i put '6' instead of 6 in my query the performance is much better, but while passing the input parameters or invoking it from Informatica tool i am passing the parameters like below. Now i am confused if i pass the variable like below with out quotes my execution plan wouldn't be same. Kindly advise further.


    1. fcs_stage_data_delete(6,3,2012-12-31,92500) 


    Thank you.

    And ALL of those parameters should be defined to be %TYPE of the table column that they are used with.

     

    Procedure fcs_stage_data_delete (p_provider in LOAN.PROVIDER%TYPE, p_ver_num in LOAN.VER_NUM%TYPE, p_extract_dt in LOAD.EXTRACT_DT%TYPE, p_uninum in LOAN.UNINUM%TYPE)

    Always use %TYPE if the parameter is supposed to be a value represented in a table column.

  • 8. Re: Passing Varchar Input Parameters To a Procedure
    Ariean Newbie
    Currently Being Moderated
    Always use %TYPE if the parameter is supposed to be a value represented in a table column

    you mean to say if i define the input parameters declaration in procedure as of table column%TYPE, then i don't need to worry about how i pass parameters (6 instead of '6') & execute the procedure? my execution plan wouldn't be different?

     

    Below is excerpt of my table definition:

    create or replace loan
    (
    provider varchar2(5),
    ver_num number,
    extract_dt varchar2(20),
    uninum varchar2(20)
    );
    
    
    

     

    Call:

    fcs_stage_data_delete(6,3,2012-12-31,92500) 

    Thank you.

  • 9. Re: Passing Varchar Input Parameters To a Procedure
    rp0428 Guru
    Currently Being Moderated

    As you have already been told you should ALWAYS pass parameters of the correct datatype and not rely on implicit conversions.


  • 10. Re: Passing Varchar Input Parameters To a Procedure
    Ariean Newbie
    Currently Being Moderated

    so i have to pass & call like this from my application

    1. fcs_stage_data_delete('6',3,'2012-12-31','92500') 

    Instead of this

    1. fcs_stage_data_delete(6,3,2012-12-31,92500)  


    Thanks.

  • 11. Re: Passing Varchar Input Parameters To a Procedure
    rp0428 Guru
    Currently Being Moderated

    Yes - because you are using the WRONG datatypes for your table you have to use the WRONG datatypes for the parameters.

     

    You should be using a DATE datatype to hold values like '2012-12-31' and not VARCHAR2.


Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points