Wrapping PL/SQL

Wrapping PL/SQL source is not so different using this method than it is using the WRAP binary. It is more flexible. Oracle developers can take advantage of many PL/SQL or Java utilities that exist in $ORACLE_HOME/bin or in supplied packages.

These utilities can load, encrypt, tune or debug code objects.  This chapter will focus on the utilities that perform these functions, including wrap, dbms_profiler, dbms_debug, loadjava, dropjava and loadpsp.

The first of these utilities to be discussed will be the wrap utility that allows PL/SQL developers to encrypt their code.

Procedure/Function                Description

WRAP                                     Overloaded function that returns the wrapped PL/SQL source code when provided with the original source.

CREATE_WRAPPED             Procedure that wraps the source code provided as input. It’s faster than using WRAP.

Create Country Table

SQL Code:
CREATE TABLE COUNTRIES
  (
    C_NAME  VARCHAR2(15 BYTE)
  );

Insert Data into Country Table

SQL Code:
REM INSERTING into COUNTRIES
Insert into COUNTRIES (C_NAME) values ('Poland');
Insert into COUNTRIES (C_NAME) values ('Germany');
Insert into COUNTRIES (C_NAME) values ('United States');
Insert into COUNTRIES (C_NAME) values ('Portugal');
Insert into COUNTRIES (C_NAME) values ('Czech Republic');
Insert into COUNTRIES (C_NAME) values ('China');
Insert into COUNTRIES (C_NAME) values ('Slovakia');
Insert into COUNTRIES (C_NAME) values ('Slovenia');

The DBMS_DDL package for Dynamic Wrapping

Use the overloaded WRAP function with EXECUTE IMMEDIATE to create the wrapped code, as the following example illustrates:

SQL Code:
DECLARE
  v_Procedure VARCHAR2(32767);
BEGIN
  v_Procedure :=  'create or replace PROCEDURE p_CountryList '
                  || 'AS '
                  || 'cursor crCountryList is '
                  || 'SELECT C_NAME FROM COUNTRIES; '
                  || 'BEGIN '
                  || ' for rc_countryList in crCountryList loop '
                  || '    dbms_output.put_line(rc_countryList.C_NAME);'
                  || ' end loop; '
                  || ' End;'; 
  EXECUTE IMMEDIATE DBMS_DDL.WRAP(v_Procedure);
END;

To see the wrapped procedure, select the text from the USER_SOURCE view.

SQL Code:
SELECT text
FROM user_source WHERE name = 'P_COUNTRYLIST';
 
Output:
TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
---------------------------------------------------------------------------
PROCEDURE p_CountryList wrapped                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
a000000                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
b2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
abcd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
7                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
c2 d2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
OBXTyU790UHZX1Pz/x6SK6zaQdowg0zwLcsVfC+EOo7QS9JbZQ2c1RiJi1GYkXCXR+Hnl92a                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
6yUmVMJdr3L/xdu8xYazffm813KuNLzJ0jnlJ1HKbCYmfWw51ec5hoXDZ2KBtgvDWEsq4RNq                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
3cHIqVKojYPLLqq6zBQLjSwb9gkM4mO6aOBaz2jrVZ+/wI9dqhpqq1XumZC83+s=

Execute the wrapped procedure to verify all works as expected

Set Serveroutput on
execute p_CountryList;

This returns the following result

Poland
Germany
United States
Portugal
Czech Republic
China
Slovakia
Slovenia

CREATE_WRAPPED

DBMS_DDL.CREATE_WRAPPED works in a similar way. The use of EXECUTE IMMEDIATE is not required.

Use SYS.DBMS_DDL.CREATE_WRAPPED (v_procedure)
with replace of EXECUTE IMMEDIATE DBMS_DDL.WRAP(v_Procedure)

PL/SQL Wrap Utility for Encryption

The wrap utility (wrap.exe) provides a way for PL/SQL developers to protect their intellectual property by making their PL/SQL code unreadable.

Instead, the wrap utility takes a readable, ASCII text file as input and converts it to a file containing byte code.  The result is that the DBA, developers or anyone with database access cannot view the source code in any readable format.

