Wednesday 25 May 2011

How to get rid of “Query has exceeded XXX rows” error message in OAF page

 

If you have inside your framework page ViewObject that brings o lot of data you probably will get at some point “Query has exceeded XXX rows” message”

In my case it was a dropdown list that displayed a list of EBS users

image

After digging into View Object definition for about an hour and finding nothing I finally found that limit value controlled by EBS profile called “FND: View Object Max Fetch Size”. I put the value of 6000 and the message disappeared

image

Tuesday 24 May 2011

XML Publisher report with barcode

 

   Basically this post refers to any font that you want to embed into you output but I was working specifically on demand to insert the barcode.

  So start with something simple. Obtain the font file. In my case I posses “WASP 39 ELC” font. Font file name is w39elc.ttf. Insall the font into Windows by double clicking on it and choosing “install”

Open your RTF template and place the barcode in the location you want

image

 

So far so good and if you run this template locally on your workstation you will see the barcode. But not in EBS.

  This is what you need to do in order to make it work in EBS

Open “XML Publisher Administrator” responsibility

Go to “Administration”-> “Font Files”

image

 

Click on “Create Font File”

image

In “Font Name” put the name of the font as it appears in Word. Choose the font file and click “Apply”

image

Go to “Administrator”-> “Font Mapping”

image

Click on “Create Font Mapping Set”

image

Put into “Mapping Name” – Bar Code Fonts, into “Mapping Code” – BAR_CODE_FONTS, into “Type” –FO To PDF and click “Apply”

image

Click on the “Bar Code Fonts” link

image

Click on “Create Font Mapping”

image

Define Font Mapping as appears in the print screen bellow and click “Continue”

image

Ok, now you need  template to “know” the barcode font we just created.

Open template definition and go to “Edit Configuration”

image

Expand “FO Processing” and put into “Font Mapping Set” – Bar Code Fonts and click “Apply”.

image

Now you can run your template and it will display the barcode properly

 

Tuesday 17 May 2011

Adding Client Side code to Oracle Framework Page

I had to add very simple logic into existing Framework page. One this page 2 checkboxes exists. Lets call one of them “first” and second one will be “second”. Desired result – whenever user clicking on “first” it should disable “second” checkbox. Well first of all I tried to do it on the server side. To do it , I had to submit the page when user clicking on “first” checkbox. This is not a problem. Just choose proper options in the GUI element as you can see in the picture bellow

image.axd

So, user clicking on the checkbox and submitting the page to the server. It is important to give some name in the event field.

Second step is to catch this event inside the controller. User can catch this event in processFormRequest method

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
   super.processFormRequest(pageContext, webBean);    
   if ("submitByfirst".equals(pageContext.getParameter(EVENT_PARAM)))
   {
     //do your logic here
   }
}

Well, I thought that I am one step of achieving the goal. The last think that I should do is to get a reference to “second” checkbox and disable it in the following way



OAMessageCheckBoxBean oaCheckBoxBean = (OAMessageCheckBoxBean) webBean.findIndexedChildRecursive("second");
oaCheckBoxBean.setDisabled(true);

And so I did. And it didn’t work at all!!!  Instead I got an error message saying "blah.. blah.. blah.. You can not do it in processFormRequest method. Nice. The only other place I could do this logic is inside processRequest method. But I can catch my event only in processFormRequest methos. Why Oracle did it in this way?? Who knows.


  Any way if I wanted to proceed this this approach I had to resubmit the page by using pageContext.forwardImmediately() to the same page and then I could proceed my code in processRequest method. I am thinking that it is too much. So I decided to write client code and attach it to the “first” checkbox.


First of all it need to written in the “processRequest” method. Only several lines of code



public void processRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processRequest(pageContext, webBean);
  
      String onClick = "if (this.checked) { document.getElementById('second').disabled =true;}";
  
      OAMessageCheckBoxBean chkEnableOmpact = (OAMessageCheckBoxBean) webBean.findIndexedChildRecursive("first");
      chkEnableOmpact.setOnClick(onClick);
   }
