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):

By |June 20th, 2009|Categories: Database|Tags: , , |0 Comments

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 listen on port 8000.

– Enterprise Manager : middle.myhost.domain.nl > HTTP-Server > Administration > Server Properties
– ‘Listening Addressen and Ports’ : Add Another Row.
– Listening IP Address: empty
– Listening Port : 8000
– apply.
– restart HTTP_Server

5. Create the virtual hosts in HTTP-Server.

– middle.myhost.domain.nl > HTTP_Server > Virtual Host.

– Create Virtual Host.
– Server Name: catchall.domain.nl
– Document Root: default -> /software/oracle/middle/Apache/Apache/htdocs
– Administrator E-mail : me@domain.nl
– IP Address: Listen on all the main server IP addresses
– Listening Ports : 8000
– Error Log Path : default
– Select Clent Access Log Filename: default

– Create Virtual Host.
– Server Name: vhost.domain.nl
– Document Root: default -> /software/oracle/middle/Apache/Apache/htdocs
– Administrator E-mail : dba@domain.nl
– IP Address: Listen on all the main server IP addresses
– Listening Ports : 8000
– Error Log Path : default
– Select Clent Access Log Filename: default

6. Adding an ‘Origin Server’ in Webcache (myhost – http-server: poort 8000)

– middle.myhost.domain.nl > Web Cache > Administration > Origin Servers
– Create
– Host: myhost.domain.nl
– Port: 8000
– Rest: default
– Restart the webcache

7. Maken the virtual host known with the Webcache, and redirect it to the origin server:

– middle.myhost.domain.nl > Web Cache > Administration > Sites
– Named Sites Definitions: create
– Host: vhost.domain.nl
– Port: 80
– select at  available Origin Servers: myhost.domain.nl:8000
– Move this to the selected Origin Servers.

8. Modify the virtual host e.q. for the log-files

– In Enterprise Manager : middle.myhost.domain.nl > HTTP-Server > Administration > Advanced Server Properties
– Open httpd.conf
– Go to the  virtual host “vhost.domain.nl”
Shows a lot like this:
—————-
Listen 8000

<VirtualHost *:8000>
ServerAdmin dba@domain.nl
ServerName vhost.domain.nl
</VirtualHost>
————————-

Put the following lines in the Virtual Host (do not use cut/paste from wordpad/notepad !!!):

ErrorLog “|/software/oracle/middle/Apache/Apache/bin/rotatelogs /software/oracle/middle/Apache/Apache/logs/vhost_error_log 43200″
CustomLog “|/software/oracle/middle/Apache/Apache/bin/rotatelogs /software/oracle/middle/Apache/Apache/logs/vhost_access_log 43200″ common

9. Test it  while the DNS is not set yet:

To test it you can put the vhost in your host-file of your client  (C:\WINDOWS\system32\drivers\etc\hosts):

194.13.16.127    vhost.domain.nl vhost

By |June 12th, 2009|Categories: App. Server|Tags: |0 Comments

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 of Oracle, like RDA, will not recognize the configuration in hsd65formweb.cfg.User your own formsweb.cfg in </forms/server. Merge the two files, and update the items being said in the documentation. As this appears also has some difficulties I’ll give hereby a great part of my formsweb.cfg, where the repository is called ‘DES10G’:

[default]

# !!modify here your Headstart settings!!

# baseHTML=<HSD65_HOME>\html\hsd65basejini.htm

baseHTML=/software/hst10g/html/hsd65basejini.htm

# baseHTML=%hsd65_HOME%\html\hsd65basejini.htm

# baseHTMLjinitiator=<HSD65_HOME>\html\hsd65basejini.htm

baseHTMLjinitiator=/software/hst10g/html/hsd65basejini.htm

# System parameter: base HTML file for use with Sun’s Java Plug-In

baseHTMLjpi=basejpi.htm

# System parameter: delimiter for parameters in the base HTML files

HTMLdelimiter=%

# System parameter: working directory for Forms runtime processes

