29 June, 2012

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.

26 June, 2012

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

23 June, 2012

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.

21 June, 2012

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)


19 June, 2012

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


17 June, 2012

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.

15 June, 2012

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

13 June, 2012

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

11 June, 2012

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


09 June, 2012

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

07 June, 2012

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

05 June, 2012

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

04 June, 2012

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

01 June, 2012

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

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...