And this is all!!!  Just write your script and attach it to the relevant event.

Building and configuring PHP upload file page

Hi. Recently I had to build some quick way to upload files to the server.  I found a lot of examples on the WEB and decided to choose PHP.

Here are the files I used for my application

upload.php

<table width="500" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<form action="upload_ac.php" method="post" enctype="multipart/form-data" name="form1" id="form1">
<td>
<table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td><strong>Single File Upload </strong></td>
</tr>
<tr>
<td>Select file
<input name="ufile" type="file" id="ufile" size="50" /></td>
</tr>
<tr>
<td align="center"><input type="submit" name="Submit" value="Upload" /></td>
</tr>
</table>
</td>
</form>
</tr>
</table>

upload_ac.php


<?php
//set where you want to store files
//in this example we keep file in folder upload
//$HTTP_POST_FILES['ufile']['name']; = upload file name
//for example upload file name cartoon.gif . $path will be upload/cartoon.gif

$path= "upload/".$HTTP_POST_FILES['ufile']['name'];
if($ufile !=none)
{
if(copy($HTTP_POST_FILES['ufile']['tmp_name'], $path))
{
echo "Successful<BR/>";
//$HTTP_POST_FILES['ufile']['name'] = file name
//$HTTP_POST_FILES['ufile']['size'] = file size
//$HTTP_POST_FILES['ufile']['type'] = type of file

echo "File Name :".$HTTP_POST_FILES['ufile']['name']."<BR/>";
echo "File Size :".$HTTP_POST_FILES['ufile']['size']."<BR/>";
echo "File Type :".$HTTP_POST_FILES['ufile']['type']."<BR/>";
echo "<img src=\"$path\" width=\"150\" height=\"150\">";
}
else
{
echo "Error";
}
}
?>

Everything is pretty straightforward, but in all examples I found no one told, that by default I cannot upload more then 2M.


So here is the solution. Detect php.ini file on your server. In my case it was located under /etc/php.ini.


Make sure you have the following line: file_uploads = On


To set/change upload  limit modify upload_max_filesize line. In my case, I put there upload_max_filesize = 100M

Sunday 15 May 2011

Creating a backup for Oracle database

I decided to write a simple script to backup the database while it is still running

This mean that I need to backup:

  • control files
  • datafiles
  • logfiles
  • startup files (spfile)
Note!! Before you execute this script make sure that your database is in Archive mode

First of all lets create a directories for the files

echo "Created By Michael Shapira"
rm files.txt
1> /dev/null 2> /dev/null
mkdir datafiles
mkdir contfiles
mkdir logfiles
mkdir startupfiles

”files.txt” will hold the commands to be executed 

echo "cp $ORACLE_HOME/dbs/spfileorcl2.ora startupfiles" >> files.txt

copy spfile to startup directory


set serveroutput on
spool files.txt

select 'cp ' || name || ' contfiles'
from v $controlfile /
SELECT 'cp ' || member || ' logfiles'
FROM V $LOGFILE / DECLARE cursor c_tablespace is
select tablespace_name name
FROM dba_tablespaces
where contents <> 'TEMPORARY';


cursor c_tableFiles(p_space_name VARCHAR2) is(
select file_name, tablespace_name
from dba_data_files
where tablespace_name = p_space_name);
BEGIN
dbms_output.put_line(
'sqlplus "/as sysdba" << EOF');
for r_names in c_tablespace loop
begin
dbms_output.put_line(
'alter tablespace ' || r_names.name ||
' begin backup;');
for r in c_tableFiles(r_names.name) loop
dbms_output.put_line(
'! cp ' || r.file_name || ' datafiles');
end loop;
dbms_output.put_line(
'alter tablespace ' || r_names.name ||
' end backup;');
exception
when OTHERS then
null;
end;
end loop;
dbms_output.put_line(
'exit');
dbms_output.put_line(
'/');
dbms_output.put_line(
'EOF');
end;
/
spool
off
exit

