Get All PLSQL Errors

I developed a function to get all PLSQL errors in schema.
This function should be used after calling program units so that can get PLSQL errors and you can use for logging and tracing.


Function Code

CREATE OR REPLACE FUNCTION GET_PLSQL_ERROS
   RETURN VARCHAR2
IS
   LC$RETVALUE   VARCHAR2 (4000);

   CURSOR LCUR$ERRORS
   IS
        SELECT DISTINCT NAME, TYPE
          FROM USER_ERRORS
      ORDER BY 1, 2;

   PROCEDURE ADD_LINE (IN_LINE IN VARCHAR2)
   IS
   BEGIN
      LC$RETVALUE := SUBSTR (LC$RETVALUE || IN_LINE || CHR (10), 1, 4000);
   END ADD_LINE;
BEGIN
   FOR LREC$ERRORS IN LCUR$ERRORS
   LOOP
      ADD_LINE (LREC$ERRORS.NAME || ' ' || LREC$ERRORS.TYPE);

      ADD_LINE (
         RPAD ('-', LENGTH (LREC$ERRORS.NAME || LREC$ERRORS.TYPE) + 1, '-'));

      FOR LREC$ERROR_DET
         IN (  SELECT LINE, POSITION, SUBSTR (TEXT, 1, 128) TEXT
                 FROM USER_ERRORS
                WHERE NAME = LREC$ERRORS.NAME AND TYPE = LREC$ERRORS.TYPE
             ORDER BY SEQUENCE)
      LOOP
         ADD_LINE (
               LPAD (LREC$ERROR_DET.LINE, 4)
            || ' '
            || LPAD (LREC$ERROR_DET.POSITION, 3)
            || ' '
            || LREC$ERROR_DET.TEXT);
      END LOOP;

      ADD_LINE ('*******************' || CHR (10));
   END LOOP;

   IF LENGTH (LC$RETVALUE) = 4000
   THEN
      LC$RETVALUE := SUBSTR (LC$RETVALUE, 1, 3996) || CHR (10) || '...';
   END IF;

   RETURN NVL (LC$RETVALUE, 'No Errors');
END;
/

Start Weblogic without user and password

When you want to launch your weblogic server at production, you need to use the startWeblogic.sh in your bin folder.

But each time it's launched it's asking you to authenticate yourself by typing a user and password. It's a problem when you want to automate the execution of your server...

To avoid this problem all you need to do is creating a file named "boot.properties" and insert into the two following lines :

username=<yourUserName>
password=<yourPassword>

this file must be placed in each server security folder : $WLS_HOME/user_projects/domains/<domainName/servers/<serverName>/security

Then, start server, it shouldn't ask you for anythin, then reopen your boot.properties file, password and username should be automatically encrypted !

Thanks

Avoid java out of memory with Weblogic

This is common exception always exists if you install weblogic and doesn't extend memory arguments in server.

The file "setDomainEnv.sh" in $WLS_HOME/user_projects/domains/<domainName>/bin/ has configuration of domain.

If you edit this file it will have the following default values.

MEM_ARGS="-Xms256m -Xmx512m"

export MEM_ARGS

MEM_PERM_SIZE="-XX:PermSize=48m"

export MEM_PERM_SIZE

MEM_MAX_PERM_SIZE="-XX:MaxPermSize=128m"

export MEM_MAX_PERM_SIZE

you should modify the MEM_ARGS java memory value depending of your server, here is the suggested to increase.

MEM_ARGS="-Xms2024m -Xmx3036m"

export MEM_ARGS

MEM_PERM_SIZE="-XX:PermSize=128m"

export MEM_PERM_SIZE

MEM_MAX_PERM_SIZE="-XX:MaxPermSize=512m"

export MEM_MAX_PERM_SIZE

Note : choosing the values depend on you server hardware.

Thanks

Reset Sequence Value

I have old table already have a lot of data, I take decision to create new sequence to use it for getting serials in primary key of table to it.

For example : table SCOTT.EMP has EMPNO primary key and I want to create new sequence EMPNO_SEQ to store NEXTVAL of sequence in EMPNO column.

CREATE SEQUENCE SCOTT.EMPNO_SEQ
   START WITH 1
   MAXVALUE 9999999
   MINVALUE 0
   CACHE 25;

Oooooops, EMPNO column already has stored data that is maximum than sequence next value.
So I take decision to create generic procedure to reset sequence next value to maximum value of primary key in any table.

I created RESET_SEQUENCE procedure which takes three parameters
a-IN_SEQUENCE_NAME : name of sequence that I will use.
b-IN_TABLE_NAME : name of table which I will store sequence next value in its column
c-IN_COLUMN_NAME : name of column, If it is null I will get column which is primary key

PRAGMA INLINE in Subprogram Calling

Introduction

