Oracle: May 2013

Saturday, May 4, 2013

ORACLE ENVIRONMENT FUNCTIONS

SYS_CONTEXT
SYS_GUID
SYS_TYPEID
UID
USER
USERENV

ASCIISTR
BIN_TO_NUM
CAST
CHARTOROWID
COMPOSE
CONVERT
DECOMPOSE
HEXTORAW
NUMTODSINTERVAL
NUMTOYMINTERVAL
RAWTOHEX
RAWTONHEX
ROWIDTOCHAR
ROWIDTONCHAR
SCN_TO_TIMESTAMP

TIMESTAMP_TO_SCN
TO_BINARY_DOUBLE
TO_BINARY_FLOAT
TO_CHAR (character)
TO_CHAR (datetime)
TO_CHAR (number)
TO_CLOB
TO_DATE
TO_DSINTERVAL
TO_LOB
TO_MULTI_BYTE
TO_NCHAR (character)
TO_NCHAR (datetime)
TO_NCHAR (number)
TO_NCLOB
TO_NUMBER
TO_DSINTERVAL
TO_SINGLE_BYTE
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TO_YMINTERVAL
TRANSLATE ... USING
UNISTR

ORACLE CHARACTER FUNCTIONS RETURNING NUMBERS

ASCII
INSTR
LENGTH
REGEXP_INSTR

oracle nls character functions

NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID
NLS_CHARSET_NAME

ORACLE CHARACTER FUNCTIONS RETURNING CHARS

CHR
CONCAT
INITCAP
LOWER
LPAD
LTRIM
NLS_INITCAP
NLS_LOWER
NLSSORT
NLS_UPPER
REGEXP_REPLACE
REGEXP_SUBSTR
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
TRANSLATE
TREAT
TRIM
UPPER

oracle numeric functions

ABS
ACOS
ASIN
ATAN
ATAN2
BITAND
CEIL
COS
COSH
EXP
FLOOR
LN
LOG
MOD
NANVL
POWER
REMAINDER
ROUND (number)
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC (number)
WIDTH_BUCKET

ORACLE DATETIME FUNCTIONS

The datetime functions are:
ADD_MONTHS
CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT (datetime)
FROM_TZ
LAST_DAY
LOCALTIMESTAMP
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
NUMTODSINTERVAL
NUMTOYMINTERVAL
ROUND (date)
SESSIONTIMEZONE
SYS_EXTRACT_UTC
SYSDATE
SYSTIMESTAMP
TO_CHAR (datetime)
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_DSINTERVAL
TO_YMINTERVAL
TRUNC (date)
TZ_OFFSET

oracle object reference functions

MAKE_REF
REF
REFTOHEX
VALUE

ORACLE MODEL FUNCTIONS

CV
ITERATION_NUMBER
PRESENTNNV
PRESENTV
PREVIOUS

ORACLE BITAND FUNCTION

BIT_AND FUNCTION DOES ANDING OF 2 BINARY VALUES

syntax: BIT_AND(EXPR1,EXPR2)

1 1 -> 1

1 0 -> 0

0 1 -> 0

0 0 -> 0

SQL> SELECT BITAND(1,0) FROM DUAL;

BITAND(1,0)
-----------
          0

SQL> SELECT BITAND(01,) FROM DUAL;
SELECT BITAND(01,) FROM DUAL
                 *
ERROR at line 1:
ORA-00936: missing expression

SQL> SELECT BITAND(0,1) FROM DUAL;

BITAND(0,1)
-----------
          0

SQL> SELECT BITAND(0,0) FROM DUAL;

BITAND(0,0)
-----------
          0

SQL> SELECT BITAND(1,1) FROM DUAL;

BITAND(1,1)
-----------
          1

ORACLE BIN_TO_NUM FUNCTION

                                                   bin_to_num converts binary value to Oracle Number.
SYNTAX: bin_to_num(expr1,expr2,.....,exprn);

QUERY: Display equivalent of 110 in decimal

SQL> select bin_to_num(1,1,0) from dual;

BIN_TO_NUM(1,1,0)
-----------------
                6

QUERY: Display equivalent of 1010 in decimal

SQL> select bin_to_num(1,0,1,0) from dual;

