This example shows how to find insert/update count in the merge statement using DML Error Logging feature.

-- Create required test tables.

CREATE TABLE inp_tbl(col1 NUMBER, col2 VARCHAR2(30));
CREATE TABLE out_tbl(col1 NUMBER, col2 VARCHAR2(20));

-- Create DML Error Log Table
BEGIN
   DBMS_ERRLOG.create_error_log ('out_tbl', 'err_log_out_tbl');
END;
/

-- Insert test data
INSERT INTO inp_tbl
     VALUES (1, 'Test Data with Error(>10 char)');
INSERT INTO inp_tbl
     VALUES (2, 'Correct Data');
INSERT INTO inp_tbl
     VALUES (3, 'Correct Data');
COMMIT ;

-- Create Package Specification to merge the data.
CREATE OR REPLACE PACKAGE pkg_merge IS
   g_rows_inserted    NUMBER := 0;
   g_rows_updated     NUMBER := 0;
   g_rows_errored     NUMBER := 0;
   g_rows_processed   NUMBER := 0; 

   FUNCTION increment_merge_insert
      RETURN PLS_INTEGER;
   PROCEDURE merge_data (p_comment_str VARCHAR2);
END;
/
SHOW ERRORS 

CREATE OR REPLACE PACKAGE BODY pkg_merge
IS
   FUNCTION increment_merge_insert
      RETURN PLS_INTEGER IS
   BEGIN
      g_rows_inserted := NVL (g_rows_inserted, 0) + 1;
      RETURN 0;
   END; 

   PROCEDURE merge_data (p_comment_str VARCHAR2)  IS
      l_insert_errors   NUMBER := 0;
      l_update_errors   NUMBER := 0;
      l_rows_merged     NUMBER := 0;
   BEGIN
      g_rows_inserted := 0;
      g_rows_updated := 0;
      g_rows_processed := 0;

      MERGE INTO out_tbl
         USING (SELECT col1, col2
                  FROM inp_tbl) inp_tbl
         ON (out_tbl.col1 = inp_tbl.col1)
         WHEN MATCHED THEN
            UPDATE
               SET col2 = inp_tbl.col2
         WHEN NOT MATCHED THEN
            INSERT (col1, col2)
            VALUES ((CASE increment_merge_insert ()
                        WHEN 0
                           THEN inp_tbl.col1
                     END
                    ),
                    inp_tbl.col2)
      LOG ERRORS
       INTO err_log_out_tbl(p_comment_str)
       REJECT LIMIT UNLIMITED;

      l_rows_merged := NVL (l_rows_merged, 0) + SQL%ROWCOUNT; 

      SELECT SUM (CASE
                     WHEN ora_err_rowid$ IS NOT NULL
                        THEN 1
                     ELSE 0
                  END),
             SUM (CASE
                     WHEN ora_err_rowid$ IS NULL
                        THEN 1
                     ELSE 0
                  END)
        INTO l_update_errors,
             l_insert_errors
        FROM err_log_out_tbl
       WHERE ora_err_tag$ = p_comment_str;
      g_rows_processed :=
                     NVL (l_rows_merged, 0) + l_insert_errors
                     + l_update_errors;

      DBMS_OUTPUT.put_line ('Total Rows Processed : ' || g_rows_processed);
      DBMS_OUTPUT.put_line ('Total Merge Count    : ' || l_rows_merged);
      DBMS_OUTPUT.put_line (   'Merge Insert Count   : '
                            || (g_rows_inserted - l_insert_errors)
                           );
      DBMS_OUTPUT.put_line ('Insert Errors        : ' || l_insert_errors);
      DBMS_OUTPUT.put_line ('Update Errors        : ' || l_update_errors);
      DBMS_OUTPUT.put_line ('Final Insert Count   : '
                            || (g_rows_inserted - (2 * l_insert_errors))
                           );
      DBMS_OUTPUT.put_line ('Final Update Count   : '
                            || (  l_rows_merged
                                - (g_rows_inserted - (2 * l_insert_errors))
                               )
                           );
      DBMS_OUTPUT.put_line (   'Final Error Count    : '
                            || (l_insert_errors + l_update_errors)
                           );
   END;
