Anton Nielsen

SOAPEX at OOW 2011

2011-10-03T14:48:00.000-07:00

Recently, I've been doing a lot with Oracle Application Express (APEX) and web services. At Oracle OpenWorld I came across a presentation on just this topic. Douwe Pieter van den Bos, an Oracle Ace, presented on using APEX with the Oracle SOA Suite. I have used SOA Suite in the past, and quite like it, but recently the web services I've been using are SOAP based services that I have very little influence on. They are not built with or deployed on the Oracle SOA Suite.

The SOAPEX presentation gave a nice overview of how to set up APEX to use web services and made the smart recommendation to build your web service references in a single application (SOAPEX) and then use the inherit/subscription model of APEX to keep things up to date.

My specific challenges have been a little more difficult, specifically in the need to consume very large and complicated web services--so complex (or possibly overly complicated) that APEX is unable to parse the wsdl. More on this later...

Oracle 12c Database

2011-10-03T14:40:00.000-07:00

It's definitely about the cloud at Oracle OpenWorld 2011. In many ways Oracle has always promoted it's database in the private cloud--long before the term cloud (private or public) became popular. At previous OpenWorlds, Larry Ellison poked fun at the cloud, noting that the notion isn't new. Well, even Oracle must bend to the popularity of the term cloud. I haven't yet heard an official name for the next database release, but I'm betting on Oracle 12c.

It's Been a Long Time

2011-03-30T18:46:00.000-07:00

My last blog post was a tribute to Scott Spadafore. A year and a half before, I also offered tribute to another close friend and APEX guru, Carl Backstrom. I have had a hard time getting past the loss of these two friends. Such a hard time that this is my first post in over a year--my first since Scott's haiku. I have started to write many posts, but none seemed worthy of moving Scott's haiku down the page. Scott was a pillar of the Oracle community--not just Application Express, though he certainly dominated that arena.

The passing of these two friends was a loss to many others as well. Several months ago John Scott had a grand idea. He gathered together over a dozen people who had benefited from the work of Carl and Scott, who had enjoyed success because of their efforts, and had become friends with Carl, Scott and each other through the APEX community. John suggested that we jointly write a book, in Carl and Scott's memory, and donate the author royalties to the funds established for Carl's and Scott's children. I was honored to be included in this group.

John's blog provides the full list of authors and more info about the book, Expert Oracle Application Express. I offer my thanks to John for coordinating this project, and to all of the contributors.

Haiku Two

2010-03-22T08:53:00.000-07:00

In November 2008 I offered a haiku in Carl Backstrom's memory. That post referenced texting a haiku. That text originated with Scott Spadafore. It is with great sadness that I offer haiku two in Scott's memory.

spring leaps forth
though warmth, shining sun
brilliance lost

Thank You Granny!

2010-03-09T07:54:00.000-08:00

My wife recently took a look at this blog and told me that it would be better with pictures. She also suggested that not every post had to start with: If you are installing/configuring/coding with the Oracle product abc and you get error ORA-nnnnn . . . With that in mind, I share the following.

I used to commute to work by bike most days, but I was out of commission for about a year. Today was a beautiful morning, just right to get back on the bike for a ride to work. After a year off it was a bit of a shakey start. Just figuring out where my gear was. Pairing down my now typical road warrior gear to the bare essentials (for example, a 4 port switch instead of the 10 port). Stuffing everything into a new bag. Hoping my lunch would not spill out into traffic.

It used to take me 26 minutes door to door. This morning was a little slower. Which brings me to the title of this post. Below is a shot of my Campy Chorus Racing Triple.

A Campy Racing Triple is for people who want to think they are still fast, but realize their lifestyle will include occasionally towing a trailer with the road bike, possibly a child seat. That small ring is known as the Granny Gear. It's for those times, for people like me. It's not for a commute to work, mind you. It's for carrying heavy loads, for extreme circumstances. Note that the photo does not show me using the granny. That is, there is no actual evidence that I actually resorted to it today. The shot below is merely circumstantial.

That says 41minutes, 51 seconds. Granted, that included finding my lock in my stuffed bag and locking up, but with all the excuses I can muster, it is still sloooow. I think of it this way, though . . . By car it takes 28 minutes. That means I spent about 14 minutes this morning just doing something I enjoy. Everyone should get 14 minutes a day to do something they enjoy. And, I get another 14 minutes this evening on my way home. I wish you the same.

APEX Refresh Classic Report Region AJAX style

2010-02-25T11:49:00.000-08:00

We often have the need to refresh a classic report region, AJAX style. It is straightforward to get a refresh link on the page. I used to just build a link using $a_report (the APEX built-in for doing partial page refresh on classic reports). But I have found it is better to create a javascript function in the region header or footer. This has the advantage that you can call it from a button on the page, or from any location on the page, not just from within the header or footer itself. If you put the following

<script type="text/javascript">
function c2RefreshTasks(){
pId = '#REGION_ID#'; // report region id
$a_report(pId.substring(1),'1','15'); // APEX built-in
}
</script>

This allows me to put a link anywhere on the page

<a href="javascript:c2RefreshTasks();">refresh tasks</a>

I can also create a standard button anywhere on the page that calls this javascript.

I recently also had the need to pop a new window (child), add a task in that child window, close the child and then refresh the task region in parent window. It turned out to be easy...

Just create the APEX child window that does the insert/update.  Have it branch to a page (e.g. P99). On P99, put the following in the HTML header

<script type="text/javascript">
// important to have the try because the parent window might have changed...
{ try {
window.opener.c2RefreshTasks();
}
catch(err) {
window.close();
}
}
window.close();
</script>

Oracle Application Express 4.0 (APEX 4.0) Early Adopter

2009-12-19T10:23:00.000-08:00

Just about everyone has already blogged about it, but APEX 4.0 EA is available now.
http://tryapexnow.com/
What else is there to say that has not already been said? Not much probably, but I'll point out one new feature: APEX 4.0 is not available in Internet Explorer. I have it on good authority that this feature will only be in the beta and will not make it into the production release--we can hope, though. (Thanks to Neelesh Shah for pointing out this new feature.)

Also, check out the new SQL Developer features:
http://krisrice.blogspot.com/2009/12/application-express-and-anonymous.html

And, of course, Patrick's sample plugin:
http://www.inside-oracle-apex.com/test-drive-oracle-apex-4-0-early-adopter/

ODTUG Kaleidoscope 2010: APEX Plugin Showcase

2009-12-03T11:27:00.000-08:00

One of the best new features of APEX 4.0 is the extensible plugin architecture. You will be able to create your own item and region types as well as custom dynamic actions (javascript enabled actions on the browser). You'll be able to add these plugins right in to the builder so they are available to all of your developers. You will be able to share (or even sell) these with the APEX community. This is big.

To get you started the fine folks at ODTUG are going to build five fantastic plugins and give them away to anyone who attends Kaleidoscope 2010. You can check out the details here.

The trick to writing a great plugin is to have a great idea. I'll have some input into the five plugins. If there is an APEX item type, region type or dynamic action that you think should be there but isn't, please leave me a comment and let me know.

Interesting APEX with dblink issue

2009-11-30T19:36:00.000-08:00

We recently ran in to a problem when connecting across a database link to a Postgres database. The query looked like this

select "column1", "column2"
from "my_schema"."my_long_named_view"@my_db_link

It works fine from sql*plus and sql Developer, but when running the same query in Application Express (in an app or in the SQL Workshop) we got this error:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Generic Connectivity Using ODBC]DRV_QspecDescribe: DB_ODBC_RECORD (189): ; [OpenLink][ODBC][PostgreSQL Server] current transaction is aborted, commands ignored until end of transaction block (SQL State: S1000; SQL Code: 1) ORA-02063: preceding 2 lines from MY_DB_LINK

