8 Replies Latest reply: May 11, 2012 7:58 AM by tkyte-Oracle RSS

    Dynamic SELECT

    935011
      Hi all,
      I'm writing a simple SELECT-WHERE statement in my PL/SQL code. The problem is in the WHERE clause: I pass to my procedure a series of parameters and some of that may be NULL.
      Actually, I solve this problem using a CASE statement as shown below

      SELECT
      [ ... ]

      WHERE

      myField_01 =
      CASE
      WHEN myParam IS NULL THEN
      myField_01
      ELSE
      myParam
      END

      I pass to my procedure A LOT OF parameters (NULL or NOT NULL depending on the choice made by the user in my web form), so I don't know if with this method the performance of the procedure my invalidate.

      So, is there an alternative method to generate the WHERE statement in dynamic manner, depending on which are the parameters NOT NULL passed into the PL/SQL procedure?

      Thank you for your help. I'm new in PL/SQL programming.
      Igor
        • 1. Re: Dynamic SELECT
          908002
          for the example you have provided...



          SELECT
          [ ... ]

          WHERE

          myField_01 =nvl( myParam ,myfiield_01)
          • 2. Re: Dynamic SELECT
            Nicosa-Oracle
            Hi,

            You might want to read this message, and the Tom Kyte's article it points to :
            {message:id=10280148}
            • 3. Re: Dynamic SELECT
              indra budiantho
              1. u can use default value in the procedure parameter proc(param1 number, param2 VARCHAR2 DEFAULT NULL).
              2. u can create dynamic query to make whatever query u like. For example
              EXECUTE IMMEDIATE 'SELECT PERIODE  FROM fifocm.CM_TRN_CONTRACT_BUCKETS WHERE PERIODE < ' || TEMP_PERIODE
                BULK COLLECT INTO periodes;
              • 4. Re: Dynamic SELECT
                BluShadow
                1B wrote:
                1. u can use default value in the procedure parameter proc(param1 number, param2 VARCHAR2 DEFAULT NULL).
                2. u can create dynamic query to make whatever query u like. For example
                EXECUTE IMMEDIATE 'SELECT PERIODE  FROM fifocm.CM_TRN_CONTRACT_BUCKETS WHERE PERIODE < ' || TEMP_PERIODE
                BULK COLLECT INTO periodes;
                Poor example. It doesn't use bind variables, and if TEMP_PERIODE and PERIODE are DATE datatypes then you are causing an implicit conversion of DATE to VARCHAR2 which can cause errors.
                • 5. Re: Dynamic SELECT
                  indra budiantho
                  tx
                  • 6. Re: Dynamic SELECT
                    935011
                    Thank you to all, I'll try all your suggestion.
                    • 7. Re: Dynamic SELECT
                      935011
                      Hi all,
                      I have studied your suggestions and I have written my personal dynamic select as below. It seems to works fine. But now my question is: is this dynamic procedure more o less efficient than the first one I have written (not dynamic, but with the case-else statement)?

                      THANK YOU FOR YOUR HELP!

                      CREATE OR REPLACE PROCEDURE myProcedure(my_rset OUT myPkg.RSET_type,
                                              abiIst            CHAR,
                                              keyMIR_SSN        CHAR,
                                              keyMIR_ISN        CHAR
                                              )
                      AS
                      
                          w_select Varchar2(4000);
                          
                      BEGIN    
                      
                          w_select := w_select || 'SELECT';
                          w_select := w_select || ' f_ABI   AS ABICOD,  ';    
                          w_select := w_select || ' f_KEY   AS CHIAVE,  ';
                          w_select := w_select || ' f_PROG  AS PROGCOD, ';
                          w_select := w_select || ' f_MSG   AS MESSG,   ';
                      
                          w_select := w_select || ' FROM myTable ';
                          
                          w_select := w_select || 'WHERE ';
                          
                          w_select := w_select || ' f_ABI = ' || abiIst;
                          
                          IF (keyMIR_SSN IS NOT NULL) THEN                 
                              w_select := w_select || ' AND ';
                              w_select := w_select || ' f_ABIMIR_SSN = ' || keyMIR_SSN; 
                          END IF;
                          
                          w_select := w_select || ' AND ';
                          w_select := w_select || ' f_ABIMIR_ISN = ' || keyMIR_ISN;     
                          
                          -- Execute DYNAMIC SELECT
                          OPEN my_rset FOR w_select;
                          
                      EXCEPTION
                      
                       [ ... ]
                      
                      END;
                      • 8. Re: Dynamic SELECT
                        tkyte-Oracle
                        that procedure is scary

                        it is 100% sql injectable.

                        please re-read the link some supplied above that shows how to do this with BIND VARIABLES

                        your implementation can easily be used to read any table that the schema that owns this procedure can read - any table.

                        If I have create session and create procedure in the database - I can use your procedure to take over your schema, I can use your procedure to read/write any table you can, I can use your procedure to do anything I want in short.


                        Please do not use string concatenation - the resulting code is subject to sql injection.

                        just use binds!!!! very very very important.