# WorkingDirectory defaults to <oracle_home>/forms if unset.

workingDirectory=

# System parameter: file setting environment variables for the Forms runtime processes

# envFile=default.env

# !!modify here your Headstart settings!!

envFile=/software/hst10g/html/hsd65default.env

# Forms runtime argument: whether to escape certain special characters

# in values extracted from the URL for other runtime arguments

escapeparams=true

# Forms runtime argument: which form module to run

form=test.fmx

# Forms runtime argument: database connection details

userid=

# Forms runtime argument: whether to run in debug mode

debug=no

# Forms runtime argument: host for debugging

host=

# Forms runtime argument: port for debugging

port=

# Other Forms runtime arguments: grouped together as one parameter.

# These settings support running and debugging a form from the Builder:

otherparams=buffer_records=%buffer% debug_messages=%debug_messages% array=%array% obr=%obr% query_only=%query_only% quiet=%quiet% render=%render% record=%record% tracegroup=%tracegroup% log=%log% term=%term%

# Sub argument for otherparams

buffer=no

# Sub argument for otherparams

debug_messages=no

# Sub argument for otherparams

array=no

# Sub argument for otherparams

obr=no

# Sub argument for otherparams

query_only=no

# Sub argument for otherparams

quiet=yes

# Sub argument for otherparams

render=no

# Sub argument for otherparams

record=

# Sub argument for otherparams

tracegroup=

# Sub argument for otherparams

log=

# Sub argument for otherparams

# term=

# !!modify here your Headstart settings!!

term=/software/hst10g/hst/admin/qmsrf65w.res

# !!modify here your Headstart settings!!

# for AS10g deployment:

# RegistryPath=/forms/hsd65html/

# for Ds10g deployment:

RegistryPath=/forms/hsd65html/

serverApp=hsd65reg

# HTML page title

pageTitle=Oracle Application Server Forms Services

# HTML attributes for the BODY tag

HTMLbodyAttrs=

# HTML to add before the form

HTMLbeforeForm=

# HTML to add after the form

HTMLafterForm=

# Forms applet parameter: URL path to Forms ListenerServlet

serverURL=/forms/lservlet

# Forms applet parameter

codebase=/forms/java

# Forms applet parameter

#imageBase=DocumentBase

# Forms applet parameter

width=1024

# Forms applet parameter

height=768

# Forms applet parameter

separateFrame=true

# Forms applet parameter

splashScreen=

# Forms applet parameter

background=

# Forms applet parameter

lookAndFeel=Oracle

# Forms applet parameter

colorScheme=teal

# Forms applet parameter

logo=

# Forms applet parameter

restrictedURLparams=HTMLbodyAttrs,HTMLbeforeForm,pageTitle,HTMLafterForm,log,allow_debug,allowNewConnections

# Forms applet parameter

formsMessageListener=

# Forms applet parameter

recordFileName=

# Forms applet parameter

serverApp=default

# Forms applet archive setting for JInitiator

# archive_jini=frmall_jinit.jar

# !!modify here your Headstart settings!!

archive_jini=frmall_jinit.jar,hst65.jar

# Forms applet archive setting for other clients (Sun Java Plugin, Appletviewer, etc)

# archive=frmall.jar

# !!modify here your Headstart settings!!

archive=frmall.jar,hst65.jar

# Number of times client should retry if a network failure occurs. You should

# only change this after reading the documentation.

networkRetries=0

# Page displayed to Netscape users to allow them to download Oracle JInitiator.

# Oracle JInitiator is used with Windows clients.

# If you create your own page, you should set this parameter to point to it.

jinit_download_page=/forms/jinitiator/us/jinit_download.htm

# Parameter related to the version of JInitiator

jinit_classid=clsid:CAFECAFE-0013-0001-0022-ABCDEFABCDEF

# Parameter related to the version of JInitiator

jinit_exename=jinit.exe#Version=1,3,1,22

# Parameter related to the version of JInitiator

jinit_mimetype=application/x-jinit-applet;version=1.3.1.22

