Wednesday, July 30, 2008

Check your iDS (Oracle Forms) version and support

Since I am now temporarily working in Oracle Forms, I looked at our version of the Forms Builder, 9.0.2.9.0. I found out it was just desupported by Oracle on 01-JUL-08 (Oracle Document ID: 283326.1). Ok I looked at the next version 9.0.4. Oracle is desupporting it on 31-DEC-08 (Oracle Document ID: 298788.1). Well I guess 10g R2 (10.1.2.2) is what we should go to. The company I work does not like to go to first releases of software. I am glad they do not do that for our in-house developed software.



Mike

Tuesday, July 29, 2008

Simulate set_item_property('Block.item', intial_value, 'VALUE'); in Oracle Forms

Have you ever wanted to programmatically set and change the initial value of Oracle Form item at run time? Have you to do something like this in your Oracle Form?
set_item_property('Block.item', intial_value, 'VALUE')

and received the following error when you attempt to compile your form:
Error 201 at line .., column .. identifier 'INITIAL_VALUE' must be declared'


Well I have found a way to emulate having a dynamic value for the "Initial Value" property of an item.

1) Add a non-database item to your block. Call the item something like 'your_item_initial_value'

2) Set the "Copy Value from Item" property on your item to be your_block.your_item_initial_value

3) Whenever you want to change the initial value of your item just assign a value to the your_item_initial_value item.

4) Whenever you want to remove an initial value from your item just assign NULL to the your_item_initial_value item.

Note: I am using Oracle9iDS Release 2. Newer versions of iDS may permit you to programmatically change the initial value.

Mike

Multiple PL/SQL Editors Open in Oracle Forms Builder

This blog entry is for my own purpose. It is a little thing that I seem to frequently forget in Oracle Forms Builder. Nowadays I am still sporadicly working in Forms. Actually I am the only Forms developer left in our shop and we have quite a few production Forms applications. I actually love Oracle Forms, but our department has on its long term plan to move off of Forms to either J2EE or ApEx, but that is a topic for another future blog entry.

While getting back into the Forms Builder, I frequently want to have multiple PL/SQL Editor windows open editing different program units at the same time. A couple years ago I found out that if you hold the SHIFT key and double click on a program unit's icon in the Object Navigator pane, Forms Builder opens a new PL/SQL Editor window open while keeping all current PL/SQL Edit windows open!!

Mike

Friday, July 11, 2008

SP2-0552: Bind variable "..." not declared.

I was creating a SQL*Plus script for testing the results of a new report. The script prompted the me for some values and then ran some queries. I then was going to compare those query results with the results of the new report. Pretty basic. I have done this type of thing many, many times before.

The script issues the ACCEPT command to prompt and receive my test values. A SELECT statement runs to query the database. Here is a sample SQL*Plus script based on our friend the EMP table:
ACCEPT p_emp_no char PROMPT "Enter employee ID: "

select *
from emp
WHERE empno = &p_emp_no;

I would run the script in SQL*Plus (Oracle 9.2.0.4.0) and get:

SP2-0552: Bind variable "P_EMP_NO" not declared.
Ok, what the heck is going on? Well it ends up an hour or so earlier I had ran another SQL*Plus script which set SCAN off (SET SCAN OFF) because the prior script had an ampersand (&) in one of the remarks. Well with SCAN set OFF, SQL*Plus was somehow converting '= &p_emp_no" into "= :p_emp_no" and looking at p_emp_no as if it were an undefined bind variable. I discovered this when I finally exited SQL*Plus and reentered and reran the script and it worked. So I added SET SCAN ON to my script and it now works!!

Mike