Convert database from WE8ISO8859P1 to AL32UTF8

Below query will return the name of character set in the database:

select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

We can't simply use "ALTER DATABASE CHARACTER SET" to go from WE8ISO8859P1 or
WE8ISO8859P15 or WE8MSWIN1252 to Unicode AL32UTF8 (or UTF8) because AL32UTF8 is not a binary superset of any of these character sets.

For 10g database, the conversion is easier because the scan tool (csscan) was installed for you during the 10g installation. I followed Metalink Doc ID 260192.1 to do the conversion on a 10g database.

1. Run $ORACLE_HOME/rdbms/admin/csminst.sql by 'sys as sysdba' to create database objects and a user for the objects.
2. Check if you have no invalid code points in the current character set. Run below line by 'sys as sysdba':
csscan FULL=Y FROMCHAR=WE8ISO8859P1 TOCHAR=WE8ISO8859P1 LOG=WE8check CAPTURE=Y ARRAY=1000000 PROCESS=2
The Scan Summary Report WE8check.txt shows only "Changeless data".
3. Check which rows contain data for which the code point will change. Run csscan:
csscan FULL=Y FROMCHAR=WE8ISO8859P1 TOCHAR=AL32UTF8 LOG=WE8TOUTF8 CAPTURE=Y ARRAY=1000000 PROCESS=2

Part of the WE8TOUTF8.txt is

[Data Dictionary Conversion Summary]

Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- -----------
VARCHAR2 6,067,118 1 0 0
CHAR 448 0 0 0
LONG 151,405 0 0 0
CLOB 63,023 7,385 0 0
VARRAY 20,839 0 0 0
--------------------- ---------------- ---------------- ---------------- -----------
Total 6,302,833 7,386 0 0
Total in percentage 99.883% 0.117% 0.000% 0.000%

The data dictionary can not be safely migrated using the CSALTER script

[Application Data Conversion Summary]

Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- -----------
VARCHAR2 348,264,895 84,466 102 0
CHAR 339,986,561 114 195 0
LONG 1,270 0 0 0
CLOB 0 0 0 0
VARRAY 1,525 0 0 0
--------------------- ---------------- ---------------- ---------------- -----------
Total 688,254,251 84,580 297 0
Total in percentage 99.988% 0.012% 0.000% 0.000%


- Drop snapshot
SQL> select min(snap_id) min_snapid ,max(snap_id) max_snapid from dba_hist_snapshot
SQL> execute dbms_workload_repository.drop_snapshot_range(3737, 3905);

- Need to handle foreign keys (Doc ID 1039297.6)

dump() function

When there are special characters, like newline and end-of-line, stored in a column, it is unable to see them directly. Function dump() is the best tool in this situation:

select dump(column_name) from ...

If there is a 0 returned, it hits NULL control character (ASCII character 0, eg chr(0) ).

View other users' PL/SQL procedure code or debug

The best way to grant a role or other users to read your procedure code is

SQL> grant DEBUG ON your_id.PROC_name to A_ROLE_NAME;

In this way, all users in the Role are able to view the code, but can not EXECUTE or modify the procedure's code. It only works in 10g and above.

If a user wants to debug a PL/SQL procedure of his own schema in Microsoft Visual Studio, make below grant to the user as SYS:

SQL> grant DEBUG CONNECT SESSIOM to user_id; 

This grant will address below errors:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_DEBUG_JDWP",

Oracle CJQ0 Process and J000 Process

In Oracle alert log, you may see below message:

Starting background process CJQ0
Thu Feb 03 10:38:40 2011
CJQ0 started with pid=48, OS id=15990882
......
Thu Feb 03 11:19:38 2011
Stopping background process CJQ0

CJQ0 is a coordinator job queue (CJQ0) process to run scheduled jobs defined by DBMS_JOB package. Oracle slave processes (J000 – J999) executes the scheduled jobs. The CJQ0 process will be keeping track of the schedule and starts slave processes so as to execute the scheduled jobs.

Normally, parameter job_queue_processes is not set to zero as it will disable all job queue processing and stop the CJQ0 process. It is suggested to set job_queue_processes to a value higher than the maximum number of simultaneous jobs you expect to run.

In troubleshooting, you might kill the process at OS level by using below query to identify SID and SPID values:

SQL> SELECT A1.SID, A2.SPID FROM V$SESSION A1,V$PROCESS A2
WHERE A1.PADDR = A2.ADDR AND TYPE='BACKGROUND'
AND A1.PROGRAM LIKE '%CJQ%'

To get the description on all Oracle background processes, run below query in the database:

SQL> select name, description from v$bgprocess;

Call a procedure that has IN OUT parameter

A parameter passed in OUT mode or IN OUT mode can not have a default value and can not be passed as a literal. Let's go to the basic:

create or replace procedure p_inout (p_in varchar2 default null, p_val IN OUT number) is
begin
p_val := p_val*10;
dbms_output.put_line(' p_val is ' || p_val);
end;
/

SQL> exec p_inout ('a', 1);
BEGIN p_inout ('a', 1); END;

*
ERROR at line 1:
ORA-06550: line 1, column 21:
PLS-00363: expression '1' cannot be used as an assignment target
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

The right way to call the procedure is to define a variable first (and assign a vaule to it for the IN OUT mode), and then pass the variable to the procedure:

SQL> set serveroutput on;

SQL> var inout number
SQL> exec :inout := 1

PL/SQL procedure successfully completed.

SQL> print inout

INOUT
----------
1

SQL> exec p_inout ('a', :inout);
p_val is 10

PL/SQL procedure successfully completed.

SQL> print inout

INOUT
----------
10
--
-- A block using the OUT value
--
SQL> declare
v1 NUMBER := 1;
v2 NUMBER := 0;
BEGIN
p_inout('aa', v1);
v2:= v1 + 50;
dbms_output.put_line(' v2 uses the OUT value, and equals ' || v2);
END;
/
p_val is 10
v2 uses the OUT value, and equals 60

PL/SQL procedure successfully completed.

SQL>