Bootstrap 3 CSS Animation & Transition Effects

Animation Effects

Number of trending websites are now using CSS Animation library like Animate.css  for effects like dropIn, dropDown, fadeIn.

Lets add transition animation to  following element
<div id="header-text">This is text heading.</div>

All we have to do is to include Animation.css file in our page and then add following class to element
<div id="header-text" class="animated fadeIn">This is text heading.</div>

That's it!

Adding Animation effects on Scroll of page

Animation effect triggers even if page element is not visible on screen. We don't want animation to trigger when its not visible. We want to trigger animation when user scrolls the page and element is about to be displayed.

For that we can use WOW.js library. We need to initialize WOW object in our page and then we can add wow class which will give the desired result.

Blur Block when user user scrolls down from that element

We can set opacity of the element when user scrolls down the element. Based on size of window and scollTop position we can caculate opacity of the block.

function() {
var h = window.innerHeight;
$(window).on('scroll', function() {
var st = $(this).scrollTop();
$('#element').css('opacity', (1-st/h) );
});
},

 Show Spinner until all elements(images) are loaded

$(window).load(function() {
$('#status').delay(100).fadeOut('slow');
$('#preloader').delay(500).fadeOut('slow');
}

 In html add following

<div class="sk-spinner sk-spinner-wave">
<div class="sk-rect1"></div>
<div class="sk-rect2"></div>
<div class="sk-rect3"></div>
<div class="sk-rect4"></div>
<div class="sk-rect5"></div>
</div>

Along with css  style mentioned here

Automatically Generate SQL Loader Control File Script

Script to generate SQL Loader control file with all columns from the table_name and in the same order columns are created in table


<pre class="brush: sql">

SELECT COL_NAME, LOADER_TYPE
FROM (
SELECT 'LOAD DATA
APPEND
INTO TABLE ' || '&TABLE_NAME' || ' FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY ''"''
TRAILING NULLCOLS
('
COL_NAME,
' ' LOADER_TYPE,
-999 SORTING_N
FROM DUAL
UNION ALL
SELECT COLUMN_NAME COL_NAME,
DECODE (
COLUMN_NAME,
'CREATED_BY', '"FND_GLOBAL.USER_ID"',
'CREATION_DATE', 'SYSDATE',
'LAST_UPDATED_BY', '"FND_GLOBAL.USER_ID"',
'LAST_UPDATE_DATE', 'SYSDATE',
DECODE (
DATA_TYPE,
'TIMESTAMP(6)', 'TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF",',
'NUMBER', 'DECIMAL EXTERNAL,',
'VARCHAR2', 'CHAR "TRIM(:' || COLUMN_NAME || ')",',
'CHAR', 'CHAR',
'DATE', '"TO_DATE(SUBSTR(:'
|| COLUMN_NAME
|| ',1,19),''YYYY-MM-DD HH24:MI:SS'')",'))
LOADER_TYPE,
COLUMN_ID SORTING_N
FROM ALL_TAB_COLS
WHERE OWNER = UPPER ('&SCHEMA_NAME') AND TABLE_NAME = UPPER ('&TABLE_NAME')
UNION ALL
SELECT ')' COL_NAME, '' LOADER_TYPE, 10000 SORTING_N FROM DUAL
)
ORDER BY SORTING_N

</pre>

AngularJS Example

Develop a simple app where web page link is provided as input and it will display screenshots of the web page.

• AngularJS provides two-way data binding. If data model changes, view is updated i.e, DOM of the webpage will refresh with new data and if new value is entered in field, data model will update.
• We can divide application into different components and AngularJS Dependency Injection mechanism will inject components into each other.
• Accessing DOM should be done only in Directives. Directives are AngularJS markers which adds behaviour to DOM element. Directives are added to HTML template to add dynamic behavior.

<div  ng-repeat="c in book.notes track by $index">

<img ng-src="{{c}}" width="500" height="500">

</div>

• ng-repeat is a directive which instantiates template once per item( variable c) in collection(book.notes Array)
  
• If the value of book.notes =['link1.jpg','link2.jpg'] then view will be rendered with two div elements, one for each link in book.notes
• <input ng-model="book.note" >
  ng-model directive stores/updates value of the input field. note is model field which will be updated based on input field.

HTML Template

Following is the HTML template we will use to display one input box which expects web page url and an add button which add link to image collection.

<div ng-app="notes" ng-controller="notesController as book">

<button ng-click="book.add()">Add</button>

<input min="0" ng-model="book.note" required >

<div ng-repeat="c in book.notes track by $index">

<img ng-src="{{c}}" width="500" height="500">

Controller View Model is defined in Controller file

angular.module('notes',[])

.controller('notesController',function(){

this.notes=[];

this.add=function (){

this.notes.push('http://screenshot.etf1.fr/?url='+this.note);

};

• Controller is attached to DOM using ng-controller.
• All properties and methods defined inside controller are available  in DOM where controller is attached. Properties are called view model and methods are called behaviour.
• notes is a model and add is behaviour which is available in DOM where ng-controller="notesController" is added.

When Add button is clicked the notes model is updated and view is updated accordingly  because ng-repeat is based on notes model.

Complete HTML code and controller.js code

<html lang="en">

<head>

<meta charset="UTF-8">

<title>Example</title>

<script src="http://ajax.googleapis.com/ajax/libs/angularjs/1.3.5/angular.min.js"></script>

<script src="controller.js"></script>

</head>

<body >

<div ng-app="notes" ng-controller="notesController as book">

<button ng-click="book.add()">Add</button>

<input min="0" ng-model="book.note" required >



<div class="col-xs-3" ng-repeat="c in book.notes track by $index">

<img ng-src="{{c}}" width="500" height="500"> </img>

</div>

</body>

</html>

Controller.js

angular.module('notes',[])

.controller('notesController',function(){

this.notes=[];

this.add=function (){

this.notes.push('http://screenshot.etf1.fr/?url='+this.note);

};

})

Developing Chrome Extension - How to Build First Chrome Extension

Let's develop a  simple chrome extension to modify some text on visited webpage.
Whenever Google website is visited we will modify all 'Google' text string on page to 'Foogle'.

Following files will be required to achieve this - manifest.json and inject.js

manifest.json

• Manifest file is required for all extensions.


• It defines meta data for Chrome.


• All files & permissions should be declared here.


• Resource files like images or other template files should be declared as "web_accessible_resources"


• 
  
  [sourcecode language="javascript"]
  
  {
  // required
  "manifest_version": 2
  "name": "Foogle Search",
  "version": "0.1",
  
  "content_scripts": [
  {
  "matches": ["https://www.google.com/*"],
  "js": ["inject.js"],
  "run_at": "document_end"
  }
  ],
  
  }
  
  [/sourcecode]
  
  "Foogle Search" is the name of chrome extension, and 0.1 is its version.
  content_scripts section tell chrome to insert javascript file inject.js in web pages which matches URL pattern google.com/*
  manifest_version is a mandatory field.

inject.js

• Content Scripts execute in the context of the visited web page and can access and modify the DOM of the visited page.


• Content Script do not have access to JavaScript variables or functions of visited web page. Content scripts are not aware of the JavaScript code of the visited page.


• Since they run in context of webpage they cannot directly communicate with rest of the extension. Message passing is used for communication between content script and rest of the extension.


• Content scripts can be injected every time a matching URL web page is visited  or can be inserted dynamically in the webpage based on some conditions. Cross Origin Permission should be specified in the later case.


• Following javascript code searches for all occurrences of text 'Google' in web page and replaces it with 'Foogle'.

[sourcecode language="javascript"]

document.body.innerHTML = document.body.innerHTML.replace(new RegExp("Google", "g"), "Foogle");

[/sourcecode]

Now, to test the extension in brower

1. Open extensions option in Chrome. Type chrome://extensions/ in chrome tab
2. Enable Deverloper Mode checkbox.
3. Click on 'Load Unpackged Extension'
4. Select folder where you have saved extension files.

 

Supplier Conversion in Oracle Apps 11i / r12

If you can solve a problem with single SQL query instead of writing long PL/SQL procedure, you will sound as cool as Apple fans sound when criticizing Microsoft.

I enjoy challenge of solving problems with single SQL statements. Personally, I find code written using lots of cursor, loops, IF condition and exceptions too difficult to maintain.

Here is a Supplier conversion program that I wrote recently using mostly SQL statements.
Data File Validations
Doing null validation for staging table fields using single DML statement

One boring way to do validation is by using PL/SQL code. Open staging table cursor, check for each field value and update error message field.

But same thing can be achieved using single update statement.

UPDATE xxxav_sup_stg_tab xsst

SET vendor_error =

(SELECT DECODE (

a.err_msg,

NULL, NULL,

SUBSTR (a.err_msg, 1, LENGTH (a.err_msg) - 1)

|| ' SHOULD NOT BE NULL')

error_msg

FROM (SELECT DECODE (vendor_name,

NULL, ' Vendor Name,',

NULL)

|| DECODE (vendor_type_lookup_code,

NULL, ' Vendor Type lookup,',

NULL)

|| DECODE (vendor_site_code,

NULL, ' Vendor Site Code,',

NULL)

|| DECODE (vendor_type_dff,

NULL, ' Vendor Type DFF,',

NULL)

|| DECODE (lgcy_vendor_ref2,

NULL, 'Legacy Vendor REF2,',

NULL)

err_msg,

ROWID line_no

FROM xxxav_sup_stg_tab) a

WHERE a.line_no = xsst.ROWID);

The innermost SQL subquery will check each column for null value and if value is null it will concatenate all error messages and updates error_message column.

Duplicate Record Validation on staging table

If unique id is assigned to all rows in staging table, we can join

UPDATE xxxav_sup_stg_tab out

SET site_error =

site_error

|| ' Duplicate Line for vendor and site combination'

WHERE EXISTS

(  SELECT vendor_name, vendor_site_code

FROM xxxav_sup_stg_tab inn

WHERE inn.vendor_name = out.vendor_name

AND inn.vendor_site_code = out.vendor_site_code

and inn.record_id <> out.record_id);

Staging Table Validations

Populate Derived Columns in Staging table

For almost all values that are inserted in interface tables, I like to keep one derived column in staging table like for vendor type  value coming in file, I will create one corresponding derived column o_vendor_type_lkp_code in staging table.
This ensure that we are inserting all validated column values in interface table and makes code easy to debug. This also helps to separate value derivation from error message updates.

Populate derived Column values using Single SQL statement

UPDATE xxxav_sup_stg_tab xsst

SET o_vendor_type_lookup =

DECODE (

xsst.vendor_type_lookup_code,

NULL, NULL,

(SELECT lookup_code

FROM po_lookup_codes

WHERE     xsst.vendor_type_lookup_code IS NOT NULL

AND lookup_type = 'VENDOR TYPE'

AND (displayed_field) = xsst.vendor_type_lookup_code)),

o_tax_type =

DECODE (xsst.awt_group_name,

NULL, NULL,

(SELECT income_tax_type

FROM ap_income_tax_types aitt

WHERE     1 = 1

AND xsst.awt_group_name IS NOT NULL

AND xsst.awt_group_name) = (aitt.income_tax_type)),

o_inspection_flag =

DECODE (UPPER (xsst.match_option),

NULL, 'N',

'2-WAY', 'N',

'3-WAY', 'N',

'4-WAY', 'Y',

NULL),

o_receipt_required_flag =

DECODE (UPPER (xsst.match_option),

NULL, 'Y',

'2-WAY', 'N',

'3-WAY', 'Y',

'4-WAY', 'Y',

NULL)

WHERE o_vendor_id IS NULL;

 Update All validation Errors

UPDATE xxxav_sup_stg_tab xsst

-- oracle Name

SET vendor_error =

vendor_error

|| (SELECT ' Mismatch Vendor number and Name'

FROM po_vendors pv

WHERE     pv.vendor_id = xsst.o_vendor_id

AND pv.segment1 <> xsst.oracle_vendor_number

AND xsst.o_vendor_id IS NOT NULL)

|| DECODE (

xsst.vendor_type_dff,

NULL, NULL,

DECODE (o_vendor_type,

NULL, ' Invalid Vendor Type',

NULL))

|| DECODE (

xsst.vendor_type_lookup_code,

NULL, NULL,

DECODE (o_vendor_type_lookup,

NULL, 'Invalid Vendor Type lookup code',

NULL))

|| DECODE (

xsst.awt_group_name,

NULL, NULL,

DECODE (xsst.o_tax_type,

NULL, ' Invalid AWT Group name',

NULL))

|| (SELECT ' Invalid Fedral Flag'

FROM DUAL

WHERE UPPER (xsst.federal_reportable_flag) NOT IN

('Y', 'N')

AND federal_reportable_flag IS NOT NULL)

|| (SELECT ' Invalid State Report Flag'

FROM DUAL

WHERE UPPER (xsst.state_reportable_flag) NOT IN

('Y', 'N')

AND state_reportable_flag IS NOT NULL)

|| DECODE (

UPPER (xsst.federal_reportable_flag),

'Y', DECODE (xsst.awt_group_name,

NULL, ' Missing Awt group name',

NULL),

NULL)

|| DECODE (

xsst.match_option,

NULL, NULL,

(SELECT ' Invalid Match Approval Level'

FROM DUAL

WHERE UPPER (xsst.match_option) NOT IN

('2-WAY', '3-WAY', '4-WAY')))

WHERE o_vendor_id IS NULL;

Keep Log function separate instead of hard coding fnd_file.put_line(fnd_file.LOG,v_message); or dbms_output.put_line();

PROCEDURE LOG (MESSAGE VARCHAR2)

AS

BEGIN

--      DBMS_OUTPUT.put_line (MESSAGE);

fnd_file.put_line (fnd_file.LOG, MESSAGE);

END;

Performance Tuning Oracle Reports and Programs

Here are some of the performance tuning tips and tricks that I have learned over time. Most of them will work some of the time and  some of them will work most of the time.

People often ask me - Have you tried using temporary table for Oracle reports which has performance issue or have I analysed trace file of reports. But the truth is that most of the time analysing current query in toad proves enough to find bottleneck of the program. I submit the Oracle report, and in toad window I monitor corresponding process to check for running SQL query. I watch the queries which are executing and the plan which is being used for the query. Queries which test your patience will be the queries which needs your attention. I start from these SQL statements and I try to Optimize them.

1) Do not use truncate function on database Date column instead use timestamp or explicit expression for date comparisons.

problems with date comparisons

Like  transaction_date of rcv_transactions is an indexed column, so if we use truncate on transaction_date, index will not be used.

Instead of trunc(transaction_date) = '12-JUL-2013' use transaction_date between to_date('12-JUL-2013 00:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('12-JUL-2013 23:59:59','DD-MON-YYYY HH24:MI:SS')
similarly for creation_date of rcv_shipment_headers

2) Use same data type as index column type in expressions

If indexed column is number and expression uses string, index is used
if indexed column is string and number is used in expression , index is skipped

3) Use lexical parameters to build query, this will improve performance most of the time.

4) Using Index is not always good. People would often say index will lead to performance improvement. But using index of on large table instead of performing full table scan of small table may some time prove otherwise.
Modify query to use  full table scan of small table instead of using index on big table and check run time.

If temporary table is being used in report and some table join is used with large tables, do not use large table indexes instead force full table scan of temporary table.

5) When checking for existing values, like if  purchase order exists for a vendor
Use rownum in query. Instead of

select count(*) from po_headers_all where vendor_id = <>

use select 1 from po_headers_all where vendor_id = <> and rownum <2

6) Mention all column joins explicitly in SQL query.

7) Less cost doesn't always ensures improved performance

parent_transaction_id = -1 on rcv_transaction shows very less cost but takes lot of time similarly, queries with rownum <2 will show very less cost.

8) For excel reports, if post processing time is more, then generating delimited file using PL SQL package will improve performance.
To check for how much time  a program has spent in post processing,  check  fnd_concurrent_requests.PP_START_DATE column

9) Remember if report is run with trace enabled, queries will always perform hard bind.

We had a strange issue wherein after enabling trace, the program was completing faster. We could not find out the reason as to why this is happening, but I think it was related to hard bind performed each time.
The report was using bind variable and there are some known problem of bind variable peeking.

11) Using sub query in FROM clause is expensive Instead try using sub query in select clause with where condition if you want to select only few values in main select clause.

12) Do not user formula column which are header dependent at line level. header level details should be fetched only once.

13) Distinct clause is little tricky. Because even if query is wrong sometimes, it conceals the problem and presents correct output. If you have missed some join in a query with distinct clause, then it might give correct output, but performance is highly impacted.

I worked on one performance tuning of one report recently. It was taking huge time for one particular Org. I identified the query which was taking long time. When I analyzed the query I found that instead of using org dependent view, query was using _all table with no where condition for org_id. The output of report was coming as expected but in background the query was killing performance.

 

Oracle Report Builder - Internal Error Code 600

While compiling program unit in report builder 6i, I got the below error message. The code is simple SQL statement like

select 1 into var from apps.wip_discrete_jobs;

ORA-00600 : internal error code, arguments [17069], [97056504], [] ,[] ..

But after changing the schema name from apps to wip the code compiled successfully.

BI / XML Publisher - Fixed number of records per page

Requirement
* Display fixed number of records per page.
* On each page, display header
* Display footer on last page
* In case if footer is spanning across pages, move one record(last record) to next page and display footer at bottom of the page.

Fig1. Pages others than last one

Fig2. Display Footer on last page

Fig 3. Special condition where footer spans across pages

Fig 4. To handle footer spanning across pages, move last record to next page and then display footer


Solution
Download complete template code
(open it and save it as rtf)
Download sample data
XML Data is of form
<ROOT>
 <A>
  <B1>lineB11</B1>
  <B2>lineB12</B2>
  <B3>lineB13</B3>
  <B4>lineB14</B4>
 </A>
..
..
</ROOT>

Lets quickly freshen up our XML Publisher Reports knowledge

How to declare variables?
XSL variable declaration
<? variable : a ; number(1) ?>
XML Publisher variable declaration
<? xdoxslt:set_variable($_XDOCTX,'i',1) ?>

How to use conditional logic in template?
 <?if@inlines: [condition] ?>  [value]  <? end if ?>
Example
<?if@inlines: $a = 1 ?>   Text    <?end if?>
@Inline is used to print text on the same line, else its displayed on next line

How to use Looping constructs?
Looping for particular XML tag
for-each:current_group()

Looping n number of times<? for-each@inlines : xdoxslt:foreach_number ( $_XDOCTX,1,3,1) ?>This is an Example

<?end for-each?>

"This is and Example" text will be printed 3 times in same line(notice @inline).

Parameters for foreach_number function are same like C programming for loop - initial value, end value and increment value

How to use Page Breaks?Below tags work only in Word form field. Other declarations can be entered like normal text, but this tags should be put in Word Form field tag.

<xsl:attribute name="break-after">page
<xsl:attribute name="break-before">page

Use this inside inline IF condition to give appropriate page breaks.


Now, Lets solve our problem.

1. Decide Number of lines per page : There is no formula that works for all scenarios. Number of lines that need to be displayed per page has to be decided based on
header size,
font size and
other factors.

Create a simple for-each loop in table to find out how many rows a page can accommodate and assign that value to the variable nlpp.

Lets say after the header which I have decided(refer Fig1) a page can accommodate 46 records. Declare number of lines per page in a a variable nlpp

<?variable:nlpp;number(46)?>

2. Calculate total number of lines : Total number of lines coming in data can be calculated by using count() function on particular node. Assign that value to variable TOTLINES

Consider that records are coming inside A node(refer to sample data above), like for 4 records there will be 4 A nodes present in data.

<?xdoxslt:set_variable ($_XDOCTX,'TOTLINES',count(//A))?>

this will count number of A tags coming in data, and assign that value to TOTLINES variable.

3. Page Break after every nth row : After every 46th record we want to give a page break. We decided number of records that we want per page and based on that we have to insert page breaks.

Using for-each:tag, loop through all the records

<?for-each:A?> <?B1?> <end for-each>

To keep track of count of records, we will increment variable i value using following loop

<?for-each:A?>
<?xdoxslt:set_variable($_XDOCTX,'i',xdoxslt:get_variable($_XDOCTX,'i')+1)?>
<?B1?>
<?end for-each?>

It gets the current value of i using get variable, adds 1 to that and sets new value of i.

After each nlppv(same as variable nlpp with value 46) number of records we want page break.

Check value of counter variable. When 47the record is reached give page break using xsl break-before and reset the counter variable value to 1.

<?if@inlines : xdoxslt:get_variable($_XDOCTX,'i') = xdoxslt:get_variable($_XDOCTX,'nlppv') + 1 ?>
<?xsl:attribute name="break-before"> page </xsl:attribute>
<?xdoxslt:set_variable($_XDOCTX,'i',1)?>
<?end if ?>

Now we have code in place to display each record column value and code to make sure that after every nth page a page break is inserted.

Just add Footer after the loop condition and when all records are processed, a footer will be displayed after it. But we need to display footer section at bottom of the page, so we need to add spaces based on some logic.

Decide Number of Blank Lines to add to display footer at bottom of the page.
The same way we decided value of variable nlpp in step 1, we need to run some test and check how many records a page can accommodate along with footer section().

<!--?xdoxslt:set_variable($_XDOCTX,'ilast',70)?>

The last IF EF condition is outside of main loop to display records.

If number of records on the page(value of variable i after loop is complete) is less that the total number of records that the last page can accommodate along with footer section(ilast value), we need to insert blank lines.

Following code will display blank lines to make sure footer appears at bottom of the page.
<?if:xdoxslt:get_variable($_XDOCTX,’i’)
<  (xdoxslt:get_variable($_XDOCTX,’ilast’) - 1)?>
<?for-each:xdoxslt:foreach_number($_XDOCTX,1,xdoxslt:get_variable($_XDOCTX,'ilast') - xdoxslt:get_variable($_XDOCTX,'i'),1)?>


4. Special Scenario that need to be handled
Footer coming across pages:  This scenario is for last page alone. We need to decide the number of records that can come in single page along with footer, without footer breaking across page.

Complete Code Explanation

Variable Declarations
<?variable:nlpp;number(46)?> -- number of lines that should be displayed per page before page break

<!--?xdoxslt:set_variable($_XDOCTX, 'page', 1)?> -- keeps track of page numbers

<!--?xdoxslt:set_variable($_XDOCTX, 'i', 0)?>        --- variable used for looping through all records

<!--?xdoxslt:set_variable ($_XDOCTX,'nlast', (xdoxslt:get_variable($_XDOCTX,'TOTLINES') mod $nlpp)) ?>         --calculate number of lines for last page

<!--?xdoxslt:set_variable($_XDOCTX, 'nlppv', $nlpp)?>     --xsl variable nlpp declared above just for convenience of using short variable name

<!--?xdoxslt:set_variable ($_XDOCTX,'tpage', ceiling (xdoxslt:get_variable($_XDOCTX,'TOTLINES') div $nlpp)) ?>    --calculate total number of pages based on number of records per page calculation

<!--?xdoxslt:set_variable ($_XDOCTX,'skip',0)?>
skip is used to handle the special scenario mentioned above. To prevent footer from coming across pages, move footer on next page along with one last record.

<!--?xdoxslt:set_variable($_XDOCTX,'ilast',70)?>
-- ilast is used to decide on number of spaces that should be displayed to display footer at bottom of page. ilast is number of records that can be displayed on a page with footer coming on that page.

<!--?if@inlines:xdoxslt:get_variable($_XDOCTX,'nlast') = 0?>
<!--?xdoxslt:set_variable($_XDOCTX,'nlast',$nlpp)?>
<?end if?>

If number of records for last page is more than what last page can accommodate along with footer then we need to page break on last record.
<!--?if@inlines:xdoxslt:get_variable($_XDOCTX,'nlast') >= xdoxslt:get_variable($_XDOCTX,'ilast')?>
<!--?xdoxslt:set_variable($_XDOCTX,'skip',1)?
<?end if?>

Looping through records
<!--?xdoxslt:set_variable($_XDOCTX,'i',xdoxslt:get_variable($_XDOCTX,'i')+1)?>

for special condition give page break just after second last record
<!--?if@inlines:xdoxslt:get_variable($_XDOCTX,'tpage') = xdoxslt:get_variable($_XDOCTX,'page')
and  xdoxslt:get_variable($_XDOCTX,'skip') =1
and  xdoxslt:get_variable($_XDOCTX,'i') = xdoxslt:get_variable($_XDOCTX,'skip_at')?>

<xsl:attribute name="break-after">page        

<?end if?>

--give page break after fixed number of lines per page
<!--?if@inlines:xdoxslt:get_variable($_XDOCTX,'i') = xdoxslt:get_variable($_XDOCTX,'nlppv') +1 ?>
<xsl:attribute name="break-before">page
<!--?xdoxslt:set_variable($_XDOCTX,'i',1)?>riable($_XDOCTX,'page')+1)?>
<?end if?>

--spaces to display footer at bottom of page
<!--?if:xdoxslt:get_variable($_XDOCTX,’i’)< (xdoxslt:get_variable($_XDOCTX,’ilast’) - 1)?>
<!--?for-each:xdoxslt:foreach_number($_XDOCTX,1,xdoxslt:get_variable($_XDOCTX,'ilast') -xdoxslt:get_variable($_XDOCTX,'CR'),1)?>
<?end for-each?>
<?end if?>

Signal 11 Error in Oracle Report

Unhandled Exceptions in Oracle report results in generic signal 11 error, most of the time.

Identifying the cause
Run the report in Oracle report builder with correct input parameter values.
Navigation : File->Generate to File->XML

If report errors with signal 11 in oracle apps,  then running the report in report builder will throw exception with meaningful error message.

Reason

• There is no provision for handling exceptions in main query block in Oracle report,  so if main block query has some issue,  it will result in signal 11 error or some warning, without showing meaningful error message. Mostly,  the reason main query block fails is because of :
  exact fetch returns more than one requested number of rows :  select sub-query in main query block returning multiple rows or user defined function throwing exception.
  numeric or value error : to_number function on alphanumeric column or joining two different data type columns.

• Unhandled exceptions in report triggers
  validation trigger or after parameter form trigger or any other trigger having incorrect select queries or variable assignments which are not handled in exception block.

Implementing Supplier Bank Account Approval in Oracle 11i

Overview

Employees who have access to Bank Account form can deliberately assign suppliers to a bank account for one’s advantage. Implementing Supplier Bank Account Approval process can eliminate fraud.

Business Need

• Supplier Bank Account setup should be temporarily end dated after new supplier assignment or change in existing supplier assignment. No further changes should be allowed for submitted supplier assignment until approval (refer Figure 1 and 2).
• Approver should be notified by email for changes in supplier assignment (Figure 3).
• Approver can either approve or reject
  
  • After Approve, supplier assignment should become active.
  • After Reject, it should be on hold, but submitter should be allowed further changes.

HTML output in Oracle PL SQL

Requirement : Generating HTML output using Oracle PL SQL

Solution :
Thomas Kyte shared below piece of code on his blog for generating html output from refcursor.

[sourcecode language="sql"]

CREATE OR REPLACE FUNCTION apps.fncrefcursor2html (rf sys_refcursor)
RETURN CLOB
IS
lretval       CLOB;
lhtmloutput   XMLTYPE;
lxsl          CLOB;
lxmldata      XMLTYPE;
lcontext      DBMS_XMLGEN.ctxhandle;
BEGIN
-- get a handle on the ref cursor --
lcontext := DBMS_XMLGEN.newcontext (rf);
-- setNullHandling to 1 (or 2) to allow null columns to be displayed --
DBMS_XMLGEN.setnullhandling (lcontext, 1);
-- create XML from ref cursor --
lxmldata := DBMS_XMLGEN.getxmltype (lcontext, DBMS_XMLGEN.NONE);

IF lxmldata IS NOT NULL
THEN
-- this is a generic XSL for Oracle's default XML row and rowset tags --
-- " " is a non-breaking space --
lxsl := lxsl || q'[<?xml version="1.0" encoding="ISO-8859-1"?>]';
lxsl :=
lxsl
|| q'[<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">]';
lxsl := lxsl || q'[ <xsl:output method="html"/>]';
lxsl := lxsl || q'[ <xsl:template match="/">]';
lxsl := lxsl || q'[   <table >]';
lxsl := lxsl || q'[     <tr >]';
lxsl := lxsl || q'[      <xsl:for-each select="/ROWSET/ROW[1]/*">]';
lxsl := lxsl || q'[       <th><xsl:value-of select="name()"/></th>]';
lxsl := lxsl || q'[      </xsl:for-each>]';
lxsl := lxsl || q'[     </tr>]';
lxsl := lxsl || q'[     <xsl:for-each select="/ROWSET/*">]';
lxsl := lxsl || q'[      <tr>]';
lxsl := lxsl || q'[       <xsl:for-each select="./*">]';
lxsl := lxsl || q'[        <td><xsl:value-of select="text()"/> </td>]';
lxsl := lxsl || q'[       </xsl:for-each>]';
lxsl := lxsl || q'[      </tr>]';
lxsl := lxsl || q'[     </xsl:for-each>]';
lxsl := lxsl || q'[   </table>]';
lxsl := lxsl || q'[ </xsl:template>]';
lxsl := lxsl || q'[</xsl:stylesheet>]';
-- XSL transformation to convert XML to HTML --
lhtmloutput := lxmldata.transform (XMLTYPE (lxsl));
-- convert XMLType to Clob --
lretval := lhtmloutput.getclobval ();
RETURN lretval;
ELSE
RETURN NULL;
END IF;
END fncrefcursor2html;
[/sourcecode]

Simple package using above function to write table output to Oracle Apps output file.

[sourcecode language="sql"]
CREATE OR REPLACE PACKAGE  apps.write_html_pkg
AS
PROCEDURE write_clob (l_clob_data CLOB);
PROCEDURE write_html_prc (o_err_msg OUT VARCHAR2, o_err_code OUT NUMBER);

END;

CREATE OR REPLACE PACKAGE BODY apps.write_html_pkg
AS
PROCEDURE write_clob (l_clob_data CLOB)
AS
l_char_data      VARCHAR2 (32767);
v_clob_len       NUMBER;
v_chunk_length   NUMBER;
v_iterations     NUMBER;
v_clob_temp      CLOB;
BEGIN
IF l_clob_data IS NOT NULL
THEN
v_chunk_length := 32767;
v_clob_len := DBMS_LOB.getlength (l_clob_data);
v_iterations := CEIL (v_clob_len / v_chunk_length);

-- v_chunk_length is 32767 and v_clob_length is length of the XML data stored inthe clob variable
FOR i IN 0 .. v_iterations
LOOP
v_clob_temp :=
DBMS_LOB.SUBSTR (l_clob_data,
v_chunk_length,
(i * v_chunk_length) + 1
);
fnd_file.put_line (fnd_file.output, v_clob_temp);
END LOOP;
ELSE
NULL;
END IF;
END;

PROCEDURE OUT (p_text VARCHAR2)
AS
BEGIN
fnd_file.put_line (fnd_file.output, p_text);
END;

PROCEDURE write_html_prc (o_err_msg OUT VARCHAR2, o_err_code OUT NUMBER)
IS
l_cursor      sys_refcursor;
l_clob_data   CLOB;
l_style       VARCHAR2 (32767);
BEGIN</pre>
l_style :=
         q'[<style type="text/css">
         table
{
border-collapse:collapse;
}

table td, table th {
    border:1px solid grey;
    border:1px solid #98bf21;
    padding:3px 7px 2px 7px;
}

table th
{

text-align:left;
padding-top:5px;
padding-bottom:4px;
background-color:#A7C942;
color:#fff;
}

  </style>]';
      OUT (q'[ <html>]');
      OUT (q'[ <head>]');
      OUT (l_style);
      OUT (q'[ </head>]');
      OUT (q'[  <body>]');
<pre>

--------------------------------write table-----------------------------
OPEN l_cursor FOR
SELECT *
FROM scott.emp;

l_clob_data := fncrefcursor2html (l_cursor);
write_clob (l_clob_data);
--------------------------------END :write table-----------------------------
OUT (q'[  </body>]');
OUT (q'[ </html>]');
END;
END write_html_pkg;
/
[/sourcecode]

Output Screenshot

Sudoku Solver (Easy Level) in Oracle PL SQL

Data structures used

Sudoku grid consist of this 9x9 cells. Each cell in grid has actual value(decided value) and a list of possible values.

Cell is represented by PL/SQL Object, consisting of a

• Number type which holds actual value, and


• Nested table which holds possible values for cell.

[sourcecode language="sql"]
--Cell
CREATE TYPE cell AS OBJECT (
actual_value number,
poss_values possible_values
)

--create nested table of possible values
CREATE TYPE possible_values AS TABLE OF number;
[/sourcecode]
[sourcecode language="sql"]
--Sudoku grid of 9 columns
CREATE TABLE xx_test (C1 cell,C2 cell,C3 cell,C4 cell,C5 cell,C6 cell,C7 cell,C8 cell,C9 cell,id number)
NESTED TABLE C1.poss_values STORE AS possible_val_tab1
NESTED TABLE C2.poss_values STORE AS possible_val_tab2
NESTED TABLE C3.poss_values STORE AS possible_val_tab3
NESTED TABLE C4.poss_values STORE AS possible_val_tab4
NESTED TABLE C5.poss_values STORE AS possible_val_tab5
NESTED TABLE C6.poss_values STORE AS possible_val_tab6
NESTED TABLE C7.poss_values STORE AS possible_val_tab7
NESTED TABLE C8.poss_values STORE AS possible_val_tab8
NESTED TABLE C9.poss_values STORE AS possible_val_tab9

--set row number for
update xx_test set id = rownum

[/sourcecode]

A block is defined as group of 9 cells like
Block B1 consists of cells
11 12 13
21 22 23
31 32 33

Update Rule for Possible values of cell

Possible values for a cell is calculated by removing actual value that is present in other cells in that particular row, column and block from list of possible values of the cell.

If a value is present in a row/column/block then it should not appear in any other cell in the same row/column/block.

For example, for cell 22, possible value is calculated by removing actual values of all cells in row R2, column C2 and Block B1 from Varray 1..9(all possible values)

Update Rule for Actual Value of a cell
1.Cell containing only one possible value.
2.Value possible only in one cell in a row, column and block

Pseudo Code

For all cells
Check If actual value is null then
check list of possible value for cell. If only one value possible, then update cell with this value.
For all values 1..9 check if the value is not possible in other cells of that particular row column or block then update cell with that value.

Create .CSV file in Oralce PL SQL

Requirement: To generate delimited output of SQL query using Oracle PL SQL

Solution :

Please find below complete package code.

This package can be used to write delimited output to DBMS output, Oracle Apps Log/Output or File Directory.

Usage

Call the procedure xxtk_generate_csv with mandatory parameter values.

PROCEDURE xxtk_generate_csv (

p_query VARCHAR2,

p_delimiter varchar2,

p_file_handle number,

p_dir varchar2 DEFAULT NULL,

p_filename varchar2 DEFAULT NULL,

p_cur_qry VARCHAR2 DEFAULT 'Q'

) ;

First parameter is SQL query as Varchar2 data type, second is delimiter(tab or comma or other) and third is file handle i.e where to write output.

Example 1

Consider below query

select a "col1", b from (select 1 a, level b from dual connect by level < 4)

Comma separated Output of this query is required

[sourcecode language="sql"]
DECLARE
--q operator is available in 11i to escape quotes withing string
l_qry varchar2(1000):=q'[select a "col1", b from (select 1 a, level b from dual connect by level < 4)]';
BEGIN
--write result of l_qry as comma separated values to dbms output
xxtk_generate_csv_pkg.xxtk_generate_csv
(l_qry,',',xxtk_generate_csv_pkg.write_to_db_out);
END;
[/sourcecode]

Q operator is used to escape quote pairs appearing in data. Otherwise, we need to escape each quote separately.

DBMS Output

"col1","B"

1,1

1,2

1,3

Example 2

4 and 5 parameter are used to specify directory location and file name where to write output.

6 parameter p_cur_qry is used when passing cursor handle.

When input parameters are required for query either we can

• pass query text(first parameter)  with concatenated variable values (like where select ....where dept_no='||p_dept_no||'..' or


• pass parsed query with bind variable and send the parsed query handle (following example)

Second option is better because using bind variable is always preferable instead of concatenating variables in SQL query.

In case when SQL query is already parsed  with bind variable values.

[sourcecode language="sql"]

DECLARE
cursor_name   INTEGER         DEFAULT DBMS_SQL.open_cursor;
l_qry varchar2(1000):=q'[select *  from scott.emp where deptno =:p_dept_no and hiredate > :p_hire_date]';
BEGIN
--get parsed query handle in cursor_name
DBMS_SQL.parse (cursor_name, l_qry, DBMS_SQL.native);
--set value of dept_no to 20
DBMS_SQL.bind_variable (cursor_name, ':p_dept_no',20 );
--set hire date
DBMS_SQL.bind_variable (cursor_name, ':p_hire_date','01-JAN-1981' );

xxtk_generate_csv_pkg.xxtk_generate_csv(p_query=>cursor_name,p_delimiter=>',',

p_file_handle=>xxtk_generate_csv_pkg.write_to_db_out,p_cur_qry=>'C' );
END;
[/sourcecode]

Output

[sourcecode]

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7788,SCOTT,ANALYST,7566,09-DEC-82,3000,,20
7876,ADAMS,CLERK,7788,12-JAN-83,1100,,20
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20
[/sourcecode]

2 GB File limit on writing file using Oracle SQLPlus in Unix

Requirement

Dump of huge data is required from oracle database. Say, dump of GL data (specific query output and not complete table dump) is required for particular month.

I simply spool the output of query and what I observe is - it always produces 2GB output file, for few months I tested, without giving any error message or warning.

So, I google for "2GB Unix oracle SQLPlus" and I find number of links mentioning this problem

unix 2GB oracle export

One of the solution involves using named pipe.

A named pipe doesn't write data to disk, but instead write to buffer in memory. Writer writes at one end and Reader reads from other end.

Named Pipe

• named pipe is actually file in file-system


• Like unnamed pipe it is  used for Inter process Communication


• Unlike unnamed pipe it is system-persistent, that is, it exists beyond process life and has to be created and deleted explicitly.


• Process that reads or writes to pipe blocks until the other end of pipes performs read or write operation on same

Create a named pipe, redirect data written to it to sed command.

Removes extra tabs coming in spool file(spool usually gives extra tabs when colsep is used), and then pipe output to compress command which writes compressed data to disk.

Sqlplus will write data at one end to named pipe and following command will write compressed data to disk.

cat /tmp/aug11_pipe | compress  > gl_aug11_pipe.csv

Oracle Correlated Query Scope. Invalid Identifier Error

Consider query

[sourcecode language="sql"]
SELECT (SELECT tab.col  FROM DUAL) a
FROM (SELECT 1 col  FROM DUAL) tab;
[/sourcecode]

Output is

A
---
1

Here I am using tab table value inside subquery that is one level down and it's recognized by system.

Now, If I take it one more level down, that is, using tab value inside subquery's subquery, something like

[sourcecode language="sql"]
SELECT (SELECT * FROM (SELECT tab.col  FROM DUAL)) A
FROM (SELECT 1 col  FROM DUAL) tab
[/sourcecode]

It throws "invalid identifier TAB.COL" error

At Second level its not recognized by system.

Oracle NVL function always evaluates second parameter

NVL function doesn't do short circuit evaluation  i.e, all parameters are evaluated irrespective of condition but, value is returned only when condition returns true.

Lets test this

Consider below query to generate test data

[sourcecode language="sql"]
SELECT '' col
FROM DUAL
UNION ALL
SELECT     DBMS_RANDOM.STRING ('L', 5) col
FROM DUAL
CONNECT BY LEVEL < 5
[/sourcecode]

it returns 5 rows of which one row is always null

Now, Lets write one simple function which returns some value. We will call this function in case column value is null. Like

[sourcecode language="sql"]
SELECT NVL (tab.col, temp_func (tab.col))
FROM (SELECT '' col
FROM DUAL
UNION ALL
SELECT     DBMS_RANDOM.STRING ('L', 5) col
FROM DUAL
CONNECT BY LEVEL < 5) tab;
[/sourcecode]

The above statement should ideally call function temp_func(defined below) only for one row.

--takes input value and prints it in output
--always returns '***'

[sourcecode language="sql"]
CREATE OR REPLACE FUNCTION temp_func (p_text VARCHAR2)
RETURN VARCHAR2
AS
i   VARCHAR2(30) := 'who ';
BEGIN
i := i || p_text;
DBMS_OUTPUT.put_line (i);
RETURN '***';
END;
[/sourcecode]

But above SELECT statement returns five lines of dbms output, one for each row. That means function temp_func got called for each row irrespective of condition.

Unlike NVL, COALESCE function uses short circuit evaluation and so, prints only one line of dbms output

[sourcecode language="sql"]
SELECT coalesce(tab.col, temp_func (tab.col))
FROM (SELECT '' col
FROM DUAL
UNION ALL
SELECT     DBMS_RANDOM.STRING ('L', 5) col
FROM DUAL
CONNECT BY LEVEL < 5) tab;
[/sourcecode]

Oracle Table NULL column validation. Update all error message at once

Requirement:

Consider table stg_table with columns c1 c2 c3 and c4(no index or not null constraints) and values as mentioned below

id    c1    c2    c3    c4
-----------------------------------
#1    1    2              5
#2    3            3
#3                         6

We wish to do NULL validation on all columns and update the error in error_msg column .For some reason NOT NULL constraint is not put on these columns.

Required output

id    c1    c2    c3    c4    error_msg
----------------------------------------------------
#1    1    2               5    C3 NULL
#2    3             3            C2, C4 NULL
#3                           6    C1, C2, C3 NULL

Solution

One simple method is to write as many number of sql UPDATE statement as number of columns that needs to be validated

UPDATE stg_table set error_message = error_msg||'C1, ' where c1 is null
UPDATE stg_table set error_message = error_msg||'C2, ' where c2 is null
.
.
and finally if error message was updated by any of above row then append ' NULL' to error_msg
UPDATE stg_table set error_message = decode(error_message,null,null,error_msg||' NULL')

Other method to perform same thing with single update statement is

[sourcecode language="sql"]
SELECT   id, DECODE (c1, NULL, 'C1,', NULL )
|| DECODE (c2, NULL, 'C2,', NULL)
|| DECODE (C3, NULL, 'C3,',NULL)
|| DECODE (c3, NULL, 'C3,', NULL) err_msg
FROM stg_table
[/sourcecode]

Above statement gives output like

id    err_msg
----------------------
1    C3,
2    C2,C4,
3    C1,C2,C3,

Now we need to remove the extra comma from end and append 'NULL' string

[sourcecode language="sql"]
SELECT id, DECODE (t2.err_msg,
NULL, NULL,
SUBSTR (t2.err_msg, 1, LENGTH (t2.err_msg) - 1)
|| ' NULL'
) error_msg
FROM (SELECT    DECODE (emp_no, NULL, 'EMP NO,', NULL )
|| DECODE (invoice_no, NULL, 'INVOICE NO,', NULL)
|| DECODE (invoice_date, NULL, 'INVOICE DATE,',NULL)
|| DECODE (vendor_no, NULL, 'VENDOR NO,', NULL) err_msg,
ID
FROM xx_invoices_stg) t2
[/sourcecode]

id    err_msg
----------------------
1    C3 NULL
2    C2,C4 NULL
3    C1,C2,C3 NULL

Now simply update stg table correlating above query id with table id

[sourcecode language="sql"]
UPDATE stg_table t1
SET        error_msg =
(SELECT DECODE (t2.err_msg,
NULL, NULL,
SUBSTR (t2.err_msg, 1, LENGTH (t2.err_msg) - 1)
|| ' should Not be NULL'
) error_msg
FROM (SELECT    DECODE (emp_no, NULL, 'EMP NO,', NULL )
|| DECODE (invoice_no, NULL, 'INVOICE NO,', NULL)
|| DECODE (invoice_date, NULL, 'INVOICE DATE,',NULL)
|| DECODE (vendor_no, NULL, 'VENDOR NO,', NULL) err_msg,
ID
FROM xx_invoices_stg) t2
WHERE t2.ID = t1.ID)
[/sourcecode]

DECODE inside IN clause Oracle SQL

Requirement : Consider Emplyoee table emp which has Emplyee number and Deparment number columns.

Table : emp

empno  deptno
---------------------
1       10
2       20
3       20
4       10
5       30

Based on some input parameter var that isnot part of any table we wish to select employees of specific Departments.

If value of var = 1 then
select dept 10 employees
empno 1 and 4

If value of var = 2 then
select dept 20 and 30 employees.
empno 2, 3 and 5

var variable has no significance with respect to Employee data

Solution :

[sourcecode language="sql"]
SELECT empno,deptno
FROM emp
WHERE deptno IN (DECODE(:var,1,10),decode(:var,2,20),decode(:var,2,30))
[/sourcecode]

when var value is 1 then
both second and third decode returns null, and evaluates to false for all rows, and first decode returns 10. The where clause translates into something like
where deptno = 10 or deptno = null or deptno = null
So, it returns deptno 10 employees only.

Similarly, for var value of 2 the where clause translates into
where deptno = null or deptno = 20 or deptno = 30
and returns deptno 20 and 30 employees.

Oracles demo mail Package for sending mail

Oracles Demo mail package was available on OTN site previously, but this time when I searched I got 404 error. So, I thought of keeping a copy for future references.

Its a wrapper program to send mail using pl/sql. It hides the low level SMTP protocol details.

gist link of package

String Aggregation in Oracle SQL using XMLAGG

Requirement

We have table test5 with following data
c1
-----
ADD
DELETE
MODIFY

Required output
col
------
ADD,DELETE.MODIFY

i.e we want column value to be aggregated with comma.

Solution
In real scenario we will have another column on which we need to group and then aggregate corresponding values.

In oracle 11g there is one function wm_concat which concatenates column values with comma. We can write a procedure to perform this thing but here we will  try  to do it with sql query alone.

This solution uses XMLELEMENT, XML_AGGR functions

[sourcecode language="sql"]
SELECT replace (replace (replace (XMLELEMENT ("col" , XMLAGG(XMLELEMENT("col", c1))), '</col><col>' , ',' ),'<col><col>' , '' ),'</col></col>', '')
as "col"  FROM test5
[/sourcecode]

Output
col
------
ADD,DELETE,MODIFY