END;
/
SHOW ERRORS

--Call the merge package

SET serveroutput on
BEGIN
   pkg_merge.merge_data ('Test Run');
END; 

-- Check output.
SELECT *
  FROM out_tbl;
SELECT *
  FROM err_log_out_tbl;

-- Cleanup tables
DROP TABLE inp_tbl;
DROP TABLE out_tbl;
DROP TABLE err_log_out_tbl;

I was getting few queries about calling a webservice in PLSQL using HTTP protocol. We can use UTL_HTTP package to do it. Below is the example that calls SOAP service from PLSQL.

<code>

SET serveroutput on
DECLARE
   -- SOAP REQUESTS/RESPONSE
   soap_req_msg    VARCHAR2 (2000);
   soap_resp_msg   VARCHAR2 (2000);

   -- HTTP REQUEST/RESPONSE
   http_req        UTL_HTTP.req;
   http_resp       UTL_HTTP.resp;

BEGIN
   --
   -- Create SOAP request via HTTP
   --
   soap_req_msg :=
      '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:myschema="http://HelloEjb"> <soapenv:Header/>
  <soapenv:Body>
  <myschema:in0><myschema:message>Test Message</myschema:message></myschema:in0>
  </soapenv:Body></soapenv:Envelope>';

   http_req :=
      UTL_HTTP.begin_request
                         ('http://localhost:8080/HelloBeanService/HelloBean',
                          'POST',
                          'HTTP/1.1'
                         );
   UTL_HTTP.set_header (http_req, 'Content-Type', 'text/xml');
   UTL_HTTP.set_header (http_req, 'Content-Length', LENGTH (soap_req_msg));
   UTL_HTTP.set_header (http_req, 'SOAPAction', '');
   UTL_HTTP.write_text (http_req, soap_req_msg);

  --
   -- Invoke Request and get Response.
   --
   http_resp := UTL_HTTP.get_response (http_req);
   UTL_HTTP.read_text (http_resp, soap_resp_msg);
   UTL_HTTP.end_response (http_resp);
   DBMS_OUTPUT.put_line ('Output: ' || soap_resp_msg);
END;
/

</code>

Database Build Structure

In Database programming, you will be dealing with lot of database objects and it is important to maintain the proper build structure.

• It keeps code organized.
• Group related code together
• Makes DBA life easier in applying the build in production database especially in isolated environments.

In the ideal build scenario, the following directory structure has to be followed.

users

All the users & roles related statements (create, update, delete) should be in this folder.

tables

All the table related statements should be in this folder. It should also contain statements that provide comments to table and columns. Triggers should go on this. For each table, separate sql script should be provided and a master script for creating all tables should be provided.

views

All the views related statements should be in this folder. For each view, separate SQL script should be provided and a master script should be provided for creating all the views.

sequences

All the sequence related statements should be in this folder. For each sequence, separate SQL script should be provided and a master script should be provided for creating all the sequences.

synonyms

All the synonyms related statements should be in this folder. More than one synonym can be placed in a single script.

types

All the persistent Object types and Table types related statements should be in this folder. For each type, separate SQL script should be provided. Object Type and a related table type can be combined into a same sql script.

grants

All the grants for various objects to various users/roles should be in this folder. For each schema, separate SQL script should be provided.

data_seed

All the initial seed operations (ONLY DML) should be in this folder. For each table seeding, separate sql should be provided.

plsql

All the plsql packages should be in this folder. For each package, there should be two files, one for specification and one for body. All the anonymous function/procedures should be in separate sql files (Idealy there should not be any anonymous functions)

The sample database build is here.