BIN_TO_NUM(1,0,1,0)
-------------------
                 10

QUERY: PL/SQL VARRAY has 5 binary numbers, converts into equivalent Number.

declare
type vab is varray(5) of number(1);
v_t vab:=vab(1,0,1,1,1);
n number;
begin
select bin_to_num(v_t(1),v_t(2),v_t(3),v_t(4),v_t(5)) into n from dual;
dbms_output.put_line(n);
end;
/

SQL> /
23

P/SQL procedure successfully completed.

ORACLE AVG FUNCTION

avg function calculates avg of the values supplied

QUERY: Calculate Average Weight of the Person.

SQL> desc person;
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------
ID                                                             NUMBER(2)
NAME                                                           VARCHAR2(20)
DOB                                                            DATE
EMAIL                                                          VARCHAR2(20)
WEIGHT                                                         NUMBER(5,2)

SQL> select * from person;

        ID NAME                 DOB       EMAIL                    WEIGHT
---------- -------------------- --------- -------------------- ----------
         1 Peter                12-DEC-97 1@gmail.com                 100
         6 life style           11-SEP-97
         2 shyam purru          12-JAN-00 2@gmail.com                 111
         3 Jho                  01-NOV-02 3@gmail.com                 133
         4 Jhony                30-AUG-60 4@gmail.com              133.44
         5 pearso               21-APR-13 5@gmail.com              150.01

6 rows selected.

SQL> select avg(weight) from person;

AVG(WEIGHT)
-----------
     125.49

QUERY: GET AVERAGE SALARY FROM EACH DEPARTMENT

SQL> select DEPARTMENT_ID,AVG(SALARY) from hr.employees
2 group by DEPARTMENT_ID;

DEPARTMENT_ID AVG(SALARY)
------------- -----------
          100 8601.33333
           30        4150
                     7000
           90 19333.3333
           20        9500
           70       10000
          110       10154
           50 4376.08889
           80 8955.88235
           40        6500
           60        5760

           10        4400

12 rows selected.

ORACLE ABS FUNCTION

ABS FUNCTION RETURNS ABSOLUTE VALUE OF THE EXPRESSION

SYNTAX: abs(expr)

QUERY: get absolute value of -10

SQL> SELECT ABS(-10.22) FROM DUAL;

ABS(-10.22)
-----------
10.22

QUERY: get absolute value of '-10+20-30'

SQL> select abs(-10+20-30) from dual;

ABS(-10+20-30)
--------------
20

QUERY: get absolute value of dates.

SQL> select abs(months_between(to_date('1973-12-12','yyyy-dd-mm'),sysdate)) from dual;

MONTHS_BETWEEN(TO_DATE('1973-12-12','YYYY-DD-MM'),SYSDATE)
----------------------------------------------------------
472.7595

QUERY: Display absolute values of variable.

SQL>declare n number;

SQL>exec :n:=-10;

SQL>select :n,abs(:n) from dual;

:n abs(:n)

------ -------------------

-10 10

Friday, May 3, 2013

ORACLE ROUND FUNCTION

Round function rounds the number into nearest integer

round(decimal number, n);

SQL>select round(17.7) from dual; --rounds to 18

SQL> select round(17.49) from dual; --rounds to 17.

Note: the number >.5 will be rounded to next integer <0.5 rounded to lowest number.

SQL>select round(17.7,1) from dual;

SQL> select round(17.71,1) from dual;

ROUND(17.71,1)
--------------
          17.7

SQL> select round(17.7,1) from dual;

ROUND(17.7,1)
-------------
         17.7

SQL> select round(17.7,10) from dual;

ROUND(17.7,10)
--------------
          17.7

SQL> select round(17.9,10) from dual;

ROUND(17.9,10)
--------------
          17.9

SQL> select round(17.99,10) from dual;

ROUND(17.99,10)
---------------
          17.99

SQL> select round(17.999,1) from dual;

ROUND(17.999,1)
---------------
             18

SQL> select round(17.9,1) from dual;

ROUND(17.9,1)
-------------
         17.9

SQL> select round(17.99,1) from dual;

ROUND(17.99,1)
--------------
            18

SQL> select round(17.56,1) from dual;

