SUMMARY:

• Expert Oracle SQL, PL/SQL developer with 20 years of experience interested in opportunities in Oracle database (SQL, PL/SQL) development, Oracle Database Security, Database Architecture/Design, Database/SQL Performance Tuning, on premise or in the cloud.
• 20 years of development experience in criminal justice systems integration and government services.
• 11 years of criminal justice systems integration between client servers and mainframes.
• 15 extensive hands - on experience in secure, high-performing and complex PL/SQL packages/procedures/functions/triggers programming, deep understanding of the Oracle database and good design for better performance.
• Extensive experience with Oracle built-in packages (DBMS APPLICATION INFO, DBMS ASSERT, DBMS SQL, UTIL HTTP, UTIL FILE, DBMS AQ, DBMS AQADM, DBMS MGWADM, DBMS SCHEDULER, DBMS HPROF, DBMS STATS, DBMS SESSION, etc).
• Extensive experience in writing effective SQL statements with Merge, Insert All and the With construct.
• Extensive experience in using binding/bulk binding with Associative Arrays, Nested Tables, Records, Varrays, %RowType and other Oracle object types for reducing/eliminating parsing, bulk processing and parallelism to reduce context switching between the PL/SQL engine and the SQL engine and achieve superior database performance.
• Extensive experience in Oracle XML DB, Advanced Queuing, and Messaging Gateway that communicates with IBM Websphere MQ.
• Extensive experience with Oracle database index strategy.
• Experience in using PL/SQL hierarchical profiling, DBMS HPROF, DBMS STATS, TKPROF and other tools to identify bottlenecks.
• Extensive experience with SQL*Plus’s set autotrace on and set autotrace traceonly features for SQL explain plan and performance analysis.
• Extensive experience with SQL*Plus, Oracle SQL Developer, TOAD and Oracle JDeveloper.
• Extensive experience with dynamic SQL using SYS REFCURSOR and CONTEXT, SET CONTEXT and SYS CONTEXT for parameter binding and together with DBMS ASSERT for SQL injection-proof.
• Extensive experience with Oracle XML DB functions XMLElement, XMLAttribute, XMLAgg, etc, to write schema compliant and complex XML document objects from SQL tables, and transform data between SQL tables and XML.
• Extensive experience with the Oracle database built-in DBMS SCHEDULER package to schedule on-time database jobs.
• Extensive experience with Oracle data dictionary views and dynamic views.
• Experience in creating Java stored procedures.
• Experience in database analysis, modeling and design.
• 4 years experience in BizTalk administration and development, open source Drupal/PHP development
• Currently doing BizTalk 2010/2016, SQL Server 2008/2012 and Visual Studio 2010/2015 .Net development.

PROFESSIONAL WORK EXPERIENCE:

Confidential, Ramsey County, MN

Applications Architect/Oracle Developer

Responsibilities:

