Montag, 5. Dezember 2011

Add some source...

Now I want to add some source to my profiler data. It is easy to find the necessary information for stored code. So I first add the missing lines to plsql_profiler_data.

...
  MERGE INTO plsql_profiler_data d USING
  (
    SELECT
      runid
    , unit_number
    , line AS line#
    FROM
      plsql_profiler_units
    JOIN dba_source
    ON
      unit_owner  = owner
    AND unit_name = name
    AND unit_type = TYPE
    WHERE
      (
        runid, unit_number
      )
      IN
      (
        SELECT
          runid
        , unit_number
        FROM
          plsql_profiler_units
        WHERE
          runid = runid_in
      )
  )
  s ON
  (
    d.runid = s.runid AND d.unit_number = s.unit_number AND d.line# = s.line#
  )
WHEN NOT MATCHED THEN
  INSERT
    (
      d.runid
    ,d.unit_number
    ,d.line#
    ,d.total_occur
    ,d.total_time
    ,d.min_time
    ,d.max_time
    )
    VALUES
    (
      s.runid
    ,s.unit_number
    ,s.line#
    ,0
    ,0
    ,0
    ,0
    );
...

and then I create the necessary information in the related table plsql_profiler_data_source

...
  MERGE INTO plsql_profiler_data_source d USING
  (
    SELECT
      runid
    ,unit_number
    ,line AS line#
    ,text
    FROM
      plsql_profiler_units
    JOIN dba_source
    ON
      unit_owner  = owner
    AND unit_name = name
    AND unit_type = TYPE
    WHERE
      (
        runid, unit_number
      )
      IN
      (
        SELECT
          runid
        , unit_number
        FROM
          plsql_profiler_units
        WHERE
          runid = runid_in
      )
  )
  s ON
  (
    d.runid = s.runid AND d.unit_number = s.unit_number AND d.line# = s.line#
  )
WHEN NOT MATCHED THEN
  INSERT
    (
      d.runid
    ,d.unit_number
    ,d.line#
    ,d.text
    )
    VALUES
    (
      s.runid
    ,s.unit_number
    ,s.line#
    ,s.text
    );
...


Source

to be continued...

Keine Kommentare:

Kommentar veröffentlichen