Gerger

Wrapping PL/SQL Source Code and a Strange Bug (PLS-00753)

February 15th, 2011

Wrapping process makes PL/SQL source code unreadable. Developers can wrap package specifications, package bodies, functions, procedures, type specifications and type bodies. Unfortunately triggers cannot be wrapped. To wrap the trigger code you need to place your source code to a package or procedure and call it from the trigger.

There are two ways to wrap PL/SQL source code.

1. Wrapping PL/SQL source code with wrap utility

Wrap utility takes two parameters. First one (iname) is the PL/SQL source code file and the second one (oname) is the output file that will hold the wrapped code.

wrap iname=example_input_file.sql oname=example_output_file.plb

This is an easy way to wrap source code because you do not need to handle DDLs bigger than 32767 bytes. But this way, you need to save DDLs into different files and execute the wrap command frmo the command prompt for each file.

2. Wrapping PL/SQL source code with DBMSL_DDL package

The DBMS_DDL package contains an overloaded wrap API to wrap PL/SQL source code.

dbms_ddl.wrap(ddl varchar2, lb pls_integer, ub, pls_integer) return varchar2;

dbms_ddl.wrap(ddl dbms_sql.varchar2s, lb pls_integer, ub, pls_integer) return dbms_sql.varchar2s;

dbms_ddl.wrap(ddl dbms_sql.varchar2a, lb pls_integer, ub, pls_integer) return dbms_sql.varchar2a;

In this method of wrapping, developers need to handle CLOB values that are bigger than 32767 bytes. They need to convert CLOB values to either dbms_sql.varchar2s or dbms_sql.varchar2a format.

We wrapped all our source code the second way in Oracle 10.0.2.1. We liked the ability ti generate all the encoded scripts automatically in PL/SQL. The encoded scripts run just fine from SQLPlus or similar tool. However, when we run the scripts using JDBC, we have faced a problem. Some packages started to give the following error mesagge:

PLS-00753: malformed or corrupted wrapped unit

First, we tried to solve the problem with changing character encoding but this did not help us. Then we started again to investigate the problem and we found a really interesting statement in the following forum thread. site.

“This only happens if the last character of the wrapped code is at the end of a line.”

We looked over all our uncompiled packages and verified the problem. Then we found an odd solution to this strange bug. We started to break down the last character of the wrapped code to next line. It does not mess up the wrapped code and works just fine in different oracle 10g versions.

Here is the function which extracts the DDL of the specified object in dbms_sql.varchar2a format and then wraps the source code using the dbms_ddl.wrap utility. We used dbms_sql.varchar2a due to the length of our object DDLs. At the end, there is a code block which converts dbms_sql.varchar2a to CLOB. It breaks down the last character of the wrapped code to the next line if the last character of the wrapped code is at the end of the line.

function extractCodeWrapped(in_objectType_cd varchar2, in_objectName_tx varchar2)
return  clob
is
 v_ddl_index_nr number;
 v_cur_pos_nr number;
 v_next_pos_nr number;

 v_ddl_2a dbms_sql.varchar2a;
 v_wrapped_ddl_2a dbms_sql.varchar2a;

 v_wrapped_ddl_cl clob;

 cursor cur_objectDDL is
   select -1 line, 'create or replace'||chr(10) text from dual
   union all
   select line, text
   from user_source
   where type = in_objectType_cd
   and name = in_objectName_tx
   order by line;

