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>

select * from table_name where ... for update

The statement "select * from table_name where ... for update;" will hold rows of data for next DML statements in the same session. The selected rows is locked until a "commit" or a "rollback" command is issued. Any other session (even only a "select" statement) accessing those rows will have to wait until the lock is released. The Select For Update is easy to create a deadlock in the database. The lock can be seen from views:

SQL> select * from v$locked_object;
SQL> select * from v$transaction;


In a normal select, REDO is not used. But, the Select For Update will use REDO space.

SQL> set autotrace traceonly statistics
SQL> select * from test4update for update;

412167 rows selected.

Statistics
------------------------------------------------------
288 recursive calls
419550 db block gets
6003 consistent gets
0 physical reads
85955168 redo size 31676930 bytes sent via SQL*Net to client
192619 bytes received via SQL*Net from client
27479 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
412167 rows processed