# Page displayed to users to allow them to download Sun’s Java Plugin.

# Sun’s Java Plugin is typically used for non-Windows clients.

# (NOTE: you should check this page and possibly change the settings)

jpi_download_page=http://java.sun.com/products/archive/j2se/1.4.2_06/index.html

# Parameter related to the version of the Java Plugin

jpi_classid=clsid:CAFEEFAC-0014-0002-0006-ABCDEFFEDCBA

# Parameter related to the version of the Java Plugin

jpi_codebase=http://java.sun.com/products/plugin/autodl/jinstall-1_4_2-windows-i586.cab#Version=1,4,2,06

# Parameter related to the version of the Java Plugin

jpi_mimetype=application/x-java-applet;jpi-version=1.4.2_06

# EM config parameter

# Set this to “1” to enable Enterprise Manager to track Forms processes

em_mode=1

# Single Sign-On OID configuration parameter

oid_formsid=%OID_FORMSID%

# Single Sign-On OID configuration parameter

oracle_home=/software/oracle/middle

# Single Sign-On OID configuration parameter

formsid_group_dn=%GROUP_DN%

# Single Sign-On OID configuration parameter: indicates whether we allow

# dynamic resource creation if the resource is not yet created in the OID.

ssoDynamicResourceCreate=true

# Single Sign-On parameter: URL to redirect to if ssoDynamicResourceCreate=false

ssoErrorUrl=

# Single Sign-On parameter: Cancel URL for the dynamic resource creation DAS page.

ssoCancelUrl=

# Single Sign-On parameter: indicates whether the url is protected in which

# case mod_osso will be given control for authentication or continue in

# the FormsServlet if not. It is false by default. Set it to true in an

# application-specific section to enable Single Sign-On for that application.

ssoMode=false

# The parameter allow_debug determines whether debugging is permitted.

# Administrators should set allow_debug to “true” if servlet

# debugging is required, or to provide access to the Forms Trace Xlate utility.

# Otherwise these activities will not be allowed (for security reasons).

allow_debug=false

# Parameter which determines whether new Forms sessions are allowed.

# This is also read by the Forms EM Overview page to show the

# current Forms status.

allowNewConnections=true

# EndUserMonitoring

# EndUserMonitoringEnabled parameter

# Indicates whether EUM/Chronos integration is enabled

EndUserMonitoringEnabled=

# EndUserMonitoringURL

# indicates where to record EUM/Chronos data

EndUserMonitoringURL=

# Example Named Configuration Section

# Example 1: configuration to run forms in a separate browser window with

# “generic” look and feel (include “config=sepwin” in the URL)

# You may define your own specific, named configurations (sets of parameters)

# by adding special sections as illustrated in the following examples.

# Note that you need only specify the parameters you want to change. The

# default values (defined above) will be used for all other parameters.

# Use of a specific configuration can be requested by including the text

# “config=<your_config_name>” in the query string of the URL used to run

# a form. For example, to use the sepwin configuration, your could issue

# a URL like “http://myhost.mydomain.com/forms/frmservlet?config=sepwin”.

[sepwin]

separateFrame=True

lookandfeel=Generic

# Example Named Configuration Section

# Example 2: configuration forcing use of the Java Plugin in all cases (even if

# the client browser is on Windows)

[jpi]

baseHTMLJInitiator=basejpi.htm

# Example Named Configuration Section

# Example 3: configuration running the Forms ListenerServlet in debug mode

# (debug messages will be written to the servlet engine’s log file).

[debug]

serverURL=/forms/lservlet/debug

# Sample configuration for deploying WebUtil. Note that WebUtil is shipped with

# DS but not AS and is also available for download from OTN.

[webutil]

WebUtilArchive=frmwebutil.jar,jacob.jar

WebUtilLogging=off

WebUtilLoggingDetail=normal

WebUtilErrorMode=Alert

WebUtilDispatchMonitorInterval=5

WebUtilTrustInternal=true

WebUtilMaxTransferSize=16384

