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.

Addiction of Social Gaming

Now a days people are just crazy about social network games. They spend whole day in virtual shops, farms, collecting virtual money to buy virtual stuff with which they achieve some goals that at last gives them, I consider, virtual pleasure.

I experienced similar thing when my brother was playing candy crush game on Facebook.  Suddenly he turned to me and said

"You get married!"

"What?.. What happened suddenly?"

"I was thinking it would be easy to get approval from 3rd person to reach next level"

*To cross some levels you need approval from 3 people in candy crush game.

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.

Writer's Block

crazyloud:  "Hey, How are you?"
"Struggling."  said lazycloud engrossed in his thoughts.  "suffering from Writer's Block."
"But you are not a writer." said crazyloud.
lazycloud: "Yes, I am."

crazyloud bewilderedly asked  "But you have never written anything."
"Because, I am experiencing Writers Block." snapped lazycloud.
crazyloud: "Like what, Since birth?"
lazycloud : "Yeah. I scribbled few words when I was a  kid, and then writer's block hit me, and  someone suggested that I try doing other things, and not think about it. So I forgot about it."
crazyloud : "And now after 30 years you realized that you are in Writers-Block phase"
lazycloud  : "Well, Now you know."

After moments pause

crazyloud : "Do you want to go out, and discover something that might inspire you."
lazyloud : "I don't need no Inspiration, I only need something to stimulate my mind."
crazyloud : "Well, that is the definition of Inspiration."
lazycloud " Oh! Okay."

crazyloud : "Hey, I have got something you might be interested in; Our company is coming with this, new social networking website, and we are looking for some really cool tag lines. Can you come up with something?"
lazycloud: "Why not. Let me think."
lazycloud:  "What about - A platform to find yourself HOT and SE...". Before lazycloud could complete crazycloud interrupts.
lazycloud "What?"
crazyloud "Its SNW, here we... blah blah.... not some fake dating or po*no website.You know."
lazycloud :"I thought that's what social networking websites are for."
lazycloud : "I didn't know they meant real social network by that. You know what, We should really talk often."

lazycloud : "No worries. I'll think of something else."
lazycloud:  "What about-  "Now flaunting has got a style. Get a real life." and then "Chance for nerds, geeks, losers to increase their social quotient, and Bitc*es to .."
crazyloud : "Wait ... Wait..  I think the first part is Okay, but  you know, we are actually targeting normal people so, nerd ,losers and ...those are  not really needed here. We need something more Creative, imaginative ... something Magical."
lazycloud: "Well, I am a writer not some....... Investment Banker or something.. I don't give a damn for these things."
"Of course, Writers don't need those qualities." said crazyloud sarcastically.
"Well, you are not incompetent enough to argue with me." said lazycloud with some pride.

Manager's Dose Of Advice

Manager's Preaching

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.

Web scrapping using Jsoup

Download latest jsoup jar file (Download Link).

Compile code with appropriate class path value, like

javac -cp "C:\jsoup-1.7.1.jar"  "TestClass.java"

java  -cp  "C:\jsoup-1.7.1.jar"  TestClass

Simple Example using Jsoup to connect to server using login credentials and then retrieving specific page.

[sourcecode language="java"]

import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.Connection;
import java.io.IOException;
import org.jsoup.Connection.Method;
import java.util.HashMap;
import java.util.Map;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.select.Elements;

public class TestClass
{
public static void main(String args[]) throws IOException
{

Document doc = Jsoup.connect("<URL>").get();

Elements viewState = doc.select("input[name=__VIEWSTATE");
Elements eventValidation = doc.select("input[name=__EVENTVALIDATION]");

Map<String,String> allFields = new HashMap<String,String>();
allFields.put("__VIEWSTATE", viewState.val());
allFields.put("__EVENTVALIDATION", eventValidation.val());
allFields.put("txtLogin", "<USERNAME>");
allFields.put("txtPassword",   "<PASSWORD>");
allFields.put("butSubmit",   "Sign In");

System.out.println(allFields);

Connection.Response res = Jsoup.connect("<URL2>")
.userAgent("Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/535.21 (KHTML, like Gecko) Chrome/19.0.1042.0 Safari/535.21")
.data(allFields)
.method(Method.POST).
execute();

String sessionId = res.cookie("<COOKIENAME>");

System.out.println(sessionId);

Document doc2  = Jsoup.connect("URL3")
.cookie("ASP.NET_SessionId", sessionId)
.timeout(0)
.get();

System.out.println(doc2.html());

}
}

[/sourcecode]

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