Spool copy command of control and logfiles to “files.txt”. Next loop existing tablespaces (but not temporary ) alter them to begin backup. Copy relevant datafile to datafiles directory and stop backup on this tablespace


FILE=files.txtcat $FILE | grep -v '^[ ]*\\n\\'  > $FILE.tmpcat $FILE.tmp | sed -e 's/^[ \t]*//' | sed '/^$/d' > $FILE. $FILE

remove empty lines and align all code to the left in the files.txt. Execute set of commands in “files.txt”. This will actually do all work


jar -Mcvf db_backup.zip contfiles logfiles startupfiles datafilesrm -rf  $FILE* contfiles logfiles startupfiles datafiles

zip all directories and delete temporary files


That is all. Attached complete source and example of files.txt


 


files.txt (1.28 kb)


backup_db.sh (1.71 kb)

Problem looping files under UNIX

I was writing some script under korn shell which suppose to loop X directory and search for the files with “prog” extension. The script is very simple

for file in "*.prog"
do
   echo $file
done;

 


Just to clarify, where was no prog files in this directory while I executed it!!! But, the result of the script was


*prog


I was shocked!!. I tried to execute the same script under “bash" and result were the same. For some reason UNIX was thinking that I do have “prog” file in my directory, but I didn’t. Finally I found how to do it safely. Use the following piece of code





for file in `ls *.prog 2> /dev/null`
do  
   echo $file
done;

This one worked just fine.


If any one knows what is the reason for this strange behavior , please comment it

Creating "Value Set" for the framework Documents - Oracle Application

Ok. So here is the task. I need to create a "Value Set" of Framework documents. So far so good, bacause I have a nice package called jdr_utils with a nice function "listDocuments".

But the problem is that procedure outputs the results into dbms_output.put_line and "Value Set" can be based on  table or view only.

So what do I need to do. Read the output of dmbs_output, and insert it into some table or view

Step 1: Reading from dbms_output.

Well, reading from  dbms_output in not so tricky, bacause we have a function called dbms_output.get_lines. Here is the code:

declare
l_outtab dbms_output.chararr;
l_fetchln
INTEGER := 90000;
begin
jdr_utils.listDocuments(p_document_prefix, true);
dbms_output.get_lines(l_outtab, l_fetchln);
end;

using this code l_outtab array holds all output lines and l_fetchln holds the number of fetched lines


 


Step 2: Creating view


Now we need to build a view, which holds lines from  l_fetchln array. For this, we will use pipelined function.


First we need to create the types for our pipelines function


create or replace type XXPCK_JDR_DOCUMENTS_ITEM as object
(
document
varchar2(400)
)
/
create or replace type JDR_OEF_DOCUMENTS_TAB
as table of JDR_OEF_DOCUMENTS_ITEM


now lets modify the the script above to loop over result array and pipe each row


create or replace function Get_Oef_Documents(p_document_prefix varchar2) return JDR_OEF_DOCUMENTS_TAB pipelined is
l_outtab dbms_output.chararr;
l_fetchln
INTEGER := 90000;
begin
jdr_utils.listDocuments(p_document_prefix,true);
dbms_output.get_lines(l_outtab, l_fetchln);
--read from standart output
FOR i IN 2 .. l_fetchln LOOP
pipe row (JDR_OEF_DOCUMENTS_ITEM(document => l_outtab(i)));
END LOOP;
return;
end Get_Oef_Documents;


However, according to the company policy, it if forbidden to compile functions and procedurs in the apps schema. No problem!!!. We will compile it under JDR schema. The following Metalink note holds


the list of all grants and synonyms that we need to create in order to use jdr_utils in non apps schema Note:399780.1.


The only thing that should be done after this, is giving grants on our function to apps and create a synonym




  • grant all on get_oef_documents to apps


  • create synonym app.get_oef_documents for jdr.get_oef_documents

Finally we can create a view in the apps schema


