HOW TO CREATE, DROP, DISABLE, AND ENABLE A PRIMARY KEY

 how to create, drop, disable, and enable a primary key in Oracle with examples.

WHAT IS A PRIMARY KEY ?

In Oracle, a primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a null value. A table can have only one primary key.

NOTE

In Oracle, a primary key can not contain more than 32 columns.
A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

CREATE PRIMARY KEY - USING CREATE TABLE STATEMENT

You can create a primary key in Oracle with the CREATE TABLE statement.

SYNTAX

The syntax to create a primary key using the CREATE TABLE statement in Oracle/PLSQL is:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...
  
  CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n)
);

EXAMPLE

Let's look at an example of how to create a primary key using the CREATE TABLE statement in Oracle:

CREATE TABLE supplier
(
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

In this example, we've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field.
We could also create a primary key with more than one field as in the example below:

CREATE TABLE supplier
(
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

CREATE PRIMARY KEY - USING ALTER TABLE STATEMENT

You can create a primary key in Oracle with the ALTER TABLE statement.

SYNTAX

The syntax to create a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

EXAMPLE

Let's look at an example of how to create a primary key using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);

In this example, we've created a primary key on the existing supplier table called supplier_pk. It consists of the field called supplier_id.
We could also create a primary key with more than one field as in the example below:

ALTER TABLE supplier
ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name);

DROP PRIMARY KEY

You can drop a primary key in Oracle using the ALTER TABLE statement.

SYNTAX

The syntax to drop a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

EXAMPLE

Let's look at an example of how to drop a primary key using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
DROP CONSTRAINT supplier_pk;

In this example, we're dropping a primary key on the supplier table called supplier_pk.

DISABLE PRIMARY KEY

You can disable a primary key in Oracle using the ALTER TABLE statement.

SYNTAX

The syntax to disable a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

EXAMPLE

Let's look at an example of how to disable a primary using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
DISABLE CONSTRAINT supplier_pk;

In this example, we're disabling a primary key on the supplier table called supplier_pk.

ENABLE PRIMARY KEY

You can enable a primary key in Oracle using the ALTER TABLE statement.

SYNTAX

The syntax to enable a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

EXAMPLE

Let's look at an example of how to enable a primary key using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
ENABLE CONSTRAINT supplier_pk;

In this example, we're enabling a primary key on the supplier table called supplier_pk.

Sql Functions

Aggregate  functions

• Aggregate functions return a single result row based on groups of rows, rather than on single rows.
• Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses.

They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle divides the rows of a queried table or view into groups.
All aggregate functions except COUNT(*)and GROUPING ignore nulls.

These are Aggregate functions
SUM()
SELECT SUM(salary) as Sum_Sal FROM emp;
AVG()
SELECT AVG(salary) as Avg_Sal FROM emp;
Max()
MAX returns maximum value of expression.
SELECT Max(salary) as Max_Sal FROM emp;
Min()
Min returns minimum value of expression.
SELECT Min(salary) as Min_Sal FROM emp;
Count()
Count returns the number of rows in the query.
SELECT COUNT(*) as total_rec FROM emp;
Example of Aggregate Function using GROUP BY:
To return the minimum and maximum salaries for each department in the emp table,we can use:group by clause
SELECT department_id,MIN(salary),MAX(salary)
FROM emp
GROUP BY dept_id;
To return the minimum and maximum salaries for each department in the emp table where minimum salary should be more than 5000
SELECT department_id, MIN(salary), MAX (salary)
FROM emp
WHERE MIN(salary) > 5000
GROUP BY dept_id;

Character Functions

• It calculates the ASCII equivalent of the first character of the given input string.

ASCII(<Character>)
ascii('A')     would return 65
ascii('a')     would return 97
ascii('a8')     would return 97

CHR(<Character>)
Returns the character equivalent of the given integer.
Example
SELECT CHR(65), CHR(97) FROM dual;
Output : A a

CONCAT(<string1>,<string2>)
This function returns String2 appended to String1.
Example:
SELECT CONCAT('Fname', 'Lname') Emp_name FROM emp;

INITCAP(<String>)
This function returns String with the first character of each word in upper case and rest of all in lower case.
Example:
SELECT INITCAP('oracle tutorial') FROM Dual;
Output :  Oracle Tutorial

instr( string1, string2 [, start_position [,  nth_Appearance ] ] ):
string1 is the string to search.

string2 is the substring to search for in string1.

start_position is the position in string1 from where the search will start. This argument is optional. If not mentioned, it defaults to 1.
The first position in the string is 1. If the start_position is negative, the function counts backward direction

nth_appearance is the nth appearance of string2. This is optional. If not defined, it defaults to 1.

Example
SELECT INSTR('Character','r',1,1) POS1
, INSTR('Character','r',1,2) POS2
, INSTR('Character','a',-1,2) POS3
,INSTR('character','c',) POS4
FROM Dual;
pos1 pos2 pos3 pos4
  4          9          3        6

LENGTH(<Str>)
Returns length of a string
secect length('Sql Tutorial') as len from dual;
O/p len
     12
LOWER(<Str>)
This function returns a character string with all characters in lower case.

UPPER(<Str>)
This function returns a character string with all characters in upper case.

LPAD(<Str1>,<i>[,<Str2>])
This function returns the character string Str1 expanded in length to i characters, using Str2 to fill in space as needed on the left side of Str1.
Example
SELECT LPAD('Oracle',10,'.') lapd_doted from Dual, would return ....Oracle
SELECT  LPAD('RAM', 7)  lapd_exa  from Dual    would return    '    RAM'

RPAD(<Str1>,<i>[,<Str2>])
RPAD is same as LPAD but Str2 is padded at the right side

LTRIM(<Str1>[,<Str2>])
The LTRIM function removes characters from the left side of the character Srting, with all the leftmost characters that appear in another text expression removed.
This function returns Str1 without any leading character that appears in Str2.If  Str2 characters are leading character in Str1, then Str1 is returned unchanged.
Str2 defaults to a single space.
Example
Select LTRIM('datawarehousing','ing') trim1
, LTRIM('datawarehousing   ') trim2
, LTRIM('         datawarehousing') trim3
, LTRIM('datawarehousing','data') trim4 from dual
O/P trim1                                      trim2                                     trim3                                        trim4
       datawarehousing               datawarehousing               datawarehousing                warehousing

RTRIM(<Str1>[,<Str2>])
Same as LTRIM but the characters are trimmed from the right side

TRIM([[<Str1>]<Str2> FROM]<Str3>)

• If present Str1 can be one of the following literal: LEADING, TRAILING, BOTH.
• This function returns Str3 with all C1(leading trailing or both) occurrences of characters in Str2 removed.
• If any of Str1,Str2 or Str3 is Null, this function returns a Null.

Str1 defaults to BOTH, and Str2 defaults to a space character.
Example
SELECT TRIM(' Oracle   ') trim1, TRIM('Oracle ') trim2 FROM Dual;
Ans trim1             trim2
        Oracle          Oracle
It'll remove the space from both string.

REPLACE(<Str1>,<Str2>[,<Str3>]
This function returns Str1 with all occurrence of Str2 replaced with Str3
Example
SELECT REPLACE (‘Oracle’, ’Ora’, ’Arti’) replace_exa FROM Dual;
O/p replace_exa
       Atricle

Essential Numeric Functions
ABS()
Select Absolute value
SELECT ABS(-25) "Abs" FROM DUAL;

  Abs
-------
  15
ACOS ()
Select cos value
SELECT ACOS(.28)"Arc_Cosine" FROM DUAL;
ASIN ()
Select sin value
SELECT ASIN(.6)"Arc_Cosine" FROM DUAL;
ATAN()
Select tan value
SELECT ATAN(.6)"Arc_Cosine" FROM DUAL;
CEIL()
Returns the smallest integer greater than or equal to the order total of a specified orde
SELECT  CEIL(239.8) FROM Dual would return 240

FLOOR()
Returns the largest integer equal to or less than value.

SELECT FLOOR(15.65) "Floor" FROM DUAL;

 Floor
------
15

MOD()
Return modulus value
SELECT MOD(11,3) "Mod" FROM DUAL;

 Modulus
-------
   2

POWER()
SELECT POWER(3,2) "Power" FROM DUAL;

 power
-------
   9
ROUND (number)
SELECT ROUND(43.698,1) "Round" FROM DUAL;

     Round
----------
      43.7

TRUNC (number)
The TRUNC (number) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point.
SELECT TRUNC(12.75,1) "Trunc" FROM DUAL;

  Trunc
----------
  12.75

SELECT TRUNC(12.75,-1) "Trunc" FROM DUAL;

  Trunc
------
  10
Date And Time Function

ADD_MONTHS(date,number_of_month)
SELECT SYSDATE, ADD_MONTHS(SYSDATE,2), ADD_MONTHS(SYSDATE,-2) FROM DUAL;
Result:
SYSDATE     ADD_MONTH ADD_MONTH
-------------    ------------------------------------------
10-Feb-13       10-Apr-13 10-Dec-13

EXTRACT(<type> FROM <date>)
'Type' can be YEAR, MONTH, DAY, HOUR, MIN, SECOND, TIME_ZONE_HOUR, TIME_ZONE_MINUTE, TIME_ZONE_REGION
SELECT SYSDATE, EXTRACT(YEAR FROM SYSDATE)YEAR, EXTRACT(DAY FROM SYSDATE)DAY , EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP) TZH FROM DUAL;
LAST_DAY(<date>)
Extract last day of month
Example:
SELECT SYSDATE, LAST_DAY(SYSDATE) END_OF_MONTH  FROM DUAL;
Result: SYSDATE      END_OF_MO
               ----------    ---------------
             10-Feb-13      28-Feb-13

NEXT_DAY(<date>,<day>)
SELECT NEXT_DAY('28-Feb-13','SUN') "FIRST MONDAY OF MARCH”  FROM DUAL;
O/P FIRST MONDAY OF MARCH
               03-Mar-13
ROUND (date[,<fmt>])
SELECT SYSDATE, ROUND(SYSDATE,'MM'), ROUND(SYSDATE,'YYYY') FROM DUAL;
Result:SYSDATE   ROUND(SYS   ROUND(SYS
                                         -----------       ---------------
                                10-FEB-13 01-MAR-13 01-JAN-13
TRUNC(date[,<fmt>])
SELECT SYSDATE, TRUNC(SYSDATE,'MM'), TRUNC(SYSDATE,'YYYY') FROM DUAL;
Result: SYSDATE   TRUNC(SYS    TRUNC(SYS
                    -------      - ------------              --------
                    10-FEB-13 01-FEB-13    01-JAN-13

QUERY TO FIND TABLE NAME BY COLUMN NAME

QUERY TO FIND TABLE NAME BY  COLUMN NAME

SELECT TABLE_NAME FROM USER_TAB_COLUMNS
WHERE COLUMN_NAME like'%COLUMN_NAME%' and TABLE_NAME LIKE 'TABLE_NAME%' 

OR

SELECT TABLE_NAME FROM USER_TAB_COLUMNS
              WHERE COLUMN_NAME ='COLUMN_NAME' and TABLE_NAME= 'TABLE_NAME'

File Extensions in Oracle

File Extensions in Oracle

SQL*Plus files

• .sql - SQL script

• .lst - spool file

PL/SQL files

• .pls - PL/SQL source

• .plb - PL/SQL binary

• .pks - Package source or package specification

• .pkb - Package binary or package body

• .pck - Combined package specification plus body

Oracle database files

• .dbf - database file

• .log - Online Redo Log

• .rdo - Online Redo Log

• .arc - Archive log

SQL*Loader files

• .ctl - Control file

• .dat - Data file

• .bad - Bad file

• .dsc - Discard file

SQL*Net files

• .ora - tnsnames.ora, sqlnet.ora, etc.

Forms files

• .fmb - Forms binary

• .fmt - Forms text

• .fmx - Forms executable

Reports files

• .rdf - contains a single report definition in binary format. .RDF files are used to both  run and edit reports.

• .rep - contains a single report definition in binary format. .REP files are used solely to run reports; you cannot edit a .REP file

• .rex - contains a single report definition in text format. .REX files are portable.

Other files

Other commonly used extensions:

• .txt - text files

• .pdf - Acrobat PDF files

• .log - Log files

• .csv - Comma Separated Values (CSV)

• .gif - GIF Image file

• .jpg - Jpeg Image file

• .jpeg - Jpeg Image file

• .png - PNG Image file

• .bmp - Bitmap Image file

• .xbm - X Bitmap Image file

EXPLICIT CURSOR

Explicit Cursor : Syntax with Example

There are four steps in using an Explicit Cursor.

• DECLARE the cursor in the declaration section.
• OPEN the cursor in the Execution Section.
• FETCH the data from cursor into PL/SQL variables or records in the Execution Section.
• CLOSE the cursor in the Execution Section before you end the PL/SQL Block.
  

   Syntax:

   DECLARE

      variables;

      records;

      create a cursor;

   BEGIN 

     OPEN cursor;

     FETCH cursor;

       process the records;

     CLOSE cursor;

   END;

  Example: 

  declare
  abc tb1%rowtype;
  cursor c_cur is select * from tb1 where age>25;
  begin
  open c_cur;
  loop
  fetch c_cur into abc;
  dbms_output.put_line('the senoirs peoples are'||abc.id);
  dbms_output.put_line('the senoirs peoples are'||abc.name);
  dbms_output.put_line('the senoirs peoples are'||abc.age);
  dbms_output.put_line('the senoirs peoples are'||abc.salary);
  dbms_output.put_line('the senoirs peoples are'||abc.gender);
  exit when c_cur%notfound;
  end loop;
  close c_cur;
  end; 

CREATE TABLE STATEMENT

CREATE TABLE STATEMENT

The syntax for the SQL CREATE TABLE statement is:

CREATE TABLE table_name
( 
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...
);

Each column must have a datatype. The column should either be defined as "null" or "not null" and if this value is left blank, the database assumes "null" as the default.

For Example

CREATE TABLE suppliers
( supplier_id number(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50)
);

Query_Find Form in Oracle Apps

Steps To Develop a Query_Find Form in Oracle Apps

• Step 1: Open Template.fmb with form builder save it with another name(XXMZ_Query_Find.fmb)  and give the module name as XXMZ_QUERY_FIND.
  

• Step2 : Delete  BLOCKNAME from Window,Canvas and Data Block and delete DETAILBLOCK from Data Block.
  

• Step 3: Create window(DEPT_WINDOW) apply subclass information as WINDOW and give the title.
  

• Step 4:  Give this window name in PRE-FORM trigger (Form Level) and give this window   name in in APP_CUSTOM  body   Program_Unit( in place of window name).
  

Compile and Close The Window.

Compile and Close The Window.

 Step 5: Create Canvas(DEPT_CANVAS) apply subclass information as Canvas.

 Step 6:  Now Create Data block(DEPT)  with Wizard or Manual ,apply subclass information to the block as BLOCK   and also apply subclass information to the items as TEXT_ITEM.

 Step 7:Step 5: Drag QUERY_FIND Object Group from APPSTAND.fmb to Our Form(XXMZ_Query_Find.fmb) Object Group. Here we get  window,canvas,datablock will come automatically  with the name QUERY_FIND.

Step 8: Apply subclass information to Window,Canvas as well as Block. And drag these to first position   as shown in below.

 Step 9:  In QUERY_FIND data block we get 3 buttons(Clear,New,Find) with 3 Triggers.

Give Block name as DEPT in   WHEN-BUTTON-PRESSED trigger of New and Find Button. Compile and close.

Step 10: Next create  Control item in the Query_Find Block apply subclass information as TEXT_ITEM  and attach that item to the  QUERY_FIND canvas.

Step 9:

Next drag the QUERY_FIND trigger from APPSTAND.fmb and Place it in DEPT block  level trigger.

Syntax:             APP_FIND.QUERY_FIND(<results block window>,

<Find window>,

<Find window block>);

And write the following code in that trigger:

APP_FIND.QUERY_FIND(‘DEPT_WINDOW’, ‘QUERY_FIND’,'QUERY_FIND’);

 Step 10:

Next Create PRE-QUERY trigger in the block level(DEPT)

Syntax:          IF :parameter.G_query_find = TRUE THEN

COPY (<find Window field>,<results field>);

:parameter.G_query_find := FALSE;

END IF;

Write the following code

IF :parameter.G_query_find = ‘TRUE’ THEN

COPY(:QUERY_FIND.DEPTNO, ‘DEPT.DEPTNO’);

:parameter.G_query_find := ‘FALSE’;

END IF;

 Step 11: In the form module(XXMZ_Query_Find) give the first navigation block as QUERY_FIND. In the  QUERY_FIND datablock give next navigation block as DEPT.

 Step 12:   Finally Save and move your form from our Local Machine  to CUSTOM_TOP using WINSCP tool.

 Step 13:  Compile the Form using f60gen

In the UNIX environment, type the following command:

f60gen XXMZ_QUERY_FIND.fmb apps/apps

Now XXMZ_QUERY_FIND.fmx should be generated in the CUSTOM_TOP.

 Step 14:

Connect to Oracle Applications. Create a Form

(Navigation:Application Developer->Application-> Form)

Here give Form =>XXMZ_QUERY_FIND(.fmx )

Application => xxmz Custom(Custom TOP Application)

User Form Name=>Give any name

 Step 15: Create a function (Navigation: Application->function)

 Step 16:Attaching Form to the Function shown in below.

Attach that function to the Custom TOP menu

Click on Find Button