ROUND(17.56,1)
--------------
          17.6

SQL> select round(17.55,1) from dual;

ROUND(17.55,1)
--------------
          17.6

SQL> select round(17.49,1) from dual;

ROUND(17.49,1)
--------------
          17.5

ORACLE SYSTIMESTAMP

systimestamp returns OS Date & time along with timezone on which oracle DB runs.

--Display Date and Time with timezone

SQL>select systimestamp from dual;

--Display only Date from systimestamp

SQL> select trunc(systimestamp) from dual;

SQL> select to_char(systimestamp,'yyyy-mm-dd') from dual;

--Display only Time from systimestamp

SQL> select systimestamp-trunc(systimestamp) from dual;

oracle trunc number function

truncates the specified value to specified decimal places.

SYNTAX: trunc(n1,n2)

it truncates n1 by n2 decimal places

for ex: trunc(15.71, 1) ->outputs 15.7

SQL> select trunc(15.71) from dual;

TRUNC(15.71)
------------
15

*Default 0.

SQL> select trunc(15.71,-1) from dual;

TRUNC(15.71)
------------
10

GET Fractional PORTION OF THE REAL VALUE.

--------------------------------------------

SQL> select 15.71-trunc(15.71,0) from dual;

15.71-TRUNC(15.71,0)
--------------------
.71

ORACLE CONCAT FUNCTION

conact FUNCTION CONCATENATE 2 STRINGS.

syntax: CONCAT(STR1,STR2);

SQL>SELECT CONCAT('HELLO',' WORLD') FROM DUAL;

CONCAT('HELLO',' WORLD')

---------------------------------------

HELLO WORLD

SQL>
SELECT CONCAT('HELLO',NULL) FROM DUAL;

CONCAT('HELLO',NULL)

---------------------------------

HELLO

SQL>SELECT CONCAT(NULL,' WORLD') FROM DUAL;

CONCAT(NULL,' WORLD')

---------------------------------------

WORLD

CONCATENATE 'I' , 'LOVE','PARIS' USING CONCAT

SQL> SELECT CONCAT(CONCAT('I','LOVE'),'PARIS') FROM DUAL;

