11 Replies Latest reply: Aug 4, 2013 8:45 PM by Ariean RSS

    Passing Varchar Input Parameters To a Procedure

    Ariean

      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

          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

            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

              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

                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

                  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

                    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

                      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
                        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

                          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

                            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

                              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.