baseHTMLjinitiator=webutiljini.htm

baseHTMLjpi=webutiljpi.htm

archive_jini=frmall_jinit.jar

archive=frmall.jar

lookAndFeel=oracle

form=webu_test.fmx

envFile=default.env

# !!modify here your Headstart settings!!

[hsd65_serverinstall]

form=hsdinst.fmx

logo=menulogo.gif

[hsd65_demo]

form=hsd0000f.fmx

userid=hdemo65/hdemo65@des10g

logo=menulogo.gif

# to find *.gif files in jars

imageBase=codebase

[hsd65_utilities]

form=hsustart.fmx

logo=menulogo.gif

archive_jini=frmall_jinit.jar,hst65.jar,hsu65.jar

userid=

# to find *.gif files in jars

imageBase=codebase

[hsd65_administration]

form=hsu0000f.fmx

logo=menulogo.gif

userid=hsu65/hsu65@des10g

# to find *.gif files in jars

imageBase=codebase

[hsd65_foundation]

form=qfd0000f.fmx

logo=menulogo.gif

userid=hsu65/hsu65@des10g

# to find *.gif files in jars

imageBase=codebase

[hsd65_debug_monitor]

form=qms0002f.fmx

logo=menulogo.gi

#userid=hsu65/hu65@des10g

# to find *.gif files in jars

imageBase=codebase

[run_report_demo]

form=run_report

userid=hdemo65/hdemo65@des10g

logo=menulogo.gif

Then update the <appserver_home>/forms/server/forms.conf.

Here a part of the forms.conf, where /software/hst10g is the home of Headstart :

# !! modify her your headstart settings!!

AliasMatch ^/forms/hsd65java(..*) “/software/hst10g/java/$1″

AliasMatch ^/forms/hsd65html(..*) “/software/hst10g/html/$1″

AliasMatch ^/forms/hsd65icons(..*) “/software/hst10g/html/icons/$1″

AliasMatch ^/forms/hsd65-help(..*) “/software/hst10g/doc/$1″

AliasMatch ^/forms/hsd65-cdm(..*) “/software/hst10g/hsu/cdm/$1″

# forms90temp of formstemp? I think it is not used anymore….

AliasMatch ^/forms/forms90temp(..*) “/software/oracle/middle/temp/$1″

You can test this partly by the url: http://ltoa.jobacle.nl:7778/hsd65html/hsd65basejini.htm .

If you use a default environment file, update the file /software/hst10g/html/hsd65default.env. Be aware that in the several PATH’s the seperation character is a colon (:), instead of a semicolon (;), like the documentation said.

The best you can do is copy the library- , class- and forms-paths from an existing environment-file.

#hsd65default.env

# Please refer to the Forms documentation for details.

#

ORACLE_HOME=/software/oracle/middle

FORMS_MAPPING=/forms/forms-output/

FORMS_OUTPUT=/software/hst10g/output

FORMS_REPFORMAT=html

NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

NLS_DATE_FORMAT=DD-MM-YYYY

# Headstart utility environment settings

HSD65_CDM_MAPPING=/forms/hsd65-cdm/

HSD65_HELPFILE_WEB_PATH=/forms/hsd65-help/

HSD65_HOME=/software/hst10g

HSD_REPSERVER=reptest

#

# Search path for Forms applications (.fmx files, PL/SQL libraries)

# If you need to include more than one directory, they should be semi-colon

# separated (e.g. c:\test\dir1;c:\test\dir2)

#

FORMS_PATH=/software/oracle/middle/forms:/software/hst10g/hst/admin:/software/hst10g/hst/fndapp:/software/hst10g/hst/demo:/software/hst10g/hsu/forms:/software/ontw/<applicatie>

REPORTS_PATH=/software/hst10g/hst/admin;/software/hst10g/hst/demo

TNS_ADMIN=/software/oracle/middle/network/admin

# webutil config file path

WEBUTIL_CONFIG=/software/oracle/middle/forms/server/webutil.cfg