CONCAT(CON
----------
ILOVEPARIS

ORACLE init cap function

InitCAP functions makes first letter in each word as Upper case.

Synatx: InitCap(char expr);

SQL>select initcap('hello world') from dual;

initcap('hello world')

--------------------------

Hello World

SQL> select initcap(first_name),initcap(last_name),initcap(first_name||' '||last_name) from employees

it makes first_name ,last_name,concatenating name and displays Upper case in each word.

ORACLE ASCII FUNCTION

oracle ascii function finds ascii value of the char.

SYNTAX: ascii(char)

SQL>select ascii('a') from dual;

ascii('a')

--------------------

SQL> select ascii('ab') from dual;

ASCII('AB')
-----------
97

Note: ascii value of ab , it takes only first character. i.e a

Ascii value of blank is 32.

SQL>select ascii(' ') from dual;

ascii(' ')

--------------

DISPLAY ASCII VALUES OF THE STRING

------------------------------------------------------------

declare
v varchar2(20):='hello oracle!';
c char;
begin
for i in 1..length(v)
loop
c:=substr(v,i,1);
dbms_output.put_line(c||'='||ascii(c));
end loop;
end;

SQL> /
h=104
e=101
l=108
l=108
o=111
=32
o=111
r=114
a=97
c=99
l=108
e=101
!=33

PL/SQL procedure successfully completed.

ASCII values for a-z : 97-122

ASCII values for A-Z: 65-90

ASCII values for 0-9: 48-57

ORACLE MIN FUNCTION

                                 MIN function finds MINIMUM value in the column, ignores null values.

SYNTAX: MIN(expr)

MINIMUM SALARY IN THE COMPANY.
----------------------------------------------------
SQL> select min(salary) from employees;

min(SALARY)
-----------
      24000
Get MINIMUM SALARY DRAWN FROM EACH DEPT.
------------------------------------------------------------------------
SQL> select department_ID,min(Salary) from employees
2 group by department_ID;

DEPARTMENT_ID min(SALARY)
------------- -----------
          100       12008
           30       11000
                     7000
           90       24000
           20       13000
           70       10000
          110       12008
           50       11808
           80       14000
           40        6500
           60        9000
           10        4400

12 rows selected.

Note: Group by department and then find min(salary)

GET min SALARY DRAWN ON EACH JOB CATEGORY
---------------------------------------------------------------------------
SQL> select job_ID,min(SALARY) from employees
2 group by job_id;

JOB_ID     min(SALARY)
---------- -----------
IT_PROG           9000
AC_MGR           12008
AC_ACCOUNT        8300
ST_MAN           11808
PU_MAN           11000
AD_ASST           4400
AD_VP            17000
SH_CLERK          4200
FI_ACCOUNT        9000
FI_MGR           12008
PU_CLERK          3100
SA_MAN           14000
MK_MAN           13000
PR_REP           10000
AD_PRES          24000
SA_REP           11500
MK_REP            6000
ST_CLERK          5184
HR_REP            6500

19 rows selected.
Note: Group by Job category and then find min(salary)

GET SECOND HIGHEST SALARY
---------------------------------------------------
SQL> select min(salary) from employees where salary > (select min(salary) from employees);

min(SALARY)
-----------
      17000
Note: First find min salary then in the remaining values find again min salary(second minimum).

ORACLE MAX FUNCTION

max function finds max value in the column, ignores null values.

SYNTAX: MAX(expr)

MAXIMUM SALARY IN THE COMPNAY.

----------------------------------------------------

SQL> select max(salary) from employees;

MAX(SALARY)
-----------
24000

Get MAXIMUM SALARY DRAWN FROM EACH DEPT.

------------------------------------------------------------------------

SQL> select department_ID,max(Salary) from employees
2 group by department_ID;

DEPARTMENT_ID MAX(SALARY)
------------- -----------
          100       12008
           30       11000
                     7000
           90       24000
           20       13000
           70       10000
          110       12008
           50       11808
           80       14000
           40        6500
           60        9000
           10        4400

12 rows selected.

Note: Group by department and then find max(salary)

GET MAX SALARY DRAWN ON EACH JOB CATEGORY

---------------------------------------------------------------------------

SQL> select job_ID,MAX(SALARY) from employees
2 group by job_id;

JOB_ID     MAX(SALARY)
---------- -----------
IT_PROG           9000
AC_MGR           12008
AC_ACCOUNT        8300
ST_MAN           11808
PU_MAN           11000
AD_ASST           4400
AD_VP            17000
SH_CLERK          4200
FI_ACCOUNT        9000
FI_MGR           12008
PU_CLERK          3100
SA_MAN           14000
MK_MAN           13000
PR_REP           10000
AD_PRES          24000
SA_REP           11500
MK_REP            6000
ST_CLERK          5184
HR_REP            6500

19 rows selected.

Note: Group by Job category and then find max(salary)

GET SECOND HIGHEST SALARY

---------------------------------------------------

SQL> select max(salary) from employees where salary < (select Max(salary) from employees);

MAX(SALARY)
-----------
17000

Note: First find MAX salary then in remaining values find MAX salary.

Find Experience Number of Years in Oracle

Find Number of Years Experience in Oracle

Oracle supports months_between function which accepts, date1 and date2, & returns number of months.

if date 1 is < date 2 result will be -ve .

SQL> select empno,round(months_between(sysdate,hiredate) /12)||' Yrs' "Exp" from emp;

     EMPNO Exp
---------- --------------------------------------------
      9003 31 Yrs
      7369 32 Yrs
      7499 32 Yrs
      7521 32 Yrs
      7566 32 Yrs
      7654 32 Yrs
      7698 32 Yrs
      7782 32 Yrs
      7788 26 Yrs
      7839 31 Yrs
      7844 32 Yrs

     EMPNO Exp
---------- --------------------------------------------
      7876 26 Yrs
      7900 31 Yrs
      7902 31 Yrs
      7934 31 Yrs
      1011 18 Yrs
      1012 19 Yrs

17 rows selected.

Execute Immediate Update returns Bulk Collect

This tutorial explains how to use update command with Execute Immediate which returns Bulk collect.

Q) Update Stock Manager or Stock Clerk by 20% and display updated employee's last name.

