Posts Tagged ‘Oracle’

Parsing a CSV file in PL/SQL




create or replace procedure parse_csv(
  p_clob clob,
  p_delim varchar2 default ',',
  p_optionally_enclosed varchar2 default '"' ) is
  l_char char(1);
  l_lookahead char(1);
  l_pos number := 0;
  l_token varchar2(32767) := null;
  l_token_complete boolean := false;
  l_line_complete boolean := false;
  l_new_token boolean := true;
  l_enclosed boolean := false;
  l_lineno number := 1;
  l_columnno number := 1;


    -- increment position index
    l_pos := l_pos + 1;

    -- get next character from clob
    l_char := dbms_lob.substr( p_clob, 1, l_pos);

    -- exit when no more characters to process

    -- if first character of new token is optionally enclosed character
    -- note that and skip it and get next character
    if l_new_token and l_char = p_optionally_enclosed then
      l_enclosed := true;
      l_pos := l_pos + 1;
      l_char := dbms_lob.substr( p_clob, 1, l_pos);
    end if;
    l_new_token := false;

    -- get look ahead character

    if l_char = p_optionally_enclosed and l_enclosed then

      if l_lookahead = p_optionally_enclosed then
        l_pos := l_pos + 1;
        l_token := l_token || l_lookahead;
      elsif l_lookahead = p_delim then
        l_pos := l_pos + 1;
        l_token_complete := true;
        l_enclosed := false;
      end if;

    elsif l_char in ( CARRIAGE_RETURN, LINE_FEED ) and NOT l_enclosed then
      l_token_complete := true;
      l_line_complete := true;

      if l_lookahead in ( CARRIAGE_RETURN, LINE_FEED ) then
        l_pos := l_pos + 1;
      end if;

    elsif l_char = p_delim and not l_enclosed then
      l_token_complete := true;

    elsif l_pos = dbms_lob.getLength( p_clob ) then
      l_token := l_token || l_char;
      l_token_complete := true;
      l_line_complete := true;

      l_token := l_token || l_char;
    end if;

    -- process a new token
    if l_token_complete then
      l_columnno := l_columnno + 1;
      l_token := null;
      l_enclosed := false;
      l_new_token := true;
      l_token_complete := false;
    end if;

    -- process end-of-line here
    if l_line_complete then
      dbms_output.put_line( '-----' );
      l_lineno := l_lineno + 1;
      l_columnno := 1;
      l_line_complete := false;
    end if;
  end loop;
end parse_csv;


  l_clob clob :=
    -- DOS EOL
    -- Apple up to OS9 EOL
    -- Acorn BBD and RISC OS EOL
    -- Unix and OS X EOL
    -- Line with EOF only
  parse_csv( l_clob );

And when I run it I get…

R1C1: A
R1C2: B
R1C3: C
R1C4: D
R1C5: E
R1C6: F
R1C7: G
R1C8: H
R1C9: I
R2C1: 1
R2C2: 2,3
R2C3: 1"2
R2C4: "4,
R2C5: ,5
R3C1: 6
R3C2: this is a "test
R3C3: ",8
R3C4: 9",
R3C5: 10,"
R4C1: normal
R4C2: commas,,,in the field
R4C3: "enclosed"
R4C4: random " double " quotes
R4C5: commas,,, " and double "" quotes
R5C1: ,F"
R5C2: **null**
R5C3: **null**
R5C4: **null**
R5C5: abcde





Email BLOB Attachments From The Database

December 1, 2009 34 comments


For this example we have a table

create table assets(
  id number primary key,
  name varchar2(100),
  content blob
  mime_type varchar2(100) );

And the code

