Developing SQL for automated execution

We have a team of developers who have very good knowledge of our applications. Sometimes the business wants the app changed but cannot make the changes through the application itself. The developers figure out which tables and columns need to be updated, test in non-production and then pass the script to the DBA team for execution. Often these scripts require inspecting the results of queries or other SQL statements and making a decision on whether to proceed with the next step or stop and roll back the changes. Not a big deal except when the business also requires the changes go into effect late at night or on the weekend.

I have been modifying the SQL for those off-hour execution times and then scheduling them to run at the expected time. I decided to train the development team so they could write the SQL themselves. They are new to Oracle but know SQL Server and SQL in general. I showed them how to write SQL that checks the actual results and rolls back or commits depending on expected results.

This works great and saves the DBA team a lot of late night work.

Here’s an example :

drop table SCHEMANAME.TABLENAME_BKP_changenum_B4;
 create table SCHEMANAME.TABLENAME_BKP_changenum_B4 as select * from SCHEMANAME.TABLENAME
 where
 (
 column1 in ('0040040','0040144','0040403','0040404','0040423','0040702')
 And column2 = '*ANY'
 ) or (
 column1 in ('0040040','0040144','0040403','0040404','0040423','0040702')
 ) or (
 column1 in ('0040019','0040085','0040086','0040145','0040405','0040406')
 And column2 <>'*ANY'
 ) or (
 column1 in ('54','55','56','57','59','79')
 And column2 <>'*ANY'
 );
 -- >>>> 210 ROWs or more is okay to proceed

declare
 exp_rows number;
 u_rows number;
 rowcount_fail exception;
 BEGIN
 dbms_session.set_identifier('changenumber');
 exp_rows:=41;
 dbms_output.put_line('Expecting '|| exp_rows || ' rows.');
 -- SQL#1 - DELETE #1

delete SCHEMANAME.TABLENAME
 WHERE
 (
 column1 in ('0040040','0040144','0040403','0040404','0040423','0040702')
 And column2 = '*ANY'
 );
 -- >>>> 41 ROWs or more is okay to proceed

u_rows:=SQL%ROWCOUNT;
 dbms_output.put_line('Updated ' || u_rows || ' rows.');
 if u_rows < exp_rows then
 raise rowcount_fail;
 else
 dbms_output.put_line('Rows updated >= expected row count');
 end if;

exp_rows:=27;
 dbms_output.put_line('Expecting '|| exp_rows || ' rows.');
 -- SQL #2 - update #1

UPDATE SCHEMANAME.TABLENAME
 set column3 = '*ANY',
 column3y = '*A',
 column4 = (100000 + to_char(sysdate,'YYDDD')),
 column5 = 'changenumber',
 colummn6 = SUBSTR(sys_context('USERENV','SERVER_HOST'),1,10)
 where
 (
 column1 in ('0040040','0040144','0040403','0040404','0040423','0040702')
 );
 -- >>>> 27 ROWs or more is okay to proceed

u_rows:=SQL%ROWCOUNT;
 dbms_output.put_line('Updated ' || u_rows || ' rows.');
 if u_rows < exp_rows then
 raise rowcount_fail;
 else
 dbms_output.put_line('Rows updated >= expected row count');
 end if;

exp_rows:=129;
 dbms_output.put_line('Expecting '|| exp_rows || ' rows.');
 -- SQL #3 - update #2

UPDATE SCHEMANAME.TABLENAME
 set column3 = '*ANY',
 column3y = '*A',
 column4 = (100000 + to_char(sysdate,'YYDDD')),
 column5 = 'changenumber',
 colummn6 = SUBSTR(sys_context('USERENV','SERVER_HOST'),1,10)
 where
 (
 column1 in ('0040019','0040085','0040086','0040145','0040405','0040406')
 And column2 <>'*ANY'
 );
 -- >>>> 129 ROWs or more is okay to proceed

u_rows:=SQL%ROWCOUNT;
 dbms_output.put_line('Updated ' || u_rows || ' rows.');
 if u_rows < exp_rows then
 raise rowcount_fail;
 else
 dbms_output.put_line('Rows updated >= expected row count');
 end if;

exp_rows:=13;
 dbms_output.put_line('Expecting '|| exp_rows || ' rows.');
 -- SQL#4 - DELETE #2

delete SCHEMANAME.TABLENAME
 WHERE
 (
 column1 in ('54','55','56','57','59','79')
 And column2 <>'*ANY'
 );
 -- >>>> 13 ROWs or more is okay to proceed

u_rows:=SQL%ROWCOUNT;
 dbms_output.put_line('Updated ' || u_rows || ' rows.');
 if u_rows < exp_rows then
 raise rowcount_fail;
 else
 dbms_output.put_line('Rows updated >= expected row count');
 end if;

--rollback;
 commit;