The command line options for wrap are:

wrap iname=[file] oname=[file]

· iname – The name of the unencrypted PL/SQL file to be used as input (your source file).

· oname – The name of the output file.  This file will be encrypted.

For more information http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/c_wrap.htm

How to use Ref Cursor example

How to use Ref Cursor example

A REF CURSOR is basically a data type.  A variable created based on such a data type is generally called a cursor variable.  A cursor variable can be associated with different queries at run-time.  The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).

Declaring a SYS_REFCURSOR Cursor Variable

The following is the syntax for declaring a SYS_REFCURSOR cursor variable:

name SYS_REFCURSOR;

name is an identifier assigned to the cursor variable.

The following is an example of a SYS_REFCURSOR variable declaration.

DECLARE
    rc_emp SYS_REFCURSOR;

Opening a Cursor Variable

Once a cursor variable is declared, it must be opened with an associated SELECT command. The OPEN FOR statement specifies the SELECT command to be used to create the result set.

Syntax:
OPEN name FOR query;

name is the identifier of a previously declared cursor variable.  Query is a SELECT command that determines the result set when the statement is executed. The value of the cursor variable after the OPEN FOR statement is executed identifies the result set.

Declaring a User Defined REF CURSOR Type Variable

You must perform two distinct declaration steps in order to use a user defined REF CURSOR variable:

• Create a referenced cursor TYPE


• Declare the actual cursor variable based on that TYPE

The syntax for creating a user defined REF CURSOR type is as follows:

Syntax:
TYPE cursor_type_name IS REF CURSOR [RETURN return_type];

The following is an example of a cursor variable declaration.

DECLARE
    TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;
    rc_emp emp_cur_type;

Closing a Cursor Variable

Unlike static cursors, a cursor variable does not have to be closed before it can be re-opened again. The result set from the previous open will be lost. The example is completed with the addition of the CLOSE statement.

Syntax:
CLOSE cursor_name;

N.B:

• Comparison operators cannot be used to test cursor variables for equality, inequality, null, or not null.


• Null cannot be assigned to a cursor variable.


• The value of a cursor variable cannot be stored in a database column.


• Static cursors and cursor variables are not interchangeable. For example, a static cursor cannot be used in an OPEN FOR statement.

Permitted Cursor Variable Parameter Modes

Operation	IN	IN OUT	OUT
OPEN	No	Yes	No
FETCH	Yes	Yes	No
CLOSE	Yes	Yes	No

Create Country Table

SQL Code:
CREATE TABLE COUNTRIES
  (
    C_NAME  VARCHAR2(15 BYTE)
  );

Insert Data into Country Table

SQL Code:
REM INSERTING into COUNTRIES
Insert into COUNTRIES (C_NAME) values ('Poland');
Insert into COUNTRIES (C_NAME) values ('Germany');
Insert into COUNTRIES (C_NAME) values ('United States');
Insert into COUNTRIES (C_NAME) values ('Portugal');
Insert into COUNTRIES (C_NAME) values ('Czech Republic');
Insert into COUNTRIES (C_NAME) values ('China');
Insert into COUNTRIES (C_NAME) values ('Slovakia');
Insert into COUNTRIES (C_NAME) values ('Slovenia');

Procedure using ref cursor

create or replace
PROCEDURE p_Get_Country (
    p_CountryName     IN VARCHAR2
    )
  AS
  rc_Country_Search  SYS_REFCURSOR;
  v_query            VARCHAR2(30000) := NULL;
  rows_fetched       number;
  TYPE t_tab IS TABLE OF COUNTRIES%ROWTYPE;
  rec_tab t_tab;