-- Declare a table type/indexed table which holds stock manager and stock clerk's last name

--Declare variable for storing sql command here Update.

-- Use Execute command for update.

--Display stock manager/clerk last name

This example updates Stock manager and Stock clerk salary by 20% using Execute Immediate and returns their last name.

declare
type typeemployees is table of employees.last_name%type;
v_employee typeemployees;
sql_stmt varchar2(250);

pct number:=20;

begin

sql_stmt := 'update employees set salary =salary+(salary*20/100) where job_ID in(''ST_MAN'',''ST_CLERK'') '||
'RETURNING last_name INTO :2';
dbms_output.put_line(sql_stmt);

EXECUTE IMMEDIATE sql_stmt RETURNING BULK COLLECT INTO v_employee;

FOR I IN v_employee.FIRST..v_employee.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(v_employee(I));
END LOOP;

END;

SQL>set Serveroutput on;

Execute the above PL/SQL block using /

SQL>/

update employees set salary =salary+(salary*20/100) where job_ID
in('ST_MAN','ST_CLERK') RETURNING last_name INTO :2
Weiss
Fripp
Kaufling
Vollman
Mourgos
Nayer
Mikkilineni
Landry
Markle
Bissot
Atkinson
Marlow
Olson
Mallin
Rogers
Gee
Philtanker
Ladwig
Stiles
Seo
Patel
Rajs
Davies
Matos
Vargas

PL/SQL procedure successfully completed.

TAGS:Execute Immediate Update returns Bulk Collect, Update command with dynamic SQL which returns bulk collect, Bulk collection from Execute Immediate, How to get bulk collect from Execute Immediate Update operation, Update for Execute immediate which returns Collection.

Thursday, May 2, 2013

Execute Immediate Bulk Collect in oracle

Execute Immediate Bulk Collect in oracle PL/SQL

You can use the BULK COLLECT INTO clause with the EXECUTE IMMEDIATE statement to store values from each column of a query's result set in a separate collection.

Table data can be filled into indexed tables using bulk collect for row by row processing. Bulk collect means all rows are fetched in batches.

Using Dynamic SQL also User can perform Bulk operations on collections.

Here is the Example.

You have a customer table

SQL> DESC CUSTOMERS
Name                                                                                Null?    Type
------------------------------------------------------------------------------- -------- --------------
CUSTOMERID                                                                          NOT NULL NCHAR(5)
COMPANYNAME                                                                         NOT NULL NVARCHAR2(40)
CONTACTNAME                                                                                  NVARCHAR2(40)
CONTACTTITLE                                                                                 NVARCHAR2(30)
ADDRESS                                                                                      NVARCHAR2(60)
CITY                                                                                         NVARCHAR2(15)
REGION                                                                                       NVARCHAR2(15)
POSTACODE                                                                                    NVARCHAR2(10)
PHONE                                                                                        NVARCHAR2(24)
FAX                                                                                          NVARCHAR2(24)

Here is the example of BULK COLLECT using DYNAMIC SQL/EXECUTE IMMEDIATE

--declare a indexed table of customers table

--define type of that table

--declare variable to hold sql statement here select

--execute the dynamic sql

--display bulk collect data using for loop

declare
type typecustomers is table of customers%rowtype;
v_cust typecustomers;
sql_stmt varchar2(250);
begin
sql_stmt :='select * from customers';
execute immediate sql_stmt bulk collect into v_cust;
if SQL%ROWCOUNT >=0 then
dbms_output.put_line('rows inserted into indexed table');
else
dbms_output.put_line('rows failed to fetch into bulk collect');
end if;
for i in v_cust.first..v_cust.last
loop
dbms_output.put_line(v_cust(i).customerid||' '||v_cust(i).contactname||' '||v_cust(i).REGION);
end loop;
end;

SQL>Set Serveroutput on;

SQL> /
rows inserted into indexed table
HUNGC Yoshi Latimer OR
HUNGO Patricia McKenna Co. Cork
ISLAT Helen Bennett Isle of Wight
KOENE Philip Cramer
LACOR Daniel Tonini
LAMAI Annette Roulet
LAUGB Yoshi Tannamuri BC
LAZYK John Steel WA
LEHMS Renate Messner
LETSS Jaime Yorres CA
LILAS Carlos Gonzßlez Lara

