Daily Archives: June 30, 2010

11g, Oracle, Oracle 10g, oracle 9i

Oracle UTL_RECOMP package and the RECOMP_PARALLEL subprogram

Leave a comment

During the normal administration and operation of an Oracle database system, programming units within the database can become invalid for various reasons. To resolve this issue, the Oracle RDBMS provides the package UTL_RECOMP to recompile objects which are currently in an invalid status. The UTL_RECOMP package provides the administrator with 3 subprogram units: PARALLEL_SLAVE, RECOMP_PARALLEL, and RECOMP_SERIAL. This article covers the user of package and subprogram unit UTL_RECOMP.RECOMP_PARALLEL and was written against an Oracle 10.2.0.4 RDBMS on RHEL.

1. Logon to you Oracle database server as the Oracle software owner.

2. Connect to SQLPLUS as the sysdba user.

mylinux:> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 30 19:58:23 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. The RECOMP_PARALLEL subprogram unit recompiles all objects or objects within a schema using a degree of parallelism specified in the command or by the init parameter JOB_QUEUE_PROCESSES. This means that if you specified a degree of parallelism of 4, than Oracle would attempt to recompile 4 objects at a single time.

4. To compile all objects in the database with a parallelism of 2 use the command: execute utl_recomp.recomp_parallel(2);

SQL> execute utl_recomp.recomp_parallel(2);

PL/SQL procedure successfully completed.

SQL>

5. To compile all object in a particular schema with a parallelism of 2 use the command: execute utl_recomp.recomp_parallel(2, ‘SCOTT’);

SQL> execute utl_recomp.recomp_parallel(2, 'SCOTT');

PL/SQL procedure successfully completed.

SQL>

This completes the recompilation of objects using the package UTL_RECOMP with subprogram unit RECOMP_PARALLEL.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i, SQL

Oracle – Encoding and Decoding Oracle data with UTL_ENCODE Package

Leave a comment

Oracle RDBMS provides the ability to encode and decode data through the package UTL_ENCODE. This functionality can be extremely helpful in the transmission of data over open networks where plan text transmission would not be wanted. In this article we will review the use of encoding plain text and decoding the string produced.

1. Logon to your Oracle database server as the Oracle software owner.

2. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 30 20:00:09 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Execute the following PL/SQL block to produce the encoded text string for: Test encode procedure.

set serveroutput on

declare
v_text varchar2(100):='Test encode procedure';
v_encoded_text varchar2(200);
v_ct number;
begin

v_encoded_text:=utl_encode.text_encode(v_text,'WE8ISO8859P1',
UTL_ENCODE.BASE64);
dbms_output.put_line(v_encoded_text);

end;
/

Output from SQL*PLUS

SQL> declare
2
3 v_text varchar2(100):='Test encode procedure';
4 v_encoded_text varchar2(200);
5 v_ct number;
6
7 begin
8
9 v_encoded_text:=utl_encode.text_encode(v_text,'WE8ISO8859P1',
UTL_ENCODE.BASE64);
10
11 dbms_output.put_line(v_encoded_text);
12
13 end;
14 /
VGVzdCBlbmNvZGUgcHJvY2VkdXJl

PL/SQL procedure successfully completed.

SQL>

4. The PL/SQL block executed generated the encoded version of ‘Test encode procedure’ as VGVzdCBlbmNvZGUgcHJvY2VkdXJl. Now we will decode the string with the text decode function.

set serveroutput on

declare

v_text varchar2(100):='VGVzdCBlbmNvZGUgcHJvY2VkdXJl';
v_encoded_text varchar2(200);
v_ct number;

begin

v_encoded_text:=utl_encode.text_decode(v_text,'WE8ISO8859P1',
UTL_ENCODE.BASE64);

dbms_output.put_line(v_encoded_text);

end;
/

Output from SQL*PLUS:

SQL>
SQL> declare
2
3 v_text varchar2(100):='VGVzdCBlbmNvZGUgcHJvY2VkdXJl';
4 v_encoded_text varchar2(200);
5 v_ct number;
6
7 begin
8
9 v_encoded_text:=utl_encode.text_decode(v_text,'WE8ISO8859P1',
UTL_ENCODE.BASE64);
10
11 dbms_output.put_line(v_encoded_text);
12
13 end;
14 /
Test encode procedure

PL/SQL procedure successfully completed.

SQL>

The decode function displays the original text presented to the encode function.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com