QueryLight

Here are the fixes and new development done. Latest Update and Fixes Added ability to write xlsx in new office xlsx format. Added ability to view code with F4. Added additional describe capabilities with F4. Download Page :  Go to Project HomePage https://sourceforge.net/projects/query-light-light-orcl-client/

Many production oracle database instances do not have auto incrementing tablespaces. You would need to know beforehand before users start getting ORA-01688 unable to extend tablespace errors. You would probably have some sort of monitoring on the tablespaces already , OEM shows alerts about tablespaces readily as well. But its good to have a query handy to quickly check the tablespace usages you have in your system. select df.tablespace_name ,totalusedspace "totalusedspace_mb" , (df.totalspace - tu.totalusedspace) "Free_MB", df.totalspace "Total_MB", round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free" from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df, (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name ;

Oracle string quote escape tool : There will be a times when you are writing dynamic sql and that sql  has lot of quote constants in where clause or in your select list , escaping these quotes can be quite cumbersome if you don't have the right tool. If you have a large query or string which has quotes in it  and you do not want to bother checking the escapes and want to use the escaped query / string readily in your plsql. Below tool will help you generate escaped string from Oracle  plsql standpoint , just copy and paste it in your code It's not ours to take. Escape quotes

There are times when writing a code when we want to write files with Oracle utl_file package, but we do not have access to v$parameter tables or do not know if the directory object exists in Oracle or not. There are also many times cases when you are tracing error in multi hundres of lines of code and do not know if the file writing is causing error or its some other code in your plsql. One can quickly test file writing is happening or not with the help below small code. DECLARE ---------- Oracle utl file write test. L_HANDLER UTL_FILE.FILE_TYPE; BEGIN L_HANDLER := UTL_FILE.FOPEN ('/xfer/data', 'utl_test.txt', 'W'); UTL_FILE.PUTF (L_HANDLER, 'UTL_FILE Test Success'); UTL_FILE.FCLOSE (L_HANDLER); END;

This is a Beginner level article but can be referred readily by guys using Oracle Database moderately for a refernence. You can identify the duplicate records in Oracle Table by using a group by clause. You need to group by column / columns from which you are trying to identify the duplicates , use count() function to identify the duplicates : expression is self exploratory having count () > 1 . Ex 1 : Identify duplicates first names from employees table.  Note in this context : Employee records is not necessarily duplicate we are only trying to find employees with same first name. select empl_first_name , count(empl_first_name) from employees group by empl_first_name having count(empl_first_name) > 1 Ex 2 : Identify duplicates or repeats based on 2 or more columns. select empl_first_name , empl_last_name , count(empl_first_name) from employees group by empl_first_name , empl_last_name having count(1) > 1 Identify duplicate / repeats based on 2 or mo...

We always find a need to use incremental sequence for our table data columns like primary key or other unique keys. Examples of such fields could be id fields like employee id or order sequences for order processing in OLTP databases. Oracle provides very efficient non blocking way of getting and generating these new sequences with a database object called sequence. Sequence should be created and used as follows. CREATE SEQUENCE employee_id_seq START WITH 10000 -- Start your sequence with 10000 INCREMENT BY 1 -- Sequence would be incremented by 1 when nextvalue is fetched from sequence NOCYCLE; -- Oracle sequence would not restart -- If you want sequence to start over after N then specify maxvalue N clause in the sequence Full syntax of sequence statement Once we are done creating sequence object in database next thing is pretty straightforward. You can use your sequence immediately in insert statement. INSERT INTO EM...

 If we want to perform hierarchical queries we need to use connect by clause in our sql query. Hierarchical query is any query wherin you want to display parent child relationship with the case that a parent may have multiple children and those have their own creating a tree like structure. One classic scenario highlighted on almost all sites is employee table wherein employee's manager is stored in the employee row the manager id is again one of the employee in the employee table. But we are not going to take it as you can reference it almost on any site that hosts oracle based content. We are going to do a query based on DBA_DEPEDENCIES or ALL_DEPENDANCIES if you do not have access to former. In DBA_DEPENDANCY table we have hierarchy of REFERENCED_NAME(parent) and the referring objects in column NAME(child ) that have dependency on the parent. with this in mind we would need to set our connect by clause as prior NAME= REFERENCED_NAME This co...

In this tutorial video we have demonstrated how to use oracle analytical functions like lag , lead , sum and avg etc. Example 1. Use of oracle function sum to display running totals with the use of unbounded preceding. select sum(Salary) over (order by salary rows unbounded preceding) running_total , salary , e.* from employees e Identify gap in the contiguous sequences with the lag function. Ex. In Employees table we have contiguous sequence of employees but due to some anomaly we found that there is now gap in the sequences. Ex Employee Id 210 comes after 206 which is not contiguous. We can write a query as given below to identify such sequence gaps. Oracle Lag Function select * from ( select lag (e.employee_id , 1) over (order by e.employee_id) as prev_emp, e.* from employees e ) tmp where (tmp.employee_id - tmp.prev_emp) > 1

If you have 3000 lines of code and a custom exception handler like when others. The ORA 06502 can be  notoriously elusive to find. This error is caused by . Trying to assign a higher length/precision value to variable whose size / precision is smaller than the value being assigned. Or trying to assign a non numeric value to a numeric variable. Ex . This is easy to see in 5 line code but if you have 3000+ lines of code and you did not handle the exception correctly. (plenty of times we write a generic when others handler which does not give us the line number but only a petty error message which says Numeric or valuer error.)  This would be the case wherein you would need to debug or trace where the error is occurring. Here's how you can debug such failure in this case. 1. If you are on your development or test enviornment , strip off all your excption handler in your package , procedure , function whatever it is that's throwing this error. When you ru...

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 Top N Query using rank analytical functions.

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 wante...

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 ...

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 ever...

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...

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. EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('EMPLOYEES' , 'EMPL_ERROR'); -- FOR CREATING ERROR LOG TABLE : TO LOG ERRORS DURING...

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. --alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; DECLARE cnt number ; function dump_csv( p_query in varchar2, p_separator in varchar2 default ',' , p_dir in varchar2 default '/non_edi_data', p_filename in varchar2 default 'temp1.csv' , qualify_with_quotes in boolean...