Sunday, January 31, 2016

Introduction to Oracle Analytical functions , rank , denserank.

In this video we tried to demonstrate how oracle anaytical function works. How to do Top N query with oracle's rank and dense_rank function.

Also explained is how outputs and functionality of rank and dense_rank differs , what should be used when.

The demonstration video is from Linux Mint OS and I have used my own Query Light application. Hope you find it enlightening.

 

Here are some of the screen prints. 

Use of Oracle Rank Analytical Function.
Oracle Rank Analytical Function.

Oracle Top N Query using rank analytical functions.






Thursday, January 28, 2016

Get a PLSQL code dump of all your Oracle database code.

I used to work as a part time DBA and when I was doing my supposedely menial tasks like unlocking accounts. One of the frequent request was to get an code for the object a Function or Package or Trigger.

While this was trivial task to get an single object code it had to be quick. I never bothered to write any code or script to get that.
 However one day a developer came and asked me for a code dump for a whole schema ( We had 2500 eligible objects). What he wanted was the code dump for every plsql object stored in the system. This included triggers, functions, procedures , packages. So I searched the internet and came up with a code to do that. It was fairly small 10 liner code which was astonishing. Curiously I never saw the function used anywhere.

The function clob2file is from package dbms_xslprocessor. This one is available in oracle 11g so if you ever found yourself yearning to dump a clob object to file you should try using this. There was just a little problem I wanted to get this dump from 10G database; So I went ahead and wrote a simple script. This script is given below.

 Note : that commented line starting with dbms_xslprocessor works with oracle 11g only you should use either of the 11g or 10g call not both  so one call should always be co. You also need to modify directory names and schema names (SCOTT )


Thanks.

Oracle Procedures to delete the parent child table rows with integrity constraints.

With some very hard work and innovative thinking we came up with the procedures (can be easily clubbed into package) to delete the parent child relationship row with any number of levels.

Below code would find the child row of the parent row being deleted and delete it first , if the child row has its own child then that childs children are fetched and deleted so its basically a recursive algorithm

There are not many situations where you would want to use this. We had a customer mstr record to be deleted which had 20 levels of child records held tight by (ORA-02292: integrity constraint   violated - child record found ) with their parent child constraints.

Since this only was a delete for 1 Master record we went ahead and used this script. If you have more than 20 levels Wise course would be to disable constraints and really think why you want to delete such master record.

Note of caution : BE CAREFUL using this on production environment.

The procedures create the insert statements along the line as it deletes the parent child rows  , but  its done in DBMS_OUTPUT so make sure you have that turned on if you want insert statement roll back plan to be created.  You can of course be innovative and dump inserts in another table or use utl_file to write them elsewhere.

Usage : Find DEL_PARENT_CHILD On
2nd Last line is the call to main recursive delete.
1st Argument is TABLE NAME here (Order_Header_TBL)
2nd Argument is Where Clause  PROCEDURE CAN DELETE ONLY 1 PARENT LINE AT A TIME.
MAKE SURE YOUR WHERE CLAUSE IS FETCHING ONLY 1 ROW.
If you want to delete multiple rows call it that many times in a lopp or whichever way you see fit.

Wednesday, January 27, 2016

Cyclic blocking session removal script written by me for Oracle Database.

Plenty of times we have cyclic database blocking sessions. We have the script which runs in loop and kills them , only to see new blocking sessions have resurfaced.

This is a classic scenario that happens many time due to poor application design.

A was blocking B , B was blocking C.

Unless you have some automated blocking session clearing script.

You would run a script at point in time killing session of A.
Giving the lock ownership to B.   B could realize it late that he has the lock and not commit his work.
So DBA again goes in and sees block kills B's session.

In meantime Frustrated A logs in and start his activity again queuing him up in wait for lock retrieval . This goes on and you end up running the script 5-10 times to kill these sessions to finally clear the blocks.

Note this is not a deadlock which is normally apprehended and identified by oracle.

In such case we had written below plsql to clear the sessions. This basically goes in and check for locks every 2 seconds until all blocks are cleared.

