Categories
Database Programming

Generic PL/SQL procedure to update target table data with source table data

Note: I have transferred this blog from my old blog site: http://dcarapic.blogspot.com/ so I am reposting most of the stuff from there

Sometimes you might need to update data inside a table with fresh data from another table which has the same structure (but it might be in another schema). Usually this might occur if you have some sort of a 'master' schema where the 'global' data is located and you want to periodically transfer this data to some the same table in the local user schema.

Although it is relatively easy to create an update procedure/PLSQL block to do this type of thing, it is very tedious to do this every time for each table that you want to synchronize in this way The following procedure takes the name of the table and creates dynamic SQL which it then executes via EXECUTE IMMEDIATE. There are some restrictions in order for this to work:

  • Table must have a unique key
  • User must be able to query 'all_tab_cols' view
  • User must be able to execute selects against the source table (this one is understandable)

Here is the procedure:

procedure copy_from_source
(
      p_table_name in varchar2
)
as
  cursor cur_columns(c_v_table_name varchar2, c_v_source varchar2) is
    select column_name from all_tab_cols 
    where
    table_name = c_v_table_name and owner = c_v_source and column_id is not null;

  v_id_col varchar2(38) := 'ID';
  v_source_schema varchar2(38) := 'SOURCE';
  v_target_schema varchar2(38) := user;
  v_query varchar2(10000);
  v_columns varchar2(10000):= null;
  v_start number := 0;
begin     
  v_query := null;
  for v_col in cur_columns(p_table_name, v_source_schema) loop
      if v_query is null then
         v_query := 'update ' || v_target_schema || '.' || p_table_name || 
            ' l set ' || chr(13) || chr(10);

         v_query := v_query || '  ' || '"' || v_col.column_name || '"' || ' = (';
         v_query := v_query || 'select ' || '"' || v_col.column_name || '"' || 
            ' from ' || v_source_schema || '.' || p_table_name || ' g where g.' || 
            v_id_col || ' = l.' || v_id_col;

         v_query := v_query || ')';
      else
         v_query := v_query || ', ' || chr(13) || chr(10);
         v_query := v_query || '  ' || '"' || v_col.column_name || '"' || ' = (';
         v_query := v_query || 'select ' || '"' || v_col.column_name || '"' ||
            ' from ' || v_source_schema || '.' || p_table_name || ' g where g.' || 
            v_id_col || ' = l.' || v_id_col;

         v_query := v_query || ')';
      end if;
  end loop;      
  v_query := v_query || chr(13) || chr(10) || ' where ' || v_id_col || ' in ' || 
    chr(13) || chr(10);

  v_query := v_query || ' (select ' || v_id_col || ' from ' || v_source_schema || 
    '.' || p_table_name || ')';

  v_query := v_query || chr(13) || chr(10);
  -- dbms_output.putline(v_query);
  execute immediate v_query;

  v_query := null;
  for v_col in cur_columns(p_table_name, v_source_schema) loop
      if v_columns is null then
         v_columns := v_columns || '"' || v_col.column_name || '"';
      else
         v_columns := v_columns || ', ' || '"' || v_col.column_name || '"';
      end if;
  end loop;
  v_query := 'insert into ' || v_target_schema || '.' || p_table_name || 
    ' (' || v_columns || ')' || chr(13) || chr(10);
  v_query := v_query || ' select '  || v_columns  || ' from ' || v_source_schema || 
    '.' || p_table_name || ' where ' || v_id_col || ' not in ' || chr(13) || chr(10);
  v_query := v_query || ' (select ' || v_id_col || ' from ' || v_target_schema || 
    '.' || p_table_name || ')';
  -- dbms_output.putline(v_query);
  execute immediate v_query;
end;
Categories
Database

Intermittent ORA-12545 error

Note: I have transferred this blog from my old blog site: http://dcarapic.blogspot.com/ so I am reposting most of the stuff from there

Recently I have encountered a strange kind of error while setting up an application on the client computer(s). Application (.NET) connects to an Oracle 11g database (via Microsoft Oracle database provider and Oracle InstantClient 11.0.7.0) which is on a central server. All client computers are on remote locations with a Cisco router and a permanent VPN connection with NAT translation.

On some clients the application would report ORA-12545 error few times while attempting to connect to a database. Once the connection was established the application worked fine. Needless to say I was baffled by the error and could not locate the problem. Finally, after several hours of googling, I have determined that the problem was that we specified the database server with an IP address and there was no DNS name for it.

