Oracle Database : Quickly test if the directory you are writing to is writable and configured in utl

Oracle Database : Quickly test if the directory you are writing to is writable and configured in utl_file or Oracle directory object.

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;

Comments

Oracle Analytical Functions : Tutorial Part 2 Covers sum avg lag lead.

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

Oracle Database find duplicate records on certain column in a table.

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

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

QueryLight

Search This Blog