create or replace view jdr_oef_documents as
select document
from table(Get_Oef_Documents('/')).

That is all. Now we can use this view as a source for our "Value Set" inside Oracle Application

Invoiking Web-Services with pure PL/SQL and building LOV from the result

Recently I had to find a way to build a LOV for all issues inside Serena's Mashup system.

I found that Serena have a Web-Service interface. I downloaded it and was able to extract the exact SOAP envelop that I need to send to Mashup

Here is the full code:

create function Build_Mashup_Lov return XX_MASHUP_USSUES_TAB pipelined is
      l_mashup_host     varchar2(200);
      l_mashup_user     varchar2(200);
      l_mashup_pass     varchar2(200);
      l_mashup_table    varchar2(200); --points to the mashup table id
      soap_request      varchar2(30000);
      soap_respond      varchar2(32767);
      http_req          utl_http.req;
      http_resp         utl_http.resp;
      l_clob            CLOB;
      resp              XMLType;
      i                 integer;
      /*-----
      * This cursor returns a VARRAY of XMLTypes or XMLSequenceType
      * given a base XMLType and an XPath search string.
      -------*/
      CURSOR crsrNodeList(cv_doc XMLType,
                          cv_path VARCHAR2) IS
                          SELECT    XMLSequence(extract(cv_doc, cv_path)) XML
                           FROM dual;
      /* XMLSequenceType Object the will contain an array of XMLType Objects */
      nodeList XMLSequenceType;
   begin
      --get mashup host url
      select profile_option_value
      into   l_mashup_host
      from   fnd_profile_options_vl v, 
             FND_PROFILE_OPTION_VALUES t
      where  v.profile_option_name='XXPCK_CCS_HOST'
      and    v.profile_option_id = t.profile_option_id
      and    level_id=10001;
     
     
      select profile_option_value
      into   l_mashup_table
      from   fnd_profile_options_vl v, 
             FND_PROFILE_OPTION_VALUES t
      where  v.profile_option_name='XXPCK_CCS_TABLE'
      and    v.profile_option_id = t.profile_option_id and  level_id=10001;
    
      select profile_option_value
      into   l_mashup_pass
      from   fnd_profile_options_vl v, 
             FND_PROFILE_OPTION_VALUES t
       where v.profile_option_name='XXPCK_CCS_PASS'
       and   v.profile_option_id = t.profile_option_id
        and  level_id=10001;
       
      select profile_option_value
      into   l_mashup_user
      from   fnd_profile_options_vl v, 
             FND_PROFILE_OPTION_VALUES t
       where v.profile_option_name='XXPCK_CCS_USER_NAME'
       and   v.profile_option_id = t.profile_option_id
       and   level_id=10001;
      
      soap_request:= '<?xml version = "1.0" encoding = "UTF-8"?>
                      <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:aewebservices70">
                       <soapenv:Header/>
                       <soapenv:Body>
                          <urn:GetItemsByQuery>
                             <!--Optional:-->
                             <urn:auth>
                                <!--Optional:-->
                                <urn:userId>'||l_mashup_user||'</urn:userId>
                                <!--Optional:-->
                                <urn:password>'||l_mashup_pass||'</urn:password>
                                <!--Optional:-->
                                <urn:hostname>'||l_mashup_host||'</urn:hostname>
                             </urn:auth>
                             <urn:tableID>'||l_mashup_table||'</urn:tableID>
                                   </urn:GetItemsByQuery>
                       </soapenv:Body>
                      </soapenv:Envelope>
                             ';
       DBMS_LOB.createtemporary(l_clob, FALSE);
     
       http_req:= utl_http.begin_request
                  ( l_mashup_host
                  , 'POST'
                  , 'HTTP/1.1'
                  );
        utl_http.set_header(http_req, 'Content-Type', 'text/xml'); -- since we are dealing with plain text in XML documents
        utl_http.set_header(http_req, 'Content-Length', length(soap_request));
        utl_http.set_header(http_req, 'SOAPAction', ''); -- required to specify this is a SOAP communication
        utl_http.write_text(http_req, soap_request);
        http_resp:= utl_http.get_response(http_req);
        BEGIN
          LOOP
            UTL_HTTP.read_text(http_resp, soap_respond, 32767);
            --strip namespace
            soap_respond:=replace(replace(soap_respond,'SOAP-ENV:',''),'ae:','');
            soap_respond:=replace(soap_respond,'xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"','');
            DBMS_LOB.writeappend (l_clob, LENGTH(soap_respond), soap_respond);
           
           
           
          END LOOP;
            utl_http.end_response(http_resp);
            --read CLOB to XML
            resp := sys.xmltype.createXML(l_clob);
     
        EXCEPTION
          WHEN UTL_HTTP.end_of_body THEN
            begin
             --read CLOB to XML
             resp := sys.xmltype.createXML(l_clob);
            OPEN crsrNodeList(resp, '/Envelope/Body/GetItemsByQueryResponse/return');
               FETCH crsrNodeList
               INTO nodeList;
            CLOSE crsrNodeList;
           --loop nodelist
           FOR x IN 1 .. nodeList.count() LOOP
             pipe row (XX_MASHUP_USSUE_ITEM(issue_title  => nodelist(x).extract('return/title/text()').getStringVal(),
                                            issue_number => nodelist(x).extract('return/genericItem/itemName/text()').getStringVal(),
                                            issue_id     => nodelist(x).extract('return/genericItem/itemID/text()').getStringVal()));
             
           END LOOP;
            UTL_HTTP.end_response(http_resp);
            DBMS_LOB.freetemporary(l_clob);
           
            return;
           
            exception
               when others then
                  UTL_HTTP.end_response(http_resp);
                  DBMS_LOB.freetemporary(l_clob);
            end;
        END; -- end read respond text
       
    EXCEPTION
    WHEN utl_http.end_of_body THEN
      utl_http.end_response(http_resp);
   end  Build_Mashup_Lov;

