Creating PDF's with PL/SQL and itext

There are several ways to create PDF’s, on the client (e.g. PDFCreator) or on the middletier.
But to create PDF’s merely with packages in the database, that’s another story. Even Apex uses some Java on the middletier.
The product PLPDF on the other hand is a well known utility which provides this functionality (using open-source ‘itext’), but.. you have to pay for this.

So why not do it ourselves with itext, open-source (answer: a lot of work and knowledge of Java…).

What follows are the basics to use pl/sql in combination with ‘itext’.
1. Download itext-jar-file (itext-2.1.5.jar):

http://sourceforge.net/project/showfiles.php?group_id=15255&package_id=12596

Copy the jar-file to your database server

2. load these java-classes in the database.

grant create procedure to <owner>;
grant create table to <owner>;

loadjava -user exg/exg iText-2.1.5.jar

Some explanation about loadjava:
http://www.oracleutilities.com/OSUtil/loadjava.html

Check if it’s correctly loaded in the database:

select object_name, object_type, status
from dba_objects
where object_type like ‘%JAVA%’
and owner=’<owner>’
and status <> ‘VALID’

3. A lot of Java is not valid, you need some other Java classes of ‘bouncycastle’ (encryption):

(more…)

By |June 20th, 2009|Database|0 Comments
Read More

Create virtual host with Webcache on OAS 10.1.0.2

Creating Virtual Host with Webcache and HTTP-server.

Not that high-tech, but takes quite a lot of small steps, and must be done without taking risks in a production environment. I did this with Enterprise Manager, while I did not completely rely on  my abilities on updating the $ORACLE_HOME/webcache/webcache.xml ….. , and eventually I had to hand it over to the production-DBA.

Example: vhost.domain.nl on the server  ‘myhost’.
Goal: make the webcache also listen on port 80 (besides the default 7779) and connect the incoming request to the http-server on port 8000.

1. Preparation to make the webcache listen on port 80. All ports under 1024 is managed by the  user root, so it needs to be changed:
- take the webcache down (through enterprise manager: middle.myhost.domain.nl –> Web Cache, radio button, stop)
- connect  as root (or through “su -”)
- set the right environment (you need $ORACLE_HOME !)
- cd $ORACLE_HOME/webcache/bin
- ls -al webcached  –> shows that the owner is oracle.
- “./webache_setuser.sh setroot oracle”
- ls -al webcached  –> should show the owner is root.
- startup the webcache.

2. Let the webcache listen on port 80.
Through Enterprise manager:
- middle.myhost.domain.nl –> Web Cache, tab Administration.
- choose Ports
- under ‘Listen Ports’ –> Add a row.
- IP Address: *
- Port: 80
- Protocol: HTTP
- restart the webcache.

3. Put the virtual hosts in de /etc/hosts of the node “myhost”:

194.13.16.125      vhost.domain.nl vhost
194.13.16.125      catchall.domain.nl catchall

The second virtual host I use in this case as a workaround for catching all the requests on poort 8000 which are not for the virtual host ‘vhost.domain.nl. The startup-page of Apache will be showed.This virtual host must be the first in httpd.conf (port 8000) by the way.
This can also be done in the configuration of webcache by the way I’ve been told…

4. Make the http-server […]

By |June 12th, 2009|App. Server|0 Comments
Read More

Headstart 10g installation/configuration 6.5.4

10 years ago the installation and configuration of Headstart was a lot of sweating, mostly not automated installation-scripts with a lot of faults in it. And thank God it has not changed!! Scripts are Windows-oriented, documentation is not quite self-explainable.

I am not allowed to publish an updated documentation, but sharing experiences is something else.

Maybe still unclear, but 1 different grant or statement in Headstart can make a world of difference…

Situation

- Designer repository-database op Linux, had ooit iemand half Headstart 6.5.0 op geïnstalleerd

- Applicatieserver op Linux (10.2.0.2), idem.

- Windows-clients.

Downloaded:

- PATCH6540_FOR_10G.zip (looks like a patch, maar it isn’t a patch…)

- PATCH65401_FOR_10GR2.zip ( indeed, it is a patch)

- ONE_OFF_QMSOLM65.zip

Install this in this order on your windows-client and on the applicatie-server, check with “unzip –l” where the files will come. Chosen by me: c:/hst10g en /software/hst10g.

Compile the forms, libraries, menu’s against the hst65-user in de designer-repository, will we do this in another step.

Next step

Got the installation document ‘Installation Instructions for Oracle Headstart 6.5.4.0 for 10g (Revision 6.5.4.1 for 10g)’. This document is not in the base-set you download…. Exists of 4 parts, client (1) , application server(2), the development database (3) and the designer repository (4)

1. Client

Put the shortcuts of the directory ‘environment-setup’ on my desktop, updated the properties. The documentation keep mentioning about ‘forms90’ which is not right. Your URL for the serverinstall could be something like ( my case: “LTOA”,domain JOBACLE, de URL ) : http://ltoa.jobacle.nl:7778/forms/frmservlet?config=hsd65_serverinstall .A little bit in the blind, on the application server isn’t installed anything yet.

2. On the application server LTOA

The documentation tells me that you have to update the file <10gasbifhome>\j2ee\OC4J_BI_Forms\config\oc4j.properties (back-slashes, so this will not work..) in order to use hsd65formsweb.cfg.

Advice: don’t do that. Several tools […]

By |June 8th, 2009|App. Server|0 Comments
Read More

Updating sequences, the Developer's way

As a DBA it’s quite common to update sequences by altering the increment of the sequence temporarily. Therefore it’s nice to know how Developers are  acting with the problem that a sequence is not in line with the corresponding id of a table. Just another view on this matter, kind a liked it..

declare
v_num number;
v_max number;
–
v_table varchar2(30) := ‘table_x’;
v_id    varchar2(10) := ‘s_id’;
v_seq   varchar2(15) := ‘s_seq1′;
begin
–
execute immediate
‘select  max(‘|| v_id ||’)
from’|| v_table
into v_max;
–
execute immediate
‘select ‘|| v_seq ||’.nextval from dual’
into   v_num;
–
dbms_output.put_line(‘max: ‘ || v_max);
–
while v_num < v_max
loop
execute immediate
‘select ‘|| v_seq ||’.nextval
from   dual’
into   v_num;
dbms_output.put_line(‘seq is now:’ || v_num);
end loop;
–
end;

With thanks to my  colleague  Diana Veerman.

By |June 8th, 2009|Database|0 Comments
Read More

Discoverer, can't edit some workbooks, OLException

A Discoverer item:  while trying to edit some workbook items or when choosing Edit -> Condition , errors popped up like this:
“OracleBI Discoverer   :    java.lang.ArrayIndexOutOfBoundsException ”

“An error occurred while attempting to perform the operation.
The operation did not complete successfully.

Extra Internal Info:
(OLException
Cause:OLException {
Code: OLCmRefException::eNullRef
Args: OLCmRefException::eNullRef}}

Version Discoverer: 10.1.2.54.25

Doc ID:     559541.1:
After applying Discoverer Cumulative Patch 5 (Patch 6472361), or Cumulative Patch 6 (Patch 6669003) certain workbooks are failing to open or are reporting an error when editing conditions in Discoverer Plus.
Bug : 6933011

Solution in the doc is not quite self-confident:

1. Open a Support Request (SR) with Oracle Support and request the password for Discoverer Cumulative Patch 8, Patch 7306816.

2. Test the patch on a development or test server first.

3. Once the fix is confirmed, you may promote the patch to your production server environment.

Known Workaround

“If this problem is only occurring on a limited number of workbooks, you may recreate the workbook in Discoverer Plus.  Recreating and then re-saving the workbook eliminated the error in all cases.”

And that’s what we did….

By |June 8th, 2009|App. Server|0 Comments
Read More