procedure mail_asset(
  p_asset_id number,
  p_from varchar2,
  p_to varchar2,
  p_subject varchar2,
  p_message varchar2 ) is
  l_asset assets%rowtype;
  l_blob blob := to_blob('1');
  l_conn utl_smtp.connection;
  l_raw raw(57);
  l_len integer := 0;
  l_idx integer := 1;
  l_buff_size integer := 57;
  l_boundary varchar2(32) := sys_guid();
  l_attachment_name long;

  -- Connect
  l_conn := utl_smtp.open_connection( '' );
  utl_smtp.helo( l_conn, '' );
  utl_smtp.mail( l_conn, p_from );
  utl_smtp.rcpt( l_conn, p_to );

  -- Header
                               utl_tcp.crlf );
                               utl_tcp.crlf );
                               utl_tcp.crlf );
  utl_smtp.write_data( l_conn, utl_tcp.crlf );

  -- Body
                               utl_tcp.crlf );
  utl_smtp.write_data( l_conn, utl_tcp.crlf );
  utl_smtp.write_data( l_conn, l_message || utl_tcp.crlf );
  utl_smtp.write_data( l_conn, utl_tcp.crlf );

  -- Fetch the asset info
  for c in ( select *
               from assets
              where asset_id = p_asset_id ) loop

    -- Compress if a content and not already zipped.
    if ( c.mime_type != 'application/zip' then
      utl_compress.lz_compress( src => c.content,
                                dst => l_blob );
      l_attachment_name := || '.gz';
      l_blob := c.content;
      l_attachment_name :=;
    end if;

    -- Attachment
                                 || utl_tcp.crlf );
                                 utl_tcp.crlf );
                                 || utl_tcp.crlf );
                                 utl_tcp.crlf );
    utl_smtp.write_data( l_conn, utl_tcp.crlf );

    -- Loop through the blob
    -- chuck it up into 57-byte pieces
    l_len := dbms_lob.getlength(l_blob);
      utl_smtp.write_raw_data( l_conn, utl_encode.base64_encode(l_raw) );
      utl_smtp.write_data( l_conn, utl_tcp.crlf );
      l_idx := l_idx + l_buff_size;
    end loop;
    utl_smtp.write_data( l_conn, utl_tcp.crlf );

  end loop;

  -- Close Email
                                         utl_tcp.crlf );
  utl_smtp.close_data( l_conn );
  utl_smtp.quit( l_conn );

  -- smtp errors, close connection and reraise
  when utl_smtp.transient_error or
       utl_smtp.permanent_error then
    utl_smtp.quit( l_conn );

end mail_asset;

Just a few things about the code.






Content-Type: multipart/mixed;

^ (single leading space)




Enhanced APEX Shuttle Item

April 28, 2009 34 comments

Username/Password: demo/demo


How It Works.




