10g: Use V$SQL_BIND_CAPTURE to find bind variables values

In your database, you probably see a lot of queries running using bind variables,
like :B1 in the following script, and you don't know its value:

DELETE FROM ASO_PRICE_ADJ_ATTRIBS WHERE PRICE_ADJUSTMENT_ID = :B1

If you need to find the value of this variable, first, query V$SQL to find the SQL_ID:

SELECT SQL_ID, SQL_TEXT
FROM V$SQL
WHERE SQL_TEXT LIKE 'DELETE FROM ASO_PRICE_ADJ_ATTRIBS%';

SQL_ID	SQL_TEXT
5tya0jzb74tu9	DELETE FROM ASO_PRICE_ADJ_ATTRIBS WHERE PRICE_ADJUSTMENT_ID = :B1

Then, use the SQL_ID in V$SQL_BIND_CAPTURE to find the value:

SELECT NAME, VALUE_STRING, DATATYPE_STRING, LAST_CAPTURED
FROM V$SQL_BIND_CAPTURE
WHERE SQL_ID='5tya0jzb74tu9';

NAME	VALUE_STRING	DATATYPE_STRING	LAST_CAPTURED
:B1	15499478	NUMBER	03/12/2010 03:15:39 πμ
:B1	15075864	NUMBER	19/11/2010 02:31:16 πμ
:B1	15148907	NUMBER	20/11/2010 02:31:50 πμ
:B1	14251241	NUMBER	15/10/2010 03:45:30 μμ

You can see that at various points in time the query is used with different :B1 values.
From the LAST_CAPTURED column you can decide which value is the one you are interested.

Of course, you may combine these queries:

SELECT NAME, VALUE_STRING, DATATYPE_STRING, LAST_CAPTURED
FROM V$SQL_BIND_CAPTURE A, V$SQL B
WHERE A.SQL_ID = B.SQL_ID
AND SQL_TEXT LIKE 'DELETE FROM ASO_PRICE_ADJ_ATTRIBS%'
ORDER BY LAST_CAPTURED;

You may use DBA_HIST_SQLBIND instead of V$SQL_BIND_CAPTURE, if you want to base your conclusions on AWR historical data.

How to change CHARACTER SET parameter not to a superset

This is a method to change CHARACTER SET not to a superset as UTF-8, but to one at the same level,
e.g. from WE8ISO8859P1 to EL8ISO8859P7 (Greek).

For Oracle 9 and up, make sure you are connected "AS SYSDBA" in sqlplus.
For Oracle 8/8i, make sure you are connected as INTERNAL in svrmgrl.
Then follow these steps:

SHUTDOWN IMMEDIATE;

Make sure there is a database backup you can rely on, or create one.

STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE EL8ISO8859P7;

An alter database takes typically only a few minutes or less.
It depends on the number of columns in the database, not the amount of data.

SHUTDOWN;

If you use Oracle 8, then also do:

STARTUP RESTRICT;
SHUTDOWN;

The extra restart/shutdown is necessary in Oracle8(i) because of a SGA initialization bug which is fixed in Oracle9i.
Restore the parallel_server parameter in INIT.ORA, if necessary.
Restart the database:

STARTUP;

Forms fail to generate after 10g RDBMS upgrade [cannot pass cursor variables to a procedure that is called through a database link]

After 10g RDBMS upgrade some forms fail to generate. An example is shown in the image, where you can see the generation is stuck compiling a trigger.
The relevant Metalink note is 300990.1:
Compiling Form Fails or FRM-10760 When Using TYPE Declarations In Procedure Called Via DB Link.

Although the note refers to Forms 9.0 to 10.1, the problem also occurs in Forms 6i, since it is a RDBMS functionality change:

In Oracle Server 10g and higher restrictions have been placed on cursor variables. To quote from the Oracle Server 10g documentation, Chapter 6 Performing SQL Operations from PL/SQL:
Restrictions on Cursor Variables
You cannot pass cursor variables to a procedure that is called through a database link.
The restriction therefore applies to TYPE declarations, %TYPE and %ROWTYPE used in a remote database procedure.

In the trigger, in which the compilation hungs, there is a call to a procedure:

XXE_F36_LLU_WCRM.prc_Get_LocationInfo

In this package there are declarations, like:

prec_cli IN w_llu_ll_order@datallu%ROWTYPE

p_wcrm_order_id IN w_llu_ll_order.order_id@datallu%TYPE

w_llu_ll_order is a table in a remote database, accessed via a DB link.
These declarations have to be replaced.
I tried 2 workarounds, which both work.
The first is to create local views that point to the remote table, like:

create view apps.v_w_llu_ll_order as select * from w_llu_ll_order@datallu;

and then replace the above declarations:

prec_cli IN v_w_llu_ll_order%ROWTYPE

p_wcrm_order_id IN v_w_llu_ll_order.order_id%TYPE

The second is to check the column types in the remote table and explicitly set the variable types to match them:

p_wcrm_order_id IN number(10)

Now, the form generation will not hung.