TAGS: Execute Immediate Bulk Collect in oracle,declare indexed table in PL/SQL block, BULK COLLECT INTO using Execute Immediate.

Delete row(s) using Execute Immediate

Oracle PL/SQL supports Dynamic sql through which DML operations can be performed using bind variables.

You have a table called ab

SQL>desc ab;

SQL> desc ab;
Name                                                                                Null?    Type
----------------------------------------------------------------------------------- -------- ------------
ID                                                                                           NUMBER
NAME                                                                                         VARCHAR2(20)

SQL> select * from ab;

ID NAME
---------- ---------------
2 hello world

Delete records from ab table using Execute Immediate/Dynamic SQL

--delcare variable of type that included in where clause

--declare another variable to hold sql statement. here insert

--assign value to variable

--pass sql statement to execute immediate statement along with variable .

declare
n number:=1;
sql_stmt varchar2(250);begin
sql_stmt :='delete from ab where id=:1';
n:=2;
execute immediate sql_stmt using n;end;

--Execute the above block of code

SQL> /

PL/SQL procedure successfully completed.

--test whether insert successful or not

SQL> select * from ab;

Tags:delete from a Table using Execute Immediate,Execute Immediate for delete ,Execute Immediate with using variables, delete data using dynamic sql, deleting data using Execute immediate,

UPDATE Table using Execute Immediate

Oracle PL/SQL supports Dynamic sql through which DML operations can be performed using bind variables.

You have a table called ab

SQL>desc ab;

SQL> select * from ab;

ID NAME
---------- ---------------
2 hello

Update records in ab table using Execute Immediate/Dynamic SQL

--it has 2 columns declare 2 variables of same type

--declare another variable to hold sql statement. here insert

--assign values to 2 variables

--pass sql statement to execute immediate statement along with 2 variables .

declare
n number:=1;
nm varchar2(20);
sql_stmt varchar2(250);
begin
sql_stmt :='update ab set name=:nm where id=:1';
n:=2;
nm:='hello world';
execute immediate sql_stmt using nm,n;
end;

--Execute the above block of code

SQL> /

PL/SQL procedure successfully completed.

--test whether insert successful or not

SQL> select * from ab;

SQL> select * from ab;

ID NAME
---------- ---------------
2 hello world

Tags:update Table using Execute Immediate,Execute Immediate for update ,Execute Immediate with using variables, update data using dynamic sql, updating data using Execute immediate,updating table data using Execute immediate,How to update data using Execute immediate, how to update data using dynamic sql.

Insert into a Table using Execute Immediate

Oracle PL/SQL supports Dynamic sql through which DML operations can be performed using bind variables.

You have a table called ab

SQL>desc ab;

Insert records into ab table using Execute Immediate/Dynamic SQL

--it has 2 columns declare 2 variables of same type

--declare another variable to hold sql statement. here insert

--assign values to 2 variables

--pass sql statement to execute immediate statement along with 2 variables .

declare
n number:=1;
nm varchar2(20);
sql_stmt varchar2(250);
begin
sql_stmt :='insert into ab values(:1,:2)';
n:=2;
nm:='hello';
execute immediate sql_stmt using n,nm;
end;

--Execute the above block of code

SQL> /

PL/SQL procedure successfully completed.

--test whether insert successful or not

SQL> select * from ab;

ID NAME
---------- --------------------
2 hello

Tags:Insert into a Table using Execute Immediate,Execute Immediate for Insert,Execute Immediate with using variables, Insert data using dynamic sql, inserting data using Execute immediate,

Create Table in PL/SQL

          Oracle PL/SQL supports dynamic SQL through which user can execute DDL operations through PL/SQL blocks .i.e anonymous blocks, stored procedures/packages,or through functions.