• Created PL/SQL packages to be used with Messaging Gateway (MGW), Advanced Queuing (AQ) queues, Oracle XML DB and PowerBuilder applications to integrate with the Minnesota Bureau of Criminal Apprehension ( Confidential )’s e-Charging system
• The PL/SQL packages made extensive use the Oracle XML DB functions (XMLElement, XMLAttribute, XMLAgg, XMLTable and XPath, etc) to create schema compliant XML documents from SQL tables, queued in AQ queues and were sent to the IBM WebSphere MQ queues, and to extract data from in-coming XML documents the IBM WebSphere MQ queues into SQL tables. This works better in some ways than graphic tools such as BizTalk maps.
• Processed in-coming XML documents like SQL queries using XQuery by passing in parameters to maintain data integrity in complex XML in a very effective way.
• Used the Oracle built-in package DBMS SCHEDULER to set up a large number of automatic jobs that perform various database tasks.
• Used the explain plans through the SQL*Plus autotrace to identify the wrong indexes for very slow batch jobs and built the appropriate indexes to greatly improve performance on batch jobs. Used sound index strategy to balance insert/update speed and data retrieval speed.
• Make extensive use of inline views, correlated queries, user-defined PL/SQL functions in fast dual, coalesce and case when constructs in composing complex but effective SQL statements for data retrievals and reports.
• Used the Java key tool to create SSL key stores and the DBMS MGWADM to apply such SSL key stores to communicate with the IBM WebSphere MQ that requires SSL connections. Created Oracle DIRECTORY objects that are used by the built-in package UTIL FILE for data export batch jobs. Used the DBMS DATAPUMP to export and import data. Used external table to import data.
• Completed an Oracle University Database Administration II class in February 2013 and since then I have been working 30% as an Oracle DBA and 70% as an architect/developer. I have been using SQL Developer/JDeveloper, SQL*Plus for the development work, and SQL*Plus, RMAN and TOAD for the DBA work (database backup, refresh, recovery, performance monitoring.
• Performed root cause analysis and tuning using the Oracle execution plans and Wait Interface on some slow Peoplesoft ERP batch jobs and tuned some report SQLs to run faster (e.g., from 30 minutes to a few seconds, from 1 hour 10 minutes to 1 minute 47 seconds, and reducing the consistent gets from 435M to 800K). Accomplished this by identifying the wrong indexes and creating the correct indexes.
• And since 2014, I have been working with open source Drupal and PHP. I am currently part of the Drupal development team

Tools: SQL*Plus, Oracle SQL Developer, JDeveloper, TOAD.

Confidential

Senior Software Engineer/Consultant

Responsibilities:

• Created PL/SQL packages to be used with Messaging Gateway (MGW), Advanced Queuing (AQ) queues, Oracle XML DB and PowerBuilder applications to integrate in real time with the Minnesota Court Information Systems
• Prior to Confidential, this client is the only district court and the sheriff department in Minnesota that had real time integration with the state mainframe court system, and as result helps reduce the “suspense files” significantly. My client was the winner of the National Court Management Association’s award for criminal justice systems integration in 2005. My client has not sent employees to take notes (as they used to do) in the court rooms since 1998, and they know when to send the defendants to court and when to release them. When the county and city attorneys file formal charges against a detainee, all court related data are automatically updated.
• Always used proper table aliases for each select list item and qualified each column with the appropriate alias SQL statements, qualified each variable intended to be resolved in the current PL/SQL unit with the names of the blocks in which it is declared. Labels of the blocks and names of the procedures and functions within a package are used as such qualifiers.
• Always use static or embedded SQL if possible, use native dynamic SQL and program the binding by using techniques such as the CONTEXT object and DBMS.SESSION.SET CONTEXT and SYS CONTEXT. If the SQL is too complex for native dynamic SQL and the SQL has place holders,, then use the built-in DBMS SQL package to build the SQL. Use the built-in package DBMS.ASSERT.Simple SQL Name() and .Enquote Literal() to secure the input for the SQL statements.
• Used explicit cursors. Processed large result sets in batches using fetch ... bulk collect into associative arrays with the limit clause to improve performance and conserve system resources. When the rows to be selected can be assumed to be of a manageable size, use the cursor-less select ...bulk collect into associative arrays in a single step. If dynamic SQL is required, the execute immediate ... bulk collect into can be used.
• Created database synonyms and roles to protect and easily manage database objects. Defined producer/consumer API as a function whose return data type is the data that is produced. Records and collection of records, depending on the number of rows selected. Used forall statements for repetitive DML operations. Used analytic SQL functions such as Rank(), partition by, etc. to build Top-N reports. Used the SQL table operator for the in list in where clauses. Used the returning ... into construct for easy check on data affected by any DML statements.
• Single-handedly finished and brought into production a very difficult project, of which all other people quit out of frustration. The finished application replaces the mainframe terminal with a Windows GUI but leverages the mainframe power and integrates the District Court and the Sheriff Department with the Minnesota State Court’s mainframe systems (TCIS), Minnesota Bureau of Criminal Apprehension’s CJIS and the Confidential ’s Confidential in real time.
• Wrote a large number of large Oracle9i/10g/11g PL/SQL packages that automatically export inmate data to the Minnesota Department of Corrections, the inmate phone monitoring system vendor in California and the Victim Notification Enforcement Systems (VINE) in Kentucky, and send instant email notifications to the Corrections’ agents and Warrant Office clerks whenever any of their clients are booked again in the jail (plus many other email notifications to some other law enforcement agencies such as DHS). The Corrections’ officials have commented that this is the best thing they have seen in their 25 years of correction career.

Tools: SQL*Plus, Oracle SQL Developer, JDeveloper, TOAD.