Dave Rydzewski came up with the solution. Shorten the name of the view in Postgres and change the query to look like this (note two fewer double quotes "):

select "column1", "column2"
from "my_schema.short_v"@my_db_link

I'm still not sure what APEX does to make it blow up. I wonder how many people use APEX with a db link to Postgres.

VMware pc2mac

2009-11-24T20:07:00.000-08:00

I thought tonight was going to be the night. I purchased a macbook pro about 6 weeks ago. I've slowly been getting familiar with the mac, and realizing that I'll probably still need a PC image at least occasionally. Last night I installed VMware Fusion for the mac (nice that it's called Fusion, that way it almost has something to do with this Oracle blog). Tonight I was to create the image from my work laptop and test it out on the macbook. Alas, try as I might, I still don't have an image. This is my story.

I read the VMware readme (yep, I do that kind of thing). I learned about pc2mac, the utility that lets you stream the image right off your running pc and onto an image on your mac. I read the guides. I installed the pc2mac utility on my PC, restarted my PC and the VMware Fusion PC Migration Agent screen opened right up. I ran VMware on the mac and followed the instructions, typed in the four-digit passcode, the administrator password, clicked continue, and got an error:

Converter failed to connect to remote machine.
An error occured while transferring data

I tried a few times, but did not get any further. I decided to come back later, but first, I returned to the PC to close out the migration agent. Noticing the checkbox "Run the VMware Fusion PC Migration Agent Every time I start my PC", I realized that I probably don't want this to every time. I unchecked the box.

Returning later to try again, I could not find a way to start up that migration assistant. I googled. I rebooted, re-installed pc2mac, rebooted, uninstalled, rebooted, reinstalled, rebooted. Mind you, this was the PC I was rebooting--not a quick affair, much like this blog post. I'll cut to the chase, even an uninstall and reinstall would not bring up that screen again. I finally found the trick though. Edit the registry:

HKEY_CURRENT_USER > Software > VMware, Inc. > PC Migration Agent
Edit RunAtStartup and change from 0 to 1.

Of course, the change from 0 to 1 was just a guess, but it worked.

Now, at 11:06pm, I'm back to having a pc2mac utility that will run the first screen, but it still does not work. Now that's progress. Oh, and my wife just said, "How do I find your blog?" Good night everybody.

[Update: 25 Nov 2009, 4:53pm] I also had an issue logging an issue with VMware. The first problem was that I could not register the product. The VMware registration website kept giving an error that it was not a valid code despite the fact that it was in the email and the product installation accepted it. I was able to log a customer service issue about not being able to register, but I was not able to log a technical issue without first registering.

I called and spoke with an extremely helpful support rep. She told me they were having issues with some registration numbers and offered to create the technical ticket for me. She was so helpful that I went back to the VMware site to drop a note to anyone that would accept it. This is the note I typed:

I recently purchased VMware Fusion. I generally know my way around technology, but just registering my VMware software on the website was impossible (truly, as there is currently a bug in your system). After quite a bit of frustration, though, I finally called and spoke with a support representative. I'm sorry that did not get her full name, but she created a new SR for me, 1459858086, and it has been assigned to SGARDNER.

This support rep was very helpful, polite and overall reflected very well on VMware. Though my issue has not been resolved, I had a very positive experience speaking with this rep. If you have the ability to commend her I hope you will.

That's not bad, right? Unfortunately, though, it never went anywhere. The only page I could find a place type the note was here: https://www.vmware.com/contact/contact_sales.html
The page never allowed me to pick a Country or State. It just kept showing "loading..." without ever showing a country. Of course, those fields are mandatory.

Sorry friendly support rep, I'm afraid that note won't make it anywhere.

Looking for APEX Developers

2009-11-04T20:26:00.001-08:00

I have a client in the northeast of the US. If you are really good with the Oracle DB, pl/sql and APEX, and live or want to move to the US Northeast, send me your info and I'll pass it along. Please send an email with your resume to me (anton) at work (concept2completion.net).

Thanks,

Anton

Cloning an Oracle Schema

2009-09-30T14:23:00.000-07:00

When I started this blog I decided I would only blog about things you could not find reasonably easily with a simple google search. This post violates that rule. Cloning a schema is something that I do fairly frequently but not frequently enough that I remember the exact syntax. First I googled it every time, later I created a little text file with the commands. Having already gone to the trouble to write a text file, I might as well just paste it in here. I use datapump. In the example below I want to export the SCOTT schema which has all of its data in the USERS tablespace. I want to import it into a database (either the same or another Oracle database) as the user SCOTT2 in the tablespace USERS2. That means I need to change both the database schema and tablespace. Here are the commands (replace values as appropriate for your env):

export ORACLE_HOME=/opt/oracle/product/oracle10g
export ORACLE_SID=c2dev1
cd $ORACLE_HOME/bin
./expdp system/ dumpfile=scott.dmp schemas=scott

Note: this will create the file scott.dmp in the location $ORACLE_HOME/admin/dpdump. You can create a different directory for it, but for my purposes this is sufficient.

That is all it takes to do the export.
If you plan to import into the same database it can stay in the same location.

To do the import into a different database you will need to copy the scott.dmp file to the right location for the other database.

cp $ORACLE_HOME/admin/c2dev1/dpdump/scott.dmp /mynas/scott.dmp

Then I ssh to the target machine and copy to the new target location

cp /mynas/scott.dmp $ORACLE_HOME/admin/c2dev2/dpdump/scott.dmp

export ORACLE_HOME=/opt/oracle/product/oracle10g
export ORACLE_SID=c2dev2
cd $ORACLE_HOME/bin
./impdp system/ dumpfile=scott.dmp remap_schema=scott:scott2 remap_tablespace=USERS:USERS2

That should do it. This should make it easy for me to do in the future and hopefully help someone else along the way.

APEXposed 2009 - A request for input

2009-09-29T18:31:00.000-07:00

I will be speaking at APEXposed 2009 in Atlanta, GA on 10 & 11 November. Some of you have certainly seen the How to Hack an APEX Application presentation. I will be giving a revised version of that. It is difficult to find interesting things because the APEX developers keep adding features to make it harder for developers to get into trouble, but I'll have a few items of interest, plus the old standby's.

My second talk is APEX and the Oracle Database.
The power of APEX is partly the immense scope of capabilities present in the Oracle database. In this presentation I am going to show how to use many of these capabilities within APEX. Below are a few topics I have in mind.

Oracle Text (Intermedia)
Spacial
SQL Analytics
File Compression
owa routines
utl_inaddr
External Tables
Virtual Private Database

I'd love to get additional ideas--if you have any favorites, please let me know!

Migrating Portal Repository with change in DN

2009-06-10T14:15:00.000-07:00

Another in a long line of very esoteric issues...

If you are migrating a portal repository, possibly from Production back into Dev or vice versa you may run into the following error when running ptlconfig

STEP 1 : Populating Portal seed in OID
Connected.
Creating Lightweight User Accounts and Groups in OID
Portal schema version is: 10.1.2.0.2
Error code : -6502
Error message: ORA-06502: PL/SQL: numeric or value error
ERROR: creating lightweight users and groups in OID ... exiting

PL/SQL procedure successfully completed.

This happens if you have changed the base dn of your OID. For example, you might have had a dn of dc=concept2completion,dc=net
and then decided to change to dot com
dc=concept2completion,dc=com

or possibly you removed a sub-domain
dc=chicago,dc=concept2completion,dc=net
changed to
dc=concept2completion,dc=net

In either case, you will get the error listed above. The problem is that the script secoidd.sql relies on the dn stored in the table wwsub_model$. Below is an extract of secoidd.sql

if l_subscriber_dn is null then
-- The control should never reach here as the subscriber DN
-- should be available in the wwsub_model$ table.

So, if you get into this position, you will need to update the value in the dn column of wwsub_model$.

Seems like such a simple solution. So simple that I have now gone to the process of figuring it out three times.

SQL for Date Spanned Data

2009-06-08T10:58:00.000-07:00

I was recently presented with a sql challenge. Although I came up with something that works, I'm not convinced it is the best solution. This is a very long post that is mostly question, but possibly a helpful example. The details are below.

I was given a table with the following columns
ID -- Primary Key
DIVISION -- Identifier of a Division
START_DATE -- Date when the Division starts
END_DATE -- Date when the Division ends

If you want to try this yourself, I added all the create statements and sample data creation to the end of this note.

The data has an implicit concept of a "span." A Division may have many spans. The start date of a new span is typically one day after the end date of the previous span. In this case a Division has a contiguous span. Spans never overlap; that is, a single division never has a row with a start date between the start and end date of another row of the same division (and never has an end date between the start and end of another row of the same division).

Below is some sample data:

Id, Division, Start_date, End_date

10 1 09-JUN-2009 10-JUN-2009
11 1 11-JUN-2009 12-JUN-2009
12 1 13-JUN-2009 14-JUN-2009 -- Note the following row is not contiguous
14 1 17-JUN-2009 18-JUN-2009
15 1 19-JUN-2009 20-JUN-2009

If this were the total data set then Division 1 would have 5 spans, but only two contiguous spans (09 - 14 Jun and 17 - 20 Jun).

The challenge was to create a sql statement to only return contiguous spans by division. I immediately thought of a CONNECT BY but the challenge was to determine the START WITH condition.

[Update: In response to this post Alex Nuijten came up with a better method than any described below. Take a look here:
http://nuijten.blogspot.com/2009/06/analytic-function-finding-gaps.html ]
I came up with two ways to do this. I'm still not certain if there is a better solution. The first is far better and uses some SQL analytics to determine the START WITH condition. If you were to use a WHERE clause, it should go in the WITH section as identified. My example is below.

with span_info as (select s.division, s.start_date, s.end_date
, (s.start_date -lag(end_date, 1) over (partition by s.division order by start_date)) new_span
-- null or > 1 indicates a new contiguous span
, (lead(start_date, 1) over (partition by s.division order by start_date) - s.end_date) end_span
-- the end_span is not needed, just left for example
-- null or > 1 indicates the end of a contiguous span
from spantest s
-- a WHERE clause would go HERE, not in the main SELECT below
order by 1, 2 )
select division
, connect_by_root start_date span_start -- start of a contiguous span
-- , start_date -- start of the last span of a contiguous span
, end_date -- end of the contiguous span
-- , new_span, end_span, connect_by_isleaf
from span_info
where connect_by_isleaf = 1
-- gets only "leaf" recodes, i.e. the end of a contiguous span
start with nvl(new_span,1000) > 1
-- gets beginning of a contiguous span
connect by
prior end_date + 1 = start_date
-- connects spans, the 1 here and in the "start with" can be changed together
and prior division = division
-- ensures spans are for the same division
order by 1, 2

My second idea is to use an exists command to determine if the the first there exists a row with an end_date equal to start_date - 1 for the division. I can write the statement, but I'm not sure how to get the combination of the DIVISION and the START_DATE in to the START WITH. I could do some concatenation, but I don't like that at all. The code is below, but DO NOT RUN THE QUERY; it is very slow.

-- ************ DO NOT RUN THIS QUERY

select division
, connect_by_root start_date span_start -- start of a contiguous span
-- , start_date -- start of the last span of a contiguous span
, end_date -- end of the contiguous span
-- , new_span, end_span, connect_by_isleaf
from spantest
where connect_by_isleaf = 1
-- gets only "leaf" recodes, i.e. the end of a contiguous span
start with to_char(start_date,'yyyymmdd') ||'~' || division in
(select to_char(st2.start_date,'yyyymmdd') ||'~' || st2.division
from spantest st2
where not exists
(select 1 from spantest st3
where st3.division = st2.division
and st3.end_date = st2.start_date + 1
and st3.id != st2.id)
)
-- gets beginning of a contiguous span
connect by
prior end_date + 1 = start_date
-- connects spans, the 1 here and in the "start with" can be changed together
and prior division = division
-- ensures spans are for the same division
order by 1, 2

This is a lot of code and no-one other than me may ever read it. If you do, though, and have other ideas, I would love to hear them.

-- ********* Create Statements and sample data

CREATE TABLE "SPANTEST"
( "ID" NUMBER NOT NULL ENABLE,
"DIVISION" NUMBER NOT NULL ENABLE,
"START_DATE" DATE NOT NULL ENABLE,
"END_DATE" DATE NOT NULL ENABLE,
CONSTRAINT "SPANTEST_PK" PRIMARY KEY ("ID") ENABLE
)
/

CREATE INDEX "SPANTEST_IDX3" ON "SPANTEST" ("END_DATE")
/
CREATE INDEX "SPANTEST_IDX2" ON "SPANTEST" ("START_DATE")
/
CREATE INDEX "SPANTEST_IDX1" ON "SPANTEST" ("DIVISION")
/

CREATE SEQUENCE "SPANTEST_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1441 CACHE 20 NOORDER NOCYCLE
/

CREATE OR REPLACE TRIGGER "BI_SPANTEST"
before insert on "SPANTEST"
for each row
begin
if :NEW."ID" is null then
select "SPANTEST_SEQ".nextval into :NEW."ID" from dual;
end if;
end;

/
ALTER TRIGGER "BI_SPANTEST" ENABLE
/

-- create sample data for 10 Divisions
begin
for i in 1..20 loop
insert into spantest (division, start_date, end_date)
select 1, trunc(sysdate + (rownum*2 - 1)),trunc(sysdate + (rownum*2)) from all_tables;
end loop;
end;
/

-- create span gaps on the 15th of each month
delete from spantest where
to_char(start_date,'dd') = '15'
/

Locating a Corrupt PDF (or many types of corrupt files) in a BLOB

2009-05-06T13:57:00.000-07:00

I recently had a client pose the following question to me.

We have a system that generates pdf's based upon data from our Oracle database. The pdf's are then stored in a BLOB column in a table. We recently realized that at least one of those pdf's was corrupt. Is there a way within Oracle to determine if others are corrupt?

I initially thought about trying to write a script that would pull each blob, run it through some kind of pdf validator and write a log entry if it were bad. Hmmm, use plsql and call out to a validator on the O/S? Write a java routine to do the same thing? What validator would I use?

Then I had an idea--Oracle Text (formerly Intermedia and Context). I could create a context index on the blob column and see if there are any errors.

CREATE INDEX myIndex ON my_table(my_blob) INDEXTYPE IS ctxsys.context;

If the Oracle Text engine is unable to index the blob it writes a log entry to the view ctx_user_index_errors including the rowid. Depending on the database version you might have issues with very recent versions of the pdf, but that was not an issue in our case as every pdf was created by the same pdf generator.

I think Oracle Text is underused. This is just one more example of how to make it work for you.

Oracle SQL Delimiter

2009-05-02T10:12:00.000-07:00

It's been a while since I made a real post, one that has some technical merit. This one just barely qualifies.

There is a nice classic report type "Function Returning a SQL Query" which allows you to write a block of code that returns a select statement. That select statement is the basis for the report. I often use it to simplify building a where clause. It might look something like this:

declare
l_q varchar2(32767); -- This is the return variable. It will hold a select statement

begin
l_q := 'select d.name department, e.name employee
from dept d, emp e
where d.deptno = e.deptno ';

if :P1_DEPTNO is not null then
l_q := l_q || ' and d.deptno = :P1_DEPTNO ';
end if;

-- insert more if statements like above

return l_q;
end;

With Interactive Reports this has become less likely, but it still happens.

Sometimes, though, the query is a bit more complicated, something like this:

begin
l_q := 'select d.name department, e.name employee,
to_date(to_char(e.hiredate,''yyyymm'' ||''01''),''yyyymmdd'') first_day_of_month
from dept d, emp e
where d.deptno = e.deptno
and e.status in (''NEW'', ''PENDING'', ''FOO'')
and d.status = ''NEW'' ';

if :P1_DEPTNO is not null then
l_q := l_q || ' and d.deptno = :P1_DEPTNO ';
end if;

-- insert more if statements like above

return l_q;
end;

You get the idea, a lot of strings and a lot of double ticks (''). There is an easier way, though: a custom SQL Delimiter. Usually a single tick (') indicates the start of a string. If you use a tick within it, you need two ticks ('') to escape it (see code above). But you can avoid that with a custom delimiter. You turn it on with q', that's all, just the letter q followed by a tick. The trick is that the very next character is the delimiter, and a few are special. You end the statement with followed by a tick. Examples are the easiest, so here are a few:

1. l_q := q'! I don't need to escape the ' in the word don't !';
In this case the ! is the special character. q'! starts the string and !' ends it.

2. l_q = q'{ I don't need to escape the ' in the word don't }';
This is a little different. If your special character is { then you use }' to turn it off. It is pretty obvious which are special: [], {}, <>.

So, the code above becomes:

begin
l_q := q'{ select d.name department, e.name employee,
to_date(to_char(e.hiredate,'yyyymm' ||'01'),'yyyymmdd') first_day_of_month
from dept d, emp e
where d.deptno = e.deptno
and e.status in ('NEW', 'PENDING', 'FOO')
and d.status = 'NEW' }';

if :P1_DEPTNO is not null then
l_q := l_q || ' and d.deptno = :P1_DEPTNO ';
end if;

-- insert more if statements like above

return l_q;
end;

You can use this just about anywhere in sql or pl/sql where you want a string.

What is this?

2009-04-10T06:03:00.000-07:00

First, I apologize, this is my third post in a row that is not technical. I promise I will get back to the real purpose of this blog in my next post.

Now, on to the question. Does anyone recognize this device? I found it in my closet the other morning. I suspect I probably took it out of a bag after a trip, or picked it up instead of stepping on it in one of my kids' rooms. I probably knew what it was at the time, probably thought I would stick it there for a few days and deal with it later. Now it is later, though, and I have no idea what it is or where it came from.

It takes 3 AAA batteries. It has no identifying markings of any kind. Inside the battery compartment there is no writing. When I turn it on it makes a very high pitched, very low volume whining noise. There is nothing on the sides that are not shown in the two images above. You now know as much as I do about this thing.

If you know what this is, really know what it is, please post a comment. Thanks.

Update: We have a winner! I won't publish the answer here, in case you want to guess. Check out the comments for the answer.

Going Viral

2009-04-09T06:52:00.000-07:00

This is a chart of traffic on http://www.crapmanagement.com
As you can see, yesterday crapmanagement "went viral," more than doubling its former high (which was mostly me testing the web logging capabilities).

Oracle Portal and APEX RSS Feeds and Traffic Analysis

2009-03-25T12:40:00.001-07:00

I try to avoid advertising in this blog, but I'm going to break that rule as I have had a few inquiries lately on these topics. C2 has new product offerings that provide RSS feeds for Oracle Portal and APEX. For details, contact C2. C2 also has a web traffic capture and reporting tool. This tool can be used on any site, but has a few specific capabilities related to Oracle Portal and Application Express. Again, for details, contact C2.

And just an update on my last post -- the fine folks on the APEX team released patch 833890 to solve this import issue. You can get the patch at http://metalink.oracle.com.

APEX 3.2 Gives Error on Import - ERR-3331

2009-03-11T11:20:00.000-07:00

Just a quick note regarding the 3.2 import process... APEX added a number of enhancements and checks during the import of an application. The downside is that these introduced some negative performance characteristics. If you are importing a medium to large size app you are likely to get either a timeout or an error message:
ERR-3331 This page was already submitted and can not be re-submitted.

Oracle has confirmed the problem and a fix is in the works. I'll update this post when the patch is available.

The good news is that the application will import anyway. After you get the error, just go back to the builder and check to make sure the import was successful.

25-Mar-2009: More good news -- the fine folks on the APEX team released patch 833890 to solve this issue. You can get the patch at http://metalink.oracle.com.

APEX 3.2 - save state before branching

2009-03-09T11:06:00.000-07:00

Just a quick note about a little known new feature in APEX 3.2: save state before branching. When creating a branch and setting items with values, you now have the option to either pass those values in the URL or to have APEX set them into session state prior to doing the branch.

Application Express (APEX) 3.2

2009-03-05T04:54:00.000-08:00

We went through the upgrade to 3.2 last night and it was very smooth. We had a couple minor challenges but that is because of our unique environment.

1. We have an SSO configuration. APEX does not specify the owner of the package wwv_flow_custom_auth_sso when it generates the return link for SSO. This means that the APEX_PUBLIC_USER (or whatever user your DAD is configured to use) must have a synonym to wwv_flow_custom_auth_sso. You can also create a public synonym so long as one does not exist for another application.

2. I noticed that some of our custom stylesheets give a slightly different look with date picker items. This may be due to a change in the way APEX 3.2 generates date pickers. I'll look into it and post anything I find.

3. Apex has a new database schema. It has finally moved to APEX_030200. [ removed a comment about the version that was wrong... thanks Patrick! ]

A very easy install, really. Thanks to the APEX team once again!

APEX - Application Express 3.2, More than a Forms Conversion Release

2009-01-16T10:24:00.000-08:00

Application Express 3.2 has been advertised as a Forms Conversion release, but I've recently been looking over an additional category of enhancements that are due to be in the next early adopters release. It probably comes as not surprise to those who know me that the category is security. Below is a partial list of the enhancements as I understand them. Once I get my hands on the EA release I'll provide more significant commentary, but I like what I see so far.

Declarative authentication timeouts by session length and idle time
Only allow a developer to stay logged into the APEX builder for a specified period of time or time spent idle. This is handled server side, so just hacking the local cookie won't bypass the timeout--nice!

HTML Form settings for Password pages (autocomplete=off)
Does what it says.

Reduced privilege of APEX schema
No change to functionality, or really even to security as long as your FLOWS_xxx schema is secure, but any time you can skinny down privs to the minimum required it closes the possibility of a loophole.

Database monitoring disabled by default
Seems to make sense.

Declarative session state encryption
Now this makes great sense. APEX has a reasonably good security model in place to protect session state information, but we all know where session state is stored, in a single table in the FLOWS_XXX schema. I've never liked the idea that my SSN make hang out in that table for some DBA to look at, or possibly have some rogue developer write some hidden page that let's them go peek at my session state. Allowing a developer to indicate that an item be encrypted when saved in the database without having to worry about encrypting and decrypting the item greatly reduces the developer's burden--or more likely, means it will actually happen.

New Password item types that do not save state
In short, never persist this password data into the APEX sessions state table. I love the idea, though I wonder if a checkbox giving all items this option might not be better. We'll see how this turns out . . .

Ability to specify HTTPS for the APEX instance
In short, only allow the APEX builder to run when the browser is communicating via HTTPS.

OK, so how about one feature that is not on the list? Who would like a "Scratch" item type--an item that is never rendered, never posted and not settable on the URL? It would just be a place to store session state, somewhat like an application item that has session state protection enabled, but the item is on a page. Let me know what you think.

Oracle Application Express (APEX): Three Kinds of Session State

2008-12-05T13:52:00.000-08:00

One of the best features of APEX is its ability to manage session state for you. One of the trickiest things you run in to when getting in to advanced development, though, is understanding just when session state is updated and when it is persisted. The easiest way I can describe this is with some examples:

1. PERSISTED Session State
Most of the time you will be dealing with persisted session state--session state that resides within the database and that is available from subsequent page requests. Any time you set an item's value with a computation or process you get PERSISTED session state. If you pass an item and value on the URL you get PERSISTED session state. If you submit a page with a text item (or radiobox, checkbox, select list, hidden item, etc.) the item will get persisted session state.

That seems like most of the ways you set item session state, but there are a couple of other ways.

2. IN MEMORY Session State (IMSS)
This category really falls in to two parts: Item Rendering and Automated Row Fetch
So . . .

2A. Item Rendering
You can set item attributes of Source and Default. Let's say we set an text item (P2_VALUE10) to have default value to C2C. Run the page and you will see an item on the page with C2C in a text box. Check the session state (use the Session link at the bottom of the page) and you will find that P2_VALUE10 does not have any session state. How is this possible? It's right there on the page with C2C, right?

The value C2C was set IN MEMORY session state. It disappears immediately when the page is done rendering; it is not saved in the database. This is not usually an issue, because you are probably going to submit the page, in which case (according to point 1 above) it will store as PERSISTED session state.

A special note on item rendering in memory session state (IRIMSS :) ): IRIMSS happens as the item is rendered. Say you have 20 items on the page, with the tenth item, P2_VALUE10, having default value of C2C. If you have any computations or conditions inside of items 1..9 that depend on P2_VALUE10, it will not have the default value. For example, say P2_VALUE3 has a display condition: Display when P2_VALUE10 = C2C. P2_VALUE3 will not show. Items 11..20, though, will see P2_VALUE10 = C2C. It can be a bit confusing.

2B. Automated Row Fetch (ARF)
Using ARF produces IN MEMORY session state as described above, with a little twist. The ARF process allows you to choose whether to put the value of the item values into IMSS right when the process is run, or as the items are rendered. In early versions of APEX it always happened as the items were rendered. In recent versions the default is to put the value into IMSS when the ARF process runs.

Why Does It Matter
Why does it matter if it is PERSISTED Session State or IMSS? I have found many cases where IMSS caused confusion and bugs. A good example is breadcrumbs. Say we have a department report, which drills in to a department form, which in turn drills in to an employee form. You could have a breadcrumb like this:

Dept Report > Dept Form > Employee

But that is not very enlightening. Wouldn't it be better to have the following?

Dept Report > Accounting > Nielsen

Which means you would define something like this

Dept Report > &P2_DNAME. > &P3_LAST_NAME.

If you used ARF to set P2_DNAME, however, but did not submit page 2, instead clicking on an employee link, P2_DNAME will be null on the employee page, resulting in the following breadcrumb:

Dept Report > > Nielsen

There are many other similar scenarios. But, the underlying cause is IMSS.

If you need persisted session state, just do your own fetch process or computation. You can leave the ARF process if you want, it won't hurt to have both.

I hope this sheds a little light on APEX session state.

Oracle Portal and Oracle BI Publisher

2008-12-04T13:38:00.000-08:00

If you are using Oracle Portal and are used to using the Portal Forms Builder, you may want to create parameter forms for your BI reports in the Portal Forms Builder. This article provides a methodology for generating and redirecting to the BI Publisher URL from a Portal Form.

Step 1: Create a package that will do the redirect for you given the appropriate parameters.

--
--
create or replace
package bip1 as
g_url varchar2(32767);

-- add additional parameters as required
procedure bip_r
(
p_xf IN VARCHAR2 DEFAULT NULL,
p_custom1name IN VARCHAR2 DEFAULT NULL,
p_custom1val IN VARCHAR2 DEFAULT NULL,
p_custom2name IN VARCHAR2 DEFAULT NULL,
p_custom2val IN VARCHAR2 DEFAULT NULL);
end;
/

--
create or replace
package body bip1 as

procedure bip_r
(
p_xf IN VARCHAR2 DEFAULT NULL,
p_custom1name IN VARCHAR2 DEFAULT NULL,
p_custom1val IN VARCHAR2 DEFAULT NULL,
p_custom2name IN VARCHAR2 DEFAULT NULL,
p_custom2val IN VARCHAR2 DEFAULT NULL)
as
-- for information on determining the URL below, see
-- http://blogs.oracle.com/xmlpublisher/2006/07/accessing_xmlp_enterprise_repo.html
l_url varchar2(32767) := 'http://[Your_BIP_Server]:9704/xmlpserver/[your_folder]/[your_report]/[your_report].xdo?_xpf=&_xpt=1&...';
begin
l_url := l_url || '&_xf=' || p_xf || '&'
|| p_custom1name || '=' || p_custom1val || '&'
|| p_custom2name || '=' || p_custom2val ;

bip1.g_url := l_url;
end;
end;
/

Step 2: Create a Portal Form against a procedure and choose the bip1.bip_r procedure.

Step 3: Modify the Portal Form

a. p_xf is the allowable output formats: html, pdf, csv, etc.
b. p_custom1name (and all p_custom#name items) should be hidden and should be the name of your BI Publisher report variables
c. p_custom1val (and all p_custom#val items) are the values you want your users to enter, associated with the corresponding p_custom#name
d. At the Portal Form level, where you see the text area following
On successful submission of a form, execute this PL/SQL block or PL/SQL procedure:
Hint:
You can redirect your browser to a PL/SQL procedure, for example a procedure that creates a Web page, using either of these methods:

1. call('', '');
Redirects the browser to the procedure and passes a parameter containing the URL back to the form.
2. go('');
Redirects the browser to the procedure but does not pass a URL to return to the form.

Enter: go(bip1.g_url);

Running this form will redirect you to the correct BI Publisher URL.

Haiku

2008-11-10T08:19:00.000-08:00

One of the last times I hung out with Carl Backstrom we talked about texting someone a haiku. It's not much, but the occasional blog post in Carl's memory can't be a bad thing. I offer this:

Dynamic
CarlBack blogs now fixed
Early Night

I invite you to post your own haiku.

Carl Backstrom

2008-10-28T07:19:00.000-07:00

Many have already posted about the tragic loss to the APEX development team and community. I feel the need to add my own short tribute--though it will certainly take me time to collect my thoughts. For now, simply, "We already miss you, Carl."

Anton

Oracle Application Server Portal, Application Express (APEX) and OID

2008-09-11T12:22:00.001-07:00

We have done a lot of work at C2 over the past few years integrating Portal with APEX (security and themes/ui), APEX with OID/SSO, OID with MS Active Directory (AD), APEX with AD, APEX with SSL LDAP . . . Well, you get the idea. We finally put this experience into a couple of training modules. If you are interested in these topics, click on the C2 Consulting logo at the right.

We have done similar work with all of the above and Oracle Universal Content Management (UCM) but have not integrated that into the course. Let me know if you think that would be worthwhile.

Oracle Application Express (APEX) "submit" Button

2008-08-29T19:04:00.000-07:00

We just ran in to the strangest problem. All of our buttons and standard tabs stopped working on a particular page. Parent tabs worked fine, but anything that relied on posting the page (javascript:doSubmit) stopped working. Neelesh Shah came up with the solution. One of our buttons was "submit"--all lower case. Usually a submit button in APEX is either SUBMIT of Submit, with at least one upper case letter. APEX automatically adds an id="" to all the buttons. As it turns out, id="submit" (all lower case) causes browsers to stop submitting (posting) pages. Just changes the value to Submit did the trick.

Oracle Internet Directory (IDM OID) patchset 10.1.4.2 and WNA

2008-08-22T14:42:00.000-07:00

Ouch! We recently installed the OID 10.1.4.2 patch to solve some issues with Server Chaining to Microsoft Active Directory (MS AD). There were two object classes that did not get mapped for groups and there were problems that OID would not find any group that was not directly in the dn that was chained to AD. If you chained
cn=ad,cn=groups,dc=mycompany,dc=com
to
cn=groups,ou=myDept,dc=mycompany,dc=com
but you had a group in subcontainer
cn=anotherLevel,cn=ad,cn=groups,dc=mycompany,dc=com
OID would not find it.

The patch almost worked as expected--we got one of the two object classes promised and we could find the groups in subcontainers. Unfortunately the patchset broke Windows Native Authentication (WNA). The problem is that the patch introduced a new java JDK, version 1.4.2._14. After many hours of troubleshooting we found Oracle bug 6658334--WNA FAILS AFTER APPLYING IDM 10.1.4.2.0 PATCHSET. The solution appears to be to downgrade the Sun JDK to 1.4.2_13. We did this and it works, but oh what a headache.

You might get an error stack that looks like this:

DAS servlet init enter
oiddas: Release 10.1.4.0.1 Production Started
<$ORACLE_HOME>/j2ee/OC4J_SECURITY/applications/oiddas/ui/WEB-INF/lib/oiddas.jar archive
DAS servlet init exit
Getting creds for HTTP/ ...
Debug is true storeKey true useTicketCache false useKeyTab true doNotPrompt true ticketCache is null KeyTab is
<$ORACLE_HOME>/j2ee/OC4J_SECURITY/config/sso.keytab refreshKrb5Config is
false principal is HTTP/ tryFirstPass is false
useFirstPass is false storePass is false clearPass is false
principal's key obtained from the keytab
principal is HTTP/
KerberosAuthenticator: GSSException raised in constructor -
No valid credentials provided (Mechanism level: Attempt to obtain new ACCEPT
credentials failed!)
GSSException: No valid credentials provided (Mechanism
level: Attempt to obtain new ACCEPT credentials failed!)
at
sun.security.jgss.krb5.Krb5AcceptCredential.getKeyFromSubject(Krb5AcceptCreden
tial.java:189)
at
sun.security.jgss.krb5.Krb5AcceptCredential.getInstance(Krb5AcceptCredential.j
ava:80)
. . .
30 Caused by: javax.security.auth.login.LoginException:
java.lang.NullPointerException
at java.lang.StringBuffer.append(StringBuffer.java:467)
at
com.sun.security.auth.module.Krb5LoginModule.attemptAuthentication(Krb5LoginMo
dule.java:576)
at
com.sun.security.auth.module.Krb5LoginModule.login(Krb5LoginModule.java:475)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

Oracle Open World (Oracle OpenWorld)

2008-08-13T13:12:00.000-07:00

I will be speaking at OOW this year on two topics.

How to Hack an Oracle Application Express Application

If you write HTML applications in any technology and you care about your data, this presentation is for you. All HTML applications share some attributes that expose them to potential hacking. APEX has many features that will allow developers to lock down their applications – or to expose them to hacking. I will show techniques that can be used to hack HTML applications, how to close these holes within APEX and where APEX may expose these holes through its wizards. Naturally, I will show how to protect against any hack that I demonstrate. This session can benefit the novice to the highly advanced APEX developer as well as developers of any HTML application.

This is a skinnied down version of the module that C2 Consulting teaches in its Application Express classes.

Realizing ROI with Application Express

As part of Massachusetts healthcare reform, C2 Consulting worked with Harvard Pilgrim Healthcare (HPHC) to develop a new business process for providing health insurance directly to the subscriber. Utilizing Oracle Application Express, the HPHC system allows individuals to apply, enroll and be confirmed in a healthcare plan online in just a few minutes. Developed in just one month, the project cost will be recovered in just one year's saved postage.

Oracle Portal Secure Content Repository Views

2008-08-13T12:58:00.000-07:00

I recently went searching for the documentation on the Oracle Portal repository views for Portal 10.1.4 and had a hard time finding them.

The Views
http://www.oracle.com/technology/products/ias/portal/html/plsqldoc/pldoc1014/wwsbr_api_view.html

The APIs
http://www.oracle.com/technology/products/ias/portal/html/plsqldoc/pldoc1014/summary.html

The Documentation
http://download-west.oracle.com/docs/cd/B14099_19/portal.1012/b14134/toc.htm

And More Documentation
http://download.oracle.com/docs/cd/B14099_15/portal.1014/b14135/pdg_part3.htm#sthref1557

Advanced Configuration Training Course: Oracle Application Express (APEX)

2008-08-04T12:34:00.000-07:00

This is just a quick note to mention that C2 Consulting has added a new module to its Oracle Application Express (APEX) course. The training class is modular, so class attendees or clients can choose which modules are taught during a particular session. The new Advanced Configurations course covers installation and configuration in a variety of environments. Some of the topics follow:

High Availability, RAC, HA Middle Tiers (I contributed to the Oracle white paper on this topic)
Configuring Apache for SSL
Configuring Apache Virtual Hosts
Using Apache 2.x as a reverse proxy
Using Oracle Web Cache
Capturing IP Address behind Firewalls, Reverse Proxies and Web Cache
Using Apache Rewrite Rules
Custom DAD configurations--passing environment variables, setting default application, etc.
Custom Authentication and Authorization Schemes
How to enable Oracle Single Sign-On (SSO)
How to integrate with Netegrity (Computer Associates) Siteminder (Optional)
How to integrate with RSA (Optional)
How to integrate with PKI (client-side) certificates (Optional)

If you are interested in this topic or any of the C2 courses, contact C2.

DBMS_XMLSCHEMA.generateschema gives ORA-22303: type not found

2008-07-18T07:35:00.000-07:00

I just had someone call about this.
SELECT DBMS_XMLSCHEMA.generateschema('your_schema','your_type') FROM DUAL;
gives ORA-22303: type "your_schema"."your_type" not found
ora-21700
ora-06512

Just upper case your_schema and your_type -- YOUR_SCHEMA and YOUR_TYPE.

Hope this helps.

Oracle Application Express (Apex) Breadcrumbs

2008-03-25T14:25:00.000-07:00

In the early versions of Oracle HTML DB (the old name for Oracle Apex) breadcrumbs were called menus. Under the covers, in the code, they still are. They work great as breadcrumbs, but they still work as menus. Many Apex training classes will tell you to use lists for navigations and use breadcrumbs for, well, breadcrumbs. In most cases, though, you can use a single breadcrumb structure for both. Let's say you have the following page/navigation structure

Training

- In Class
- - Application Express
  - Oracle Database Tuning
  - 10g Application Server
  - Oracle Portal

- Web Based
- - Application Express
  - SQL
  - Oracle Database Management
  - PL/SQL

If you are on the SQL page you want a breadcrumb like this:
Training > Web Based > SQL

You may also want a menu that looks like this
Application Express
SQL
Oracle Database Management
PL/SQL

You can use the same breadcrumb to provide both features. Just create two regions, one with a breadcrumb template and another with a breadcrumb "menu" template, typically called Heirarchical in new versions of Apex. Play with the template, changing "Start With" to Current Breadcrumb. You will get a menu out of your breadcrumb. If you are on the "Web Based" page, you could create a template with "Start With" set to Child Breadcrumb Entries. This would give you a Menu that looks like this:
Application Express
SQL
Oracle Database Management
PL/SQL

Play with the breadcrumb templates and Start With and you may find they work far better than lists for navigation.

Security: How to Hack an Oracle Application Express Application

2008-02-07T06:50:00.000-08:00

I will be presenting at both the Northeast Oracle Users Group (NOUG) Training Day on 5 March 2008 and at the Oracle Development Tools User Group Kaleidoscope 2008 on 18 June 2008, 2:45 - 3:45 pm.

Oracle's Application Express (APEX) allows developers to quickly build highly functional applications that interact with an Oracle database. APEX dynamically generates HTML applications (hence the former name, HTML DB). All HTML applications share some attributes that expose them to potential hacking. APEX has many features that will allow developers to lock down their applications – or to expose them to hacking. I will show (live!) a number of techniques that can be used to hack HTML applications—and how to close these holes within APEX and where APEX may expose these holes through its wizards. Naturally, I will show how to protect against any hack that I demonstrate. The session will be interactive with the audience participating in ways to hack and prevent hacks. This is truly a session that can benefit the novice to the highly advanced APEX developer.

This is the same module that C2 Consulting teaches in its Application Express classes.

Chained Oracle Web Caches, Reverse Proxy, REMOTE_ADDR and CLIENT_IP

2007-10-01T07:05:00.001-07:00

Most people today are using a DMZ. There is a great deal of variety in configuring the DMZ, from having nothing but a Reverse Proxy (RP) in the DMZ to having everything including the database in the DMZ (and possibly Dataguard or Oracle Streams to push data to the DMZ database). This post will focus on the specific instances of needing to get the browser's IP (or as close as one can get) all the way through to the database--specifically using Oracle Portal, PL/SQL and Java Portlets, Application Express or anything sitting behind the Oracle Webcache.

Scenario 1: This does not even require a DMZ configuration. If you are running Oracle Portal you may find it hard to get access to the browser's IP address in a UI Template of Page or Item Skin. The problem is that the Parallel Page Engine (PPE) calls Web Cache which in turn calls Apache to get your page. Web Cache sees the call as coming from the PPE, so you get the IP address of your middle tier, not of the browser. I have another post that covers that topic.
http://c2anton.blogspot.com/2007/02/oracle-portal-client-ip-address.html
That post will be used in all of the scenarios below.

**************************************

Scenario 2: Oracle Web Cache in the DMZ (WC1) acting as a Reverse Proxy to another Web Cache that serves your content (WC2). It looks like this:

Browser ---> WC1 ---> WC2 ---> Apache ---> mod/plsql ---> Database

The browser sends its IP address in a header variable, REMOTE_ADDR, in all requests. (Note: if it uses a forward proxy, the forward proxy may change the REMOTE_ADDR value to its own IP address. We can't do anything about that. For example, all AOL users may show up with only 3 IP addresses.) When WC1 gets the request, it moves the value of REMOTE_ADDR into a new header variable, CLIENT_IP and puts its own IP address into REMOTE_ADDR. Normally this is not an issues, because mod/plsql automatically puts the value of CLIENT_IP back into REMOTE_ADDR, but with a second web cache, see what happens. WC2 replaces ClientIP with REMOTE_ADDR and puts its own IP into REMOTE_ADDR. The table below shows what happens to these values as they progress...


                Browser       WC1           WC2         MODPLSQL    DATABASE
REMOTE_ADDR     192.168.1.50  x.x.200.10    x.x.300.5   x.x.200.10  x.x.200.10
ClientIP       not set       192.168.1.50  x.x.200.10  x.x.200.10  x.x.200.10

As you can see, by the time it makes it to the database the only thing available is the IP address of WC1 (unless it is a PPE call from Portal, in which case it is the IP of WC2).

The Web Cache guys recognized this could happen and put a feature into the Web Cache settings to allow WC2 to accept the ClientIP that was forwarded from a previous Web Cache. In the WC Admin, go to Properties > Security and set "Accept client IP addresses encoded in ClientIP HTTP headers" to Yes.

If you are using Portal, be sure to read about getting the value past the PPE in the link above.

*******************************************

Scenario 3: Apache 2.x in the DMZ (RP1) acting as a Reverse Proxy to another Web Cache that serves your content (WC2). It looks like this:

Browser ---> RP1 ---> WC2 ---> Apache ---> mod/plsql ---> Database

This is similar to scenario 2, however RP1 uses the header variable X-Forwarded-For instead of ClientIP. X-Forwarded-For makes it all the way through to modplsql, but modplsql does not know to send it on to the database. You need to add the following line to your modplsql DAD:

PlsqlCGIEnvironmentList X-Forwarded-For

Your dad will look something like the following:

...
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDatabaseUsername HTMLDB_PUBLIC_USER
PlsqlDefaultPage apex
PlsqlDatabasePassword xxxxx
PlsqlCGIEnvironmentList X-Forwarded-For
Allow from all

You can then use owa_util.get_cgi_env('X-Forwarded-For') to retrieve the IP Address of the client.

*********************************

That covers it. I hope this helps. As always, click on the My Work link to the right if you would like in-person assistance.

Oracle Internet Directory (OID) ldapbind gives: sgslufread: Hard error on read, OS error = 10054

2007-09-17T08:00:00.000-07:00

We were using a Cisco Load Balancing Router (LBR or CSS) and trying to do a simple ldapbind -p 389 -h machine would give the following error:
sgslufread: Hard error on read, OS error = 10054 (on windows)
sgslufread: Hard error on read, OS error = 104 (on linux)
ldap_bind: Can't contact LDAP server

(might have also seen sgslufread: Hard error on read, OS error = 194 )

It turned out that the LBR was configured with Layer 5 load balancing (it does URL inspection). It is turned on by adding url "/*" to the content
content layer5
protocol tcp
vip address xxx.xxx.xxx.xxx
add service server1
port 389
url "/*"
active

The problem is that we have LDAP traffic, not HTTP traffic. Layer5 (url "/*") is for HTTP traffic. Removing the
url "/*"
took care of the issue.

I also received the same error when using an F5 load balancer to connect via LDAPS (SSL encrypted) on port 636. In this case the solution was to turn off the F5 LDAP monitor for port 636. For some reason the F5 does not like to monitor the OID SSL LDAP port.

ASM and ORA-29701: Unable to connect to Cluster Manager

2007-08-31T12:57:00.000-07:00

I was recently at a client. They told me that the Oracle database binaries were installed and that they wanted an ASM instance created and a couple of disk groups. Easy enough...

Every time I would try to start an ASM instance I would get this error:
ORA-29701: Unable to connect to Cluster Manager

It turns out that this machine was a clone of another machine and that the binaries weren't installed, but cloned from another machine. I don't ever do it that way, so I was at a bit of a loss. I finally found a solution. From the $ORACLE_HOME/bin directory I ran

localconfig delete
localconfig add

This took care of the problem. Hope this helps.

Oracle Portal Content Management Event Framework

2007-08-28T14:34:00.000-07:00

I recently put together a little demo that might be helpful for people starting out with the Oracle Portal Content Management Event Framework (CMEF). The documentation is here. While the documentation is good, it can be a bit overwhelming. My example gives you everything you need to get started, and nothing you don't. So, let's get started.

Goal: Capture all events from the CMEF and store the content in a table. If you can store it in a table, you can do just about anything with it.

Overview:

1. Create a sequence to be our table's primary key
2. Create the table and associated triggers
3. Create a package and procedure that will be notified when an event occurs, dequeue the event and put the contents in the table.
4. Create a subscriber to the CMEF queue.
5. Register the subscriber and the procedure created in step 3.

Full steps
All scripts run in the portal schema

1. Create a sequence to be our table's primary key

CREATE SEQUENCE "C2_EVENT_LOG_SEQ"
MINVALUE 1
MAXVALUE 999999999999999999999999999
INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE
/

2. Create the table and associated triggers

CREATE TABLE "C2_EVENT_LOG"
( "ID" NUMBER NOT NULL ENABLE,
"EVENT" VARCHAR2(4000),
"EVENT_DATE" DATE,
"EVENT_PAYLOAD" CLOB,
CONSTRAINT "C2_EVENT_LOG_PK" PRIMARY KEY ("ID") ENABLE
)
/

CREATE OR REPLACE TRIGGER "BI_C2_EVENT_LOG"
before insert on "C2_EVENT_LOG"
for each row
begin
if :NEW."ID" is null then
select "C2_EVENT_LOG_SEQ".nextval into :NEW."ID" from dual;
end if;
end;

/
ALTER TRIGGER "BI_C2_EVENT_LOG" ENABLE
/

3. Create package and procedure

CREATE OR REPLACE PACKAGE c2_ptl
is

PROCEDURE event_notify(context raw,reginfo sys.aq$_reg_info,descr sys.aq$_descriptor,payload raw, payloadl number);

end;
/

CREATE OR REPLACE PACKAGE body c2_ptl
is

PROCEDURE event_notify(context raw,reginfo sys.aq$_reg_info,descr sys.aq$_descriptor,payload raw, payloadl number)
as
dequeue_options dbms_aq.dequeue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(16);
message portal.wwsbr_event;
lclob clob;
pmsg varchar2(100);
BEGIN
dequeue_options.msgid := descr.msg_id;
dequeue_options.consumer_name := descr.consumer_name;

-- Get the Message and remove it from the queue
-- Note: this will dequeue the message. If you don't do anything with i
-- it will be gone and you won't be able to retrieve it again
DBMS_AQ.DEQUEUE(queue_name => descr.queue_name,
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);

-- Insert message contents into the table

insert into c2_event_log (event, event_date, event_payload)
values ('A',sysdate, 'message.action: '||message.action
|| ' message.object_class: '|| message.object_class);

-- there are a log of other message properties, look at the portal.wwsbr_event type
-- for a full list of the contents of the message

COMMIT;
END event_notify;

end;
/

4. Create a subscriber to the CMEF queue
begin
dbms_aqadm.add_subscriber
( queue_name => 'PORTAL.WWSBR_EVENT_Q',
subscriber => sys.aq$_agent( 'C2_SUB', null, null ) );
end;
/

5. Register the subscriber and the procedure created in step 3
begin
dbms_aq.register
( sys.aq$_reg_info_list(
sys.aq$_reg_info('PORTAL.WWSBR_EVENT_Q:C2_SUB',
DBMS_AQ.NAMESPACE_AQ,
'plsql://c2_ptl.event_notify',
HEXTORAW('FF')) ) ,
1 );
end;
/

Let me know if this works for you!

Just a little note... The following query will give you all queue registrations:
SELECT * FROM sys.reg$

The following URL will give you portal queue subscribers (you must be logged in as portal):
http://machine:port/portal/pls/portal/PORTAL.wwsbr_event_dbg.show

And finally . . . if you want to remove those subscriptions, do the following:
/* *** UNREGISTER SUBSCRIBER *** */
begin
dbms_aq.unregister
( sys.aq$_reg_info_list(
sys.aq$_reg_info('PORTAL.WWSBR_EVENT_Q:C2_SUB',
DBMS_AQ.NAMESPACE_AQ,
'plsql://c2_ptl.event_notify',
HEXTORAW('FF')) ) ,
1 );
end;
/

/* **** REMOVE SUBSCRIBER **** */
begin
dbms_aqadm.remove_subscriber
( queue_name => 'PORTAL.WWSBR_EVENT_Q',
subscriber => sys.aq$_agent( 'c2_SUB', null, null ) );
end;
/

Oracle Fusion Middleware Regional Director Program

2007-06-21T08:56:00.000-07:00

I was recently invited to become an Oracle Fusion Middleware Regional Director. While pleased by the recognition, I'm really enthused about the opportunity this will give me to meet with Oracle development and product management. The Regional Directors are invited to regular con-calls and occasional live events. I will be sure to pass along points of interest on this blog. If you have anything that you would like addressed, add a comment and I will bring it up.

Application Express Debug Mode Timing on Post/Submit

2007-06-01T08:05:00.001-07:00

A co-worker recently approached me with an Apex 2.2.1 problem. He had a report that was running extremely slowly. The report used a database link. He suspected the dblink was the problem.

We spent some time looking over it directly in sql*plus (always a good way to start) and confirmed that the query ran slowly accross the link. Next step, we ran the query directly in the source database--still slow. There were a lot of rows in the view (which was based upon two other views, which were both based upon views). Only one index, on the primary key. I recommended some specific indexes that would help his situation.

A week later he returned. Now the query ran great in sql*plus, but the report was still slow in Apex with certain query criteria. I ran the page, submitting different criteria and confirmed that with some criteria it ran very slowly. Turning on debug indicated that the query region was running fast. I suspected that the debug mode timing somehow did not include the time spent in the linked database (boy was I wrong).

After some hair pulling, I looked at the page that I was submitting (the page with the query criteria) and saw several validations on that page. Aha! Perhaps a validation was the culprit. The report was branched via a "Branch to Page or URL" branch, so debug mode was not showing anything that happened during the submit portion--only the render portion of the report page. I created a new branch with sequence=1. This branch was a Direct Branch (see image). By making it a direct branch I was able to see the time spent in the validation:

0.03: Branch point: BEFORE_VALIDATION
0.03: Perform validations:
0.03: ...PL/SQL "function body returning error text"...
317.62: ...

As you can see, the validation was taking over 5 minutes. Using the direct branch made all difference, allowing me to look at the submit processing. The validation also used the dblink, but did a select count(*) from that same view (with 50 million rows)--not using any indexes. Cleaning that up did the trick.

How to Enable Tracing in Oracle Forms 10g

2007-05-08T11:45:00.000-07:00

Enable Tracing from the url by adding the following to your forms url:
&record=forms&tracegroup=0-98,100-199 (note: item 99 causes some issues, so we are skipping it here. See the Oracle documenation for a description of what is being traced for each item.)

for example
http://machine:port/forms/frmservlet?config=myConfig&record=forms&tracegroup=0-98,100-199

You will get a trace file in the following location:

%ORACLE_HOME%\forms\trace\forms_xxx.trc where xxx is the forms session ID.

Open dos window and do the following:

set ORACLE_HOME=

set PATH=%ORACLE_HOME%\jdk\bin;%PATH%

set CLASSPATH=%ORACLE_HOME%\jdbc\lib\classes12.zip;%ORACLE_HOME%\forms\java\frmxlate.jar

java oracle.forms.diagnostics.Xlate datafile=%ORACLE_HOME%\forms\trace\forms_xxx.trc outputfile=%ORACLE_HOME%\forms\trace\html_xxx.html outputclass=WriteOutHTML

you will get a file html_xxx.html in your %ORACLE_HOME%\forms\trace directory.

Oracle Advanced Queue -- View Subscriber Registrations

2007-04-30T18:57:00.000-07:00

Have you ever wanted to see what registrations/notfications you have registered to a queue subscriber? I want to see that, but why is it so hard to figure out where the info is? I see some mention of SYS.AQ_SRVNTFN_TABLE_Q, but I haven't been able to look into that possibility.

Update: This gives what you need, but it is not as nice as I would like.
SELECT * FROM sys.reg$

Oracle DML Error Logging and ORA-38908

2007-04-30T12:30:00.000-07:00

Have you ever encountered an error like this?

ORA-38908: internal error occurred during DML Error Logging ORA-00942: table or view does not exist ORA-02291: integrity constraint (T1.T1_FK) violated - parent key not found

You are probably using DML Error Logging, a statement like this

insert into my_tab (c1, c2, c3)
select a.c1, a.c2, a.c3 from my_other_tab a
LOG ERRORS INTO err$_my_tab ('oops') REJECT LIMIT UNLIMITED;

The reason is that err$_my_tab (and probably my_tab) is owned by a user A but the statement is being run by user B and user B does not have insert on err$_my_tab. It's fairly clear from the documenation that this is required.

Security

The user who issues the INSERT statement with DML error logging must have INSERT privileges on the error logging table.

The problem is, this is the case even if the insert statement is part of a package (my_pkg) and user B has execute on my_pkg. It seems that user B should not need insert on err$_my_tab under this scenario, but it does. This is probably because the DML Error Logging uses autonomous transactions (I assume) and operates outside the context of the package. In this light, it makes sense.

Upgrading to Oracle Database R2 10.2.0.1 and the TEMP tablespace

2007-04-18T11:05:00.000-07:00

When running utlu102i.sql you get
WARNING: --> TEMP tablespace is not large enough for the upgrade.

This is because your current default TEMP tablespace is not locally managed. The error could certainly be better! Here is what you need to do.

1. Create a new temporary tablespace that is locally managed:
create temporary tablespace temp2
tempfile '/opt/oradata/orcl/temp201.dbf' size 64m
extent management LOCAL uniform size 1m
autoextend on next 32m maxsize 128m;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

2. Now, if you want to have the default tablespace you need to drop the TEMP tablespace, recreate is as locally managed, alter the database to have it as the default temporary tablespace, then drop TEMP2.

drop tablespace temp including contents;

create temporary tablespace temp
tempfile '/opt/oradata/orcl/temp01.dbf' size 64m
extent management LOCAL uniform size 1m
autoextend on next 32m maxsize 128m;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

drop tablespace temp2 including contents;

Upgrading OEM Grid Control R1 to R3

2007-04-12T09:32:00.000-07:00

I'll post more updates soon. If it takes a long time, you need to follow note 421053.1 to install the EMDiagKit and the the R1 to R2 Upgrade checklist (note: 401592.1) the the R2 to R3 checklist (note: 422061.1). Although the note has a link to the EMDiagKit, the link points inside the Oracle firewall--you'll need to call support to send you the file.

If your upgrade hangs on linux, that is, the Oracle Universal Installer (OUI) stops on the first page while detecting OMS and Agents, you may need to follow note 343158.1. The installer will say Select Install or Upgrade but does not give you any options to select. The problem is that the OUI is not playing well with your X-Windows emulator (you're not doing this right on the box, but by exporting your display). Unfortunately, that note is not going to help you immediately if you are on Linux or Unix. You will need to get Oracle Support to send you the patch.

As you do the upgrade, the new R3 documentation has the following:

If the older Oracle Management Service was a seed installation, run the following command:

$NEW_OMS_HOME/oui/bin/runConfig.sh ORACLE_HOME=<OMS HOME> MODE=perform ACTION=configure COMPONENT_XML={encap_emseed.1_0_0_0_0.xml}

This is wrong. After you run this, the install log will say "encap_emseed not found". The whole upgrade process changed in the R3 documenation, and, as far as I can tell, the R2 documentation doesn't exist on the Oracle documentation site. They changed the install method to use the -noconfig option (runInstaller -noconfig) because they had a couple bugs they wanted to fix, so they "fixed" them by having you run the process manually--only they documented it wrong!

So, here is what it should be:

If the older Oracle Management Service was a seed installation, run the following command:

$NEW_OMS_HOME/oui/bin/runConfig.sh ORACLE_HOME=<NEW_DB_HOME> MODE=perform ACTION=configure COMPONENT_XML={encap_emseed.1_0_0_0_0.xml}

Hope this helps.

Oracle Application Express 3.0 (APEX 3.0)

2007-03-20T20:34:00.000-07:00

It's available. Not much more needs to be said. We'll be downloading and installing this weekend. If all goes well I won't need another blog entry. If things don't go well, I'll post any fixes I come up with.
http://apex.oracle.com

oraDAV Portal and Third Party Authentication

2007-03-01T11:58:00.000-08:00

Have you used the Oracle Drive with your portal? It is easy to set up and it is a great feature. Do you use Third-Party Authentication (TPA) with Oracle Single Sign-On (SSO)? You probably don't need it, but if your company has another SSO standard, at least it is fairly easy to set up.

What if you want to use both? Seems it should be available. Search the documentation and you won't find anything saying you can't. So it should work, right? Maybe. Nothing in the documenation tells you how, either. So far it is impossible to tell. Oracle Support has an SSO group (tpa), the http group (oraDAV), and the Portal group. Getting them to work together on this is going to be tons of fun. I'll give an update when we figure it out.

Update

Still waiting on something from Oracle Support, but I have figured out that Portal using mod_oradav does not use SSO at all. It just does a test bind against OID. SSO can be completely down and it will still authenticate (without TPA) as long as OID is up.

This means that the authentication likely happens directly from Portal via dbms_ldap. If this is the case, it means that the user's password in LDAP will be the authentication method, not what the SSO TPA is configured to use. This won't be a problem if you keep users' passwords in OID up to date with whatever your TPA mechanism is, but it is a problem, as in my case, when OID doesn't have any idea what the users' real passwords are.

Update 2

It is clear that Portal is doing the authentication against OID. No word from Oracle on whether this is by design or if there could ever be a TPA solution. For now I have to assume that it won't work unless passwords are stored in OID. Next step is to find a way to sync those passwords.

Oracle SQL Developer - ORA-00942

2007-02-08T08:11:00.000-08:00

I recently tried to user SQL Developer with a schema and found that I could not view packages, procedures or functions. I would get ORA-00942: table or view des not exist. I still don't know why, but granting SELECT_CATALOG_ROLE to the user solved the problem.

Update: I had a similar problem in Oracle Data Miner (Data Mining). I granted unlimited tablespace to the user and the problem went away. It may have been a tablespace issue for SQL Developer as well...

Oracle Portal Client IP Address (REMOTE_ADDR)

2007-02-02T08:21:00.001-08:00

You may want to show the IP address of the client (the browser) on a Portal page. This can be tough because the page is built by the parallel page engine (PPE). At that point, the actual requestor is the mid-tier itself. You need to make a call that goes all the way to the database without being redirected through the PPE. I do this by creating a pl/sql procedure (ideally part of a package) that I then call from a bit of javascript in the page (possibly through a UI Temple / HTML Template). Note: if the user is going through a proxy you will get the proxy IP. If you have a reverse proxy setup (e.g. multiple Oracle Web Caches or Apache Reverse Proxy) you can set things up to get the correct browser IP. See my other post on setting up chained Web Caches.

Here is the procedure, it's fairly straightforward.


CREATE OR REPLACE procedure c2_getClientIP is
  l_ip varchar2(200);
begin
  l_ip := owa_util.get_cgi_env('REMOTE_ADDR');
  htp.p(l_ip);
end;
/

grant execute on c2_getClientIP to public;

Then you put a little javascript in the page to call that procedure.


<script language="Javascript">
function xmlhttpPost(strURL) {
   var xmlHttpReq = false;
   var self = this;
   // Mozilla/Safari
   if (window.XMLHttpRequest) {
       self.xmlHttpReq = new XMLHttpRequest();
   }
   // IE
   else if (window.ActiveXObject) {
       self.xmlHttpReq = new ActiveXObject("Microsoft.XMLHTTP");
   }
   self.xmlHttpReq.open('GET', strURL, true);
   self.xmlHttpReq.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');
   self.xmlHttpReq.onreadystatechange = function() {
       if (self.xmlHttpReq.readyState == 4) {
           updatepage(self.xmlHttpReq.responseText);
       }
   }
   self.xmlHttpReq.send(getquerystring());
}

function getquerystring() {
   var form     = document.forms['f1'];
   var word = form.word.value;
   qstr = 'w=' + escape(word);  // NOTE: no '?' before querystring
   return qstr;
}

function updatepage(str){
   document.getElementById("result").innerHTML = str;
}
</script>

<form name="f1">
 word: <input name="word" type="text">  <input value="Go" type="button" onclick='JavaScript:xmlhttpPost("/pls/portal/portal.c2_getClientIP")'>
 <div id="result"></div>
</form>

You can put the javascript in a template or as a text item. You can modify this however you want to either just show the IP, put in a variable, etc.

DBMS_XMLSCHEMA.GENERATESCHEMA and ORA-21500

2007-02-02T07:53:00.000-08:00

You may run into this error. You want to generate an xsd based on on object type. You create the object:

CREATE OR REPLACE TYPE my_test_type
AS OBJECT (
EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20)
);

Then you want to get the associated xsd:

SQL> Set pagesize 9999
SQL> Set linesize 132
SQL> Set long 9999
SQL>
SQL> select DBMS_XMLSCHEMA.GENERATESCHEMA( 'APPS', 'MY_TEST_TYPE')
2 from dual
3 /

Instead of getting the xsd, you get the following:

ERROR:
ORA-21500: internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]

The problem is not at the database, but with sql*plus. You are probably using a 9i sqlplus client. Try a 10g sqlplus and it should work fine.

As always, if this helps, drop me a comment. It would be great to know that this actually helped someone.

RepCA on HPUX 64-Bit

2007-01-11T12:04:00.000-08:00

You may run into the following errors when running RepCA on HPUX 64-Bit (Itanium):

in the repca.log file

[CustomExternalProcessAction] Command line: /oracle/product/10.1.2.mrca/jdk/bin/java -cp /oracle/product/10.1.2.mrca/assistants/opca/jlib/opca.jar:/oracle/product/10.1.2.mrca/portal/jlib/ptlshare.jar:/oracle/product/10.1.2.mrca/jdbc/lib/classes12.jar:/oracle/product/10.1.2.mrca/jdbc/lib/nls_charset12.jar oracle.webdb.config.PortalConfigAssistant
[CustomExternalProcessAction] Process exited abnormally
[CustomExternalProcessAction] Action failed due to: oracle.ias.repca.ifs.utils.action.ActionFailedException: Some of the operations have failed during this OracleAS RepCA session. Please see the log file at /oracle/product/10.1.2.mrca/assistants/repca/log/repca_2007-01-11_01-38-25_PM.log for further details.
ERROR - Some of the operations have failed during this OracleAS RepCA session. Please see the log file at /oracle/product/10.1.2.mrca/assistants/repca/log/repca_2007-01-11_01-38-25_PM.log for further details.
oracle.ias.repca.ifs.utils.action.ActionFailedException: Some of the operations have failed during this OracleAS RepCA session. Please see the log file at /oracle/product/10.1.2.mrca/assistants/repca/log/repca_2007-01-11_01-38-25_PM.log for further details.
at oracle.ias.repca.actions.CustomExternalProcessAction.perform(CustomExternalProcessAction.java:155)
at oracle.ias.repca.ifs.utils.action.ActionQueue.performActions(ActionQueue.java:319)
at oracle.ias.repca.ifs.utils.action.ActionQueue.run(ActionQueue.java:245)
at java.lang.Thread.run(Thread.java:534)

in the sso.log file

...end of SSO install

INSTALL_ACTION:createSSOSchema() : loadjava -resolve -verbose -user orasso/orasso@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=xxxxx))) /oracle/product/10.1.2.mrca/portal/admin/plsql/sso/SSOExtDB.class