Here is an example of creating a table called ab(id number,name varchar2(20).

--Declare a variable which can hold create table definition.
--call Execute immediate for that variable.

SQL> declare
2 v varchar2(250);
3 begin
4 v:='create table ab(id number, name varchar2(20))';
5 execute immediate v;
6 end;
7 .
SQL> set serveroutput on;
--Execute the script
SQL> /

PL/SQL procedure successfully completed.

--check wether table created or not
SQL> select * from ab;

no rows selected

--describe the table.

SQL> desc ab;
Name                                                                                Null?    Type
----------------------------------------------------------------------------------- -------- ---------------
ID                                                                                           NUMBER
NAME                                                                                         VARCHAR2(20)

Tags: create table in PL/SQL, oracle create table in pl/sql, execute immediate in pl/sql, create table with execute immediate in pl/sql.How to create a table in PL/SQL , How to create a table using Execute Immediate , Create table using dynamic sql in pl/sql, How to create table using dynamic sql.

oracle rename synonym

oracle rename renames ANY private synonym.

Synonyms can be created to table,view,sequence,procedure,function,package,materialized view,java class schema,user-defined object type or another synonym.

SYNTAX:

Rename <old Name> to <new name>;

Create synonym for scott.emp table;

SQL> Create synonym emp for scott.emp;

renames emp synonym to employees;

SQL>rename emp to employees;

SQL> select * from employees;

Tags: rename SYNONYM in oracle,oracle rename SYNONYM , oracle rename syntax, oracle rename cons,oracle rename pros, How to rename SYNONYM in oracle,rename SYNONYM affect on triggers,rename private synonym in oracle.

oracle rename sequence

oracle rename renames sequence

SYNTAX:

Rename <old Name> to <new name>;

create a sequence emp_id_seq;
SQL> create sequence emp_id_seq;

renames emp_id_seqview to employee_id_seq;

SQL>rename emp_id_seq to employee_id_seq;

SQL> select * from employee_id_seq.nextval;

Tags: rename sequence in oracle,oracle rename sequence , oracle rename syntax, oracle rename cons,oracle rename pros, How to rename sequence in oracle,rename sequence affect on triggers.

oracle rename view

oracle rename renames view, all constraints,indexes , grants will be transferred to new name.

SYNTAX:

Rename <old Name> to <new name>;

Create a View called V1;

create or replace view v1
as
select * from employees;

renames v1 view to v2;

SQL>rename v1 to v2 ;

SQL> select * from v2;

Note: rename will rename triggers associated with that view.

Tags: rename view in oracle,oracle rename view , oracle rename syntax, oracle rename cons,oracle rename pros, How to rename view in oracle,rename view affect on triggers.

oracle rename table

oracle rename renames table, all constraints,indexes , grants will be transferred to new name.

SYNTAX:

Rename <old Name> to <new name>;

renames emp table to employees;

SQL>rename emp to employees;

CONS: once rename occurs on a table, all stored procedures, functions and views AND SYNONYMS WILL BE INVALID.

Note: rename will rename triggers associated with that table.

Tags: rename table in oracle,oracle rename table, oracle rename syntax, oracle rename cons,oracle rename pros, How to rename table in oracle,rename table affect on triggers.

oracle truncate table

truncate command in oracle deletes all records in the table. this operation is faster than delete command.

because of following reasons

delete command records deleted entries will be logged into log file. where as truncate doesn't.

delete command fires triggers, where as truncate don't.

Once truncate occurs on a table, user cannot rollback data.

Syntax:

truncate table [tablename];

Multiple table truncates

truncate table [table1],[table2] ...[table n];

Truncate a tables associated with foreign key reference:

Steps to truncate tables associated with primary key and foreign key references.

Step 1) Truncate all child tables

Step 2) Truncate parent table.

Step 1)Truncate table [child table 1],[child table 2]...[child table n];

Step 2)Truncate table [parent table 1] [parent table 2] ...[parent table n];

Tags: oracle Truncate table,oracle truncate tables, oracle truncate table with foreign key references, oracle truncate multiple tables, truncate trigger in oracle.

Wednesday, May 1, 2013

oracle Add Primary Key Constraints

Primary key constraint makes sure all rows are unique. It uniquely identifies rows in the table.

It doesn't accept null values.it can be single column or multiple columns..

Every individual row can be fetched with primary key. and it returns only one row.

for ex: if Employee number is primary key in the table. then Every employee has only one employee number. with that employee number user can get his/her details. Employee table never allow more than one employee number to Employee . if administrator assigns another empno, then it becomes duplicate value for that employee.