BEGIN
  if (p_CountryName is not null) then
    v_query := 'SELECT C_NAME FROM COUNTRIES 
                where UTL_MATCH.JARO_WINKLER_SIMILARITY
                (C_NAME,'|| '''' || to_char(p_CountryName) || ''''  || ') > 60';
  else
    v_query := 'SELECT C_NAME FROM COUNTRIES';
  end if;

  OPEN  rc_Country_Search FOR v_query;
  FETCH rc_Country_Search BULK COLLECT INTO rec_tab;
  rows_fetched := rc_Country_Search%ROWCOUNT;
  --Print Data to the console
  dbms_output.put_line('--------------------------------');
  FOR i IN 1..rows_fetched LOOP
      dbms_output.put_line('Country Name -> '      ||  rec_tab(i).C_NAME||CHR( 13 ) || CHR( 10 ));
  END LOOP;
  dbms_output.put_line('--------------------------------');
  CLOSE rc_Country_Search;
END p_Get_Country;

Out put 1

Set serveroutput on
DECLARE
  RC_COUNTRY_SEARCH SYS_REFCURSOR;
BEGIN
  P_GET_COUNTRY(
    P_COUNTRYNAME => null
  );
END;
--------------------------------
Country Name -> Poland
Country Name -> Germany
Country Name -> United States
Country Name -> Portugal
Country Name -> Czech Republic
Country Name -> China
Country Name -> Slovakia
Country Name -> Slovenia
--------------------------------

Out put 2

Set serveroutput on
DECLARE
  RC_COUNTRY_SEARCH SYS_REFCURSOR;
BEGIN
  P_GET_COUNTRY(
    P_COUNTRYNAME => 'Slov'
  );
END;
--------------------------------
Country Name -> Slovakia
Country Name -> Slovenia
--------------------------------

Procedure for Ref Cursor population and return through out parameter

create or replace
PROCEDURE p_Get_Country2 (
    p_CountryName     IN VARCHAR2,
    rc_Country_Search  OUT SYS_REFCURSOR
    )
  AS
  v_query         VARCHAR2(30000) := NULL;
  TYPE t_tab IS TABLE OF COUNTRIES%ROWTYPE;
  rec_tab t_tab;
BEGIN
  if (p_CountryName is not null) then
    v_query := 'SELECT C_NAME FROM COUNTRIES
                where UTL_MATCH.JARO_WINKLER_SIMILARITY
                (C_NAME,'|| '''' || to_char(p_CountryName) || ''''  || ') > 60';
  else
    v_query := 'SELECT C_NAME FROM COUNTRIES';
  end if;

  OPEN  rc_Country_Search FOR v_query;

END p_Get_Country2;

Populate Ref cursor variable in C#.Net Console base application using ADO.NET

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;

namespace TEST_ODP
{
    class Program
    {
        static void Main(string[] args)
        {
            // create connection
            OracleConnection conn = new OracleConnection("Data Source=orcl;User;Password=test;");
            // create the command for the stored procedure
            OracleCommand cmd = new OracleCommand();

            Console.WriteLine("Please input your searching keyword.");
            string str = Console.ReadLine();

            cmd.Connection = conn;
            cmd.CommandText = "p_Get_Country2";
            cmd.CommandType = CommandType.StoredProcedure;

            // add the parameters for the stored procedure including the REF CURSOR
            // to retrieve the result set
            cmd.Parameters.Add("p_CountryName", OracleType.VarChar).Value = str.ToString();
            cmd.Parameters.Add("rc_Country_Search", OracleType.Cursor).Direction = ParameterDirection.Output;

            // open the connection and create the DataReader
            conn.Open();
            OracleDataReader dr = cmd.ExecuteReader();

            // output the results and close the connection.
            while (dr.Read())
            {
                for (int i = 0; i < dr.FieldCount; i++)
                    Console.Write(dr[i].ToString() + ";");
                Console.WriteLine();
            }
            conn.Close();
            Console.ReadLine();
        }
    }
}

Manual Cleanup process and Uninstall Oracle for Windows system

1. Uninstall all Oracle components using the Oracle Universal Installer (OUI).


2. Delete the HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE key which contains registry entries for all Oracle products by using regedit.


3. Delete any references to Oracle services/components in the following registry location: HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/. Looks for key entries that starts with “Ora” which are obviously related to Oracle.


4. Reboot the workstation.


5. Delete the ORACLE_BASE directory. (i.e C:\Oracle)


6. Delete the C:\Program Files\Oracle directory.


7. Empty the temp directory.


8. Empty the recycle bin.

