I didn’t provide any context around that last post and thought maybe I should.

In my shop, we provide the backend api support for our corporate systems web team. Basically, they build the interface and we build the data structures and primary engine components. One popular thing always needed was the demographic data itself. I started to see the trend of information being requested and opted to build a common api set to deliver that information. I built in some definition parms so that I could use it for different feeds, each with its own attributes.

However, for one particular need they needed to provide a list of values. So I leveraged associative arrays. I take the data in and dump into a temp table and run my query e.g. where id in (select value from gtt). All of these different types of ’search conditions’ are stored in a table and the web simply identifies the type of search and provides the list. I build the piece onto the base query dynamically and execute. This keeps me from having to build multiple new apis, I just add another search type entry to the table and the SQL context that goes with it. Works great… till yesterday when they wanted to do LIKE.

Since the inception of the original api set, I had created a criteria grid method which handles it just fine and more. That logic can be found here under the Dynamic SQL and Variable Parameter Input. However for them to switch to the new api, would have risked the go-live date which was in a few weeks.

Instead… after a bit of thinking… it came to me to use the approach I provided here and it resolved the issue. There… that explains the why a bit better.

P.S. If more time was permitted, I would steer them towards the criteria grid due to the substantially increased flexibility. Just my 2 cents for what its worth.

Question came across my desk today that I never had really considered... can you do a LIKE condition against an IN list of items ?

e.g. SELECT column1,column2
FROM tablename
WHERE 1 = 1
AND column3 like ('THIS%','THAT%','AND%','THEM%');

So I am thinking... I don't think so... but then I got to thinking...maybe, just maybe.

1) Insert the values into a global temp table without the wildcard first.... could be real table, but I often like temp tables.

2) Write the following SQL. Notice we concatenate the wildcard to the SQL

SELECT x.column1,x.column2
FROM tablename x
WHERE 1 = 1
AND EXISTS (SELECT 'x'
FROM t_global_table gtt
WHERE 1 = 1
AND x.column3 like gtt.columnA||'%')

3) Run the SQL... and it works like a charm... not necessarily the best performer, but it works.

Needless to say I was surprised. Hope you all find some use for it and maybe someone else has some other ways of doing the same thing... drop me a line.

Excerpt from Oracle’s documentation regarding Result Caching. I was very excited by this feature initially, but after reading further and its limitations… my excitement dimmed somewhat.

“The PL/SQL function result caching mechanism provides a language-supported and system-managed means for caching the results of PL/SQL functions in a shared global area (SGA), which is available to every session that runs your application. The caching mechanism is both efficient and easy to use, and it relieves you of the burden of designing and developing your own caches and cache-management policies.”

Click here to view complete document.

What exactly lessened my excitement ?

Restrictions on Result-Cached Functions

So is it a cool new feature yes… but with what appears to be several limiting factors. Are these things to be resolved and enhanced in future versions, or are there key technical reasons for the restrictions. At this point I am too new to the concept to answer either way. Hopefully I will gain some insight later, or if I am lucky the PL/SQL Programming (O’Reilly) 5th edition will cover it. NO its not out, but something tells me its around the corner in the near future.

For details on the restrictions read on.

Pages: 1 · 2

Question came up from a customer today requesting the ability to link to a Lotus Notes database form upon receiving an email with the associated link embedded in it. More specifically that the email is generated from a source outside of the Lotus Notes environment itself.

My first thought was a resounding NOPE… CANNOT BE DONE, however my response to the customer was more in the form of :

“Although there may exist a technical solution, it is not part of the IT Standards and Best Practices” to facilitate this type of functionality. There exists other standards in place that would be followed instead.”

Needless to say this did not go over well with the customer, who asked the question again…but differently. Realizing they were not going to let this one go, I did some more research. I decided I would share this research with everyone else also.

Much of my research had me landing here at http://www.alanlepofsky.net.

NOTE : This only works for emails received into the Lotus Notes client itself. You cannot just type it into your favorite browser and expect it to launch your Lotus Notes and drive to the requested destination. I am pretty sure that would violate most security and audit provisions regarding the execution of host applications via web page actions.

Pages: 1 · 2

Recent project requirements had presented us with a scenario where the population had informational columns that varied on a per person basis, when the population changed the columns would change. Pivot table appeared to be the best option, but we need to respond to the requirements. To further complicate things this was for a real-time web application. So it needed to be fast.

So after some scanning, found the following information, which allowed us to build a prototype.

Click here to download the sample script.

While out scanning the internet, I ran across an Oracle website with a variety of interesting features. If you have some free time, this might be just what you were looking for.

* Updatable External Tables
* Pie Charts in SQL*Plus
* Bar Graphs in SQL*Plus
* Hierarchical queries
* SQL Developer (formerly known as Raptor) by Oracle
* PL/PDF (Doc to PDF for PL/SQL environment)
* XML Publisher
* How ROWNUM can help tune a query
* PL/SQL Source Control Code in the Database
* Using PL/SQL Collection return a REF CURSOR
* Debugging PL/SQL with DBMS_DEBUG
* PLDOC, documenting your PL/SQL directly from the database
* Publishing PL/SQL Based Web Services
* Consuming Web Services from PL/SQL - Part II: A pure PL/SQL solution using UTL_HTTP (Oracle 9i+)