function populateShuttle(filter,shuttleName)


  var req = new htmldb_Get( null, 19903, 
                          'APPLICATION_PROCESS=populateShuttle', 0 );


  var resp = eval(req.get());


  if (resp)

  req = null;




  l_selected long  := wwv_flow.g_x01;
  l_search long := wwv_flow.g_x02;
  for c in (
    select ename, empno, rownum r
      from emp
             '{ id: ' || c.empno ||
  end loop;

It fetches all the values that match the filter


but not those already be selected by the user.






var resp = eval(req.get());


  if (resp)



Pipelined Functions

December 5, 2008 4 comments




create table opportunity(
  name varchar2(100),
  product_ids varchar2(255)



I came up with two possible solutions:

1. Build the query by hand.


  l_ids apex_application_global.vc_arr2;
  l_query varchar2(32767) := null;
  l_ids := apex_util.string_to_table( :p9_product_ids );
  for i in 1 .. l_ids.count loop
    l_query := l_query || <insert query here>;
    if i <> l_ids.count then
      l_query := l_query || ' union all ';
    end if';
  end loop;
  return l_query;



2. Use a pipelined function.


create or replace type myTableType as
table of varchar2(255)
create or replace
function to_myTableType( p_string varchar2 ) 
return myTableType PIPELINED is
  l_arr apex_application_global.vc_arr2;
  l_arr := apex_util.string_to_table( p_string );
  for i in 1 .. l_arr.count loop
  end loop;



select, o.product_ids
  from opportunity o,
        table( to_myTableType( :p1_product_ids )) p

Check out a working version here:


WebLogic Server and APEX

September 15, 2008 7 comments

A good friend and colleague of mine at Oracle, Mark Greynolds, shared with me his work with WebLogic and APEX and getting them to work together. I thought it was something that everyone would be interested in and asked him if he would be willing to document the steps and allow me to post them here on my blog. He was and so here it is.

WebLogic Server and APEX

When a WebLogic Server (WLS) is the primary Web server, accessing APEX pages though the WLS requires a proxy. The configuration of APEX generally follows one of two configurations – Apache with mod_plsql or the Embedded PL/SQL gateway. When WebLogic (without Apache) is the main HTTP server, getting APEX to surface on the same port as WebLogic requires some form of proxy.

Overall Approach

This solution creates a very simple Web Application that invokes a Java Proxy Servlet when a user tries to access APEX pages. Wrapping this Web Application around the Java Proxy Servlet lets the WLS serve APEX without any port conflicts. The WLS Proxy Servlet is a fully documented out of the box tool. To create and deploy the Web Application simply build the files outlined in this document, deploy the application and then access APEX.

Exploded deployment

For convenience, this solution takes advantage of the exploded deployment feature of the WSL. In addition to the ability to deploy jar, war and ear files, the WLS can deploy Web Applications as an exploded directory that contains the same contents of the archive file. An exploded archive directory contains the same files and directories as a jar archive. However, the files and directories reside directly in the file system instead of as a single archive file (JAR). This example uses the exploded deployment style to create the Web Application for this example.

Default WebLogic application

The default Web Application is what the server sends to browser clients who do not specify a recognized URI (or specify only “/” as the URI). Each server has only one default Web Application and for this solution to work, this application must be set as the default. If there is already a default, this servlet could be added to the existing application by using an exploded deployment of the default with modification to the web.xml to register the APEX proxy.


1. An Oracle database successfully serving APEX pages. The APEX instance may be on the same or different machine and served from either Apache or the Embedded PL/SQL gateway. In this example, APEX uses the Embedded PL/SQL Gateway of a database running on the same machine as the WebLogic server and natively appears at the http://localhost:8080/apex URL.

2. An Oracle WebLogic Server 10.3 running a Node Manger, the Administration Console and a Managed server. This example uses a domain created specifically for this exercise named APEXDemo. The WLS Administration console uses port 7001 and the Managed Server uses port 80.

3. There is no other “default” WebLogic application for the Managed Server.

Create the APEX Proxy Servlet

1. Create the following directory structure somewhere on disk. This example assumes the C:\ drive is used. Note: the apexproxy.war directory name mimics the normal J2EE naming convention for Web Application archive (WAR).

2. In the apexproxy.war directory, create the index.html file. The WLS Managed Server renders this page when the server cannot map the browser’s URL to a valid destination. For this example, APEX becomes the default page due to a simple redirect to the full APEX path.

    <meta http-equiv="refresh" content="0;url=apex">

3. In the WEB-INF directory, create the web.xml file that defines this simple Web Application.

  <display-name>APEX Proxy</display-name>

The <servlet-class> tag identifies the proxy servlet class and provides the
redirection URL to use when the container invokes the servlet. The two
<servlet-mapping> tags explicitly identify the two URL patterns used by APEX.
The WLS documentation suggests using a single mapping of “/” but this causes every
unmatched request to forward to the Oracle XML DB default splash page and ignores the
index.html file.
4. In the WEB-INF directory, also create the following weblogic.xml file. At deployment, WLS scans this file for the information on how to configure the deployment.


The <context-root> tag is the key for making this example function. This tag
makes this the default application for the Managed Server.

Deploy the Proxy Servlet

5. Begin the deployment by accessing the WLS Administration Console using the http://localhost:7001/console URL. For this example, the Administration Console’s Username and Password are weblogic and weblogic.

6. On the left side navigation panel, click the Deployments link.

7. Click the Install button.

8. Use the Path: field to locate the C:\APEXProxy directory. NOTE: this page can
navigate directories on the drive by entering C:\ and clicking the Next button. The server
returns an error and shows the directories in a navigable list.

Click the radio button next to the apexproxy.war directory name and then click the
Next button. NOTE: do not type in the complete path to the war directory – clicking the
radio button for the WAR automatically completes the Path specification.

9. Click the Next button to accept installing the deployment as an application.

10. Select the desired server and click the Next button.

11. Click the Finish button to accept default values for all the remaining deployment values
and finish the deployment.

12. The Administration Console displays the configuration page for the newly deployed
application with the following messages.

Lower down the page, the server display the status of the new application.

13. Enter the Managed Server URL in to a browser to see the APEX login page.


The browser receives a redirect from the index.html page and displays the APEX login

Categories: APEX, Oracle Tags: , ,

Report Hierarchical Data With APEX

September 5, 2008 12 comments

Anyone familiar with Oracle and hearing the word hierarchical immediately thinks of the sql CONNECT BY clause.  And I would bet that when they think about displaying this data, they would use some sort of tree widget.  APEX has a built-in tree widget and for many applications, it works fine.  

The drawback to the built-in tree widget is that it brings back the entire dataset on the initial page render.  Good and Bad.  Saves round trips to the server, but could take a lot of initial time depending on the size of the data.  Showing/hiding branches of the tree with many elements also can make the application feel sluggish.  

On my current project, I needed to build an interface against just such a dataset.  So I needed an alternative to the built-in tree.  I looked at either writing my own tree using javascript and AJAX or using an already developed one.  Neither one appealed to me.  They both added development complexity and maintaining them weighed on my final decision.

What I chose to do was build a breadcrumb-like report displaying the path traversed and a second report displaying the elements at the current level.  All default APEX functionality.  Simple clean interface.  Easily developed and easily maintained.  You can check it out here.

Let me quickly walk you through how it was built:

1. Create a report on the base table constrained by the hidden item.  The query should look something like:

select *
  from emp
 where nvl(mgr,-1) = :p5_empno

I then linked the ENAME column back to the same page passing the EMPNO value into :P5_EMPNO.
2. Create a hidden Item to hold the manager’s id.  I put it in the step 1’s report region and called it P5_EMPNO and set its default value to -1.

3. Create a PATH report to manage the traversing the data.  This is where the magic happens.  I make use of the SYS_CONNECT_BY_PATH() function in conjunction with the START WITH…CONNECT BY clause. The query I used was:

select '<a href="f?p=' || :app_id || ':5:' ||
         :app_session || 
         '::::p5_empno:-1">Top</a> >>> ' || 
             :app_session || '::::p5_empno:' || empno ||
             '">' || ename || '</a>', ' : ' ), 4 ) path 
  from emp 
 where empno = :p5_empno 
 start with mgr is null 