# Disable/remove this variable if end-users need access to the query-where

# functionality which potentially allows them to enter arbitrary SQL

# statements when in enter-query mode.

FORMS_RESTRICT_ENTER_QUERY=FALSE

PATH=/software/oracle/middle/bin;/software/oracle/middle/jdk/jre/lib/i386/client;/software/oracle/middle/jdk/bin

FORMS=/software/oracle/middle/forms

CLASSPATH=/software/oracle/middle/j2ee/OC4J_BI_Forms/applications/formsapp/formsweb/WEB-INF/lib/frmsrv.jar:/software/oracle/middle/jlib/repository.jar:/software/oracle/middle/jlib/ldapjclnt10.jar:/software/oracle/middle/jlib/debugger.jar:/software/oracle/middle/jlib/ewt3.jar:/software/oracle/middle/jlib/share.jar:/software/oracle/middle/jlib/utj.jar:/software/oracle/middle/jlib/zrclient.jar:/software/oracle/middle/reports/jlib/rwrun.jar:/software/oracle/middle/forms/java/frmwebutil.jar:/software/oracle/middle/jlib/debugger.jar:/software/oracle/middle/jlib/ewt3.jar:/software/oracle/middle/jlib/share.jar:/software/oracle/middle/jlib/utj90.jar:/software/oracle/middle/jdk/jre/lib/rt.jar:/software/oracle/middle/jlib/zrclient.jar:/software/oracle/middle/reports/jlib/rwrun.jar:/software/oracle/middle/forms/java/comm.jar:/software/webutil/lib/webutil.jar:/software/oracle/middle/jdk/jre/lib/i386/native_threads/

LD_LIBRARY_PATH=:/software/oracle/middle/lib:/software/oracle/middle/jdk/jre/lib/i386:/software/oracle/middle/jdk/jre/lib/i386/native_threads:/software/oracle/middle/jdk/jre/lib/i386/server:/software/oracle/middle/jdk/jre/lib/i386/native_threads/:/software/oracle/middle/jre/1.4.2/lib/i386/native_threads/

3.  Development database

There is a shortcut delivered, named ‘Headstart Serverinstall’ to use on your desktop. Do not use this. It doesn’t work. Instead of this use the downloadable document : ‘How to manually do the Server Side Installation of Headstart Oracle Designer’.

Also don’t try to run the scripts from Linux, does not work. Oracle is working on the release for Linux, is coming somewhere in the future…

So run all the scripts from your Windows-client.

In our situation users were already created by /hst/create_hsd65_users.sql, but after that they did not follow the documentation and are all objects of hst65 (template owner) placed in an owner of an application. Doesn’t matter that much, but for my story I presume that all is made under the user hst65. When you install it from zero, you will have to run /hst/insthst.sql with the parameters c:/hst10g en hst65 draaien.

Between 6.5.0 en 6.5.4 there are very few changes in the table-sctructure, and only in packages, so I’ve been told…

Started with running c:/hst10g/hst/scripts/hstmsgh.sql (messages). When you run this on Linux it won’t work, because the file ‘cg$errors.pkb’ is not there for example. The “‘$” is unknown. There are workarounds, but the smooth way is to run it from your windows-client. Another thing what will be noticed: case-sensitive stuff in the scripts.

While running the scripts, noticed that qms_transaction_mgmt need an extra column, and we missed some grants:

As SYS:

grant execute on DBMS_PIPE to hst65

grant select on v_$session to hst65;

alter table qms_transactions add (STATEMENT_CTR NUMBER(10) DEFAULT 0 NOT NULL);

After that: hsttapi.sql , ins_msg.sql (many errors, because of many unique rows exists, but many rows are inserted), ins_srp.sql, ins_ref.sql as user hst65.

Several forms, libraries, en pll’s needs to be compiled on Linux. Do this against the hst65-user in de designer-repository. Unfortunately this user does not exist in this step – that’s the next one, but when this user exists, you may be able to compile with scripts like this:

Example of compile-scripts:

(frmcmp_batch.sh should normally be in $ORACLE_HOME/bin)

#compile_pll.sh

export FORMS_PATH=.:$FORMS_PATH

oldterm=$TERM

export TERM=vt220

for i in `ls *.pll`

do

echo Compiling fmbs $i ….

frmcmp_batch.sh module=$i userid==<un>/<pw>@<db> module_type=library compile_all=yes

done

export TERM=$oldterm

#compile_forms.sh

export FORMS_PATH=.:$FORMS_PATH

oldterm=$TERM

export TERM=vt220

for i in `ls *.fmb`

do

echo Compiling fmbs $i ….

frmcmp_batch.sh module=$i userid=<un>/<pw>@<db> module_type=form compile_all=yes

done

export TERM=$oldterm

#compile_forms.sh

export FORMS_PATH=$FORMS_PATH:.

oldterm=$TERM

export TERM=vt220

for i in `ls *.mmb`

do

echo Compiling mmbs $i ….

frmcmp_batch.sh module=$i userid=<un>/<pw>@<db> module_type=menu compile_all=yes

done

export TERM=$oldterm

You will notice that 1 menu will not compile: qms65mnt.mmb: error on QMS_MENU.SET_CURRENT_ACTION. Opened the menu in developer (in windows) , added the libraries qmlsib65 and qmsevh65. Seems very sloppy. Then copied the mmb to Linux and compiled.

4. Designer repository

Repeat step 3 on the designer-repository.

After that you need to configure hsu65 (Utilities).

Hier zit al het een en ander in, maar het is het handigste om deze helemaal leeg te gooien en opnieuw te beginnen. We hebben hem maar gedropt:

When it exists, empty or drop it, and start all over again. We dropped the user:

DROP USER HSU65 CASCADE;

CREATE USER HSU65

IDENTIFIED BY VALUES ‘3A8E5269C73B1476′

DEFAULT TABLESPACE INSP_SMALL

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK;

— 3 Roles for HSU65

GRANT CONNECT TO HSU65;

GRANT HSU65_USER TO HSU65 WITH ADMIN OPTION;

GRANT RESOURCE TO HSU65;

ALTER USER HSU65 DEFAULT ROLE ALL;

— 9 System Privileges for HSU65

GRANT CREATE TRIGGER TO HSU65;

GRANT CREATE ROLE TO HSU65;

GRANT CREATE TYPE TO HSU65;

GRANT CREATE PROCEDURE TO HSU65;

GRANT CREATE ANY SYNONYM TO HSU65;

GRANT DROP ANY SYNONYM TO HSU65;

GRANT UNLIMITED TABLESPACE TO HSU65;

GRANT CREATE INDEXTYPE TO HSU65;

GRANT CREATE OPERATOR TO HSU65;

— 1 Tablespace Quota for HSU65

ALTER USER HSU65 QUOTA UNLIMITED ON INSP_SMALL;

— 16 Object Privileges for HSU65

GRANT EXECUTE ON DES10G_OWNER.CKMIGFUN TO HSU65;

GRANT SELECT ON DES10G_OWNER.I$RM_ELEMENT_TYPES TO HSU65;

GRANT EXECUTE ON DES10G_OWNER.JR_SYSTEM_UTIL TO HSU65;

GRANT EXECUTE ON DES10G_OWNER.JR_UTIL TO HSU65;

GRANT EXECUTE ON DES10G_OWNER.SDD_ACC_PRIV_LIST TO HSU65;

GRANT SELECT ON DES10G_OWNER.SDD_DEPENDENCY_TYPES TO HSU65;

GRANT SELECT ON DES10G_OWNER.SDD_GLOBAL_NAMES TO HSU65;

GRANT EXECUTE ON DES10G_OWNER.SDD_SYS_PRIV_LIST TO HSU65;

GRANT EXECUTE ON HST65.CG$ERRORS TO HSU65;

GRANT EXECUTE ON HST65.HIL_MESSAGE TO HSU65;

