A user with a Create any job privilege can create a job in any schema except the sys schema. Attaching a job with a particular class requires the execute privileges for that class.Oracle database 10g offers a collection a rich set of functionality for complex scheduling tasks.The component that causes something to be executed at a specified time is called a Job.We can use DBMS_SCHEDULER.CREATE_JOB procedure of the Dbms_scheduler package to create a jobwhich is in disabled state by default.A job becomes running and scheduled when it is explicitly enabled.Example: - How to create a job, For this you provide a name in the form [schema.]name and also need privileges from the sysdba.1. Connect to your database as user SYSTEM using SQL*Plus.2. Create a table to store times, and set your date format to show the date andtime.SQL> create table times (c1 date);SQL> alter session set nls_date_format='dd-mm-yy hh24:mi:ss';3. Create a job to insert the current time into the table every minute.SQL> begindbms_scheduler.create_job(job_name=>'savedate',job_type=>'plsql_block',job_action=>'insert into times values(sysdate);',start_date=>sysdate,repeat_interval=>'freq=minutely;interval=1',enabled=>true,auto_drop=>false);end;/PL/SQL procedure successfully completed.4. Query the job table to see that the job is scheduled.SQL> select job_name,enabled,to_char(next_run_date,'dd-mm-yy hh24:mi:ss'),run_countfrom user_scheduler_jobs;JOB_NAME ENABL TO_CHAR(NEXT_RUN_ RUN_COUNT------------------------- ----- ----------------- ----------SAVEDATE TRUE 15-01-05 14:58:03 25. Query the times table to demonstrate that the inserts are occurring.SQL> select * from times;6. Disable the job.SQL> exec dbms_scheduler.disable('savedate');7. Re-run the queries from Steps 4 and 5 to confirm that the job is disabled, andthat no more inserts are occurring.8. Drop the job:SQL> exec dbms_scheduler.drop_job('savedate');
Thursday, August 28, 2014
Wednesday, August 27, 2014
Implicit Cursor
Implicit Cursors
Implicit cursors are automatically created by Oracle whenever an we SQL statement is process, when there is no explicit cursor for the statement.
Programmers cannot control the implicit cursors and the information in it.
Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.
In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement. The following table provides the description of the most used attributes:
The following program would update the table and increase salary of each customer by 500 an
d use the SQL%ROWCOUNT attribute to determine the number of rows affected:
DECLARE
Attribute Description
%FOUND Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
%NOTFOUND The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
%ISOPEN Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
%ROWCOUNT Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.
Any SQL cursor attribute will be accessed as sql%attribute_name as shown below in the example.
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;
END;
When the above code is executed at SQL prompt, it produces the following result:
6 customers selected
PL/SQL procedure successfully completed.
Saturday, August 23, 2014
PLSQL Interview Questions
1.How Many triggers can be applied to a table?
There are maximum 12 triggers can be applied to a table.
2. Difference between Syntax error & runtime error in PLSQL?
A Syntax error is easily identified by a PLSQL compiler.For example Incorrect spelling.
but runtime error can be handled with the help of Exception Section block in PLSQL.
7.Explain Boolean Datatype with Example In Oracle
DECLARE
V_SAL NUMBER;
V_FLAG BOOLEAN;
BEGIN
SELECT SALARY INTO V_SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=&EMPNO;
V_FLAG := (V_SAL>=10000);
IF V_FLAG=TRUE THEN
DBMS_OUTPUT.PUT_LINE('HIGH GRADE');
ELSE
DBMS_OUTPUT.PUT_LINE('LOWER GRADE');
END IF;
END;
8.Find out the First_name of the employee and the name of the departent for the employee who is managing for worker employee_id is 124.
declare
fname employees.first_name%type;
dname departments.department_name%type;
begin
select first_name , department_name into
fname, dname
from employees join departments using
(department_id)
where employee_id = ( select manager_id from
employees where employee_id= 124);
dbms_output.put_line(fname);
dbms_output.put_line(dname);
end;
/
9.Write a PL Sql Block that display Top 10 employees salaries from Employees Table.
DECLARE
CURSOR EMPCUR IS SELECT * FROM EMPLOYEES;
EMPREC EMPLOYEES%ROWTYPE;
BEGIN
OPEN EMPCUR;
LOOP
FETCH EMPCUR INTO EMPREC;
DBMS_OUTPUT.PUT_LINE(EMPREC.LAST_NAME||' GETS '||EMPREC.SALARY);
EXIT WHEN EMPCUR%ROWCOUNT >= 10 OR EMPCUR%NOTFOUND;
END LOOP;
CLOSE EMPCUR;
END;
10.What is transaction?
A transaction is a set of sql statements like data manipulation that works with logical unit .
You can use commit,Rollback Savepoint command to control the transaction.
At the end of the transaction that makes database changes,oracle makes all the changes permanent save or may be undone.
if your program fails in the middle of a transaction,oracle detect the error and rollback the transaction and restoring the database.
1.Commit : Commit command to make changes permanent save to a database during the current transaction.
2. Rollback : Rollback command executes at the end of current transaction and undo any changes made since the begin transaction.
3.Savepoint : Savepoint command save the current point with the unique name in the processing of a transaction.
4.Autocommit : Set Autocommit on to execute commit statement automatically.
5.Set transaction : PL/SQL set transaction command set the transaction properties such as write/read access.
Savepoint Savepoint_names is marks the current point in the processing of a transaction.Savepoint let you rollback part of a transaction instead of the whole transaction.
SQL> create table temp (id number,fname varchar2(30));
Table created.
SQL> insert into temp values(1,'Konal');
1 row created.
SQL> savepoint a;
Savepoint created.
SQL> insert into temp values(2,'Santosh');
1 row created.
SQL> insert into temp values(3,'Nikita');
1 row created.
SQL> insert into temp values(4,'Shalini');
1 row created.
SQL> savepoint b;
Savepoint created.
SQL> select * from temp;
ID FNAME
---------- ------------------------------
1 Konal
2 Santosh
3 Nikita
4 Shalini
SQL> insert into temp values(5,'Ahmad');
1 row created.
SQL> rollback to b;
Rollback complete.
SQL> select * from temp;
ID FNAME
---------- ------------------------------
1 Konal
2 Santosh
3 Nikita
4 Shalini
SQL>
3.How to disabled multiple triggers of a table at a time ?
Alter table Table_name disable all triggers.
4. Where is the pre defined procedures stored?
In oracle standard package,procedures,functions.
5.What is a cursor for loop?
Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor,fetches rows of a table values from active set into record variable
& it closes when all the records have been carried out process.
6.What is the difference between Procedure and function?
5.What is a cursor for loop?
Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor,fetches rows of a table values from active set into record variable
& it closes when all the records have been carried out process.
6.What is the difference between Procedure and function?
Function must return value and procedure does not.
Function can be used in sql with some restrictions. where as procedure can not be called directly from sql.
DECLARE
V_SAL NUMBER;
V_FLAG BOOLEAN;
BEGIN
SELECT SALARY INTO V_SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=&EMPNO;
V_FLAG := (V_SAL>=10000);
IF V_FLAG=TRUE THEN
DBMS_OUTPUT.PUT_LINE('HIGH GRADE');
ELSE
DBMS_OUTPUT.PUT_LINE('LOWER GRADE');
END IF;
END;
8.Find out the First_name of the employee and the name of the departent for the employee who is managing for worker employee_id is 124.
declare
fname employees.first_name%type;
dname departments.department_name%type;
begin
select first_name , department_name into
fname, dname
from employees join departments using
(department_id)
where employee_id = ( select manager_id from
employees where employee_id= 124);
dbms_output.put_line(fname);
dbms_output.put_line(dname);
end;
/
9.Write a PL Sql Block that display Top 10 employees salaries from Employees Table.
DECLARE
CURSOR EMPCUR IS SELECT * FROM EMPLOYEES;
EMPREC EMPLOYEES%ROWTYPE;
BEGIN
OPEN EMPCUR;
LOOP
FETCH EMPCUR INTO EMPREC;
DBMS_OUTPUT.PUT_LINE(EMPREC.LAST_NAME||' GETS '||EMPREC.SALARY);
EXIT WHEN EMPCUR%ROWCOUNT >= 10 OR EMPCUR%NOTFOUND;
END LOOP;
CLOSE EMPCUR;
END;
10.What is transaction?
A transaction is a set of sql statements like data manipulation that works with logical unit .
You can use commit,Rollback Savepoint command to control the transaction.
At the end of the transaction that makes database changes,oracle makes all the changes permanent save or may be undone.
if your program fails in the middle of a transaction,oracle detect the error and rollback the transaction and restoring the database.
1.Commit : Commit command to make changes permanent save to a database during the current transaction.
2. Rollback : Rollback command executes at the end of current transaction and undo any changes made since the begin transaction.
3.Savepoint : Savepoint command save the current point with the unique name in the processing of a transaction.
4.Autocommit : Set Autocommit on to execute commit statement automatically.
5.Set transaction : PL/SQL set transaction command set the transaction properties such as write/read access.
Savepoint Savepoint_names is marks the current point in the processing of a transaction.Savepoint let you rollback part of a transaction instead of the whole transaction.
SQL> create table temp (id number,fname varchar2(30));
Table created.
SQL> insert into temp values(1,'Konal');
1 row created.
SQL> savepoint a;
Savepoint created.
SQL> insert into temp values(2,'Santosh');
1 row created.
SQL> insert into temp values(3,'Nikita');
1 row created.
SQL> insert into temp values(4,'Shalini');
1 row created.
SQL> savepoint b;
Savepoint created.
SQL> select * from temp;
ID FNAME
---------- ------------------------------
1 Konal
2 Santosh
3 Nikita
4 Shalini
SQL> insert into temp values(5,'Ahmad');
1 row created.
SQL> rollback to b;
Rollback complete.
SQL> select * from temp;
ID FNAME
---------- ------------------------------
1 Konal
2 Santosh
3 Nikita
4 Shalini
SQL>
Friday, August 22, 2014
Example Of Constraints in Sql
Primary Key:- YOU CAN NOW CREATE A PRIMARY KEY IN oracle with the CREATE TABLE STATEMENT.
SYNTAX
THE SYNTAX TO CREATE A PRIAMARY KEY USING THE CREATE TABLE STATEMENT IN ORACLE/PLSQL IS:
create table empl(employee_id number primary key,
last_name varchar2(50),
city varchar2(50))
/
Syntax
Alter table table_name
Add column column_defination
Add constraint clause
drop[column] column-name [cascade|Restrict]
drop{primary key}foreign key constraint_name | unique
constraint_name|check constraint-name|constraint constraint-name}
alter [column] column_alteration|locksize {Row |Table}
alter table emp1 add phone number;
In oracle Sql-PLSQL a foreign key is column that appears in one table and must appear in another table.
Create table table_name
(Column_name1 datatype Null/Not null,
Column_name2 datatype Null/Not null,
Column_name3 datatype Null/Not null,
constraint constraint_name
foreign key(column_name1,column_name2....column_namen)
On delete cascade;
Syntax to create a foreign key with on Delete Cascade in alter table statement is:
Alter Table Table_name
Add Constraint constraint_name
Foreign key (Column_name1,Column_name2,....Column_nameN)
On Delete Cascade;
Now we will take one example:-
create table dept(department_id number,
department_name varchar2(50),
location_id number,
city varchar2(50));
alter table dept add constraint pk10 primary key(department_id);
create table emp(employee_id number,last_name varchar2(50),
salary number(10,2),
department_id number )
/
alter table emp add constraint fk14 foreign key(department_id)
references dept(department_id) on delete cascade;
/
How to check user constraints from employees table
select constraint_name,constraint_type,index_name
from user_constraints
where table_name='EMPLOYEES'
/
SYNTAX
THE SYNTAX TO CREATE A PRIAMARY KEY USING THE CREATE TABLE STATEMENT IN ORACLE/PLSQL IS:
create table empl(employee_id number primary key,
last_name varchar2(50),
city varchar2(50))
/
Syntax
Alter table table_name
Add column column_defination
Add constraint clause
drop[column] column-name [cascade|Restrict]
drop{primary key}foreign key constraint_name | unique
constraint_name|check constraint-name|constraint constraint-name}
alter [column] column_alteration|locksize {Row |Table}
alter table emp1 add phone number;
In oracle Sql-PLSQL a foreign key is column that appears in one table and must appear in another table.
Create table table_name
(Column_name1 datatype Null/Not null,
Column_name2 datatype Null/Not null,
Column_name3 datatype Null/Not null,
constraint constraint_name
foreign key(column_name1,column_name2....column_namen)
On delete cascade;
Syntax to create a foreign key with on Delete Cascade in alter table statement is:
Alter Table Table_name
Add Constraint constraint_name
Foreign key (Column_name1,Column_name2,....Column_nameN)
On Delete Cascade;
Now we will take one example:-
create table dept(department_id number,
department_name varchar2(50),
location_id number,
city varchar2(50));
alter table dept add constraint pk10 primary key(department_id);
create table emp(employee_id number,last_name varchar2(50),
salary number(10,2),
department_id number )
/
alter table emp add constraint fk14 foreign key(department_id)
references dept(department_id) on delete cascade;
/
How to check user constraints from employees table
select constraint_name,constraint_type,index_name
from user_constraints
where table_name='EMPLOYEES'
/
Thursday, August 21, 2014
Oracle-Sql-Basic_Queries
1.Get the first_day of the Month
SQL> SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month"
2 FROM DUAL;
First day
---------
01-AUG-14
2.Get the Last_day of the Current Month
SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month"
FROM DUAL;
3.Write a query that check if a table exists in the current database schema.
SELECT table_name
FROM user_tables
WHERE table_name = 'EMPLOYEES'
4. Write a query that shows a Table structure.
Desc Employees;
5.Write a query to display Current User.
Show User;
6.Write a query to find last_record from a table.
SELECT employee_id,last_name,job_id
FROM employees
WHERE ROWID IN (SELECT MAX (ROWID) FROM employees)
/
7. To Check table has any data
select count(*) from employees
Or select 1 from employees
where Rownum=1
8. How to find out table name for which this constraint applied
sql> select owner_table-name from user_constraints
where constraints_name =<<your constraint_name>>
if you have access to the dba_constraints
sql> select owner_table-name from dba_constraints
where constraints_name =<<your constraint_name>>
9. How to get list of all tables in Oracle
sql> select owner,table_name from all_tables
NOte:- If you are only concerned with tablesthat you own,not those that you have access to, you could use user_table.
/
sql> select owner,table_name from user_tables
10.1. How can you convert a number into words using Oracle Sql Query?
Please see the query below:-
SELECT TO_CHAR (TO_DATE (523, 'j'), 'jsp') from dual
Output:
TO_CHAR(TO_DATE(523,'J'),
-------------------------
five hundred twenty-three
11. Display the details of employees salary in round value.
select last_name ,salary,round(salary,3) from employees
/
12.Explain Various Types of Objects In Oracle?
1. View
2. Tables
3.Synonyms
4.Indexes
5. Tablespaces
13.Display Employees details with Department name & manager name .
SELECT e.employee_id Worker_name ,DEPARTMENT_NAME, FIRST_NAME Manager_name FROM DEPARTMENTS D JOIN EMPLOYEES E ON (D.MANAGER_ID=E.EMPLOYEE_ID)
/
14.How to check if table contains any data
select count(*) from table name to know number of rows.
select count(*) from employees;
16. write a query to show table names from current database schema
select table_name from user_tables
SQL> SELECT table_name
FROM user_tables;
17.How is the primary key different from a unique key ?
8. How to find out table name for which this constraint applied
sql> select owner_table-name from user_constraints
where constraints_name =<<your constraint_name>>
if you have access to the dba_constraints
sql> select owner_table-name from dba_constraints
where constraints_name =<<your constraint_name>>
9. How to get list of all tables in Oracle
sql> select owner,table_name from all_tables
NOte:- If you are only concerned with tablesthat you own,not those that you have access to, you could use user_table.
/
sql> select owner,table_name from user_tables
10.1. How can you convert a number into words using Oracle Sql Query?
Please see the query below:-
SELECT TO_CHAR (TO_DATE (523, 'j'), 'jsp') from dual
Output:
TO_CHAR(TO_DATE(523,'J'),
-------------------------
five hundred twenty-three
11. Display the details of employees salary in round value.
select last_name ,salary,round(salary,3) from employees
/
12.Explain Various Types of Objects In Oracle?
1. View
2. Tables
3.Synonyms
4.Indexes
5. Tablespaces
13.Display Employees details with Department name & manager name .
SELECT e.employee_id Worker_name ,DEPARTMENT_NAME, FIRST_NAME Manager_name FROM DEPARTMENTS D JOIN EMPLOYEES E ON (D.MANAGER_ID=E.EMPLOYEE_ID)
/
14.How to check if table contains any data
select count(*) from table name to know number of rows.
select count(*) from employees;
15.How to find the last record from the table
SELECT *
FROM employees
WHERE ROWID IN (SELECT MAX (ROWID) FROM employees)
/
16. write a query to show table names from current database schema
select table_name from user_tables
SQL> SELECT table_name
FROM user_tables;
17.How is the primary key different from a unique key ?
Both these keys are uniquely identifying a record in a
database table.
But one difference is that you can have more than one unique
per table,
But only one primary key. Also ,the primary key does not any null value
while
the unique key allows null value.
18: Get all employee details from the employees table
Sql> select * from employees
19: Get the position of 'g' in the last_name 'King' from employees table.
Sql> select (instr(last_name,'g') ) from employees
where last_name='King';
(INSTR(LAST_NAME,'G'))
----------------------
4
4
20. select first three characters from last_name from employees table
SQL> select substr(last_name,0,3) from employees
2 where department_id=30;
18: Get all employee details from the employees table
Sql> select * from employees
19: Get the position of 'g' in the last_name 'King' from employees table.
Sql> select (instr(last_name,'g') ) from employees
where last_name='King';
(INSTR(LAST_NAME,'G'))
----------------------
4
4
20. select first three characters from last_name from employees table
SQL> select substr(last_name,0,3) from employees
2 where department_id=30;
Tuesday, August 19, 2014
Conversion function
Implicit conversion:-
A varchar2 or char value can be implicitly converted to
Number or date type value by oracle.
Similarly, a number or data type can be automatically
Converted to character data by oracle server.
For example the below select queries .both the queries will
Give the same output because oracle internally treats 10000
And '10000' are same.
Query-1
select employee_id,first_name,last_name,salary
From employees
Where salary > 10000;
Query-2
select employee_id,first_name,last_name,salary
from employees
where salary > '10000';
Explicit data type conversion are single row function which are capable of
typecasting column value,literal or expression.
To_char,To_number,To_date are three functions which perform
change of data types.
1. To_char()
This function is used to typecast a numeric value or date to character value with
a format model.
Syntax
To_char(number1,[format],[nls_paramete])
Consider the below select query.The query format the Hire_date & salary columns of
employees table using To_char function.
select first_name,To_char(hire_date,'Month DD,YYYY') hire_date,To_char
(salary,'$99999.99') salary
from employees
where rownum < 5;
select to_char(sysdate,'Month') from dual;
Elements Of the Date Model Function.
The To_number function converts a character value to a numeric data type.
if the string being converted contains non numeric characters, the function return an error.
Syntax
To_number(string1,[format],[nls_parameter])
SELECT TO_NUMBER('129.24', '9G999D99')
FROM DUAL;
TO_NUMBER('129.24','9G999D99')
------------------------------
129.24
To_date Function
This function takes character values as input & return formatted date
Equivalent of the same.
To_date function allows users to enter a date in any format, then it converts the entry
Into the default format by oracle server.
Syntax:-To_date(strin1,[format_mask],[nls_language])
SELECT TO_DATE('July 15, 1987, 09:00 A.M.', 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American')
FROM DUAL
/
A varchar2 or char value can be implicitly converted to
Number or date type value by oracle.
Similarly, a number or data type can be automatically
Converted to character data by oracle server.
For example the below select queries .both the queries will
Give the same output because oracle internally treats 10000
And '10000' are same.
Query-1
select employee_id,first_name,last_name,salary
From employees
Where salary > 10000;
Query-2
| YYYY | Full year in numbers |
| YEAR | Year spelled out (in English) |
| MM | Two-digit value for month |
| MONTH | Full name of the month |
| MON | Three-letter abbreviation of the month |
| DY | Three-letter abbreviation of the day of the week |
| DAY | Full name of the day of the week |
| DD | Numeric day of the month |
| To_char functions | |
| Element | Result |
| 9 | Represents a number |
| 0 | Forces a zero to be displayed |
| $ | Places a floating dollar sign |
| L | Uses the floating local currency symbol |
| . | Prints a decimal point |
| , | Prints a comma as thousands indicator |
select employee_id,first_name,last_name,salary
from employees
where salary > '10000';
Explicit data type conversion are single row function which are capable of
typecasting column value,literal or expression.
To_char,To_number,To_date are three functions which perform
change of data types.
1. To_char()
This function is used to typecast a numeric value or date to character value with
a format model.
Syntax
To_char(number1,[format],[nls_paramete])
Consider the below select query.The query format the Hire_date & salary columns of
employees table using To_char function.
select first_name,To_char(hire_date,'Month DD,YYYY') hire_date,To_char
(salary,'$99999.99') salary
from employees
where rownum < 5;
select to_char(sysdate,'Month') from dual;
Elements Of the Date Model Function.
The To_number function converts a character value to a numeric data type.
if the string being converted contains non numeric characters, the function return an error.
Syntax
To_number(string1,[format],[nls_parameter])
SELECT TO_NUMBER('129.24', '9G999D99')
FROM DUAL;
TO_NUMBER('129.24','9G999D99')
------------------------------
129.24
To_date Function
This function takes character values as input & return formatted date
Equivalent of the same.
To_date function allows users to enter a date in any format, then it converts the entry
Into the default format by oracle server.
Syntax:-To_date(strin1,[format_mask],[nls_language])
SELECT TO_DATE('July 15, 1987, 09:00 A.M.', 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American')
FROM DUAL
/
Friday, August 15, 2014
Convert Number Into Words
1. How can you convert a number into words using Oracle Sql Query?
Please see the query below:-
SELECT TO_CHAR (TO_DATE (523, 'j'), 'jsp') from dual
Output:
TO_CHAR(TO_DATE(523,'J'),
-------------------------
five hundred twenty-three
2. Write a pl sql block that count the department wise total salary where more than 5 employees are exists.
SELECT department_id, count(*),sum(salary) As totalsal
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) >5
Given below is the output you get
DEPARTMENT_ID COUNT(*) TOTALSAL
------------- ---------- ----------
100 6 51600
30 6 24900
50 45 156400
80 34 304500
How to create User in Oracle & assign grant revoke privileges
CREATE USER user in Oracle
Controlling User Access
In a multiple-user environment, you want to
maintain security of the database access and use. With Oracle server database
security, you can do the following:
Control database access.
Give access to specific objects in the
database.
Confirm given and received privileges with
the Oracle data dictionary.
Create synonyms for database objects.
Database security can be classified into
two categories: system security and data security. System security covers
access and use of the database at the system level such as the username and
password, the disk space allocated to users, and the system operations that
users can perform. Database security covers access and use of the database
objects and the actions that those users can have on the objects.
Privileges
Privileges are the right to execute
particular SQL statements. The database administrator (DBA) is a high-level
user with the ability to create users and grant users access to the database
and its objects. Users require system privileges to gain access to the database
and object privileges to manipulate the content of the objects in the database.
Users can also be given the privilege to grant additional privileges to other
users or to roles, which are named groups of related privileges.
Schemas
A schema is a collection of objects such as
tables, views, and sequences. The schema is owned by a database user and has
the same name as that user.
System Privileges
More than 100 distinct system privileges
are available for users and roles. System privileges typically are provided by
the database administrator.
Typical DBA Privileges
CREATE USER user
IDENTIFIED BY password;
Creating a User
The DBA creates a user by executing the
CREATE USER statement. The user does not have any privileges at this point. The
DBA can then grant privileges to that user. These privileges determine what the
user can do at the database level
CREATE USER hema
IDENTIFIED BY hema
MANAGING SCHEMA OBJECT
OBJECTIVES:
· Altering
table
· Adding
constraint
· Setting
column unused
· Creating
index at time of table creation
· Flashback
technology
v ALTERING TABLE:
ADDING
COLUMN:
Alter table <table name> add
(<column name> <datatype> );
For eg:
SQL> alter table t45 add(employee_id
number);
Table altered.
DROPPING
COLUMN:
Alter table <table name> drop column
<column name>;
For eg:
SQL> alter table t45 drop column dat1;
Table altered.
ADDING
CONSTRAINT:
SQL> ALTER TABLE N3 MODIFY ID
CONSTRAINT PK_ID PRIMARY KEY;
Table altered.
OR
SQL> ALTER TABLE N3 ADD CONSTRAINT
EMAIL_UQ UNIQUE(EMAIL_ID);
Table altered.
DROPPING
CONSTRAINT:
SQL> ALTER
TABLE N3 DROP CONSTRAINT EMAIL_UQ ;
Table
altered.
ON DELETE CASCADE:
SQL> ALTER TABLE N3 ADD CONSTRAINT
DEPT_FK FOREIGN KEY(DEPT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID) ON DELETE
CASCADE;
Table altered.
DEFERRRING CONSTRAINT:
· DEFERRABLE
AND NON-DEFERRABLE
· INITIALLY
DEFERRED AND INITIALLY IMMEDIATE
DEFERRABLE INITIALLY DEFERRED:
SQL> ALTER TABLE N3 ADD CONSTRAINT PK_ID
PRIMARY KEY(ID) DEFERRABLE INITIALLY DEFERRED;
Table altered.
(CHEKS AFTER COMMIT)
NOTE:BY DEFAULT ALL CONSTRAINT, IF NOT
SPECIFIED ,ARE INITIALLY IMMEDIATE.
TO SET A PERTICULER CONSTRAINT IMMEDIATE
FOR A SESSION BEING:
SQL> SET CONSTRAINT PK_ID IMMEDIATE;
Constraint set.
TO SET ALL CONSTRAINTS IMMEDIATE FOR A
SESSION BEING:
SQL> ALTER SESSION SET
CONSTRAINTS=IMMEDIATE;
Session altered.
TO ALTER SEQUENCE:
ALTER SEQUENCE EMPLOYEES_SEQ INCREMENT BY
10;
SET UNUSED:
SQL> ALTER TABLE N3 SET UNUSED COLUMN
DATE1;
Table altered.
NOTE:
ONCE U UNUSED A COLUMN, U CAN’T SEE IN DESC
COMMAND,ALSO U CAN DROP THAT COLUMN AND ADD COLUMN WITH SAME NAME.
SQL> DESC N3;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
ID
NUMBER
EMAIL_ID
VARCHAR2(20)
DEPT_ID
NUMBER
SQL> ALTER TABLE N3 ADD DATE1 DATE;
Table altered.
DROPPING UNUSED COLUMN
ALTER TABLE N4 DROP UNUSED COLUMN;
CREATING INDEX AT TIME OF TABLE CREATION
SQL> CREATE TABLE N4(ID NUMBER PRIMARY
KEY USING INDEX (CREATE INDEX IND123 ON N4(ID)),NAME VARCHAR2(20));
Table created.
SQL> SELECT CONSTRAINT_NAME FROM
USER_CONSTRAINTS WHERE TABLE_NAME='N4';
CONSTRAINT_NAME
------------------------------
SYS_C005613
SQL> SELECT INDEX_NAME FROM USER_INDEXES
WHERE TABLE_NAME='N4';
INDEX_NAME
------------------------------
IND123
FLASHBACK TECHNOLOGY:
SQL> DROP TABLE N3;
Table dropped.
SQL> SELECT * FROM RECYCLEBIN WHERE
ORIGINAL_NAME='N3';
OBJECT_NAME
ORIGINAL_NAME
OPERATION
------------------------------
-------------------------------- ---------
TYPE
TS_NAME
CREATETIME
-------------------------
------------------------------ -------------------
DROPTIME
DROPSCN
PARTITION_NAME
CAN CAN
------------------- ----------
-------------------------------- --- ---
RELATED BASE_OBJECT
PURGE_OBJECT SPACE
---------- ----------- ------------
----------
BIN$R5uBkInYSsS4IT5jaZWaBg==$0 N3
DROP
TABLE
USERS
2012-04-17:10:34:49
2012-05-03:10:00:25
2342497
YES YES
53369
53369
53369 8
SQL> FLASHBACK TABLE N3 TO BEFORE DROP;
Flashback complete.
SQL> SELECT * FROM RECYCLEBIN WHERE
ORIGINAL_NAME='N3';
no rows selected
NOTE:
RECYCLEBIN EXIST ONLY FORM NON-SYSTEM
TABLESPACES.
FOR NORMAL USER THERE IS PRIVATE RECYCLEBIN
.
Tuesday, August 12, 2014
What is View in oracle
How to Create SQL View
Introduce to SQL View
SQL View is a virtual table which is used to encapsulate a complex queries. After creating SQL view, you can treat a view as a table and
manipulate data on it with only some restrictions. When the data in table changes, the data in view which is dependent on table changes also.
View does not occupied the physical space as table does. The syntax of creating view as follows:
CREATE VIEW SALARY_REC
AS
SELECT e.last_name,
e.salary,
d.department_name AS department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
Output:
SELECT * FROM SALARY_REC
OConnell 2860 Shipping
Grant 2860 Shipping
Whalen 4840 Administration
Hartstein 14300 Marketing
Fay 6600 Marketing
Mavris 7150 Human Resources
Baer 11000 Public Relations
Higgins 13200 Accounting
Gietz 9130 Accounting
Sullivan 2750 Shipping
Geoni 3080 Shipping
Sarchand 4620 Shipping
Bull 4510 Shipping
Introduce to SQL View
SQL View is a virtual table which is used to encapsulate a complex queries. After creating SQL view, you can treat a view as a table and
manipulate data on it with only some restrictions. When the data in table changes, the data in view which is dependent on table changes also.
View does not occupied the physical space as table does. The syntax of creating view as follows:
CREATE VIEW SALARY_REC
AS
SELECT e.last_name,
e.salary,
d.department_name AS department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
Output:
SELECT * FROM SALARY_REC
OConnell 2860 Shipping
Grant 2860 Shipping
Whalen 4840 Administration
Hartstein 14300 Marketing
Fay 6600 Marketing
Mavris 7150 Human Resources
Baer 11000 Public Relations
Higgins 13200 Accounting
Gietz 9130 Accounting
Sullivan 2750 Shipping
Geoni 3080 Shipping
Sarchand 4620 Shipping
Bull 4510 Shipping
Working With Bfiles
Managing Bfiles
Manipulating Large Objects
A LOB is a data type that is used to store large, unstructured data such as text, graphic images, video clippings, and so
on. Structured data, such as a customer record, may be a few hundred bytes, but even small amounts of multimedia data can
be thousands of times larger. Also, multimedia data may reside in operating system (OS) files, which may need to be
accessed from a database.
There are four large object data types:
BLOB represents a binary large object, such as a video clip.
CLOB represents a character large object.
NCLOB represents a multibyte character large object.
BFILE represents a binary file stored in an OS binary file outside the database. The BFILE column or attribute stores a
file locator that points to the external file.
A table can have multiple LOB columns and object type attributes. A table can have only one LONG column.
The maximum size of LONGs is 2 GB; LOBs can be up to 4 GB.
There are two distinct parts to a LOB:
LOB value: The data that constitutes the real object being stored
LOB locator: A pointer to the location of the LOB value stored in the database.
What Are BFILEs?
BFILEs are external large objects (LOBs) stored in OS files that are external to database tables. The BFILE data type
stores a locator to the physical file. A BFILE can be in GIF, JPEG, MPEG, MPEG2, text, or other formats.
Securing BFILEs
Unauthenticated access to files on a server presents a security risk.Oracle Database 10g can act as a security mechanism
to shield the operating system from unsecured access while removing the need to manage additional user accounts on an
enterprise computer system.
A DIRECTORY is a nonschema database object that enables administration of access and usage of BFILEs in Oracle Database
10g.
Example :-
create table emp as select * from employees;
ALTER TABLE emp ADD (photo blob);
UPDATE emp
Empty_CLOB – It is a special function, which can be used in Insert or Update statement of SQL DML to initialize a NULL.
--The EMPTY_BLOB function returns an empty locator of type BLOB (binary large object).
--. It cannot be NULL. The locator might point to an empty BLOB value, but it will be a --valid BLOB locator.
SET photo = empty_blob( )
CREATE OR REPLACE DIRECTORY photo_folder AS 'F:\students:'
CONNECT sys as sysdba
GRANT CREATE ANY DIRECTORY TO Hr;
GRANT DROP ANY DIRECTORY TO hr;
connect hr/hr
CREATE OR REPLACE DIRECTORY photo_folder AS 'f:\students'
-----------------------------------------------
CREATE OR REPLACE PROCEDURE insert_photo
(p_empno NUMBER, p_photo VARCHAR2)
AS
f_photo BFILE;
b_photo BLOB;
BEGIN
-- Update the employee photo
UPDATE emp
SET photo = empty_blob()
WHERE employee_id = p_empno
RETURN photo into b_photo;
-- find where the photo's pointer is located.
f_photo := bfilename('PHOTO_FOLDER', p_photo);
-- open the photo as read-only option.
dbms_lob.fileopen(f_photo, dbms_lob.file_readonly);
-- load the photo into column photo.
dbms_lob.loadfromfile(b_photo,f_photo, dbms_lob.getlength(f_photo));
-- close the photo's pointer.
dbms_lob.fileclose(f_photo);
-- Save the loaded photo record.
COMMIT;
EXCEPTION
-- Check for your error messages
WHEN others THEN
dbms_output.put_line('*** ERROR *** Check you procedure.');
END;
EXECUTE insert_photo(198, '1.GIF')
EXECUTE insert_photo(124, '1.GIF')
SELECT employee_id,first_name,
dbms_lob.getlength(photo) "Photo Size"
FROM emp
Manipulating Large Objects
A LOB is a data type that is used to store large, unstructured data such as text, graphic images, video clippings, and so
on. Structured data, such as a customer record, may be a few hundred bytes, but even small amounts of multimedia data can
be thousands of times larger. Also, multimedia data may reside in operating system (OS) files, which may need to be
accessed from a database.
There are four large object data types:
BLOB represents a binary large object, such as a video clip.
CLOB represents a character large object.
NCLOB represents a multibyte character large object.
BFILE represents a binary file stored in an OS binary file outside the database. The BFILE column or attribute stores a
file locator that points to the external file.
A table can have multiple LOB columns and object type attributes. A table can have only one LONG column.
The maximum size of LONGs is 2 GB; LOBs can be up to 4 GB.
There are two distinct parts to a LOB:
LOB value: The data that constitutes the real object being stored
LOB locator: A pointer to the location of the LOB value stored in the database.
What Are BFILEs?
BFILEs are external large objects (LOBs) stored in OS files that are external to database tables. The BFILE data type
stores a locator to the physical file. A BFILE can be in GIF, JPEG, MPEG, MPEG2, text, or other formats.
Securing BFILEs
Unauthenticated access to files on a server presents a security risk.Oracle Database 10g can act as a security mechanism
to shield the operating system from unsecured access while removing the need to manage additional user accounts on an
enterprise computer system.
A DIRECTORY is a nonschema database object that enables administration of access and usage of BFILEs in Oracle Database
10g.
Example :-
create table emp as select * from employees;
ALTER TABLE emp ADD (photo blob);
UPDATE emp
Empty_CLOB – It is a special function, which can be used in Insert or Update statement of SQL DML to initialize a NULL.
--The EMPTY_BLOB function returns an empty locator of type BLOB (binary large object).
--. It cannot be NULL. The locator might point to an empty BLOB value, but it will be a --valid BLOB locator.
SET photo = empty_blob( )
CREATE OR REPLACE DIRECTORY photo_folder AS 'F:\students:'
CONNECT sys as sysdba
GRANT CREATE ANY DIRECTORY TO Hr;
GRANT DROP ANY DIRECTORY TO hr;
connect hr/hr
CREATE OR REPLACE DIRECTORY photo_folder AS 'f:\students'
-----------------------------------------------
CREATE OR REPLACE PROCEDURE insert_photo
(p_empno NUMBER, p_photo VARCHAR2)
AS
f_photo BFILE;
b_photo BLOB;
BEGIN
-- Update the employee photo
UPDATE emp
SET photo = empty_blob()
WHERE employee_id = p_empno
RETURN photo into b_photo;
-- find where the photo's pointer is located.
f_photo := bfilename('PHOTO_FOLDER', p_photo);
-- open the photo as read-only option.
dbms_lob.fileopen(f_photo, dbms_lob.file_readonly);
-- load the photo into column photo.
dbms_lob.loadfromfile(b_photo,f_photo, dbms_lob.getlength(f_photo));
-- close the photo's pointer.
dbms_lob.fileclose(f_photo);
-- Save the loaded photo record.
COMMIT;
EXCEPTION
-- Check for your error messages
WHEN others THEN
dbms_output.put_line('*** ERROR *** Check you procedure.');
END;
EXECUTE insert_photo(198, '1.GIF')
EXECUTE insert_photo(124, '1.GIF')
SELECT employee_id,first_name,
dbms_lob.getlength(photo) "Photo Size"
FROM emp
Thursday, August 7, 2014
Locate a Test Center
1) PROMETRIC TESTING (P) LTD
MARWAH HOUSE, SAKI VIHAR ROAD
SAKI NAKA, ANDHERI EAST
MUMBAI, Maharashtra 400072
Phone: 022-28573160
Site Code: II004
2) NIIT LTD. BANDRA-MUMBAI
2ND FLOOR GURUVIDYA C.H.S
98/A HILL ROAD
OPP.BANDRA POLICE STATION
BANDRA(W)
MUMBAI, Maharashtra 400 050
Phone: 022-26451126/4493
Site Code: II7
3) HORIZON COMPUTER
A-WING
307, OM RACHNA BUILDING
SECTOR 17
VASHI
MUMBAI, Maharashtra
Phone: 022-55912954
Site Code: IIE69
4) ULTRAMAX INFONET TECHNOLOGIES
GROUND FLOOR
DNYAN SAGAR R.B.S.K BOLE ROAD
DADAR WEST
MUMBAI, Maharashtra 400028
Phone: 24221538
Site Code: IIC89
5) ACIT COMPUTER CENTRE
17, ARABIAN APTS
PITAMBER LANE, OPP DENA BANK
MAHIM (W)
MUMBAI, Maharashtra 400016
Phone: 24468281
Site Code: IIE76
6) CMC LIMITED
C-18,BANDRA-KURLA COMPLEX
BANDRA (EAST)
MUMBAI, Maharashtra 400051
Phone: 26591057
Site Code: IIG31
7) PACIFIC NETWORKS
2, SATENDRA BHAVAN, FIRST
FLOOR, NEW NAGARDAS ROAD,
NEAR PINKI CINEMA
ANDHERI (E)
MUMBAI, Maharashtra 400069
Phone: 66949109
Site Code: IIG36
8) THE SOFT ORIGIN
15 B , Sharda Estate
Madhavdas Pasta Road
Opposite Shree Sound Studio
MUMBAI, Maharashtra 400 014
Phone: 24166655/56
Site Code: IIG41
9) GATES
SENAPATI BAPAT MARG
C-8/A,KASTURCHAND MILLS COMPOUND
DADAR-W
MUMBAI, Maharashtra 400028
Phone: 24367658
Site Code: IIG92
10) CMS COMPUTERS LTD.
COMET 6TH FLOR
BANDRA STATION ROAD
BANDRA(W)
MUMBAI, Maharashtra 400050
Phone: 26418789/90
Site Code: IIH11
11) IIHT LTD
501 PARADISE TOWER
GOKHALE ROAD, NAUPADA
NEXT TO MC DONALDS, THANE(W)
MUMBAI, Maharashtra 400602
Phone: 25303707
Site Code: IIH29
12) NIIT LIMITED-GHATKOPAR-MUMBAI
NIIT LIMITED – GHATKOPAR
2ND FLR,JAYANT ARCADE, ABOVE
SHYAMRAO VITHAL BANK, RAJAWADI
CORNER,M.G.RD,GHATKOPAR (E)
MUMBAI, Maharashtra 400077
Phone: 25093013/14/15
Site Code: IIN30
13) NIIT LTD – CHURCHGATE
EXPRESS BULDING, 14TH E-ROAD
ABOVE SATKAR HOTEL
CHURCHGATE
MUMBAI, Maharashtra 400020
Phone: 22042442/6/9
Site Code: IIN41
14) SSI EDUCATION-ANDHERI-MUMBAI
SSI EDUCATION-ANDHERI
SUKHMANI BLDG,S.V.ROAD
ANDHERI-W
MUMBAI, Maharashtra 400058
Phone: 26705316/27
Site Code: IIO6
15) SSI EDUCATION- GHATKOPAR-MUMBAI
SSI EDUCATION- GHATKOPAR
SATYAM CENTRE, B-WING
M.G.ROAD,
GHATKOPAR
MUMBAI, Maharashtra 400077
Phone: 25100660
Site Code: IIO7
16) SSI EDUCATION – BORIVALI
SSI EDUCATION – BORIVALI-W
204/B, LAXMI PALACE,S V ROAD
ABOVE SONY MONY ELECTORNICS
BORIVALI-W
MUMBAI, Maharashtra 400092
Phone: 28334229
Site Code: IIO9
17) SQL STAR INTERNATIONL LTD-MUMBAI
2nd Floor,Citi Point
Telli Gully
Andheri East
MUMBAI, Maharashtra 400 069
Phone: 26822343/44
Site Code: IIU4
18) CMS COMPUTERS INSTITUTE
NO 301 TO 306 SHOPPERS POINTS BLDG
3RD FLOOR
S.V.ROAD ANDHERI(W)
MUMBAI, Maharashtra 400058
Phone: 26202522/26202533
Site Code: IIH16
19) NIIT LTD. VASI-MUMBAI
1 ST FLOOR SHANTI CENTRE
Sector 17
Near Hotel Navratna
Vashi
Navi – MUMBAI, Maharashtra 400705
Phone: 27896359/27894524
Site Code: IIN33
20) SSI EDUCATION – VASHI
SSI EDUCATION
SECTOR -17, SHANTI CENTRE
NAVI MUMBAI, Maharashtra 400706
Phone: 55911248/49/50
Site Code: IIO29