Oracle CRUD Reportor

Database consultant, DBA and SQL developers always need to read SQL scripts to find out the tables have Create, Read, Update, Delete and Insert operations against them. If SQL scripts is complex and large than 100 lines, then it's almost impossible to do that without help with a tool. If you need to analyze hundred's of SQL scripts, then you will need a tool or API help you to do that definitely.

General SQL Parser can help you to do that automatically with less than 100 lines C# code(The code is ready here). This code can search all tables in Oracle PLSQL scripts(including procedure/function/package), function/procedure of SQL Server, MySQL and DB2. No matter how complex and large the SQL script is, as long as it's syntax valid, this CRUD reportor can process that well. Of course, you can modify this code to fit your own need.

Take the following Oracle function for example, CRUD Reportor can tell you that in Dw_dim_contact function, selected table: cmp_contact(1), contact_dim(3), LOCAL_lookuplistitem(1), local_company(1), and local_country(1). Inserted table: contact_dim(1). Updated table: contact_dim(2).

 

CREATE OR REPLACE FUNCTION Dw_dim_contact (refresh_period_start_date IN TIMESTAMP,
                                           refresh_period_end_date   IN TIMESTAMP,
                                           etlrunidentifier          IN NUMBER)
RETURN NUMBER
AS
  recordsdeleted  NUMBER (19, 0) := 0;
  minorerrorcount NUMBER (19, 0) := 0;
  CURSOR contact_cursor IS
    SELECT cd.contact_identifier,
           Nvl(cd.creation_date, current_timestamp),
           current_timestamp,
           etlrunidentifier
    FROM   cmp_contact c
           LEFT OUTER JOIN contact_dim cd
             ON c.contactid = cd.source_contactid
           LEFT OUTER JOIN local_lookuplistitem li
             ON li.lookuplistitemid = c.typeluid
           LEFT OUTER JOIN local_company cc
             ON cc.companyid = c.companyid
           LEFT OUTER JOIN local_country cty
             ON cty.countrycode = c.country
           LEFT OUTER JOIN contact_dim cd2
             ON c.managercontactid = cd2.source_contactid;
  newcontacts     CONTACT_DIM_TABLE;
  updatecontacts  CONTACT_IDENTIFIER_TABLE;
BEGIN
  OPEN contact_cursor;

  LOOP
      FETCH contact_cursor BULK COLLECT INTO contactarray LIMIT 1000;

      recordsread := recordsread + SQL%ROWCOUNT;

      BEGIN
          FORALL i IN INDICES OF newcontacts SAVE EXCEPTIONS
            INSERT INTO contact_dim
            VALUES Newcontacts(i);
      EXCEPTION
          WHEN ex_dml_errors THEN
            l_error_count := SQL%BULK_EXCEPTIONS.COUNT;
      END;

      recordsinserted := recordsinserted + newcontacts.COUNT;

      FORALL i IN updatecontacts.FIRST .. updatecontacts.LAST SAVE EXCEPTIONS
        UPDATE contact_dim
        SET    ROW = Contactarray(i)
        WHERE  contact_identifier = Updatecontacts(i);

      exit WHEN contact_cursor%NOTFOUND;
  END LOOP;

  CLOSE contact_cursor;

  UPDATE contact_dim c
  SET    c.manager_identifier = (SELECT c2.contact_identifier
                                 FROM   contact_dim c2
                                 WHERE  c2.source_contactid = c.source_managercontactid)
  WHERE  c.modify_etl_run_identifier = etlrunidentifier
         AND c.source_managercontactid IS NOT NULL
         AND c.manager_identifier IS NULL
          OR c.manager_identifier = pkg_dw_seedvalues.dw_getunknowncontactdimmgrid;

  RETURN 0;
END; 

 

Questions?

If you have any questions about General SQL Parser and this CRUD reportor, You can always send us an email and we'll get back to you within 24 hours.

 

Any other benefits can I get by using this SQL Parser?

Yes, of course. We collect all kinds of examples in this page to illustrate how and why you want to use general sql parser, hope it can help you to make better use of this library.