4 Replies Latest reply: Aug 28, 2014 4:49 AM by Roger RSS

    Bind variables in REPLACE statement

    orclrunner

      See my updated post below.

       

      I need to update a table column based on rules. But I'm having a problem. Using 11g Release 2.

       

      Here is what I doing:

       

      CREATE TABLE tab1
      (   file_id  number
        , notes    varchar2(1020)
      )
      /
      
      insert into tab1 VALUES(1,'TEST') ;
      insert into tab1 VALUES(1,'NEW|A/C COMPRESSOR KIT') ;
      
      commit ;
      
      select * from tab1;
      
      FILE_ID   NOTES
      -------   --------------------
      1         TEST
      1         NEW|A/C COMPRESSOR KIT
      
      DECLARE
         v_note1 varchar2(255) ;
         v_note2 varchar2(255)  := 'NEW';
         v_note3 varchar2(255);
         v_notes_out varchar2(1020);
         v_notes_col varchar2(30) := 't.notes';
         base_stmt varchar2(1024) :=
                q'^UPDATE tab1 t ^'    || chr(10) ||
               q'^   SET ^'                                       || chr(10) ||
               q'^      t.notes = ^' || '''' || v_notes_out || '''' || chr(10) ||
         q'^   WHERE t.file_id = 1 USING v_notes_col  ^' ;
      BEGIN
      
          -- I need to remove vnoteN from the table column "notes"
         IF ( v_note1 IS NOT NULL or v_note2 IS NOT NULL or v_note3 IS NOT NULL)
         THEN
          -- has to a string or else I get an error "Bind variable "B1" not declared."
          v_notes_out := 'RELACE(REPLACE(REPLACE(:B1,v_note1,null),v_note2,null),v_note3,null)';
         ELSE
              return ;
         END IF ;
      
      dbms_output.put_line(base_stmt);
      END ;
      /
      
      
      
      

       

      output:

       

      UPDATE tab1 t

         SET

            t.notes = '' -- where's my replace statement???

         WHERE t.file_id = 1 USING v_notes_col

       

      The correct result for the table column NOTES that I want is:

       

         FILE_ID NOTES

      ---------- -------------------------

               1 TEST

               1 |A/C COMPRESSOR KIT

       

      Any ideas on the best way to do this?

        • 1. Re: Bind variables in REPLACE statement
          34MCA2K2

          Your code has lots of problems, like miss spelled RELACE. However the most glaring one is use of column names in bind variables. You cannot use any schema object in bind variables, You can use concatenation. Why?


          During parse phase, the schema objects are validated and data dictionary look ups are performed to check table and column definitions. The bind phase is after that. So it becomes impossible for Oracle to validate this during/after binding.

           

          Please explain your requirement so experts here will suggest best solutions.

          • 2. Re: Bind variables in REPLACE statement
            orclrunner

            Thanks for the feedback.

             

            I need to update the column NOTES in the table TAB1.

            The update is based on the RULES table.

             

            If RULES.NOTE1 or RULES.NOTE2 or RULES.NOTE3 is not null we need to check if their value is in TAB1.NOTES.

            If so, we need to remove value from RULES.NOTE(n) from TAB1.NOTES.

             

            Here is the setup:

             

            create table tab1
            ( file_id   number  ,
              record_id number  ,
              part_type_id number ,
              part_number  number ,
              positon_id   number ,
              notes        varchar2(255) ,
              mapped       varchar2(1) default 'N'
            )
            /
            insert into tab1 values(1,1,123,777,1,'NEW|A/C COMPRESSOR KIT','N') ;
            insert into tab1 values(1,2,123,777,2,'REMAN|A/C LINE','N') ;
            select * from tab1;
            /*
              FILE_ID  RECORD_ID PART_TYPE_ID PART_NUMBER POSITON_ID NOTES                     MAPPED
            --------- ---------- ------------ ----------- ---------- ------------------------- ------
                    1          1          123         777          1 NEW|A/C COMPRESSOR KIT         N
                    1          2          123         777          2 REMAN|A/C LINE                 N
            */
            
            
            create table rules
            (  rule_id   number
              ,file_id   number
              ,part_type_id number
              ,part_number  number
              ,position_id  number
              ,subnote1 varchar2(255)
              ,subnote2 varchar2(255)
              ,subnote3 varchar2(255)
              ,rule_order number     -- the order in which rules are processed
            )
            /
            
            
            insert into rules values(1,1,123,777,1,'NEW',null,null,1);
            insert into rules values(2,1,123,999,1,'REMAN',null,null,2);
            commit;
            col subnote1 for a25
            col subnote2 for a25
            col subnote3 for a25
            select * from rules;
            /*
               RULE_ID FILE_ID PART_TYPE_ID PART_NUMBER POSITION_ID SUBNOTE1  SUBNOTE2   SUBNOTE3
            ---------- ------- ------------ ----------- ----------- --------- ---------- ---------
                     1 1         123         777                  1 NEW
                     2 1         123         777                  2 REMAN
            */
            
            
            -- psudeo code
            declare
              v_notes_out varchar2(1020) ;
            begin
            -- loop though the rules and do an update based on non-null input values (part_type_id,part_number and position_id)
            for   rec  in  ( select * from rules order by rule_order )
            loop
               if ( rec.subnote1 is not null )
               then
                    -- this is the part I have not figured how to do. Need help here.
                    v_notes_out := null ; -- need to check if rec.subnote1 is in tab1.notes. If so, remove rec.subnote1 from tab1.notes
               end if ;
               -- repeat process for rec.subnote2 and rec.subnote3
               
               update tab1
                  set
                       notes = v_notes_output
                     , mapped = 'Y'
                  where
                       file_id = rec.file_id
                  and
                       -- these filters will only be included if they are non-null
                       -- dynamic sql, but for simplicity i'm using static sql
                       part_type_id = rec.part_type_id
                  and
                       part_number = rec.part_number
                  and
                       position-id = rec.position_id ;
            
            
            end loop ;
            commit;
            end ;
            /
            
            
            Final output will look like this:
            
            
            /*
              FILE_ID  RECORD_ID PART_TYPE_ID PART_NUMBER POSITON_ID NOTES                     MAPPED
            --------- ---------- ------------ ----------- ---------- ------------------------- ------
                    1          1          123         777          1 |A/C COMPRESSOR KIT            Y
                    1          2          123         777          2 |A/C LINE                      Y
            */
            
            • 3. Re: Bind variables in REPLACE statement
              orclrunner

              Any suggestions?

              • 4. Re: Bind variables in REPLACE statement
                Roger

                I'm not sure, whether I have fully understand what you need to achieve but here two queries to show how you could join those tables together and modify the notes column. This could be used within a merge command.

                 

                First one:

                 

                Take first rule that matches and remove first subnote (orderd 1,2,3) from the notes column:

                 

                WITH tab1 (file_id, record_id, part_type_id, part_number, position_id, notes, mapped)

                       AS (SELECT 1,1,123,777,1,'NEW|A/C COMPRESSOR KIT','N' FROM dual UNION ALL

                           SELECT 1,2,123,777,2,'REMAN|A/C LINE','N' FROM DUAL UNION ALL

                           SELECT 1,4,123,777,2,'REMAN|NEW|A/C LINE','N' FROM DUAL UNION ALL

                           SELECT 1,5,123,777,2,'GAGA|A/C LINE','N' FROM DUAL UNION ALL

                           SELECT 1,6,123,777,1,'REMAN|NEW|A/C LINE','N' FROM DUAL UNION ALL

                           SELECT 1,3,123,777,2,'REMAN|A/C LINE','Y' FROM DUAL

                           )

                    ,RULES (rule_id, file_id, part_type_id, part_number, position_id, subnote1, subnote2, subnote3)

                        AS (SELECT 1,1,123,777,1,'NEW',NULL,NULL FROM dual UNION ALL

                            SELECT 2,1,123,777,2,'REMAN','GAGA','NEW' FROM dual)

                    ,rules2 AS (SELECT rule_id+.1 as rule_id, file_id, part_type_id, part_number, position_id, subnote1 as subnote FROM RULES WHERE subnote1 IS NOT NULL UNION ALL

                                SELECT rule_id+.2, file_id, part_type_id, part_number, position_id, subnote2 FROM RULES WHERE subnote2 IS NOT NULL UNION ALL

                                SELECT rule_id+.3, file_id, part_type_id, part_number, position_id, subnote3 FROM RULES WHERE subnote3 IS NOT NULL)

                SELECT rule_id_applied

                      ,file_id

                      ,part_type_id

                      ,part_number

                      ,position_id

                      ,subnote as subnote_to_be_used

                      ,record_id

                      ,notes

                      ,trim('|' from replace(replace(notes,subnote),'||','|'))  notes_after

                      ,mapped

                  FROM (SELECT r.rule_id as rule_id_applied

                               ,r.file_id

                               ,r.part_type_id

                               ,r.part_number

                               ,r.position_id

                               ,r.subnote

                               ,t.record_id

                               ,t.notes

                               ,t.mapped

                               ,row_number() OVER (PARTITION BY r.file_id, r.part_type_id, r.part_number, r.position_id, t.record_id

                                                       ORDER BY r.rule_id ASC) AS rule_to_apply

                           FROM            RULES2 r

                                INNER JOIN tab1  t ON (    r.file_id = t.file_id

                                                       AND decode(r.part_type_id,t.part_type_id,1,0) = 1

                                                       AND decode(r.part_number,t.part_number,1,0) = 1

                                                       AND decode(r.position_id,t.position_id,1,0) = 1

                                                      )

                          WHERE t.mapped = 'N'       

                            AND instr('|'||t.notes||'|','|'||r.subnote||'|') > 0)

                WHERE rule_to_apply = 1;

                 

                 

                Second one

                 

                Take first rule that matches and remove all subnotes from the notes column:

                 

                WITH tab1 (file_id, record_id, part_type_id, part_number, position_id, notes, mapped)

                       AS (SELECT 1,1,123,777,1,'NEW|A/C COMPRESSOR KIT','N' FROM dual UNION ALL

                           SELECT 1,2,123,777,2,'REMAN|A/C LINE','N' FROM DUAL UNION ALL

                           SELECT 1,4,123,777,2,'REMAN|NEW|A/C LINE','N' FROM DUAL UNION ALL

                           SELECT 1,5,123,777,2,'GAGA|A/C LINE','N' FROM DUAL UNION ALL

                           SELECT 1,6,123,777,1,'REMAN|NEW|A/C LINE','N' FROM DUAL UNION ALL

                           SELECT 1,3,123,777,2,'REMAN|A/C LINE','Y' FROM DUAL

                           )

                    ,RULES (rule_id, file_id, part_type_id, part_number, position_id, subnote1, subnote2, subnote3)

                        AS (SELECT 1,1,123,777,1,'NEW',NULL,NULL FROM dual UNION ALL

                            SELECT 2,1,123,777,2,'REMAN','GAGA','NEW' FROM dual)

                SELECT rule_id_applied

                      ,file_id

                      ,part_type_id

                      ,part_number

                      ,position_id

                      ,subnote1

                      ,subnote2

                      ,subnote3

                      ,record_id

                      ,notes

                      ,trim('|' from regexp_replace(replace(replace(replace(notes,subnote1),subnote2),subnote3),'(\|)\1','\1')) as notes_after

                      ,mapped

                  FROM (SELECT r.rule_id as rule_id_applied

                               ,r.file_id

                               ,r.part_type_id

                               ,r.part_number

                               ,r.position_id

                               ,r.subnote1

                               ,r.subnote2

                               ,r.subnote3

                               ,t.record_id

                               ,t.notes

                               ,t.mapped

                               ,row_number() OVER (PARTITION BY r.file_id, r.part_type_id, r.part_number, r.position_id, t.record_id

                                                       ORDER BY r.rule_id ASC) AS rule_to_apply

                           FROM            RULES r

                                INNER JOIN tab1  t ON (    r.file_id = t.file_id

                                                       AND decode(r.part_type_id,t.part_type_id,1,0) = 1

                                                       AND decode(r.part_number,t.part_number,1,0) = 1

                                                       AND decode(r.position_id,t.position_id,1,0) = 1

                                                      )

                          WHERE t.mapped = 'N'       

                            AND (   instr('|'||t.notes||'|','|'||r.subnote1||'|') > 0

                                 OR instr('|'||t.notes||'|','|'||r.subnote2||'|') > 0

                                 OR instr('|'||t.notes||'|','|'||r.subnote3||'|') > 0

                                ))

                WHERE rule_to_apply = 1;

                 

                 

                I did a little shortcut by not including the subnotes in '|' during the replace, so that should be done too...

                 

                hth