Lets examine it .

First of all I am extracting credentials values and Web-Service URL from profiles. After this, building SOAP XML

DBMS_LOB.createtemporary(l_clob, FALSE);

Creating temporary CLOB object , that will hold the response from the server

http_req:= utl_http.begin_request
                  ( l_mashup_host
                  , 'POST'
                  , 'HTTP/1.1'
                  );
        utl_http.set_header(http_req, 'Content-Type', 'text/xml'); -- since we are dealing with plain text in XML documents
        utl_http.set_header(http_req, 'Content-Length', length(soap_request));
        utl_http.set_header(http_req, 'SOAPAction', ''); -- required to specify this is a SOAP communication
        utl_http.write_text(http_req, soap_request);
        http_resp:= utl_http.get_response(http_req);

Create URL request with proper Context and get the response. We cannot read the response entierly to the CLOB, so we need to loop

the response and read each time the maximun amount of textual data. We are not interested in the namespace, so lets strip it. Append

textual data to the CLOB object

LOOP
            UTL_HTTP.read_text(http_resp, soap_respond, 32767);
            --strip namespace
            soap_respond:=replace(replace(soap_respond,'SOAP-ENV:',''),'ae:','');
            soap_respond:=replace(soap_respond,'xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"','');
            DBMS_LOB.writeappend (l_clob, LENGTH(soap_respond), soap_respond);
END LOOP;

Create XML object from CLOB. We need it , since the response is also in the XML format and we can use build in XML engine inside PL/SQL

to extract required data

resp := sys.xmltype.createXML(l_clob);

We will use XPATH expression to extract data we need from specific elements

OPEN crsrNodeList(resp, '/Envelope/Body/GetItemsByQueryResponse/return');
   FETCH crsrNodeList   INTO nodeList;
CLOSE crsrNodeList;

All we need to do now is to loop "nodelist" object and use pipe to return it as table 

