Cursor Declaration in Packages

When you use global cursor in package, you should determine where cursor declaration in your code.
Some developers declare cursor in package specification and others in package body.
My post today to illustrate difference between different approach of cursor declaration in package.

ADF : Get Current Logged User Name

Sometime we need getting current authenticated user name in ADF application.
To get user name we can do it using three ways

1- Using Groovy 

We can set default expression in view object attribute as below

adf.context.securityContext.getUserPrincipal().getName() 

or

adf.context.securityContext.getUserName() 

2- Java Code

You can get User Name in Java code also using the following code.

     ADFContext adfCtx = ADFContext.getCurrent();  
     SecurityContext secCntx = adfCtx.getSecurityContext();  
     String user = secCntx.getUserPrincipal().getName();  
     String _user = secCntx.getUserName();  

3-Expression Lnagugae

You can bind ADF Faces componenets with the following EL to get logged User Name.

 #{securityContext.userName}  

Thanks

Max Function vs Databse Sequence

In database design we always use sequence numbers for primary keys(unique values) and We can implement this by database sequences or getting max value in the column using MAX function.
I called this approach "Sequence VS MAX()".
I will explain every approach first then and list drawbacks for every one.

Run Oracle Reports from Oracle Forms

I will explain how to run and display Oracle Reports within Oracle Forms 10g/11g

1- In Oracle Forms add new Report

At your form add new report from object navigator and change following properties
Name                                      :  ID for report in Oracle Forms
Filename                                 :  Physical file name path of report at application server
Report Destination Type       :  You can choose (File, Preview, Printer, Cache, Mail, Screen)
Report Destination Format   :  Output format of report (PDF, spreadsheet)

Create View with Parameter

From the title of this post you guess that oracle give us capability to create view with parameter, but this is wrong, don't think good of oracle to give you this capability as straight forward.

I have workaround to do this capability by the following techniques

1-virtual private database context
2-global package variable
3-Lookup Tables

Strings in Java

I will present an article about different string Classes in java and comparison between them.
There are three String Classes in Java

1- java.lang.String

2- java.lang.StringBuilder

3- java.lang.StringBuffer

The most common class used in Java programming  is java.lang.String class.
To identify which Class we should use it, it depends on requirement, So let's first make comparison between them and at final get conclusion about guidance use.

Insert only One Record in Table

Sometimes you have table in your system that has only one record like Configuration, Setup, Settings tables.
You want to prevent users from inserting more than one record in this table, To do this you can use the below INDEX .

CREATE UNIQUE INDEX ONE_ROW_INDEX
   ON TABLE_NAME (1);

If you try to insert more than one record in previous table you will get below exception

ORA-00001: unique constraint (SCHEMA.ONE_ROW_ALLOWED) violated

Thanks
Mahmoud A. El-Sayed

PL/SQL : Pragma Restrict References

Introduction
In my packages functions and procedure, sometimes I have some rules to control purity rules in my database like reading from tables only, not query the database, ...... etc
So I will  use PRAGMA RESTRICT_REFERENCES to control and preserve the database state in database packages.

Syntax of PRAGMA RESTRICT_REFERENCES

PRAGMA RESTRICT_REFERENCES(SUBPROGRAM_NAME, IDENTIFIER)

SUBPROGRAM_NAME can be default which will affect all the subprograms in packages or subprogram name(function or procedure) in package

IDENTIFIER can be RNDS, WNDS, RNPS, WNPS and TRUST. I will explain every one separately.

1- RNDS

select query is restricted against any of database tables

2- WNDS

DML operations are restricted against any of database tables

3- RNPS

Selection of package variables is restricted

4- WNPS

Modification in packages variables is restricted

5- TRUST

Asserts that the subprogram can be trusted not to violate one or more rules

Genius ADF Buttons

I will present some genius ADF buttons that doing specific actions using Javascript

Oracle Forms : Data Block Based on multiple Table

As usual, data block in oracle forms is based on single table.
Today I will present a case to create single data block based on multiple tables.

I will implement solution on SCOTT schema.
I will display one block based on two tables(EMP and DEPT)

1- Create single data block using data block wizard based on EMP table
2- Create Layout wizard of EMP block and create it as tabular form like below image

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);  
   }  

Insight Code in Toad

In our daily programming  life we write repeating code more times so we want a tool to help us in doing our tasks.
I usually use toad insight so I will illustrate how can TOAD help us in this issue.

I have previous post about doing code template in Toad  "Toad Code Template" you can read it from here.

In this post I will explain how to use code insight in TOAD.
In your code editor when writing for example package name and type dot ".", the editor should display whole members of packages subprograms (function, procedures,types and global variables) like below

The previous drop-down list is displayed after specific period after typing dot "." or immediately by pressing CTRL+T keys

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