2 Replies Latest reply on Sep 26, 2017 5:47 PM by Justin Warwick

    Named macro of worksheet actions or sequence of replacements

    Justin Warwick

      A feature I have not been able to find:  record or maybe define in a script a sequence of actions in a worksheet, to be "played back" later. A more specific case of this, and my main use:  a named, easily invokable sequence of RegEx replacements. How would i achieve that in SQL Developer?

       

      Scenario: I am required to migrate ASP.NET applications or simple pages with vbscript or C# as the back-end language. The back-end sourcecode contains embedded SQL, occasionally making use of bind variables, but often constructing a static SQL statement, but creating a single concatenated string with host language variables evaluated at run time. Often the SQL makes use of various methods to create a multi-line string that has to be eliminated.

       

      I'd like to be able to copy sourcecode, paste into SQL Developer worksheet, then invoke my custom "Extract SQL from messy VB.net" macro/action/sequence to apply a sequence of RegEx replacements to the contents of the worksheet, something like:

      1. \s\&\s*["']
      2. "\s*&\s*\w*\.Fields\("(\w*)"\)
           with          &$1
      3. \&\s*vbCrLf(\s*_)?
      4. "\s*\&\s+(\w*)\s+\&\s*"
           with        :$1
      5. "\s*_

      And then i can finish it off manually.

       

      I saw this older discussion:  Re: Macro Record and Macro Play functionality is in SQL Developer

      But it seemed to conclude that only keystrokes that are directly in the worksheet are included (so the quasi-dialog of the find and replace window would be excluded), and moreover, from what I can see the Macro record feature can only record one anonymous macro. So if that is all still the case, it wouldn't really fulfill the need. I could also see how a more general application actions/macro tool could handle a lot more interesting situations, but also would of course be considerably more complex to support.

       

      An example of the source code that I would paste in::

               sSQLQuery = "insert into METER_READINGS_LOG (EQUIPMENT_UID,GAUGEDATE,GAUGE,OBGRAVITY,GRAVTEMP, REC_STATUS,EDITOR,EDIT_DATE) values" & vbCrLf _

                           & "(" & rsMSSql.Fields("EQUIPMENT_UID").Value & "," & vbCrLf _

                           & "TO_DATE('" & rsMSSql.Fields("READING_DATE") & "','MM/DD/YYYY HH:MI:SS AM'), " & vbCrLf _

                           & rsMSSql.Fields("LVL") & "," & vbCrLf _

                           & rsMSSql.Fields("OBSV_GRAVITY") & "," & vbCrLf _

                           & rsMSSql.Fields("TEMPERATURE") & "," & vbCrLf _

                           & " 'T', 'TAF_SCADA', " & vbCrLf _

                           & "TO_DATE('" & date() & " " & time() & "','MM/DD/YYYY HH:MI:SS AM') )" & vbCrLf

        • 1. Re: Named macro of worksheet actions or sequence of replacements
          Vadim Tropashko-Oracle

          Context menu -> Embed/Expose

           

          insert into METER_READINGS_LOG (EQUIPMENT_UID,GAUGEDATE,GAUGE,OBGRAVITY,GRAVTEMP, REC_STATUS,EDITOR,EDIT_DATE) values

          (

          EQUIPMENT_UID

          ,

          TO_DATE('

          READING_DATE

          ','MM/DD/YYYY HH:MI:SS AM'),

          LVL

          ,

          OBSV_GRAVITY

          ,

          TEMPERATURE

          ,

          'T', 'TAF_SCADA',

          TO_DATE('

           

          ','MM/DD/YYYY HH:MI:SS AM') )

           

          Format:

           

             INSERT INTO meter_readings_log (

                  equipment_uid,

                  gaugedate,

                  gauge,

                  obgravity,

                  gravtemp,

                  rec_status,

                  editor,

                  edit_date

              ) VALUES (

                  equipment_uid,

                  TO_DATE('

          READING_DATE

          ','MM/DD/YYYY HH:MI:SS AM'),

                  lvl,

                  obsv_gravity,

                  temperature,

                  'T',

                  'TAF_SCADA',

                  TO_DATE('

           

          ','MM/DD/YYYY HH:MI:SS AM')

              )

          • 2. Re: Named macro of worksheet actions or sequence of replacements
            Justin Warwick

            Thank you, that is a far more direct solution to what I was trying to do. I continue to be surprised by the useful features built right in to SQL Developer. It would be nice to have a couple of preferences settings for Embed/Expose that controlled whether host language variables were just nixed (current behavior) or replaced with a similarly named bind variable (or substitution token). But this is already a great time and nuisance saver!