FOR x IN 1 .. nodeList.count() LOOP
  pipe row (XX_MASHUP_USSUE_ITEM(issue_title  => nodelist(x).extract('return/title/text()').getStringVal(),
                                                      issue_number => nodelist(x).extract('return/genericItem/itemName/text()').getStringVal(),
                                                      issue_id     => nodelist(x).extract('return/genericItem/itemID/text()').getStringVal()));
             
END LOOP;

In order to use it now all I need to do is call


select *
from table(Build_Mashup_Lov)
  

 


 


 


 


 


Using PL/SQL to create OA user and attach "System Administrator" responsibility

I faced several times when I had a full access to the DB, but could not access Oracle Application, because I had no user.

So I decided to write a function which will create a new user and attach it new responsibility

First of all, you need to compile a function which attaching the responsibility to specific user. Here is the source code

create or replace procedure Attach_Responsibility(p_resp_key varchar2,
p_user_id
number) is
l_respons_id
number;
l_resp_appl
number;
begin
begin
select t.responsibility_id
into l_respons_id
from fnd_responsibility_vl t
where t.responsibility_key=p_resp_key;
select t.application_id
into l_resp_appl
from fnd_responsibility_vl t
where t.responsibility_key=p_resp_key;
exception
when no_data_found then
dbms_output.put_line(
'Cannot get responsibility_id or application_id for SYSTEM_ADMINISTRATOR');
when others then
dbms_output.put_line(sqlerrm);
end; fnd_user_resp_groups_api.insert_assignment(user_id => p_user_id
,responsibility_id
=> l_respons_id --System Administrator
,responsibility_application_id => l_resp_appl
--,security_group_id => 0
,start_date => SYSDATE - 1000
,end_date
=> SYSDATE+1000
,description
=> 'Description');
commit;
end Attach_Responsibility;

And now the script that will create new user and call the function above to attach him X responsibility


declare
l_user_exists
number;
l_user_name fnd_user.
user_name%type := 'MYNEWUSER';
l
varchar2(1000);
l_user_id
number;
l_respons_id
number;
l_resp_appl
number;
begin
select count(1)
into l_user_exists
from fnd_user
where user_name = l_user_name;
dbms_output.put_line(
'User exist: '||l_user_exists);
if l_user_exists = 0 then
fnd_user_pkg.CreateUser(x_user_name
=> l_user_name,
x_owner
=> 'CUST',
x_unencrypted_password
=> 'welcome!1');
commit;
select user_id
into l_user_id
from fnd_user
where user_name=l_user_name;
dbms_output.put_line(
'User ID ['||l_user_name||']'||l_user_id);
else
select user_id
into l_user_id
from fnd_user
where user_name=l_user_name;
fnd_user_pkg.UpdateUser(x_user_name
=> l_user_name,
x_owner
=> 'CUST',
x_unencrypted_password
=> 'WELCOME1');
end if;
commit;
Attach_Responsibility(
'SYSTEM_ADMINISTRATOR',
l_user_id);
Attach_Responsibility(
'APPLICATION_DEVELOPER',
l_user_id);
end;

User's name is MYNEWUSER

l_user_name    fnd_user.user_name%type := 'MYNEWUSER';

check if user already exists


select count(1)
into l_user_exists
from fnd_user
where user_name = l_user_name;

if user exists, change the password to WELCOME!1


fnd_user_pkg.UpdateUser(x_user_name => l_user_name,
x_owner
=> 'CUST',
x_unencrypted_password
=> 'WELCOME!1');

if it doesn't exists, create with initial password WELCOME!1


fnd_user_pkg.CreateUser(x_user_name => l_user_name,
x_owner
=> 'CUST',
x_unencrypted_password
=> 'WELCOME!1');

After user created all we need to do is attach related responsibility. In this case we are going to attach "System Administrator" and "Application Developer" responsibilities


Attach_Responsibility('SYSTEM_ADMINISTRATOR',
l_user_id);
Attach_Responsibility(
'APPLICATION_DEVELOPER',
l_user_id);