GRANT EXECUTE ON HST65.QMS$ERRORS TO HSU65;

GRANT INSERT, SELECT, UPDATE ON HST65.QMS_MDE_PARAMS TO HSU65;

GRANT SELECT ON HST65.QMS_MESSAGE_PROPERTIES TO HSU65;

GRANT SELECT ON HST65.QMS_MESSAGE_TEXT TO HSU65;

GRANT INSERT, SELECT, UPDATE ON HST65.QMS_MODULES TO HSU65;

GRANT EXECUTE ON HST65.QMS_TRANSACTION_MGT TO HSU65;

But.. we forgot something:

grant create operator to hsu65;

grant create procedure to hsu65;

grant create view to hsu65;

create role hsu65_user;

grant select on dba_role_privs to hsu65;

grant select on dba_tab_privs to hsu65;

grant select on dba_synonyms to hsu65;

grant select on sys.v_$parameter to hsu65;

grant create any synonym,

create role,

drop any synonym to hsu65;

grant hsu65_user to hsu65 with admin option;

Data-import:

C:\hst10g\hsu\scripts\imp_sgl.bat C:\hst10g c: imp.exe hsu65/hsu65@des10g

As SYS:

grant execute on DBMS_PIPE to hsu65

grant select on v_$db_pipes to hsu65;

As HST65:

@C:\hst10g\hst\scripts\hstuserb.grt C:\hst10g hsu65

(with exit at the end!)

As DES10G_OWNER:

@C:\hst10g\hsu\scripts\d6grtsyn.sql C:\hst10g hsu65

As HSU65:

@C:\hst10g\hsu\scripts\insthsu.sql C:\hst10g hst65 hsu65

(with exit at the end)

Problem while executing the script: ‘cannot insert null’ . Filthy solutiong: disable triggers of hsu65.hsu_utilities.

After disabling, run it again:

prompt … installing Quality Check utilities

set termout off

— Applications

execute hsu_qa_app.install

— Domains

execute hsu_qa_dom.install

— Entities

execute hsu_qa_ent.install

— Events

execute hsu_qa_evt.install

— Functions

execute hsu_qa_fun.install

— Table Definitions

execute hsu_qa_tbl.install

— View Definitions

execute hsu_qa_vw.install

— Sequences

execute hsu_qa_osq.install

— Modules

execute hsu_qa_gem.install

— PL/SQL Definitions

execute hsu_qa_plm.install

— Reusable Module Components

execute hsu_qa_mco.install

— Oracle Databases

execute hsu_qa_orc.install

— Non-Oracle Databases

execute hsu_qa_ans.install

set termout on

prompt … installing Productivity Boosters

–set termout off

@c:\hst10g\hsu\scripts\pb\insallpb.sql

set termout on

prompt … adding grants to role HSU65_USER

set termout off

@c:\hst10g\hsu\scripts\hsu_user.rol

set termout on

prompt … creating Utilities Tree Structure

set termout off

@c:\hst10g\hsu\scripts\ins_nod

set termout on

prompt … inserting User Groups

set termout off

@c:\hst10g\hsu\scripts\ins_ugr

set termout on

prompt … resetting sequences

set termout off

@c:\hst10g\hsu\scripts\resetsq.sql HSU_QAS_SEQ1 HSU_QA_SETS ID

@c:\hst10g\hsu\scripts\resetsq.sql OMR_FNA_SEQ1 OMR_FUNCTIONAL_AREAS ID

@c:\hst10g\hsu\scripts\resetsq.sql OMR_ANT_SEQ1 OMR_ANNOTATIONS ID

Then:

create synonym hsu65.CG_REF_CODE for hst65.QMS_REF_CODES;

By the way: ofgtel65.pll points to QMS_REF_CODES instead of CG_REF_CODES.

By |June 8th, 2009|Categories: App. Server|Tags: , , |0 Comments

Updating sequences, the Developer's way

m4s0n501

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|Categories: Database|0 Comments

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|Categories: App. Server|0 Comments