Add Primary key constraint using Alter table command

Syntax: alter table [tablename] add constraint [constraint_name] primary key (col1,col2,col3...);

for ex: alter table employees add constraint pk_emp_empno primary key (empno);

Multicolumn Primary key Constraint

ex: Alter table houseaddress add constraint pk_ha primary key(ID,hno);

Adding Primary key Constraint to Existing Table

SQL> select * from sports;

        ID NAME                 CO
---------- -------------------- --
         1 cricket              IN
         2 cricket              UK
         3 Hand FootBall        US
         4 Base Ball            US

Sports table has data and ID column has all rows are unique.(1,2,3,4), no number is repeated ,so you can add primary key constraint to this column.

Ex: alter table sports add constraint pk_sports_id primary key(ID);

Tags:oracle Add Primary Key Constraints, alter table primary key, single column primary constraint, multiple column primary key in oracle, adding primary key with alter table,
adding multi column primary key with alter table,

Adding Primary key Constraint to Existing Table

Convert Seconds to Date/timespamp in oracle

Convert Seconds to Date in oracle

If user has number between 00 to 59,That number can be converted to timestamp .

Syntax:

to_timestamp(seconds,fmt)

SQL> select to_timestamp('00','ss') from dual;

to_timestamp('00','ss')
-------------------------------------------------
01-MAY-14 12.00.00.000000000 PM

It has current date, time specified in the format.

SQL> select to_timestamp('59','ss') from dual;

to_timestamp('59','ss')
-----------------------------------------------
01-MAY-13 12.00.59.000000000 AM

If seconds is not between 00 and 59

following error message will be displayed.

ERROR at line 1:
ORA-01851: seconds must be between 0 and 59

Tags: Convert seconds to date/timestamp,convert seconds to Date/timestamp using To_timestamp function,oracle convert seconds to date ,oracle convert seconds to date/timestamp using to_date,oracle convert seconds to current seconds date/timestamp.

Saturday, May 4, 2013

oracle null related functions

ORACLE ENVIRONMENT FUNCTIONS

oracle conversion functions

ORACLE CHARACTER FUNCTIONS RETURNING NUMBERS

oracle nls character functions

ORACLE CHARACTER FUNCTIONS RETURNING CHARS

oracle numeric functions

ORACLE DATETIME FUNCTIONS

oracle object reference functions

ORACLE MODEL FUNCTIONS

ORACLE BITAND FUNCTION

ORACLE BIN_TO_NUM FUNCTION

ORACLE AVG FUNCTION

ORACLE ABS FUNCTION

Friday, May 3, 2013

ORACLE ROUND FUNCTION

ORACLE SYSTIMESTAMP

oracle trunc number function

ORACLE CONCAT FUNCTION

ORACLE init cap function

ORACLE ASCII FUNCTION

ORACLE MIN FUNCTION

ORACLE MAX FUNCTION

Find Number of Years Experience in Oracle

Execute Immediate Update returns Bulk Collect

This example updates Stock manager and Stock clerk salary by 20% using Execute Immediate and returns their last name.

Thursday, May 2, 2013

Execute Immediate Bulk Collect in oracle PL/SQL

Here is the example of BULK COLLECT using DYNAMIC SQL/EXECUTE IMMEDIATE

Delete row(s) using Execute Immediate

Delete records from ab table using Execute Immediate/Dynamic SQL

UPDATE Table using Execute Immediate

Update records in ab table using Execute Immediate/Dynamic SQL

Insert into a Table using Execute Immediate

Insert records into ab table using Execute Immediate/Dynamic SQL

Create Table in PL/SQL

oracle rename synonym

oracle rename sequence

oracle rename view

oracle rename table

oracle truncate table

Multiple table truncates

Truncate a tables associated with foreign key reference:

Wednesday, May 1, 2013

oracle Add Primary Key Constraints

Add Primary key constraint using Alter table command

Syntax: alter table [tablename] add constraint [constraint_name] primary key (col1,col2,col3...);

Multicolumn Primary key Constraint

Adding Primary key Constraint to Existing Table

Convert Seconds to Date in oracle