arguments: '-resolve' '-verbose' '-user' 'orasso/orasso@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=xxxxx)))' '/oracle/product/10.1.2.mrca/portal/admin/plsql/sso/SSOExtDB.class'

SQL Error while connecting with oci8 driver to (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=xxxxx))): Closed Connection

exiting : could not open connection

INSTALL_ACTION:createSSOSchema() : loadjava -resolve -verbose -user orasso/orasso@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=xxxxx))) /oracle/product/10.1.2.mrca/portal/admin/plsql/wwc/SSOHash.class

arguments: '-resolve' '-verbose' '-user' 'orasso/orasso@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=xxxxx)))' '/oracle/product/10.1.2.mrca/portal/admin/plsql/wwc/SSOHash.class'

SQL Error while connecting with oci8 driver to (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=xxxxx))): Closed Connection

exiting : could not open connection

INSTALL_ACTION : Set the component loading flag for SSO

ERROR: Setting the component loading flag in the DBMS registry raised exception.

It could be that the loadjava of the repca doesn't work well in this configuration. There are probably at least two issues: first, the symbolic link $REPCA_HOME/JRE does not point to the correct location. It needs to point to the full JRE location (java/1.3.1). Second, the loadjava itself is an issue. I tried to solve the problem in a bunch of ways, but found the easiest thing to do is just run the repca from a Windows or Linux box. Much faster than troubleshooting the install on HPUX.

