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

Grant ACL to APPS account after database upgraded to 11g

After database for E-Buiness Suite is upgraded to 11g, you have to grant ACL privilege to APPS account for some EBS modules to work. Here phebsapp is the EBS Apps server name.

1. Check if view dba_network_acls has any row. If yes, run below script to add privilege to APPS account:

DECLARE
acl_path VARCHAR2(4000);
BEGIN
SELECT acl INTO acl_path FROM dba_network_acls
WHERE host = 'phebsapp' AND lower_port IS NULL AND upper_port IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,'APPS','connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'APPS', TRUE, 'resolve');
END IF;
EXCEPTION
WHEN no_data_found THEN
-- below lines will create a ACL
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('apps.xml','ACL description', 'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('apps.xml','APPS', TRUE, 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','phebsapp');
END;
/

SQL> commit;

Please note "commit;" is necessary and very important. After that, you should see the entries in two views:

SQL> select * FROM dba_network_acls;
SQL> select * from dba_network_acl_privileges;

If you do not see rows, something is wrong.

And below two queries should return 1:

SQL> select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/apps.xml','APPS','connect') from dual;

SQL> select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/apps.xml','APPS','resolve') from dual;

2. if view dba_network_acls has no data. You can create ACL for the user:

SQL> show user
USER is "SYS"

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('apps.xml','APPS acess for UTL', 'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'apps.xml', principal => 'APPS',is_grant => true, privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','phebsapp');
END;
/

Sometimes, the domain name makes difference. It does not hurt to add full name to the access list:

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','phebsapp.domain.com');
END;
/

SQL> commit;

3. If things does not work, you can start over by dropping entries in dba_network_acls.

SQL> begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL('apps.xml');
commit;
end;
/

Now dba_network_acls, dba_network_acl_privileges, net$_acl should have nothing on apps.xml and everything no ACL is cleaned up.
SQL> select * from dba_network_acls;
SQL> select * from dba_network_acl_privileges;
SQL> select * from net$_acl;

Then, you can start over again.