utl_match package for string matching

There are four functions included in the package utl_match use different methods to compare a source string and destination string, and return an assessment of what it would take to turn the source string into the destination string.

Source: $ORACLE_HOME/rdbms/admin/utlmatch.sql

Oracle added the UTL_MATCH package in Version 10gRelease 2 to compare strings. The four functions included in the package use different methods to compare a source string and destination string, and return an assessment of what it would take to turn the source into the destination string. The functions are broken down into two categories. The categories are actually the algorithms employed to analyze the strings.

Levenshtein Distance

The Levenshtein Distance (LD) algorithm, commonly called the Edit Distance (ED) algorithm, is the older of the two supported methods. It measures the distance between the source and destination strings. By distance, we’re referring to the number of changes required to turn the source string into the destination string.

Jaro-Winkler

The Jaro-Winkler algorithm is the second category of algorithms used in UTL_ MATCH. These functions take the same two arguments, but instead of simply calculating the number of steps required to change the source string to the destination string, it determines how closely the two strings agree with each other. The algorithm also tries to take into account the possibility of a data entry error when determining similarity.

Procedures/Functions

edit_distance

Returns the number of changes required to turn the source string into the destination string using the Levenshtein Distance algorithm.

function edit_distance returns binary_integer (
 s1    in        varchar2,
 s2    in        varchar2       
);

edit_distance_similarity

Returns an integer between 0 and 100, where 0 indicates no similarity at all and 100 indicates a perfect match.

function edit_distance_similarity returns binary_integer (
 s1    in        varchar2,
 s2    in        varchar2       
);

jaro_winkler

Instead of simply calculating the number of steps required to change the source string to the destination string returns similarity based on Jaro-Winkler distance algorithm, determines how closely the two strings agree with each other and tries to take into account the possibility of a data entry error.

function jaro_winkler returns binary_double (
 s1    in       varchar2,
 s2    in       varchar2       
);

jaro_winkler_similarity

Returns an integer between 0 and 100, where 0 indicates no similarity at all and 100 indicates a perfect match but tries to take into account possible data entry errors.

function jaro_winkler_similarity returns binary_integer (
 s1    in                 varchar2,
 s2    in                 varchar2       
);

Example 1:

SQL> SELECT UTL_MATCH.EDIT_DISTANCE('espresso', 'expresso')
  2    AS DISTANCE
  3  FROM DUAL;

  DISTANCE
----------
         1

SQL> SELECT UTL_MATCH.EDIT_DISTANCE_SIMILARITY('espresso', 'expresso')
  2    AS DISTANCE
  3  FROM DUAL;

  DISTANCE
----------
        88

SQL> SELECT UTL_MATCH.JARO_WINKLER('espresso', 'expresso')
  2    AS DISTANCE
  3  FROM DUAL;

  DISTANCE
----------
 9.25E-001

SQL> SELECT UTL_MATCH.JARO_WINKLER_SIMILARITY ('espresso', 'expresso')
  2    AS DISTANCE
  3  FROM DUAL;

  DISTANCE
----------
        92

Example 2:

CREATE TABLE TEST
( 
        test_id        number primary key, 
        test_name      varchar2(20), 
        test_date      date
);  

INSERT INTO TEST values(1, 'Sony CD Player', '20-FEB-2010');
INSERT INTO TEST values(2, 'Sony CD Player', '24-FEB-2010');
INSERT INTO TEST values(3, 'Pioneer DVD Player', '25-FEB-2010');
INSERT INTO TEST values(4, 'Sony CD Player', '25-FEB-2010');
INSERT INTO TEST values(5, 'Bose Speaker', '22-FEB-2010');
INSERT INTO TEST values(6, 'Tascam CD Burner', '25-FEB-2010');
INSERT INTO TEST values(7, 'Nikon digital camera', '22-FEB-2010');
INSERT INTO TEST values(8, 'Canon digital camera', '26-FEB-2010');

Commit;

Select TEST_ID,TEST_NAME,TEST_DATE
FROM TEST
WHERE  UTL_MATCH.JARO_WINKLER_SIMILARITY(test_name,'dogotal') > 60;