ssoreg.sh throws exception: IAS Property not found

2006-12-22T13:30:00.000-08:00

When running ssoreg.sh you may run into this error:

Repository Access API throws exception :oracle.ias.repository.schema.SchemaException: iAS Property could not be found Property Name OIDhost in ...

This is most likely because you used the actual ORACLE_HOME when running it instead of using $ORACLE_HOME. For some reason you MUST use $ORACLE_HOME.

Suse Linux and ORA-27125

2006-12-06T08:45:00.000-08:00

It's basically Unix, right, so you almost never reboot it. That's great, except when you can't remember what you did the last time to fix something. Well, I rebooted today. I didn't really need to, but something made me do it. Then I tried to bring up my database. I saw the following:

SQL> startup;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORA-27125: unable to create shared memory segment
Linux-x86_64 Error: 1: Operation not permitted

What to do about it!? It turns out I had two distinct problems. That can be tough, because it looks like it could be one problem. Somehow the database OEM had introduced an old init.ora parameter, that's the ora-32004. If the db won't start, you don't get the output in the alert log telling you which parameter is the problem. First I solved the ora-27125, then I found that I had plsql_compiler_flags specified and reset it.

So, what caused the ora-27125? Would you believe... metalink had the answer, note: 293988.1

I'm going to paste the note here just so it is handy.

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2 to 10.2
Linux Itanium
SUSE \ UnitedLinux x86-64
Linux x86-64
Linux x86
This Problem can be seen on the SUSE SLES9 linux distribution which runs on kernel '2.6.5-7.191'