Enjoy !

Extending the functionality of SQL-NVL. This is pretty old, and most likely replaced by 10g functionality. However for those on older database versions these may be very useful.

Pages: 1 · 2

Nice extension of CLOBs that you can try to leverage in your design.

• outputs the content of an CLOB to the DBMS_OUTPUT
• converts a table of VARCHAR to CLOB
• converts a LONG to a table of VARCHAR
• converts a LONG to CLOB
• converts a CLOB to a table of VARCHAR
• converts a CLOB to LONG

Click to download code

Everyone knows the standard aggregate functions (sum, avg, and so on).
But they always don’t meet your needs. For instance, what about a product aggregate function?
This tip will show you how to add your own aggregate function. This is quite simple and can be done with a function and a type.
Note that’s available only in 9i+.

I’ll take the example (which is meaningless) of a square product; that is the square root of the product of all elements by group of rows.

Pages: 1 · 2 · 3 · 4

LONG RAW columns are not normally searchable being that they contain binary data and can also be quite large only to be compounded by the fact that Oracle doesn’t provide very many useful ways to look at large LONG RAW data fields.

Converting a LONG RAW to a BLOB makes searching possible. Using the below script you can scan for a particular search string among a group of docs or just one select document id.

Click to download script

The Microsoft SYLK (Symbolic Link) format is used mainly for the interchange of spreadsheet data between applications such as Microsoft Multiplan and Excel. Files in this format might also be imported directly by business graphics applications. SYLK files are written entirely in ASCII and, like Lotus DIF and SDI, are application-independent. SYLK, however, incorporates several features not found in other spreadsheet data interchange formats.

Click to download code

A somewhat 'popular' download here is the Mail Tools package. This package supports sending emails with and without attachments (binary/ascii). One of things I have been meaning to do is leverage our Oracle Applications environment and build concurrent programs to run these mail routines. In a sense a wrapper call.

To note, in the source code there are some references to some custom functions which you can either change to your own code or send me an email and request additional information on the specific call or code you are interested in.

To test or use, just build an appropriate PLSQL code block or setup a concurrent executable and concurrent program in the application. If the latter, integrate into your request sets.

Pages: 1 · 2 · 3 · 4

A virtual toolchest of coding utilities. I have been building this collection since 2000 and do to this day. I have collected these utilities from various locations, and built others on my own. I share them with others at no charge other than that if they improve or add to them, I would be very interested in your improvements and subsequent integration into my toolchest.

Click here to download code

Okay, I would never profess to be an expert at XML. Far from it. However, I have been dabbling with Oracle’s XML calls. I have put together a set of open api’s that permit me to receive an XML transaction, extract and parse the header, and then extract and parse the records contained within the xml transaction file.

And you wonder why this is a big deal to me… good point. Well my previous efforts resulted in memory issues when working with large XML files (in excess of 40 mb). I didn’t have the option of adding a java parser to my environment as was often suggested. What I ended up doing was pulling smaller bits of the transaction into memory from a transaction stored into a transaction log table. By doing this I was able to process the data more efficiently without burdening the system.

I am wrapping up a few pieces of it and then I will post it here for interested parties to review and comment on. Please do your best not to laugh at my efforts. LOL. However, I am trying, that much can be said. Until then… if you have any thoughts on processing XML or sample code, please feel free to post and share with others.

Pages: 1 · 2 · 3

Periodically you may run into a situation where it is beneficial to create a view that permits parameters to be passed to it. Views by design do not provide such functionality. However, by using the client_info portion of the DBMS_APPLICATION_INFO you can accomplish this.

Pages: 1 · 2

O'Reilly has published the 4th edition copy of their Oracle PL/SQL Programming book authored by Steven Feuerstein and Bill Pribyl. Over 1000 pages of good material for an Oracle Developer.

It is also my 1/15th seconds of fame. Through a collective effort of research on ways to build a FTP process from pure PL/SQL code, yet never finding one specific way that satisfied my need, I finally came up with a final product that gave me everything I was looking for : Put/Get/Rename/Delete + more. All of which was performed within a PL/SQL package.

Pages: 1 · 2

Multi-threading can be accomplished at many levels

- Separate stages that run parallel to each other, in Concurrent Manager request sets
- Using DBMS_JOB.SUBMIT at the database level
- Compiled Pro*C files (Oracle does this with PYUGEN for example)
- Unix background processes
- Scripting language such as Perl
- Java
- JSP
- etc

However, in the wise words of someone else I know, sometimes the simpliest answer achieves the desired goal. Shell Scripts, when you get down to it, are pretty simple. No real complex logic in most cases.

Pages: 1 · 2