Subprogram is procedure or function that is declare in declaration section like below

 DECLARE  
   PROCEDURE XXX (IN_PARAMATER NUMBER)  
   IS  
   BEGIN  
    --CODE OF PROCEDURE  
   END;  
   
   FUNCTION YYY (IN_PARAMETER NUMBER)  
    RETURN NUMBER  
   IS  
   BEGIN  
    --CODE OF FUNCTION  
   END;  
 BEGIN  
   --CODE OF ANONYMOUS BLOCK  
 END;  

   
When you call subprogram(procedure or function) in your code multiple time, in every call it will be loaded again so it will take more time to execute.
To enhance previous drawback we use inline to replace the subprogram call with the copy of already called subprogram before.

Change Database 11g Port Number

Sometimes after installing database you want to change port of Enterprise Manager.

To change it you should edit the following file and change ports number 
%ORACLE_HOME%\install\portlist.ini

You will find the file as below
Enterprise Manager Console HTTP Port (orcl) = 1158
Enterprise Manager Agent Port (orcl) = 3938

Change the ports as you like.

Thanks

Get Iterator of ADF Table

You can use the below method to get an iterator of ADF Rich Table.
You pass ADF Rich Table component and you will get DCIterator of table.

 
     public static DCIteratorBinding getDciteratorFromTable(RichTable table) {
        CollectionModel model = (CollectionModel)table.getValue();
        JUCtrlHierBinding treeBinding = (JUCtrlHierBinding)model.getWrappedData();
        return treeBinding.getDCIteratorBinding();
    }

You can import the following classes

 
import oracle.adf.model.binding.DCIteratorBinding;
import oracle.adf.view.rich.component.rich.data.RichTable;
import oracle.jbo.uicli.binding.JUCtrlHierBinding;
import org.apache.myfaces.trinidad.model.CollectionModel; 

Thanks

Generate Random Password in Oracle

In my application I need to generate password for users first time after registration.
The generated password must has some criteria which system administrator will configure it.
1- Character should be UPPERCASE                              =====> Abbreviation [U]
2- Character should be LOWERCASE               =====> Abbreviation [L]
3- Character should be NUMBER                  =====> Abbreviation [N]
4- Character should be any character                      =====> Abbreviation [A]
5- Character should be NON-ALPHANUMERIC character =====> Abbreviation [S]

So I thought to create dynamic function "RANDOM_PASSWORD" to return random password regarding to previous criteria.
The the system administrator will pass criteria per every character in password text to function and it will return random password
For example :-
first character should be UPPERCASE               ======> U
second character should be LOWERCASE          ======> L
third character should be NUMBER                   ======>N
forth character should be any character             ======>A
fifth character should be NON-ALPHANUMERIC  ======>S
sixth character should be Number                    ======> N   

This will generate string "ULNASN" regarding to abbreviation.

Change Look and Feel of oracle Forms

To change look and feel of Oracle Forms open the following file

Forms 11g
%MiddleWare_HOME%\user_projects\domains\<domain_name>\config\fmwconfig\servers\WLS_FORMS\applications\formsapp_11.1.1\config\formsweb.cfg

Forms 10g

%FormsHome%\forms\server\formsweb.cfg

Regarding different version of Oracle Forms 11g you can search about  formsweb.cfg file in MiddleWare Home also.

Locate element lookandfeel in any section like the following

 [sepwin]  
  lookandfeel=Generic  
   
 [webutil]  
 lookAndFeel=windows  

lookandfeel can be one of the following
1-oracle
2-Generic
3-windows

Note change lookandfeel doesn't require restart service to take effect, It effects directly after saving formsweb.cfg file.

Thanks

Oracle DB 11g New Feature (Read Only Tables)

I posted before about new features about Oracle Database 11g you can read it from below

Oracle DB 11g New Feature ( Virtual Columns ) 

Oracle DB 11g New Feature ( Compound Triggers )

Today I will produce new feature which called Read Only Tables

Read only tables are like normal  tables but it restricts any transaction to perform any DML(Insert, Update, Delete) operation against it.

Before oracle database version 11g READ ONLY was related to DATABASE and TABLE SPACE only but in version 11g you can do READ ONLY to tables too.

Playing with LOB Data Types

Lobs are the most difficult data type to store and retrieve in oracle database.

In this article, I am going to discuss extensively how to manipulate LOBs in Oracle database.

LOBs that are stored in the database itself like BLOB,CLOB,NCLOB.

BFILE which is stored outside the database as Operating System files.

BFILEs act as a pointer and store the location of the external OS files in database tables.

Dates in Oracle Database

Introduction

Oracle database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds. The default display and input format for any date is DD-MON-YY(You can change it). Valid Oracle dates are between January 1, 4712 B.C. and December 31, 9999  .

Oracle database supports simple date(True Date) and time(Date and Time) which stores in standard internal format.
Oracle supports a set of built ins function for manipulating date and time.