TEST_ID                TEST_NAME            TEST_DATE
---------------------- -------------------- -------------------------
7                      Nikon digital camera 22-FEB-00
8                      Canon digital camera 26-FEB-00

Proxy User in Oracle 10g

First we’ll create two database Schema users(cpdata, mpdata) and One Big Application User (app_user called Proxy User). The app_user will be used by a web application to connect to the database. Usually the application user, in this case cpdata and mpdata, will be authenticated by the web application through app_user.

Create User cpdata and mpdata as a Sys or System user which contain schema objects.

create user cpdata identified by cpdata;
grant create session, create table to cpdata;             
alter user cpdata quota unlimited on users;              

create user mpdata identified by mpdata;
grant create session, create table to mpdata;             
alter user mpdata quota unlimited on users;

Create Table on cpdata and mpdata schema

create table cpdata.test1
(
        Test1  varchar2(10),
        Test2  varchar2(10)
);      

create table mpdata.test2
(
        Test1  varchar2(10),
        Test2  varchar2(10)
);

Create User app_user, for a Proxy User.

create user app_user identified by app_user;
grant create session to app_user;

Grant Proxy Authentication

alter user cpdata grant connect through app_user;
alter user mpdata grant connect through app_user;

Now Connect cpdata and mpdata user using Proxy Authentication and access there schema objects

SQL> connect app_user[cpdata]/app_user@<SID>
Connected.
SQL> select user from dual;

USER
------------------------------
CPDATA

SQL> desc test1;
Name                                      Null?    Type
----------------------------------------- -------- ------------------
TEST1                                              VARCHAR2(10)
TEST2                                              VARCHAR2(10)

SQL> connect app_user[mpdata]/app_user@<SID>
Connected.
SQL> select user from dual;

USER
------------------------------
MPDATA

SQL> desc test2;
Name                                      Null?    Type
----------------------------------------- -------- ------------------
TEST1                                              VARCHAR2(10)
TEST2                                              VARCHAR2(10)

SQL> select sys_context('USERENV','PROXY_USER') from dual;

SYS_CONTEXT('USERENV','PROXY_USER')
-------------------------------------------------------------
APP_USER

More :

http://www.oracle.com/technology/products/ias/toplink/doc/1013/main/_html/dblgcfg008.htm

http://www.oracle.com/technology/tech/java/oc4j/1013/how_to/how-to-ds-proxy/doc/how-to-ds-proxy.html

http://www.dba-oracle.com/t_proxy_connect_authentication.htm

How to Insert and Update ampercent('&') character in the Oracle table

SQL> CREATE TABLE TEST ( TEST_ID  NUMBER,   TEST_DATA   VARCHAR2(50));
Table created.

To Insert or Update & into character field do the follow

SQL> show ESCAPE
escape OFF
SQL> Set ESCAPE "/"
SQL> show ESCAPE
escape "/" (hex 2f)

Now Insert Into the Test Table

SQL> INSERT INTO TEST (TEST_ID,TEST_DATA) VALUES (1,'Hospital /& Healthcare');

1 row created. 

Now Update Into the Test Table

SQL> UPDATE TEST
 2  Set TEST_DATA = 'Outsourcing /& Offshoring'
 3  WHERE TEST_ID = 1;

1 row updated.

Use the 10g Quoting mechanism:

Syntax q'[QUOTE_CHAR]Text[QUOTE_CHAR]'

N.B: Make sure that the QUOTE_CHAR doesnt exist in the text.

SELECT q'{This is Oracle’s 'quoted' text field & I like Oracle}' FROM DUAL;

SQL> INSERT INTO TEST (TEST_ID,TEST_DATA) VALUES (2,q'{ Aviation & Aerospace}');

1 row created.

SQL> UPDATE TEST
 2  Set TEST_DATA = q'{Information Technology & Services}'
 3  WHERE TEST_ID = 2;

1 row updated.

SQL> Select * from test;

 TEST_ID    TEST_DATA
---------- --------------------------------------------------
 1    Outsourcing & Offshoring
 2    Information Technology & Services