begin
 dbms_lob.createtemporary(v_wrapped_ddl_cl,true);

 -- extract DDL of the object
 v_ddl_index_nr := 1;
 for rec in cur_objectDDL loop
   v_ddl_2a(v_ddl_index_nr) := rec.text;
   v_ddl_index_nr := v_ddl_index_nr + 1;
 end loop;

 -- wrap source code of the object
 v_wrapped_ddl_2a := dbms_ddl.wrap(v_ddl_2a,1,v_ddl_2a.count);

 -- Convert dbms_sql.varchar2a to CLOB
 for i in 1..v_wrapped_ddl_2a.count loop

   -- if it is the last line check the last character. If the last character is
   -- at the end of line, break down it to next line.
   if i = v_wrapped_ddl_2a.count then
     -- find the positions of line feeds (chr(10)) at the line
     v_cur_pos_nr := 0;
     v_next_pos_nr := 0;
     loop
       v_cur_pos_nr := instr(v_wrapped_ddl_2a(i), chr(10), v_cur_pos_nr+1);
       v_next_pos_nr := instr(v_wrapped_ddl_2a(i), chr(10), v_cur_pos_nr+1);

       exit when v_cur_pos_nr = 0 or v_next_pos_nr = (length(v_wrapped_ddl_2a(i))-1)
              or v_next_pos_nr = length(v_wrapped_ddl_2a(i));
     end loop;
     -- if the line length is 74, the last character is at the end of line.
     if (length(v_wrapped_ddl_2a(i)) - v_cur_pos_nr) = 74 then
       -- append the line as two lines; first one is until last character and
       -- second one is only last character
       dbms_lob.writeappend(v_wrapped_ddl_cl, v_cur_pos_nr, substr(v_wrapped_ddl_2a(i), 1, v_cur_pos_nr));
       dbms_lob.writeappend(v_wrapped_ddl_cl, 71, substr(v_wrapped_ddl_2a(i), v_cur_pos_nr+1, 71));
       dbms_lob.writeappend(v_wrapped_ddl_cl, 1, chr(10));
       dbms_lob.writeappend(v_wrapped_ddl_cl, 2, substr(v_wrapped_ddl_2a(i), v_cur_pos_nr+72, 1)||chr(10));
     else
       -- otherwise append the line directly
       dbms_lob.writeappend(v_wrapped_ddl_cl, length(v_wrapped_ddl_2a(i)),v_wrapped_ddl_2a(i));
     end if;
   else
     -- if it is not last line append the line directly
     dbms_lob.writeappend(v_wrapped_ddl_cl, length(v_wrapped_ddl_2a(i)),v_wrapped_ddl_2a(i));
   end if;

 end loop;
 -- add '/' character end of object wrap
 dbms_lob.writeappend(v_wrapped_ddl_cl,3,'/'||chr(10)||chr(10));

 -- return wrapped source code
 return v_wrapped_ddl_cl;
end;

Here is sample code which calls the function:

declare
 v_objectType_cd varchar2(200) := 'PROCEDURE';
 v_objectName_tx varchar2(255) := 'TEST_PROCEDURE';

 v_wrapped_ddl_cl clob;
begin
 v_wrapped_ddl_cl := extractCodeWrapped(v_objectType_cd, v_objectName_tx);
 dbms_output.put_line(v_wrapped_ddl_cl);
end;

Samet Basaran

Read »

Using Where Clause with Hierarchical Queries

February 15th, 2011

Using where clause in hierarchical queries may be confusing. Oracle first evaluates the joins in the query if there are any. Then, next step, selects rows in hierarchical order. Finally, applies remaining where clause conditions. In other words, rows are filtered by where clause after evaluating the hierarchy. Lets show this with an example on HR schema:

SQL> select level, e.employee_id, e.manager_id, e.department_id, e.first_name, e.last_name
2 from employees e, departments d, jobs j
3 where e.department_id = d.department_id
4 and e.job_id = j.job_id
5 and d.department_id = 100
6 start with e.manager_id is null
7 connect by prior e.employee_id = e.manager_id;

    LEVEL EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID FIRST_NAME           LAST_NAME
———- ———– ———- ————- ——————– ————————-
        3         108        101           100 Nancy                Greenberg
        4         109        108           100 Daniel               Faviet
        4         110        108           100 John                 Chen
        4         111        108           100 Ismael               Sciarra
        4         112        108           100 Jose Manuel          Urman
        4         113        108           100 Luis                 Popp

6 rows selected.

As you can see first row begins with level 3.
However, in my experience, most of the time when I wrote hierarchical queries, I needed the evaluation of the hierarchy to be performed after the where clause. In this case, I recommend inner queries with NO_MERGE hint. Using NO_MERGE hint will prevent Oracle SQL Optimizer to merge the inner query with the hierarchical one. Of course, to prevent merge, developers should be careful about not passing any references from inner query to the outer (hierarchical) one:

SQL> select level, e2.*
2 from (select /*+ NO_MERGE */ e.employee_id, e.manager_id, e.department_id, e.first_name, e.last_name
3        from employees e, departments d, jobs j
4        where e.department_id = d.department_id
5        and e.job_id = j.job_id
6        and d.department_id = 100) e2
7 start with e2.employee_id = 108 –I know the root employee of my query
8 connect by prior e2.employee_id = e2.manager_id;

    LEVEL EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID FIRST_NAME           LAST_NAME
———- ———– ———- ————- ——————– ————————-
        1         108        101           100 Nancy                Greenberg
        2         109        108           100 Daniel               Faviet
        2         110        108           100 John                 Chen
        2         111        108           100 Ismael               Sciarra
        2         112        108           100 Jose Manuel          Urman
        2         113        108           100 Luis                 Popp