It would seem that the OCI connected via IP address, and then someone (I am not sure if it is the Cisco router or the Oracle database) returned the real DNS name of the server. This apparently confused the OCI driver which then reported ORA-12545: Connect failed because target host or object does not exist. After adding the real name of the server to the windows\system32\drivers\etc\hosts file the problem was resolved.

Edit: After googling some more I have found this blog post which might shed more light into the problem: http://tardate.blogspot.com/2007/06/check-locallistener-if-you-run-rac.html

Categories
Database Programming

Creating semi-automated database update scripts for Oracle

Note: I have transferred this blog from my old blog site: http://dcarapic.blogspot.com/ so I am reposting most of the stuff from there

If you have ever created applications that use a database to store data you must have run into a problem how to do updates on the database once the application goes into production and you need to update the production database structure and/or data with new functionality or bug fixes made on your development database. Some database tools (Oracle SQL developer) help you in this by having an integration with SVN. This means that you have a history of various database versions that you have been working on, but it takes some work to use that data to create update scripts which you can apply on your production system.

If you are using Oracle as your database solution then this problem becomes much easier because Oracle supports DDL triggers. DDL triggers enable you to do some work once a DDL command is executed. Combining this trigger with some logging can enable you to quickly produce database update scripts. Note: Everything done on the "SYS" schema is done as a SYS user. Everything else is done by 'normal' db users.To start with, we are going to need a database table to log our changes:

CREATE TABLE "SYS"."DDL_LOG" (   
    "ID" NUMBER(38,0) DEFAULT 0 NOT NULL ENABLE,
    "USER_NAME" VARCHAR2(30 BYTE),
    "DDL_DATE" DATE,
    "DDL_TYPE" VARCHAR2(30 BYTE),
    "OBJECT_TYPE" VARCHAR2(18 BYTE),
    "OWNER" VARCHAR2(30 BYTE),
    "OBJECT_NAME" VARCHAR2(128 BYTE),
    "SQL_TEXT" CLOB,
    "EXECUTE_TEXT" CLOB
);