BEWARE YOU WILL NOT HAVE CONTROL ON WHICH SESSION ARE BEING KILLED AS SOON AS SESSION BECOMES BLOCKING IT WILL BE REMOVED IF YOU USE BELOW SCRIPT. IMPORTANT SESSIONS COULD BE KILLED SO BE EXTREMELY CAREFUL AND I RECOMMEND NOT USING IT ON PRODUCTION WITHOUT TWEAKING THE FILTER FOR SPECIFIC SESSIONS OR USERNAMES IN WHERE CLAUSE.
set serverout on
declare
cursor c1 is
select  v.process,b.SQL_ID,  v.SID ,v.SERIAL#,b.SECONDS_IN_WAIT,v.username 
from        v$session v,user_definition_table ud,v$session b ,user_definition_table ubd
where      v.username not in ('SYS')
 and v.username=ud.user_name(+)
 and b.username=ubd.user_name
 and v.SID=b.FINAL_BLOCKING_SESSION
 and b.FINAL_BLOCKING_SESSION_STATUS='VALID'
 and v.status <> 'KILLED';
begin
for i in 1 ..100 
loop
dbms_lock.sleep(2);
for c1_rec in c1
loop
begin
dbms_output.put_line('USER ' || c1_rec.username);
execute immediate 'alter system kill session '''||  c1_rec.SId ||','|| c1_rec.SERIAL# ||''' immediate';
exception when others then
null;
end;
end loop;
end loop;
end;
/

Tuesday, January 26, 2016

Modify Windows file Change / modified date.

There is no straightforward command in windows to change the file modify date (Commonly referred to as timestamp) . I have spent significant time searching UNIX touch -t like command equivalent in windows but could not find a command that could achieve this feat.

However there is a a way around through  a VBS . This VBScript would let you modify the file timestamp (Modified date ) to the one you give in the parameter. In below example it would be 29-Oct-2015.

 
Sub ChangeModifiedDate(strFolder, strFile, dteNew)
    Dim oShell
    Dim objFolder
    Set oShell = CreateObject("Shell.Application")
    Set oFolder = oShell.NameSpace(strFolder)
    oFolder.Items.Item(strFile).ModifyDate = dteNew
End Sub
changemodifieddate "D:\myfolder\fmx","new_empl.fmx","29-10-2015"

So here are the things to change.
Change the last line 1 st argument is the windows folder.
2nd Argument is the file name
3rd Argument is the Date modified desired. One needs to put this code in text file (or create .vbs file) . 

Rename the file extension to .vbs . Double click to execute and you are done !!

Oracle bulk insert with a single insert statement ignoring errors.

Starting from Oracle database release 10g you can have DML's succeed with partial data failures.
How ?

Default reaction of any bulk Insert or Update to an excption is atomic failure. i.e. If you were inserting 100 rows and one of the row fails to insert due to some constraint violation or duplicate records or any other check stuff. Your whole Insert fails.

Of course you can write a quick PLSQL block with a LOOP and a null exception handler/logger which will basically ignore the exception or write it down somewhere and carry out your next loop iteration insertion.

But first this makes you write the code for a loop and is more resource hogging. You should not have to start coding blocks everytime you want such data insertion.

Below is how you can handle it.

First you need to create a table for error logging it can be in any schema.
Ex.
Once you are done with the table , the next is step straight forward, take your normal insert or update statement and append Log errors statement .
 LOG ERRORS INTO TABLE_NAME('LOAD DESC HERE') REJECT LIMIT 25000

 So Here's how the example final statement would look. Here Daily_load is just a description for the transaction , you can give anything you want.
Reject Limit : This would be number of acceptable records you are willing to let go in error.
You can give unlimited records in which case DML would be successful even if all records do fail to process all of them would be in your error table of course :) . If the REJECT LIMIT N is crossed and failures exceed N then an exception is thrown and none of the record is inserted.

 Similar example with the update .
  Happy Coding. :)

Sunday, January 24, 2016

Oracle simple function to create CSV.

This PL/SQL code function will quickly help to write CSV without having to code a single line.

Just change the query argument to function. One will need a writable directory as a  utl_file_dir parameter from init.ora or pfile , or a stored directory object. Make sure you have read access to operating system directory you are supplying else it does not make much sense to run a program at all.

You can create a database object as well just copy and replace function definition with create or replace function.



AWS solution architect exam questions.

Before taking this let it be known that the questions are sourced from random sites using simple algorithms. The authenticity of questions a...