Samstag, 3. Dezember 2011

A new table is born...

I could have updated the profiler data directly (plsql_profiler_data) using one of the unused columns. Anyhow, I decided to create my structure and relate it to the profiler data. I will create new records in the table  plsql_profiler_data if needed.

CREATE TABLE plsql_profiler_data_source
(
  runid         NUMBER NOT NULL
 ,unit_number   NUMBER NOT NULL
 ,line#         NUMBER NOT NULL
 ,text          VARCHAR2(4000 BYTE)
);

ALTER TABLE plsql_profiler_data_source ADD (
  PRIMARY KEY
  (runid, unit_number, line#)
  USING INDEX);

Source

I have to adjust the instead of trigger on plsql_profiler_runs_vw to avoid errors.

CREATE OR REPLACE TRIGGER plsql_profiler_runs_vw_io_trg INSTEAD OF
  DELETE OR
  update on plsql_profiler_runs_vw referencing new as new old as old for each row declare begin if deleting then
  DELETE FROM plsql_profiler_data_source WHERE runid = :old.runid;
  DELETE FROM plsql_profiler_data WHERE runid = :old.runid;
  DELETE FROM plsql_profiler_units WHERE runid = :old.runid;
  DELETE FROM plsql_profiler_runs WHERE runid = :old.runid;
ELSIF UPDATING THEN
  UPDATE plsql_profiler_runs
  SET run_comment = :new.run_comment
  WHERE runid     = :new.runid;
END IF;
END plsql_profiler_runs_vw_io_trg;
/

Source

to be continued...

Keine Kommentare:

Kommentar veröffentlichen