Oracle slow CLOB / STRINGS

For those of you that have used oracle string functions such as SUBSTR you'll know that it can end up being very slow if you are trying to repetedly scan through long strings of data.

I sometimes use this kind of function when looping through rows of a CSV or XML file, it gets increasingly slow as it gets further in to the string.

To get around this once you have processed a row of a CSV file chop the bit you have finished with off and then there is not as much to search through next time around.

Oracle Apex Reports

In Oracle Apex 3 (possibly 4) there are a number of views that allow you to query the tables that form the apex pages, this includes vies of forms, items, buttons etc.

I've found this particularly useful when looking for particular bits of SQL and PL/SQL code where it is not practical to look through all of the pages.

To list the apex tables run this query on the system schema:

select * from ALL_VIEWS

where VIEW_NAME LIKE 'APEX_%'

and the views that I've found most useful are the APEX_APPLICATION_PAGE_* tables.

Exception Exception!

Exception messages can be less than helpfull sometimes so for that extra bit of information user

DBMS_UTILITY.FORMAT_ERROR_STACK

Within your exception statement to provide further information about the location of the error.

Oracle Portal Session Variables

Because of the way Oracle portlets work with Oracle Portal you can't use standard PL/SQL package session variables.

Instead you need to use the built in Portal session API wwsto_api_session.

Firstly declare a variable to act as an object to store the session data in.

l_store portal30.wwsto_api_session;

Then load your session, if it doesn't exist yet it will get created. Domain and subdomain do not have anything to do with web addresses but instead would be better described as a unique reference, I would define the domain as the application and the subdomain as a page within the application.

l_store := portal30.wwsto_api_session.load_session ('DOMAIN', 'SUBDOMAIN');

Then you probably want to save some data to your session, you do this by defining a attribute name and value in set attribute.

l_store.set_attribute ('NAME', 'VALUE');

If you want to grab a variable that has been set then you would use get_attribute_as_varchar2, there are also date and number variations of this API function.

my_var := l_store.get_attribute_as_date ('NAME')

If you've set any attribute variables then you need to make sure you save your session

l_store.save_session;

The session and session variables will automatically get dropped when you log out of Portal or close down your browser, you can manually drop the session with.

wwsto_api_session.drop_session('DOMAIN','SUBDOMAIN');

Finally if you want to grab your session id you can use.

wwctx_api.get_sessionid

Croc Clips

Was thinking about electrical circuits today (as you do) and the thought look me back to school days where we used an application called Crocodile Clips. I remember hours of fun being had coming up with random electronic circuits using this application.

The great news is this program is still around, although now it's called "Yenka" and it's free for home use. So if you have kids that need to use it for school projects or you just want to have a play with it it's free for use at home.

Even better it now includes gears & cog, scientific and mathematical features.

Check it out here.

Conditional Unique Constraints

Well here was the chalenge, I wanted to be able to mark a record as deleted, what is so tricky with that?

Well the record has a unique constraint on everything apart from the id column and the delete column, so if I want to insert an identical row again it will throw up a uniqueness exception. If I've marked a row as deleted it still wont let me insert the same row again, so you would not be able to insert row 2 in the following example:

ID	data	delete
1	ud1	1
2	ud1	0

So what you need is a Function Based Index, FBIs have been around since Oracle 8i so they are not a new thing. A normal unique index will allow you to create a unique constraint on multiple columns however a unique contraint using FBIs will only allow one input so you have to concatonate all of the columns together into one string.

Anyway this is some code that should get you up and running, I only used one condition to test the delete column but you could use as many a you want to really:

CREATE UNIQUE INDEX U01
ON [ TABLENAME ] (
CASE WHEN [ CONDITION 1 ]
THEN [ COLUMN 1 ]||[ COLUMN 2 ]||[ COLUMN N ]
CASE WHEN [ CONDITION 2 ]
THEN [ COLUMN 1 ]||[ COLUMN 2 ]||[ COLUMN N ]
ELSE
NULL;
END);

Server Side Image Map

I found an interesting PL/SQL feature today, when you use the

htp.formImage

procedure in a PL/SQL form it will return the value of the form image and also the coordinates of the click made on that image, so you get 3 variables returned.

• name - the value
• name.x - x coordinate
• name.y - y coordinate

So you could then use this information to create server side image maps, these could be created dynamically from a database table and updated easily by users.