connect by prior empno = mgr

Some other tweeks to this region. No Pagination. Report Template of Value Attribute Pairs. Layout above the first report region. No region template.

4. ???

5. Profit!

Now you can use the main report to drill into the children of the row you selected, all the while maintaining the context of where you are in the hierarchy with the path.

It’s a quick simple technique, and 100% APEX. No additional javascript libraries or custom coding to accomplish a clean and simple interface. And it will be easily maintained, either by you, or by the developer that comes after you.

Let me know what you think.

Categories: APEX, SQL Tags: , , , ,

Regular Expression Searching With APEX

August 26, 2008 1 comment

Ever since Oracle introduced regular expression functions in the database, I have been a big fan of them.  They really make certain tasks much easier and give you added functionality.  One place I always used them is in my APEX apps where I supply the user with a search box to constrain the results of a query.

The old way I would have written the query constraint:

where upper(COL1) like ‘%’ || upper( :P1_SEARCH_TEXT ) || ‘%’

Now using REGEXP_LIKE() you can achieve the same functionality while simplifying the constraint.  And you get the added bonus of advance searching for the power user.

where regexp_like( COL1, nvl(:P1_REGEXP,COL1), ‘ix’ )

The first thing you will probably notice is that there is no conditional operator in that expression.   None is needed.  The regexp_like function is a boolean function and is used as such.

So now, any basic search will work just as before.  But now, you can issue regular expression searches as well.  If you want all the entries that start with S, search for ^s

End with R, use r$

Start with S and end with H, use ^s.*h$

Start with S or end with R, use ^s|r$

All 4 letter names, use ^….$

Contains B, C, D or K, use [b-d,k]

How about all names with double letters, use (.)\1

There are many more things you can do.  If you want to read up about regular expressions in oracle, docs can be found here.

Using regexp_like() opens up the advanced searching for all the regular expression junkies without compromising the simple searching and it add NO complexity to your applications.

I have staged a simple demo on, here