6 rows selected.

Same result except level values. This is because SQL Optimizer first evaluated inner query (joins and other where clause filters) and then evaluated the hierarchy. Furthermore, if you compare SQL plans of the two queries, you will see a full table access for the first one while second one has no full table access.

Uğur Koçak

Read »

Running ant javac inside Eclipse

February 15th, 2011

You may often want to use javac task in your ant build files. In order to execute javac tasks inside Eclipse, you need to do a little bit of work. Below are the steps I follow to get javac executed succesfully. I hope it helps.

1. Define build.compiler property in your ant build file.

<project basedir="."  default="compile"  name="Test Builder">
   <property name="build" location="C:\Users\Administrator\Desktop\build"/>
   <property  value="org.eclipse.jdt.core.JDTCompilerAdapter" name="build.compiler"/>
   <target name="compile" >
     <javac srcdir="." destdir="${build}" target="1.5" source="1.5" debug="true">
   </target>
</project>

2. You should have a file named org.eclipse.jdt.core_{version}.jar located at plugins directory under your
Eclipse distribution. Extract this file into a desired location. You should see “jdtCompilerAdapter.jar”
at the extracted location.

3. Select Run->External Tools->External Tools Configuration from the Eclipse menu.

a- Open the Classpath tab. Click on the Add External Jar button and select the jdtCompilerAdapter.jar file
that you obtained from step 2.

b- Open the JRE tab. Make sure to check “Run in the same JRE as the workspace” box.

Read »

Query optimization in MySQL with Subqueries

February 11th, 2011

Recently I had to fetch data from several tables using ORDER BY and LIMIT statements in MySQL. The problem with LIMIT statement is that it’s applied in the last step after all the rows are fetched. I needed to fetch rows that fit in boundaries specified by x1,y1,x2,y2 as well as information about the writer of these entries. Tables used in the process were:

CREATE TABLE `location` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `lat` double NOT NULL,
  `lng` double NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_latLng` (`lat`,`lng`),
  KEY `idx_lat` (`lat`),
  KEY `idx_lng` (`lng`)
) ENGINE=InnoDB

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullName` varchar(255) DEFAULT NULL,
  `gender` varchar(1) DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `language` varchar(3) DEFAULT 'en',
  `createdate` datetime NOT NULL,
  `lastaccessdate` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_uk` (`email`),
  UNIQUE KEY `userName_uk` (`username`)
) ENGINE=InnoDB

CREATE TABLE `entry` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` text NOT NULL,
  `title` varchar(4000) DEFAULT NULL,
  `location` int(11) NOT NULL,
  `user` int(11) NOT NULL,
  `createdate` datetime NOT NULL,
  `lastmodifieddate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `entry_user_fk` (`user`),
  KEY `entry_location_fk` (`location`),
  KEY `idx_createdate` (`createdate`),
  CONSTRAINT `entry_location_fk` FOREIGN KEY (`location`) REFERENCES `location` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `entry_user_fk` FOREIGN KEY (`user`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

Location table has ~250 records, entry table has ~13K records, and user table has ~50 records. This is auto-generated test data that we’ve used for development purposes.

The initial query was:

select e.*, u.*, l.*
from entry e, user u, location l
where e.user = u.id and e.location = l.id and ((l.lat between x1 and x2) and (l.lng between y1 and y2))
order by e.id desc
limit 0,10

It took ~1.5 second to complete. It is not very very bad but it must be improved. Then I decided to use location as a derived table:

select e.*, u.*, l.*
from entry e, user u, (select l2.* from location l2 where (l2.lat between x1 and x2) and (l2.lng between y1 and y2)) l
where e.user = u.id and l.id = e.location
order by e.id desc
limit 0,10

This last one took ~1.5-2 seconds. The last change clearly didn’t help very much!

I only need 10 rows from thousands so why to join thousands of rows then limit to 10? LIMIT statement can be moved into a derived table too:

select e.*, u.*, l.*
from (
    select * from e2
    where e2.location in (select l2.id from location l2 where (l2.lat between x1 and x2) and (l2.lng between y1 and y2))
    order by e2.id desc
    limit 0,10
) e, user u, location l
where e.user = u.id and l.id = e.location
order by e.id desc

The last query took ~0.2 second to complete. Query times were obtained from MySQL profiler. Difference between queries can also be viewed using EXPLAIN statement:

Query 1:

+----+-------------+-------+--------+------------------------------------+--------------------+---------+--------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                      | key                | key_len | ref                | rows | Extra
                             |
+----+-------------+-------+--------+------------------------------------+--------------------+---------+--------------------+------+----------------------------------------------+
|  1 | SIMPLE      | l     | range  | PRIMARY,idx_latLng,idx_lat,idx_lng | idx_latLng         | 16      | NULL               |   68 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | e     | ref    | entry_user_fk,entry_location_fk  | entry_location_fk | 4       | test.l.id |  149 |
                             |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY                            | PRIMARY            | 4       | test.e.user     |    1 |
                             |
+----+-------------+-------+--------+------------------------------------+--------------------+---------+--------------------+------+----------------------------------------------+

Query 2:

+----+-------------+------------+--------+-----------------------------------+--------------------+---------+----------------+------+---------------------------------+
| id | select_type | table      | type   | possible_keys                     | key                | key_len | ref            | rows | Extra
                |
+----+-------------+------------+--------+-----------------------------------+--------------------+---------+----------------+------+---------------------------------+
|  1 | PRIMARY     |  | ALL    | NULL                              | NULL               | NULL    | NULL           |   23 | Using temporary;
 Using filesort |
|  1 | PRIMARY     | e          | ref    | entry_user_fk,entry_location_fk | entry_location_fk | 4       | l.id  |  149 |
                |
|  1 | PRIMARY     | u          | eq_ref | PRIMARY                           | PRIMARY            | 4       | test.e.user |    1 |
                |
|  2 | DERIVED     | l2        | ALL    | idx_latLng,idx_lat,idx_lng        | NULL               | NULL    | NULL           |  268 | Using where
                |
+----+-------------+------------+--------+-----------------------------------+--------------------+---------+----------------+------+---------------------------------+

Query 3:

+----+--------------------+------------+-----------------+------------------------------------+---------+---------+---------------+------+----------------+
| id | select_type        | table      | type            | possible_keys                      | key     | key_len | ref           | rows | Extra
    |
+----+--------------------+------------+-----------------+------------------------------------+---------+---------+---------------+------+----------------+
|  1 | PRIMARY            |  | ALL             | NULL                               | NULL    | NULL    | NULL          |   10 | Using filesort |
|  1 | PRIMARY            | u          | eq_ref          | PRIMARY                            | PRIMARY | 4       | e.id     |    1 |
    |
|  1 | PRIMARY            | l2        | eq_ref          | PRIMARY                            | PRIMARY | 4       | e.id |    1 |
    |
|  2 | DERIVED            | e2        | index           | NULL                               | PRIMARY | 4       | NULL          |   10 | Using where
    |
|  3 | DEPENDENT SUBQUERY | l          | unique_subquery | PRIMARY,idx_latLng,idx_lat,idx_lng | PRIMARY | 4       | func          |    1 | Using where
    |
+----+--------------------+------------+-----------------+------------------------------------+---------+---------+---------------+------+----------------+

M. Serdar Biçer @msbicer

Read »

Passing function parameters from JavaScript to JavaFX

January 7th, 2011

Most of the time, you won’t notice a delay while calling JavaFX functions from JavaScript. However, there are times you may have performance issues. Recently, we had such an issue in one of our projects: A JavaScript function was consuming a considerable amount of time. When we looked into the problem, we noticed that we were passing a big JavaScript String array to a JavaFX function as a parameter. Obviously, there was an issue about passing the array object. As an alternative, we concatenated the values in the String array and passed them to JavaFX as a single String object. And it worked! Our application ran much faster after this change.

I have created a simple JavaFX application for testing purposes. The code has two different functions which take different parameters:

import javafx.scene.*;

// This function takes a String array as parameter
function passStringArray(value: String[]){    

}

// This function takes a String as parameter
function passString(value : String){
    var stringArr = value.split(",");
}

And I call the JavaFX applet from JavaScript like this:

function passStringArray(){
  var stringArr = [];
  //Create a String array of size 5000 for test purpose
  for(i=0; i<5000;i++){
     stringArr.push(i + "");
  }

  var t1 = new Date();
  var test = document.getElementById("test");
  //Pass the String array as parameter
  test.script.passStringArray(stringArr);     

  //Output the measured time
  document.getElementById("output").innerHTML = "Measured Time: " + (new Date()-t1) + " ms";
 }

 function passString(){
  var stringArr = [];
  //Create a String array of size 5000 for test purpose
  for(i=0; i<5000;i++){
     stringArr.push(i + "");
  }

  var joinedString = stringArr.join(',');
  var t1 = new Date();

  var test = document.getElementById("test");
  //Pass the String as parameter
  test.script.passString(joinedString);

  //Output the measured time
  document.getElementById("output").innerHTML = "Measured Time: " + (new Date()-t1) + " ms";
 }

You can download all the files needed to test the application from here.
Follow these steps to test this application:
1. Extract the package you downloaded.
2. Run “Test.html” file in your browser.
3. Click on the “Pass String Array” and “Pass String” buttons to see the performance difference.

Yücel Ünlü

Read »

Adding CLOB values to XML as CDATA

January 5th, 2011

Adding large CLOB values into an XML structure using XMLDOM package in PL/SQL is challenging because XMLDOM methods do not accept CLOB data type.

The solution is to divide large CLOB values into multiple smaller chunks. The size of each chunk we create is 8000. We are not using the maximum length allowed for VARCHAR2 data type (32767 bytes) because we have a multi-lingual database and some characters are stored in 4 bytes.
We are using CDATA nodes to store these chunks because our CLOB values generally contain data as XML and we don’t want to deal with escape characters.

The following function takes a CLOB and an XMLDOM.domNode as parameters and returns an XMLDOM.domNode which contains the CLOB value. If the length of the CLOB is bigger than the defined maximum length (8000 in our case), the method divides the CLOB into several chunks prior including it to the domNode.

function addValueToXMLNode(in_parent_node xmldom.domNode, in_value_cl CLOB)
return xmldom.domNode
is
  v_offset_nr         number;
  v_left_nr           number;
  v_partNo_nr         number;
  v_buffer_tx         varchar2(32767);
  v_maxdatalength_nr  number := 8000;

  v_parent_doc          xmldom.domDocument;
  v_part_node           xmldom.domNode;
  v_bufferedValue_cdata xmldom.DOMCDATASection;
  v_temp_node           xmldom.domNode;
  v_value_node          xmldom.domNode;

begin
  -- get the owner document of the parent node
  v_parent_doc := xmldom.getownerdocument(in_parent_node);

  -- create a new node named "value" and add it to the parent node
  v_value_node := xmldom.makeNode(xmldom.createelement(v_parent_doc, 'value'));
  v_temp_node:= xmldom.appendchild(in_parent_node, v_value_node);

  -- get the length of CLOB value
  v_left_nr := dbms_lob.getlength(in_value_cl);

  -- if CLOB is bigger then the defined maximum value,
  -- divide it, otherwise add it directly to the parent node
  if v_left_nr > v_maxdatalength_nr then
    -- set multi part attribute to Yes
    xmldom.setattribute(xmldom.makeelement(v_value_node), 'multiPart', 'Y');

    v_offset_nr := 1;
    v_partNo_nr := 1;
    loop
      exit when v_left_nr <= 0;

      if v_left_nr > v_maxdatalength_nr then
        v_buffer_tx := dbms_lob.substr(in_value_cl, v_maxdatalength_nr, v_offset_nr);
        v_left_nr := v_left_nr - v_maxdatalength_nr;
        v_offset_nr := v_offset_nr + v_maxdatalength_nr;
      else
        v_buffer_tx := dbms_lob.substr(in_value_cl, v_left_nr, v_offset_nr);
        v_left_nr := 0;
      end if;
      -- create a node for each chunk and give it a number
      v_part_node := xmldom.makenode(xmldom.createelement(v_parent_doc,'part'));
      xmldom.setattribute(xmldom.makeelement(v_part_node), 'no', v_partNo_nr);
      v_partNo_nr := v_partNo_nr + 1;

      -- create a CDATA node with buffered value and add it to the v_part_node
      v_bufferedValue_cdata:=xmldom.createcdatasection(v_parent_doc,v_buffer_tx);
      v_temp_node := xmldom.appendchild(v_part_node, xmldom.makeNode(v_bufferedValue_cdata));
      -- add v_part_node to the value node
      v_temp_node := xmldom.appendchild(v_value_node, v_part_node);
    end loop;

  else
    -- set multi part attribute to No
    xmldom.setattribute(xmldom.makeelement(v_value_node), 'multiPart', 'N');
    -- create a CDATA node with buffered value and add it to the value node directly
    v_bufferedValue_cdata:=xmldom.createcdatasection(v_parent_doc, to_char(in_value_cl));
    v_temp_node := xmldom.appendchild(v_value_node, xmldom.makeNode(v_bufferedValue_cdata));

  end if;

  return in_parent_node;
end;

As the next step, we need to extract the chunks from the XML and rebuild the CLOB..
The following function accepts an XMLDOM.domElement as its input parameter, extracts the CLOB inside the domElement and returns it.

function readValueFromXMLNode(in_parent_node  xmldom.domNode)
return clob
is
  v_value_cl            clob;
  v_multiPart_yn        varchar2(1);

  v_parent_doc          xmldom.domDocument;
  v_part_node           xmldom.domNode;
  v_children_nodeList   xmldom.domNodeList;
  v_child_node          xmldom.domNode;
  v_value_node          xmldom.domNode;
begin

  -- get owner document
  v_parent_doc := xmldom.getownerdocument(in_parent_node);

  -- get value node
  v_value_node := xmldom.getfirstchild(in_parent_node);
  -- check if this is multipart value or not
  v_multiPart_yn := xmldom.getAttribute(xmldom.makeelement(v_value_node), bdf_constant.attrmultipart);
  if v_multiPart_yn = 'Y' then
    -- get the list of the nodes that have the parts
    v_children_nodeList := xmldom.getchildnodes(v_value_node);
    -- empty clob value
    v_value_cl := '';
    -- read all values and append to clob
    for index_nr in 0..xmldom.getlength(v_children_nodeList)-1 loop
      v_child_node := xmldom.item(v_children_nodeList, index_nr);

      v_part_node := xmldom.getfirstchild(v_child_node);
      v_value_cl := v_value_cl || xmldom.getnodevalue(v_part_node);
    end loop;

  else
   -- if it is not multi-part value, get the CDATA node and read the value
    v_child_node := xmldom.getfirstchild(v_value_node);
    v_value_cl := xmldom.getnodevalue(v_child_node);
  end if;

  return v_value_cl;

end;

Here is sample code which calls both functions:

declare
  v_value_cl      clob;
  v_returned_cl   clob;
  v_parent_doc    xmldom.domDocument;
  v_parent_node   xmldom.domNode;
begin
  -- create new xml document
  v_parent_doc := xmldom.newdomdocument('
');
  -- get parent noode
  v_parent_node := xmldom.makenode(xmldom.getdocumentelement(v_parent_doc));
  -- create sample clob value
  v_value_cl :=
'<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://sinop/fs_smt/bdf_ide_dev.xsd">
  <mainframe title="Formspider" width="500" height="500" maximizeOnStartUp="Y" icon="/icons/mainframe/fs.png" resizable="Y" close="Y" minimize="Y" maximize="Y">
    <events>
      <keyEvents></keyEvents>
    </events>
  </mainframe>
</root>';
  -- add value to XML
  v_parent_node := addValueToXMLNode(v_parent_node, v_value_cl);
  -- read value from XML
  v_returned_cl := readValueFromXMLNode(v_parent_node);
  -- compare CLOB values
  if dbms_lob.compare(v_value_cl,v_returned_cl) = 0 then
    dbms_output.put_line('successful');
  end if;

end;

Samet Basaran

Read »

How to get DDL from Oracle dictionary programmatically

October 26th, 2010

While you are happily developing PL/SQL with your favorite tool be it TOAD, SQL Navigator or SQL Developer, all of a sudden your boss comes and tells you that you have to extract all stored programs in your Oracle Database Schema and save them to the file system. Since you have 45678457 PL/SQL packages out there you know you have to automate this process. You know Oracle saves these stored programs somewhere, but how do you get them back? As most of the time, Oracle has a built-in solution for this need as well: the DBMS_METADATA. package in the SYS schema.

DBMS_METADATA.GET_DDL (
object_type     IN VARCHAR2,
name            IN VARCHAR2,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

The function works as advertised but there are some tricks that you might need to know.

First of all, the resulting DDL script is schema specific. When you extract the DDL of a package named “Package1″ in schema HR, the generated DDL starts with;

CREATE OR REPLACE PACKAGE "HR"."Package1" .

This is not always desired. Here is a small trick that removes schema name,

select replace(dbms_metadata.get_ddl('PACKAGE','PACKAGE1'),'"'||user||'"."PACKAGE1"','PACKAGE1') from dual

Below is a function that removes schema name from DDL scripts;

CREATE OR REPLACE
FUNCTION GETDDL ( in_object_type_tx     IN  VARCHAR2,
                  in_name_tx            IN  VARCHAR2
                ) return clob  is
  v_username_tx varchar2(255);
  v_cl clob;
  v_number_nr number;
begin
  select user into v_username_tx from dual;
  v_cl := dbms_metadata.get_ddl(in_object_type_tx,in_name_tx,v_username_tx);
  -- remove schema name...
  v_cl := replace(v_cl,upper('"'||v_username_tx||'"."'||in_name_tx||'"'),upper(in_name_tx));
  return v_cl;
end;

Another trick that you should remember is that, Oracle stores all object types and object names in upper case. If you do not use upper case you will get an error even though object name you provide seems correct.This tip may seem obvious to PL/SQL Developers but not so much if you are primarily a Java Developer who happens to maintain some PL/SQL code.

Final advice is about the object_type input parameter of the GET_DDL function. As you can guess, the valid values for this input parameter are the object types that are available in Oracle. The most commonly used ones are;

FUNCTION
INDEX
PACKAGE
PACKAGE_BODY
PROCEDURE
SEQUENCE
TABLE
TRIGGER
TYPE
VIEW

Well, the problem is, when you extract ‘PACKAGE’, resulting DDL includes the package specification and the package body. However, if you need to extract only the package specification, the types above will not get the job done.

There is another object type that is not well documented; ‘PACKAGE_SPEC’. If you need to extract only Package specification, you should use ‘PACKAGE_SPEC’ as object type.

Serkan Kasapbaşı

Read »

FormattedTextField (with MaskFormatter) on Javascript

October 21st, 2010

While working on the Formspider framework (which has both Java and JavaScript renderers), we needed to implement a FormattedTextField component. It was easy to implement on Java side by using JFormattedTextField with a MaskFormatter. For JavaScript renderer we implemented a new MaskFormatter class as similar as possible to java.

Here is the source code that also includes an example:
JavascriptMaskFormatter.rar

In handlers.js, you can see event handlers for key inputs (including navigation keys), paste operation and validation on blur.

(###) ###-##-## mask is used in the example. Available masks are { # U L A ? * H }. See Java MaskFormatter page for more information.

Tested on IE8, FF3 and Chrome6.

Öskan Şavlı

Read »

Installing Oracle 10g R2 Using Oracle Public Yum Repository on 64 Bit Oracle Enterprise Linux

October 10th, 2010

There are a lot of documents about installing Oracle database on Linux systems on the Internet. Most of them are quite complex and the remaining ones do not include Oracle Public Yum Repository installation. Below is a concise document that should get the job done.

Preinstallation Tasks

1. Verify Kernel Version

To start with, your system must be running kernel version 2.6.9-11.EL or later. To verify whether the required kernel is installed, enter the following command:

# uname -r or to get more detailed information execute # cat /proc/version

2. Install Missing Packages

Additionally, the following packages (or later versions) must be already installed in your system:

binutils-2.15.92.0.2-10.EL4
compat-db-4.1.25-9
control-center-2.8.0-12
gcc-3.4.3-9.EL4
gcc-c++-3.4.3-9.EL4
glibc-2.3.4-2
glibc-common-2.3.4-2
gnome-libs-1.4.1.2.90-44.1
libstdc++-3.4.3-9.EL4
libstdc++-devel-3.4.3-9.EL4
make-3.80-5
pdksh-5.2.14-30
sysstat-5.0.5-1
xscreensaver-4.18-5.rhel4.2

Missing packages can be found in the RPM search sites like http ://rpm .pbone .net /, but installing these packages individually may require too much time and effort. The fast alternative to install all the packages is to use Oracle validated RPMs.

The Oracle validated RPMs automatize the install of the various components required to setup the Oracle database. They install all required packages and also configure a lot of Linux configuration files and create users. There are a few steps you need to take before installing Oracle validated RPMs.

Go to yum.repos.d folder by typing

# cd /etc/yum.repos.d

Execute command

# wget http ://public -yum .oracle .com /public -yum -el 5.repo

Next, enable the appropriate repository by editing the yum configuration file. To achieve this, open the yum configuration file in a text editor. Locate the section in the file for the repository you plan to update from, e.g. [el5_u5_base]. Change [el5_u5_base] and [ol5_u5_base] entries’ enabled property from 0 to 1.

The final configuration file should look like something like this:

[el5_u5_base]
name=Enterprise Linux $releasever U5 - $basearch - base
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/5/base/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1

[ol5_u5_base]
name=Oracle Linux $releasever - U5 - x86_64 - base
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL5/5/base/x86_64/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1

Save and exit from the editor.

Run;

# yum clean all

To upgrade all recommended packages associated with the Unbreakable Enterprise Kernel, use the following yum command:

# yum install oracle-linux

Reboot your system to run the newly installed Unbreakable Enterprise Kernel. For detailed information about Public Yum Server please refer to http ://public -yum .oracle .com /.

Next, download the proper version of Oracle validated RPM from http://oss.oracle.com/el5/oracle-validated/. I used oracle-validated-1.0.0-22.el5.x86_64.rpm for EL5 64bit Linux. Select the proper RPM for your Linux distro’s kernel and architecture and run:

#yum install ./oracle-validated-1.0.0-22.el5.x86_64.rpm

Other than installing required packages, this command configures required kernel parameters, the /etc/security/limits.conf file and creates required user and groups.

Go to /etc/sysctl.conf file and do required changes (just read the instructions in the file, it is easy) and after editing this file re-run

#sysctl-p.

3. Disable SELinux

Due to two internal bugs in the Oracle Database (Bug 6140224, Bug 6079461), SELinux has to be disabled on RHEL/OEL 5 to work with Oracle database 10.2.

To permanently disable SELinux go to /etc/selinux/ folder and edit the config file. Change SELINUX = enforcing to permissive or disabled

4. Create Directories

Create the directories in which the Oracle software will be installed:

# mkdir -p /u01/app/oracle/product/10.1.0/db_1

Set the ownership:

# chown -R oracle.oinstall /u01

Create oraInventory under /u01/app/oracle

5. Modify Oracle User’s Bash Profile

Add the following lines to the /home/oracle/.bash_profile file

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2/db_1; export ORACLE_HOME
ORACLE_SID=ORCL; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

This completes the preinstallation tasks.

Install the Oracle Database

Download the Oracle database setup and then run:

$ gunzip 10201_database_linux_x86_64.cpio.gz
$ cpio -idmv < 10201_database_linux_x86_64.cpio

Now you are ready to run the installer
Login as the oracle user and run:

# ./runInstaller

Post Installation

After installing Oracle 10g R2 execute:

$ lsnrctl

LSNRCTL> start

LSNRCTL> exit

$ sqlplus sys/sys as sysdba

SQL> startup

At this point your Oracle database should be up and running.

Aziz Ünsal

Read »

Be Careful When Using xmlDom.getElementsByTagName

September 16th, 2010

If you are working with PL/SQL XML DOM API, searching for elements using xmldom.getElementsByTagName may dramatically affect the performance of your code, especially if you are dealing with big XML Documents. Instead of searching for the same element over and over again, you should cache the node returned by xmldom.getElementsByTagName API and use the cached reference in subsequent DOM operations.

In the code below, new elements are added to an XML Document. We get the parent node with xmldom.getElementsByTagName API. In the first example, we get the parent node each time when a new node is added. The code executes in 0.27 seconds. In the second example, we find the parent node only once and reuse it. The same operation now takes only 0.05 seconds.

package test_xml

exec test_xml.initxml;
/

select test_xml.getxmltype from dual
/

-- add new action elements (slow way)
declare
  v_doc_xml xmldom.domdocument;

  v_newActionElement_xml xmldom.domelement;
  v_newActionNode_xml    xmldom.domnode;

  v_newActionCount_nr number := 1000;
begin
  v_doc_xml := test_xml.getxml;

  for i in 1..v_newActionCount_nr loop
    v_newActionElement_xml := xmldom.createelement(v_doc_xml, 'action');
    xmldom.setAttribute(v_newActionElement_xml, 'atrib1', 'a');
    -- find actions node using xmldom.getElementsByTagName
    -- it is executed 1000 times
    v_newActionNode_xml := xmldom.appendchild(xmlDom.item(xmldom.getelementsbytagname(v_doc_xml, 'actions'),0),
                                              xmldom.makeNode(v_newActionElement_xml));
  end loop;
end;
/

-- add new action elements (fast way)
declare
  v_doc_xml xmldom.domdocument;
  v_actionsnode_xml xmldom.domnode;

  v_newActionElement_xml xmldom.domelement;
  v_newActionNode_xml    xmldom.domnode;

  v_newActionCount_nr number := 1000;  ---TODO bu degiskenin ismini degistir
begin
  v_doc_xml := test_xml.getxml;

  -- find actions node using xmldom.getElementsByTagName
  -- it is executed once only
  v_actionsnode_xml := xmlDom.item(xmldom.getelementsbytagname(v_doc_xml, 'actions'),0);
  for i in 1..v_newActionCount_nr loop
    v_newActionElement_xml := xmldom.createelement(v_doc_xml, 'action');
    xmldom.setAttribute(v_newActionElement_xml, 'atrib2', 'b');
    v_newActionNode_xml := xmldom.appendchild(v_actionsnode_xml, xmldom.makeNode(v_newActionElement_xml));
  end loop;
end;
/

select test_xml.getxmltype from dual
/

Uğur Koçak

Read »