Date Format

As we mentioned before that oracle stores Dates internal in numeric format, but in displaying dates it displays it in different formats.
The default date format is "DD-MON-YY", the conversion of formats is done by TO_CHAR function that has below syntax

 TO_CHAR(DATE,'DATE_FORMAT','NLS_PARAMETERS');  

Date : is date value
NLS_PARAMETERS : is an optional and determine different NLS parameters in conversion.
DATE_FORMAT: It contains different format from below table

Setter and Getter of Session Parameters in ADF

We usually use session parameters as global variables that is still alive for entire session.

I will produce today the getter and setter of session parameters.

Setter of Session Parameters 

   public void setSessionParameter(String name, Object value) {  
     HttpServletRequest request =  
       (HttpServletRequest)FacesContext.getCurrentInstance().getExternalContext().getRequest();  
     HttpSession session = request.getSession(false);  
     session.setAttribute(name, value);  
   }  

Getter of Session Parameters

   public Object getSessionParameter(String name) {  
     HttpServletRequest request =  
       (HttpServletRequest)FacesContext.getCurrentInstance().getExternalContext().getRequest();  
     HttpSession session = request.getSession(false);  
     return session.getAttribute(name);  
   }  

Playing with XML in Oracle Database

I posted before about storing physical  XML files in Database Table, You can read it from Here

Today I want to generate XML file from database based on certain query, then store XML file in database table.

First, I will create table to store employees XML files

 CREATE TABLE EMP_XML  
 (  
   EMPNO     NUMBER,  
   EMP_XML_FILE  XMLTYPE  
 );  

Store Physical XML Files in Database Table

I have XML files stores as physical files and I want to store these file on database table.

Scenario

1- Create a table for storing XML files
2- Create Directory object in database to refer to path of XML files
3- Use my previous post List Contents of Directory to list all files in directory
4- Read every XML file and store it on database table

Oracle DB 11g New Feature ( Compound Triggers )

In previous post I explained Virtual Column new feature in Oracle Database 11g, you can read it from here
Today I will produce new feature which called Compound Triggers.

In previous version of database you can control the execution sequence of triggers using FOLLOWS key word when creating triggers.

 CREATE OR REPLACE TRIGGER XXX_TRG  
   BEFORE INSERT  
   ON XXX_TABLE_NAME  
   REFERENCING NEW AS NEW OLD AS OLD  
   FOR EACH ROW  
   FOLLOWS YYY_TRG  
 DECLARE  
 BEGIN  
   NULL;  
 END;  

Oracle database 11g support new feature called compound triggers which can do the same purpose of FOLLOWS but in different manner.

Encrypt and Decrypt Passwords in Database

Sometimes we store passwords in database table regarding to business requirement.
If we store password as plain text in table, Everyone who have access to database can read password easily. That's mean big security hole.

So I decided to develop package for encrypting and decrypting password.
I used DBMS_OBFUSCATION_TOOLKIT, DBMS_CRYPTO built-ins package to help me doing encryption and decryption.

I developed MAHMOUD_ENCRYPT_DECRYPT package which contains four functions (ENCRYPT1, ENCRYPT2, DECRYPT1, DECRYPT2) .

ENCRYPT1 and  DECRYPT1 functions use DBMS_OBFUSCATION_TOOLKIT package.
ENCRYPT2 and  DECRYPT2 functions use DBMS_CRYPTO package.

Commit After n Updates

If you have table has a millions of rows and you want to update whole rows in table like below statement

UPDATE TABLE_NAME

SET COLUMN_NAME='XXXXXX';

COMMIT;

It will raise an exception because of limited size of UNDO/ROLLBACK log file.
ORA-1555 errors, contact your DBA to increase the undo/ rollback segments. 

To solve this problem by code, you can commit after n updates to ignore overloading redo log file.

Cumulative Summary in Hierarchical Query using CONNECT_BY_ROOT

Today I will explain how to write hierarchical query, then modify it for getting cumulative summary function of every child using CONNECT_BY_ROOT which is supported within hierarchical queries.

For example I will create hierarchical query for employees and their manager, then display for every manager  how many  employees whose he manages cumulatively.

Java JDBC VS Java in Database

We can use Java code directly in PLSQL to manipulate database (insert data , update , do transaction .... etc) , and also we can write java code to manipulate database through JDBC connection.

When you have two solutions to do the same task, you should wait and choose the best solution.
So in my post today I will illustrate the difference between embedded java in PLSQL and Java run through JDBC connection.

For our demo I will create java code to delete and insert record in SCOTT.EMP table.
I will write code to run from PLSQL and write the same code to run through JDBC connection.

I created below Java class contains dmlOperation() method which do delete and insert record in SCOTT.EMP table and track time used for finishing transaction.