Here is the explanation of the columns:

  • ID - id of the log (I'll explain later why we need it)
  • USER_NAME - name of the Oracle user that made the DDL command
  • DDL_DATE - date and time of the modification
  • DDL_TYPE - type of modification (ALTER, CREATE etc.)
  • OBJECT_TYPE - type of object on which the modification was made (TABLE, TRIGGER etc.)
  • OWNER - schema owner
  • OBJECT_NAME - name of the object that was modified (created, altered dropped etc.)
  • SQL_TEXT- actual command that was executed
  • UPDATE_TEXT -I'll explain this later

Here is the actual trigger that will do the logging:

CREATE OR REPLACE TRIGGER "SYS"."DDL_TRIG"
    AFTER DDL
ON DATABASE
DECLARE
    v_date varchar2(100);
    v_id number;
    v_stmt clob;
BEGIN
    IF ora_dict_obj_owner = 'DEVELOPMENT' THEN
        v_date := TO_CHAR(sysdate, 'YYYY-MM-DD');
        SELECT ddl_log_seq.nextval INTO v_id FROM dual;
        v_stmt := REPLACE(last_sql_text(), '''', '''''');

       INSERT INTO DDL_LOG (
        id,
        user_name,
        ddl_date,
        ddl_type,
        object_type,
        owner,
        object_name,
        sql_text,
        execute_text
        ) VALUES (
        v_id,
        ora_login_user,
        SYSDATE,
        ora_sysevent,
        ora_dict_obj_type,
        ora_dict_obj_owner,
        ora_dict_obj_name,
        last_sql_text(),
        'execute_if_not_executed(''' || ora_login_user || ''', ''' || v_date
        || ''', ' || v_id || ', ''' || v_stmt || ''');'
        );
    END IF;
END;

The trigger is pretty straightforward, it just fills the DDL_LOG table if the DDL commands were made on the DEVELOPMENT schema. What is interesting are the fields ID and EXECUTE_TEXT.

Why do we need ID field? This is because, later on, we need to create some sort of a script from the records in the DDL_LOG table. This script should have all the commands executed in the same order as the developers have executed them on the development database. While we could use DDL_DATE field for this, it is not a good solution when you are using database management tools.

The tools (such as PL/SQL developer or Oracle SQL developer) usually execute DDL commands in a batch so you get a lot of commands within the same millisecond. Ordering by DDL_DATE field does not help you much in those cases, but if you have ID then you can be certain of the order in which the DDL commands were executed.

UPDATE_TEXT field also serves to help us create an update script. This field will be filled with a procedure call ('execute_if_not_executed') that can be executed on the production server. Why would we need such an elaborate system? Why not just take the SQL_TEXT and run it on the production? This is certainly possible, but one of the things that might occur (and does occur) on the production database (and even more so if you have multiple production databases) is that a DDL command or a group of commands never get executed!

You may simply overlook a system because you forgot about it, or the guy responsible for updates got sick and someone else does a bad job out of it. If such an error occurs frequently it would be nice to have a log of updates (DDL commands) that have been made on the database. It would be even nicer if you could automatically check if an update was executed and execute it if necessary. This is exactly what the 'execute_if_not_executed' function does: Checks if we have already executed a command If not then executes it Records that the update has been made so that future calls will not do an update again The update records are kept in a database table for each production database.

CREATE TABLE "PRODUCTION"."SCHEMA_VERSION" (
    "VERSION_USER" VARCHAR2(50 BYTE) NOT NULL ENABLE,
    "VERSION_DATE" DATE NOT NULL ENABLE,
    "VERSION_NUMBER" NUMBER NOT NULL ENABLE,
    CONSTRAINT "SCHEMA_VERSION_UK" UNIQUE
    ("VERSION_USER", "VERSION_DATE", "VERSION_NUMBER") ENABLE
);

Here is the EXECUTE_IF_NOT_EXECUTED procedure.

CREATE OR REPLACE PROCEDURE "PRODUCTION"."EXECUTE_IF_NOT_EXECUTED"
(
    p_user in VARCHAR2,
    p_date in VARCHAR2,
    p_order_no in NUMBER,
    p_command in VARCHAR2
)
    AUTHID CURRENT_USER
AS
BEGIN
    IF EXISTS_VERSION(p_user, p_date, p_order_no) = 0 then
        EXECUTE IMMEDIATE p_command;

        insert_version(p_user, p_date, p_order_no);
        commit;
    end if;
END;

The EXECUTE_IF_NOT_EXECUTED procedure is relatively simple. It takes parameters which serve to uniquely identify the update command (user, date and id) and the actual DDL command text. One thing that is specific is that the procedure has AUTHID CURRENT_USER option set. This gives the procedure will execute the DDL commands with the same rights as the user that is executing the procedure. You may remove this option which means that the rights of the user that created the procedure will be used when executing the DDL commands.
This depends on how you have set up your update process. Once you have everything in place, all DDL commands that you are making on developer database will be recorded into the DDL_LOG table. When you want to create an update script just browse the table and copy/paste the update commands into a file, surround them with begin/end; and you are good to go.

Appendix: Several various functions and procedures have also been used but I will not explain them since they are pretty much straightforward. I am just going to list them here.

CREATE OR REPLACE FUNCTION "PRODUCTION"."LAST_SQL_TEXT"
    RETURN CLOB IS
    v_sql_text ORA_NAME_LIST_T;
    v_stmt CLOB;
    n INTEGER;
    i INTEGER;
BEGIN
    n := ORA_SQL_TXT(v_sql_text);
    FOR i IN 1..n LOOP
        v_stmt := v_stmt || REPLACE(SQL_TEXT(i), chr(0));
    END LOOP;
    RETURN v_stmt;
END;

CREATE OR REPLACE FUNCTION "PRODUCTION"."EXISTS_VERSION"
(
    p_username in VARCHAR2,
    p_isoDate in VARCHAR2,
    p_versionNum in NUMBER
)
    RETURN NUMBER
    IS
    v_counter NUMBER;
BEGIN
    SELECT COUNT(1) INTO v_counter FROM SCHEMA_VERSION
    WHERE VERSION_USER = p_username AND
    VERSION_DATE = parse_iso_date(p_isoDate) AND
    VERSION_NUMBER = p_versionNum;
    IF counter = 0 then
        RETURN 0;
    ELSE
        RETURN 1;
    END IF;
END;

CREATE OR REPLACE PROCEDURE "PRODUCTION"."INSERT_VERSION"
(
    p_username in VARCHAR2,
    p_isoDate in VARCHAR2,
    p_versionNum in NUMBER
)
    IS
BEGIN
    INSERT INTO SCHEMA_VERSION (VERSION_USER, VERSION_DATE, VERSION_NUMBER)
    values (p_username, parse_iso_date(p_isoDate), p_versionNum);
END;


CREATE OR REPLACE FUNCTION "PRODUCTION"."PARSE_ISO_DATE"
(
    p_isoDate in varchar2
)
    RETURN DATE
    IS
BEGIN
    RETURN TO_DATE(p_isoDate, 'YYYY-MM-DD');
END;