Symptoms

While starting the database instance following error is encountered.

SQL> startup
ORA-27125: unable to create shared memory segment
Linux Error: 1: Operation not permitted

Cause

This is caused by '2.6.5-7.nnn' kernels requiring MLOCK privilege for hugetlb memory.

Solution

To enable use of "hugetlb memory", or to allow the init.ora parameter "lock_sga=true" to be used, do:

--- Login as root
--- # echo 1 > /proc/sys/vm/disable_cap_mlock

If you need to make this change persistent

--- Login as root
--- Edit /etc/sysctl.conf
Add following lines
# Oracle requires MLOCK priviledge for hugetlb memory.
vm.disable_cap_mlock=1
--- Execute the command
#/etc/init.d/boot.sysctl start
Setting current sysctl status from /etc/sysctl.conf
vm.disable_cap_mlock = 1
--- Dont forget to enable the service.
chkconfig boot.sysctl on

Export Portal schema and move to another db

2006-11-22T09:58:00.000-08:00

OK, so I admit this is very esoteric, but if you ever need to move a Portal schema by using export/import (e.g., you are changing database operating system), you may run into this problem... (I'll try to put up a more comprehensive "how to" later, but thought this little issue deserved its own spot.) In the meantime, we have done a bunch of these moves. If you need some help, click on the C2 Consulting link to the right.

The solution is right in the documentation
http://download-east.oracle.com/docs/cd/B14099_18/core.1012/b13995/prodtest.htm#BHAHIHBH
but if you miss it, the errors are not too helpful.

You get a servlet error

Servlet Error

An unexpected servlet error was encountered.

Please check the log file for more details.

In the log file
$ORACLE_HOME/j2ee/OC4J_Portal/application-deployments/portal/OC4J_Portal_default_island_1
you will see the following:

06/11/22 09:43:41 portal: [module=PortalServlet, ecid=92635952220,1] ERROR: oracle.webdb.repository.service.RepositoryServiceException: Unable to obtain Portal Repository version.

Search all through metalink and on the web and it will likely tell you that your PORTAL schema password is wrong. Well, that might be true, but once you find it isn't wrong, log in as either PORTAL, sys or system and run the following command:

ALTER USER PORTAL_PUBLIC GRANT CONNECT THROUGH PORTAL;

Bounce everything. I'm interested in finding out if this helps anyone. If it does, leave a comment.

Thanks,

Anton

High Availability Identity Management Install (10gAS)

2006-11-08T11:58:00.000-08:00

The installation guides tell you that you must have your load balancer resolve via dns or in the host file. The guides don't mention that if you have a metadata repository on a separate box, then the load balancer must resolve from that box as well. If it doesn't, you will get the following error in InstallActions.log:

SQL> Connected.

SQL> Creating OID entries for SSO
Error code : 1

Error message: User-Defined Exception

LDAP error : ORA-31203: DBMS_LDAP: PL/SQL - Init Failed.

ERROR: deleting application entry

Error code: 1

Error message: User-Defined Exception

ERROR: creating SSO users and groups in OID

PL/SQL procedure successfully completed.

Oracle REPCA on a RAC Database

2006-11-07T13:47:00.000-08:00

If you run the Oracle Metadata Repository Creation Assistant (RepCA) into a RAC Database you may find that the PORTAL_DEMO schema does not get created. You get an error saying that some things failed at the same moment that you get an installation completed successfully message. The fix is fairly easy, just remove the MR and re-install, but only indicate a single RAC node in the installer. Section 2.3.2, Step 4 of the following document mentions this:
http://download-west.oracle.com/docs/cd/B14099_19/sol.1012/repca.1012/repca/load.htm#sthref158

Oracle Portal Virtual Host and SSL

2006-10-31T07:56:00.000-08:00

I recently ran into an issue that Oracle Portal was presenting the wrong base href in some pages. This was a virtual hosted environment, that is, several machine names point to the same installation of portal. We had followed the virtual hosting documentation perfectly, and in fact, we had it running for quite some time without any issue. The issue cropped up when we added an additional virtual host.
This additional host was the first one that was not SSL. It took a little while to figure it out, but the solution was to make sure that all of the https (ssl) virtual hosts appear in the httpd.conf file prior to the first http virtual host. I'm still not sure why this would make a difference, but it solved the problem.

Setting Up SSL for Application Express

2006-10-30T08:52:00.000-08:00

If you are reading this, you might be interested in this post as well and probably this one.

I'll try to give sufficient info to do this...
All steps are done with ORACLE_HOME set to the middle tier home (Apache, Companion CD home)

1. You need to use Oracle Wallet Manager (OWM) to create a wallet and get it set up.
-- a. set up ORACLE_HOME, etc.
-- b. go to $ORACLE_HOME/bin
-- c. run Oracle Wallet Manager ./owm
-- d. create a new wallet and save in a new directory (e.g. /home/oracle/myWallet/)
-- e. create a certificate request
-- -- i. the common name is the name of the url, for example www.concept2completion.net
-- -- ii. Organizational Unit and Organization are just text associated with your company
-- -- iii. You should spell out the state
-- f. export the request to a text file
-- g. You will need to go to a certificate authority (CA) to get a certificate and paste in the contents of the text file created in (f). I have had problems with both goDaddy and Verisign. I have had good luck with entrust.com and thawte.com
-- h. If you get a trial certificate you will need to get the trial Root Certificate (aka trusted certificate) from the CA. Save it as a text file. Install that into OWM as a trusted cert.
-- i. Now install your cert (from g) in OWM and save.
2. Configure your ssl.conf file, located in $ORACLE_HOME/Apache/Apache/conf
-- a. You can use the default listen and port settings (probably 4443 or 4447) or you can change to 443. If you change to 443, you need to change all occurences. Also, the apachectl file will need to be owned by root (located in $ORACLE_HOME/Apache/Apache/bin/). Same requirement as running on Port 80.
---- 1. Setting up Apache to run on ports below 1024
1 Shutdown OHS
2 Become root
3 cd $ORACLE_HOME/Apache/Apache/bin
4 chown root .apachectl
5 chmod 6750 .apachectl
6 cd $ORACLE_HOME/Apache/Apache/logs
7 rm -f *
-- b. Besides the port change, you need to change the location of your wallet and give your wallet password. You can encrypt the password, but I'm not covering that here and now. (See Oracle Support Note 184677.1)
SSLWallet file:/home/oracle/myWallet/
SSLWalletPassword mySuperPW1
-- c. Save ssl.conf
3. Here is the trick. You need to configure OPMN to run in SSL mode. Edit the file opmn.xml (located in $ORACLE_HOME/opmn/conf/)
-- a. under start mode, look for ssl-disabled
-- -- change to
-- -- ssl-enabled
-- b. Save opmn.xml
4. go to $ORACLE_HOME/opmn/bin and restart
-- a. opmnctl stopall
-- b. opmnctl startall

OK, now you are running in SSL. You might want to run everything in SSL, or just some things. To do this, you can set an Apache Rewrite Rule. Here is an example that will rewrite everything that is in pls/apex to https (assumes you are running on port 443). Edit your httpd.conf file, add the lines below at the end of the file, opmnctl stopall, opmnctl startall:
RewriteEngine On
RewriteCond %{SERVER_PORT} !^443$
RewriteRule ^/pls/apex/(.*)$ https://concept2completion.net/pls/apex/$1 [L,R]

Hope this captures all of the steps. Let me know if this helps.

If you are reading this, you might be interested in this post as well and probably this one.

Oracle Instant Portal - Public

2006-10-19T12:22:00.000-07:00

I recently began playing with OIP a bit more. It has a fun UI, so when my son asked to make a website for his school project I suggested that my son use it for his project. I figured it would force me to learn the in-and-outs a bit better. Well, I was right.

After getting some content into the site, my son asked to make it available so anyone could see it. I had never even tried it. So, I just tried it. I added PUBLIC to the oip_available_users group in Oracle Internet Directory (OID). Then I added PUBLIC as a view user to the pages I wanted to be public. Seems to make sense, right? It didn't work. After a little poking around I found out that OIP won't allow a page to be public. So, what's the next thing to do? Remove the public user, right? Wrong!

If you use the OIP user management feature to remove a user, it DELETES the user from OID. That, of course, is exactly what I did. I deleted the public user from OID, thereby causing all of my public portal pages to break. I could barely log in.

I did a little research to find a solution. The two solutions I could find were reinstall and try reseeding all of the initial users, then clean up. I did not like either choice. I just created a new user in OID called PUBLIC. Everything has worked since then, but I may yet have issues.

Update: Since this original post I have found that a lot of permissions get lost. You will need to run the script that recreates all of the public permissions on form building, etc. If you run in to this, run all the set_user_acl procedures in the /portal30/admin/plsql/wwv/wwvsecd.sql script, which reference the PUBLIC user (USER_PUBLIC).

Oracle Application Express File Download

2006-09-20T12:12:00.000-07:00

I recently had a client complain that when clicking on a file from an ApEx application it always asked them to save or open the file rather than just rendering it in the browser via a plug-in (e.g. rendering a pdf in Acrobat Reader).

I almost always want that, so it never seemed to be an issue to me, but I could see the point. I had utilized the standard ApEx routines for downloading files from a blob in my custom table. This has the following code:

htp.p('Content-length: ' || v_length);
-- the filename will be used by the browser if the users does a save as
htp.p('Content-Disposition: attachment; filename="' || v_file_name || '"');
-- close the headers
owa_util.http_header_close;
-- download the BLOB
wpg_docload.download_file( Lob_loc );

This code is fine and it does just what it will do, but the blue text causes (at least some, including IE) browsers to force a download, and never open within the browser.

It was easy to comment out that line, but that left another issue. Now when the user does a save (right clicks and does "Save as" or saves from the plugin) it doesn't have the correct filename. It will have as a filename whatever comes after the last slash (/) in the url. That probably looks something like my_package.download?p_id=12442. Users really didn't like that.

So . . . What to do? We changed the download links to look like this instead:
/apexfiledownload//
like this
/apexfiledownload/12442/myFile.pdf

Then we used an Apache rewrite rule to route that to our download routine (my_package.download?p_id=12442). Here is the rule:

RewriteRule ^/apexfiledownload/([^/]+)/?(.*) /htmldb/my_package.download?p_id=$1 [PT]

That works and everyone is happy now.

I have an idea that I could also have implemented this by simply changing the offending line from attachment to inline:
htp.p('Content-Disposition: inline; filename="' || v_file_name || '"');
Testing that will be left to another day.

Always something to do during a con-call

2006-09-14T13:16:00.000-07:00

Just to show that there is more to do than Oracle . . . I was recently on a long con-call. And you can always find something to do during a long con-call. I had to switch phones to make the call because a couple of the keys on my cordless Motorola phone (md400 series) had stopped working. It happened over time, becoming more and more annoying until a few just don't work at all. Of course, the "1" doesn't work--and I live in the Boston area, where you need to dial a 1 to call next door. So, I grabbed another phone, dialed the call-in number, and got to work.

The hardest part, of course, is to just get the darn thing apart. At least nothing was glued together. It just required some prying and determination (and removing a few screws, 2 obvious, 2 hidden under a panel).

Then off to figure out the problem. The contacts were all good, and the keys all looked the same, but a few just wouldn't cause the contacts to connect.

So, I added a little piece of aluminum foil over each key and reassembled (you can see the first five in the picture). Now it works great, maybe a little too easy to get a number, but I suspect that will change over time. Now I don't need to buy a new phone, and it didn't take me any time because I was on a con-call.

Oracle 10gAS install in a custom database

2006-08-07T18:51:00.000-07:00

If you create a custom database (10.2.0.2) with the dbca, it will automatically install Oracle Spatial. If you use the command line or sqlplus, it doesn't. Spatial is required prior to running Repca. But, the pre-check doesn't catch that it isn't there. And the select statement given in the 10gAS documentation returns true when it isn't really there. So, be sure to put in prior to installing, otherwise you'll have to remove the metadata repository and start over. No fun.

Application Express (html db) Breadcrumbs

2006-07-28T14:59:00.000-07:00

Here is a little bug (in 2.0 anyway).

Create a breadcrumb, b1. Create a child, b1_1. Delete the page associated with b1. The child still exists, but there is no way to edit it or delete it.

Solution:
select to_char(id), * from flows_02000.wwv_menu_options
where flow_id =

find the option that you want to edit. Copy the id.

Edit a different menu option, you'll see a link like the one below:
htmldb/f?p=4000:290:::NO:290:F4000_P290_ID:6009524130400412

change the final number to the id you copied.

Voila! Now you can edit your menu option and give it a new parent.

Hope this helps someone.

Oracle Advanced Queue pl/sql Subscriptions

2006-07-28T14:53:00.000-07:00

Here is a quick pointer on Oracle Advanced Queues and pl/sql registrations. In the documenation it tells you that the queue name must be less than or equal to 30 characters for pl/sql subscriptions to work. It doesn't mention that schema.queue_name must be <= 30. But that is the case--at least in 9i. Once you get to 10g (or 10.2.0.1 anyway) it is even more difficult. Same rule applies, but what they don't tell you is that in 10g it is "schema"."queue_name" must be <= 30. So really, the length of the queue name plus the length of the schema must be <= 25. This can be tricky if you do an upgrade and suddenly your plsql queue stops working.

Installing Oracle 10g AS w/ Forms on SuSE 9

2006-05-19T21:06:00.000-07:00

Wow, this has been a challenge. I'll try to put up a few useful URLs and then add comments later. I wish I had taken closer notes along the way.

Be sure to install Motif (32 Bit). And get this... the SuSE ES9 will likely have a newer version of Motif than Forms Server will accept, so you will need to be sure to install the correct version. If you just want to get it to install, but don't want to compile forms, you can get it to work with a link, but then frmcmp.sh won't work:
ln -s libXm.so.3 libXm.so.2
So, you should just do the download (I think this is the link, though I'm not even sure anymore):
http://support.novell.com/techcenter/psdb/388a4c0511756c9a8aff38e2c5170509.html

If you forget to do this, you will get an error like this:
01/05/21 09:42:44 Internal server error
java.lang.UnsatisfiedLinkError: /lib/librwu.so: libXm.so
.2: cannot open shared object file: No such file or directory

Set up Enviroment: Run OraRun by Novell
http://ftp.novell.com/partners/oracle/sles-9/

Install GCC 32 Bit, DB1 32 Bit

Error:

checking for hardnofiles = 65536 found hardnofiles = 1024 Failed
checking for softnofiles = 4096; softnofiles = 1024

Solution: Set Up Security Parameters (I should boil this down, but the URL below gets you there, ):
http://asktom.oracle.com/pls/ask/f?p=4950:8:::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:454420165038

Error in WebCache Configuration Assistant: error while loading shared libraries: libdb
See http://nextre.it/oracledocs/gridcontrolonsles9.html (basically install db1 32 bit)

Also check this URL:
http://ivan.kartik.sk/oracle/install_ids10_suse.html

Fast Refreshing a Materialized View

2006-05-19T11:49:00.000-07:00

Just a quick post about fast refreshing an MV.

Example:
schema1 in db1 has a database link to schema2 in db2

schema2 has select on table t1 in schema3 in db2

You want to create a fast refreshable MV in schema1 on table t1.

You must create a materialized view log on t1 and grant select on the log to schema2:

SQL> connect schema3@db2
Enter password: ********
Connected.
SQL> create materialized view log on t1;

Materialized view log created.

SQL> grant select on mlog$_t1 to schema2;

Grant succeeded.

If you neglect to do the grant, you will get the following error:

ORA-12008: error in materialized view refresh path
ORA-01031: insufficient privileges
ORA-02063: preceding line from LINK_TO_ESUBMIT
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 2

Seems like there could be a better error message, but that's what you get.

Oracle 10gAS SSO UID attribute

2006-04-24T15:21:00.000-07:00

Here is a quick tidbit on Oracle SSO for v10.1.2.0.2. There is a new user attribute, uid, that is required for v10.1.2.0.2. I'm not sure what version introduced it, but it was not required in 9iAS R2. You will get a 500 Internal Server Error if you attempt an SSO login with a OID user account that does not have a UID attribute.

I'll try to get a full upgrade plan from 9i R2 Portal to 10gAS v10.1.2.0.2 shortly.

Time to Start

2006-03-24T13:21:00.000-08:00

Wow. I can't believe it has taken me this long to join the blogging fray, but here I am. Of course, I've had time to create the blog site, but that's all the time I have at the moment. More soon . . .