Tech Blog
Oracle 10gReports containing images generate
huge PDF files
October 18, 2007 - Kim
Nupp
Add the following registry string value entry to
your Oracle forms key: REPORTS_JPEG_QUALITY_FACTOR
provide a setting between 0 and 100. I chose 50. The
default is 100. Oracle suggests 75.
My report contained 12 images. Adding this
entry with a value of 25 took my 24mb file down to 1mb and the
images were still very good. It will depend on your image
resolution and original compression, as to how far you can decrease
the quality factor.
REP error - Unable to retrieve a string from the
report builder message
October 17, 2007 - Kim
Nupp
After searching many forums for the resolution to
this error and trying many faulting solutions, I discovered this
error occurred simply because the disk was full. I changed the
reports_tmp to a different disk drive and cleaned up the reports
output virtual directory.
Client_Text_IO (Webutil), File 0 bytes
September 22, 2007 - Kim
Nupp
I was using webutil client_text_io to build a
dynamic batch file to be executed from the form using the webutil
host procedure. The problem was, the batch file was not
executing because it always still open (with zero bytes)
notwithstanding my failed attempts at using the
client_test_io.FClose. What I found was that the file would
close once the full set of logic under the when-button-pressed
trigger was completed. I found by using the "SYNCHRONIZE"
forms built-in, the file would close and the batch file would
execute as expected.
Uploading JPEGS to Oracle database via Oracle
Forms - Error: Could not find file
September 21, 2007 - Kim
Nupp
I found that not all jpegs are created equal.
Especially when trying to upload them to the Oracle database using
Oracle forms and Read_Image_File. The software that we
developed allows the user to upload images from their digital
cameras to the Oracle database, however, we were consistently
running into errors stating that it could not "find" the
file. We downloaded image compression software,
compressed and "converted" the files and they uploaded
just fine.
WebUtil Error: WUT-121 The file transfer has
been forbidden by the Administrator
September 8, 2007 - Kim
Nupp
I received the above noted Webutil error "The
file transfer has been forbidden by the Adminstrator", when
executing the webutil_file_transfer.as_to_client function. My
webutil.cfg had the transfer.appsrv.enabled=TRUE and the
transfer.appsrv.write.1=c:\temp (which
is the default). My form requirement, however, allowed the
user to select the client location where they wanted to download the
file. To eliminate the error, just change the c:\temp
to * as follows:
transfer.appsrv.write.1=*
Query MySQL images (longBlob) from Oracle -
ORA-0997
August 17, 2007 - Kim Nupp
A recent customer requirement had be diving into
Oracle Heterogeneous Services (HSOBC). I had to write an
Oracle report (Report Builder 6i) to display images from a MySQL
database from a 3rd party vendor. I had used the Transparent Gateway
before and had remembered certain workarounds to obtain the Blobs or
longs via a remote database link (ie, copy table), so I proceeded
along my way setting up the hsodbc connectivity.
Once I had it setup, I was successfully querying
basic data from MySQL via SQL*Plus. I proceeded on to my report only
to receive the dreaded ORA-0997 Illegal use of long datatype. I
could not copy the data to a temporary table (which was my initial
plan) or read the longBlobs from MySQL. I needed a different
workaround.
Here is what I came up with. I would read the
data from MySQL and use a dumpfile to write the images to the
operating system. I could then set the "Read
From File" property in the report to display the image.
The key here was a HS pass-through query:
v_out:=
dbms_hs_passthrough.execute_immediate@hsrdb(v_qry);
I built a database procedure as follows:
create or replace procedure
write_image(p_tkt_nbr IN VARCHAR2) as
v_out number;
v_qry varchar2(300) := 'select img into dumpfile "C:/img_tmp/img_'||p_tkt_nbr||'.jpg" '||
' from images where tkt_nbr = '||p_tkt_nbr;
begin
v_out := dbms_hs_passthrough.execute_immediate@hsrdb(v_qry);
end;
/
Then in my report, I created a functional column
that called the database procedure to write the file and build the
file name string. The file name string was returned as the
data for the column (properties: datatype | Character, Read
from File | Yes, File Format | Image).
Works like a charm.
Remember to remove the files after running your
report. If you don't you will get a fatal exception if the
file name already exists (which you can handle in your procedure
call in the report). Also, you may fill up the
disk.
Also, note in the example above I had created an
img_tmp directory off the root of c:\
For notes on setting up heterogeneous
services see Metalink Note: 217186.1
Cannot copy .fmx while user is in form
July 29, 2007 - Kim Nupp
Ever get that support call that requires you to make
a quick fix to a form, but now the form is open by many users in the
production environment. You want to roll the fix out as soon as
possible, but do not want to kick everyone out of the system?
You may see the error "... cannot copy file with a user-mapped section open".
Well, to get around this you can turning off the FORMS "Memory
Mapping" string in the registry on the appserver. The
various value are as follows:
- Forms 6.0.x : FORMS60_MMAP = 0
- Forms 9.0.x : FORMS90_MMAP=FALSE
- Forms 10.1.2: FORMS_MMAP=FALSE
Notice that pre-9i, the value is zero.
To turn it back on, use a 1 (number one). HOWEVER, please
understand that this is not recommended in a production environment
because it increase memory usage. If more than one user opens
the same form, it will be loaded into memory again and not
"re-use" the same form in memory. As you can see if
you have a system with a lot of users, this could create a monster.
Oracle also recommends a
"dual-environment" approach, where you have two
directories of the production code and you simply copy the form to
the "2nd directory" then change the forms path to
"roll-out" the newer version of the form. The new
users logging in will pick up the new forms, while existing users
will still view the "older" forms. For more details
see Oracle Metalink Note:286762.1
SQLPlus - Update multiple columns with
subquery
December 2, 2006 - Kim Nupp
This is one of those simple SQL*Plus DML commands
for which we often forget the syntax. It is a multi-column,
sub-query UPDATE statement; allowing you to update multiple columns
in the same table at once using a select statement. The key is
remembering the simple placement of the parenthesis and
commas. The update below increases everyone's salary in the
sales dept by 5% and increases their commission by 2%.
UPDATE
EMP EB
SET (SAL, COMM, LSTUPDT_DT) =
(SELECT E.SAL+(E.SAL*.05),
E.COMM+.02,
SYSDATE
FROM EMP E
WHERE E.EMP_ID = EB.EMP_ID)
WHERE dept = 'SALES';
Oracle Forms batch text conversion
August 30, 2006 - Kim Nupp
If you ever have the need to dump a set of Oracle forms to text this should do the trick. I come across this need every so often when the client needs know every place in the system that a field or object is referenced.
The database is easy. Just look in the USER_SOURCE or ALL_SOURCE dictionary views. As for the forms you can either open every form and do a search or dump the forms to text and use your operating system to perform the search. This simple batch or cmd file can be executed from the command prompt to convert all forms in a specified directory to text (fmt files).
::convert_forms.cmd, execute from directory where fmbs reside
cls
echo converting Forms to text
for /f "delims=." %f IN ('dir /b *.fmb') do (frmcmp
module=%f.fmb userid=scott/tiger@prod batch=yes module_type=form script=yes build=no
output_file=c:\my10gfmt\%f.fmt window_state=minimize
echo Finished
Oracle Forms batch compile
August 30, 2006 - Kim
Nupp
If you ever have the need recompile a complete directory of Oracle forms this should do the trick. This simple batch or cmd file can be executed from the command prompt to convert all forms in a specified directory to fmx files.
::compile_forms.cmd, execute from directory where fmbs reside
cls
echo compiling Forms
for /f "delims=." %f IN ('dir /b *.fmb') do (frmcmp
module=%f.fmb userid=scott/tiger@prod batch=yes module_type=form script=yes build=no
output_file=c:\my10gfmx\%f.fmx window_state=minimize
echo Finished
|