exception
 when rowcount_fail then
 dbms_output.put_line('Rows updated not >= expected row count, rolling back the change');
 rollback;
 when others then
 dbms_output.put_line('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
 rollback;
 END;
 /

Advertisements

Oracle Application Express 5.1.1 on Exadata cluster

Our DBA team really likes using APEX to develop DBA applications. We are a pretty small team and we like to automate and standardize everything we do. We make a habit of using database tables to store our configuration information and we write scripts that read those DBA tables in order to support our business users.

We recently took control of a new Exadata cluster and of course we created a DBA repository database as we were creating the business databases. Since APEX 5.1.1 was just released, we decided to replace our old APEX system. That system was was a single instance running on a virtual machine and controlled by an outsourced administration team. We are a lot more comfortable having control over installation and maintenance of a system we depend on to get our work done.

The new Exadata is a four node RAC and our new DBA repository has an instance running on each node. We did not want to install software on the Exadata so that ruled out using Rest Data Services (aka APEX Listener). It also ruled out using Oracle HTTP Server with mod_plsql.

Since this APEX system is only used by our DBA team and is not exposed to outside networks we felt comfortable using the Embedded PL/SQL Gateway. However, the Oracle documentation contains this ominous note:”Oracle recommends that you do not select the Embedded PL/SQL Gateway option for Oracle RAC installations. Because the Embedded PL/SQL Gateway uses an HTTP Server built into the database instance, it does not take advantage of the Oracle RAC shared architecture.” (https://docs.oracle.com/database/apex-5.1/HTMIG/choosing-web-listener.htm#HTMIG370)

What to do? We decided to move forward with the Embedded PL/SQL Gateway and see how useable it would be. We could always spin up another virtual machine later if we needed to go the Rest Data Services route. I went through the steps in the APEX documentation to set up the Embedded PL/SQL Gateway. I could not get a response when I hit the database machine APEX URL that I had configured. I poked around on the servers and noticed that the XML db listeners were not running. I had forgotten that when I created the DBA database I intentionally did not install any options like Java and XML db. After installing XML db and restarting the database APEX started working. (https://docs.oracle.com/database/121/ADXDB/appaman.htm#ADXDB2700)

But that’s not all – here’s the real reason I wanted to blog about this. This APEX system is reachable using the scan listener! So that scary quote in the documentation should be ignored. We can connect using a URL like this: http://corpexa-scan.corp.com:8080/apex . No server names are required. So we CAN take advantage of RAC functionality.

This is just what we wanted so we are very satisfied with our new APEX system.

Use cx-Oracle in Python to bulk load arbitrary tables using csv files

I recently got a request to load hundreds of database tables in a day and a half using csv files our developers created. I considered using SQL Loader but I didn’t want to spend the time understanding the table structure and creating the control file for each table. Another alternative was to use SQL Developer to import the csv files. That goes against my nature because it would be a manual, repetitive and error prone process.

The csv files were fairly small so plain inserts would work. I looked at writing a BASH script to generate the insert statements and it became very complex very quickly. PL/SQL was an option too but I ruled it out because of the requirement to read flat files. I have been using the Python cx-Oracle module recently so I decided to write a Python script for this task.

Python turned out to be the right choice and the script was very easy to write. The only tricky part was recognizing the date fields. Those are unquoted like a numeric field but contain literals like JAN,FEB, etc. Python and cx-Oracle handled everything with a minimum of effort on my part.

Create the csv files in a dedicated subdirectory like : tablename.csv. The first row of each csv file must contain the column names. The script truncates the target table before inserting the csv records. Any double quoted field is considered a literal and unquoted fields are numeric except date fields which must have the format 01-JAN-16.

Use these GitHub links for more information : ReadmeSource code

Setting up Ansible and cx_Oracle

I have been automating and centralizing dba and system administration processes using Python recently. One of the problems I solved was how to create Bash and SQL scripts to run on the remote database servers, copy them to the database servers and then execute them as another Linux account. After I got my Python scripts working perfectly, I discovered that I had re-invented Ansible 🙂

My scripts would have been much less complex and easier to write if I had done them with Ansible in mind. So that’s how I will write them going forward. I also wanted to implement a DBA Data Warehouse so I added cx_Oracle to the mix and used it to store the admin data in a database. I have been using a local virtual machine for Python development and the database was just a local installation on the VM.

Before moving to production I needed to figure out an easy way to get this stuff installed on the production Linux box. Setting it up on my VM was an adventure in Googling and trial and error. So here’s the documentation on how to set it up. You need to define the shell variables first.

export http_proxy=http://$my_userid:$my_password@$proxy_server:$port
export https_proxy=https://$my_userid:$my_password@$proxy_server:$port
wget https://bootstrap.pypa.io/ez_setup.py -O - | python
easy_install pip
export ORACLE_HOME=/base/oracle/product/12102
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
yum install python-devel
pip install --proxy=https://$my_userid:$my_pasword@$proxy_server:$port cx_Oracle
pip install --proxy=https://$my_userid:$my_pasword@$proxy_server:$port ansible