Oracle blog: PL/SQL

Difference between ALL, ANY, SOME in Oracle

ALL : compare one value to a list or subquery.

SELECT empno, sal

FROM emp

WHERE sal > ALL (2000, 3000, 4000);

EMPNO SAL

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

7839 5000

ANY/SOME : compare one value to a list or subquery.

SELECT empno, sal

FROM emp

WHERE sal > ANY (2000, 3000, 4000);

EMPNO SAL

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

7566 2975

7698 2850

7782 2450

7788 3000

7839 5000

7902 3000

Command to encrypt PL/SQL code

1. Get the SQL file which is to be wrapped

2. Open Run > cmd è to open windows command prompt and run below shown commands

C:\Users\MKumaraVel\Documents>cd C:\Users\MKumaraVel\Documents

C:\Users\MKumaraVel\Documents>wrap iname=unwrapped.sql oname=wrapped.sql

PL/SQL Wrapper: Release 10.1.0.4.2- Production on Fri Oct 05 16:29:44 2018

Processing unwrapped.sql to wrapped.sql

3. The file would be encoded, open wrapped.sql to check the same

Difference between View and Materialized view

View: Views are logical virtual (read only) table created by “select query”, the result of the query is not stored in the disk. Performance of the view depends on the query

Materialized View: MVs are also logical table created by “select query”, the result of the query is stored in the disk.

View	Materialized View
In views result is not stored in the disk In case of views we always get the latest data Performance of the view is less than the MV	In MVs, result is stored in the disk In MVs, we need to refresh it to get latest data

How to refresh Materialized View

Normalization concept
Cascading concept
Difference between inner join and outer join
String functions
Analytical functions
Aggregate functions
What are the triggers available in Oracle Reports

SMSing using PL/SQL

· 1. Compile the below procedure on sql*plus

CREATE OR REPLACE PROCEDURE send_sms (
p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
p_message IN VARCHAR2
)
AS
mailcon UTL_SMTP.connection;
BEGIN
mailcon := UTL_SMTP.open_connection ('localhost');
UTL_SMTP.helo (mailcon, 'localhost');
UTL_SMTP.mail (mailcon, p_sender);
UTL_SMTP.rcpt (mailcon, p_recipient);
UTL_SMTP.DATA (mailcon,
'From: '
|| p_sender
|| UTL_TCP.crlf
|| 'To: '
|| p_recipient
|| UTL_TCP.crlf
|| 'Subject: SMS From Database'
|| UTL_TCP.crlf
|| p_message
);
UTL_SMTP.quit (mailcon);
END;

2. We need to execute the above procedure so as to send SMS. The systax is as follows:

exec SEND_SMS('arun@yahoo.com', '984812345@ideacellular.net','This is my first SMS');

Note: In place of the '984812345@ideacellular.net' you need to provide your mobile number. Also please refer to the below list of mobile operator and the area of the service.

Andhra Pradesh AirTel-----------Mobile No@airtelap.com
Andhra Pradesh Idea Cellular-----Mobile No@ideacellular.net
Chennai Skycell/Airtel------------Mobile No@airtelchennai.com
Chennai RPG Cellular-------------Mobile No@rpgmail.net
Delhi Airtel-----------------------Mobile No@airtelmail.com
Delhi Hutch-----------------------Mobile No@delhi.hutch.co.in
Gujarat Airtel---------------------Mobile No@airtelmail.com
Gujarat Idea Cellular--------------Mobile No@ideacellular.net
Gujarat Celforce/Fascel-----------Mobile No@celforce.com
Goa Airtel-------------------------Mobile No@airtelmail.com
Goa BPL Mobile-------------------Mobile No@bplmobile.com
Goa Idea Cellular------------------Mobile No@ideacellular.net
Haryana Airtel--------------------Mobile No@airtelmail.com
Haryana Escotel-------------------Mobile No@escotelmobile.com
Himachal Pradesh Airtel-----------Mobile No@airtelmail.com
Karnataka Airtel-------------------Mobile No@airtelkk.com
Kerala Airtel-----------------------Mobile No@airtelkerala.com
Kerala Escotel---------------------Mobile No@escotelmobile.com
Kerala BPL Mobile-----------------Mobile No@bplmobile.com
Kolkata Airtel----------------------Mobile No @airtelkol.com
Madhya Pradesh Airtel-------------Mobile No@airtelmail.com
Maharashtra Airtel-----------------Mobile No@airtelmail.com
Maharashtra BPL Mobile-----------Mobile No@bplmobile.com
Maharashtra Idea Cellular----------Mobile No@ideacellular.net
Mumbai Airtel----------------------Mobile No@airtelmail.com
Mumbai BPL Mobile----------------Mobile No@bplmobile.com
Punjab Airtel-----------------------Mobile No@airtelmail.com
Pondicherry BPL Mobile------------Mobile No @bplmobile.com
Tamil Nadu Airtel-------------------Mobile No@airtelmail.com
Tamil Nadu BPL Mobile-------------Mobile No@bplmobile.com
Tamil Nadu Aircel-------------------Mobile No@airsms.com
UP (West) Escotel-------------------Mobile No@escotelmobile.com

Nth highest Salary:

Using dense rank:

Select * from

(Select sal, dense_rank() over (order by sal desc) ranking from table)

Where

Ranking = 4;

Using level:

Select level, max(sal) from emp where level = &level

Connect by prior sal > sal group by level;

To find duplicates

Select col1, col2, count(*) from table group by col1, col2 having count(*) > 1;
Select * from (Select t.*, count(*) over (partition by col1, col2) ct from table t) where ct > 1;
With counts as (select t.*, count(*) over (partition by col1, col2) ct from table t)

Select * from counts where ct > 1

To delete duplicates

Delete from table a where a.rowid > (

Select b.rowid from table b where a.col1 = b.col1 and a.col2 = b.col2);

REGEXP_SUBSTR

https://livesql.oracle.com/apex/livesql/file/content_9I9GXKCB2CD2T27ZJE3CMP6I.html

Extracting letter and number sequences from a string

Statement

with strings as (

select 'ABC123' str from dual union all

select 'A1B2C3' str from dual union all

select '123ABC' str from dual union all

select '1A2B3C' str from dual

)

select regexp_substr(str, '[0-9]'), /* Returns the first number */

regexp_substr(str, '[0-9].*'), /* Returns the first number and the rest of the string */

regexp_substr(str, '[A-Z][0-9]') /* Returns the first letter with a following number */

from strings

REGEXP_SUBSTR(STR,'[0-9]') REGEXP_SUBSTR(STR,'[0-9].*') REGEXP_SUBSTR(STR,'[A-Z][0-9]')/*RETURNSTHEFIRSTLETTERWITHAFOLLOWINGNUMBER*/

1 123 C1

1 1B2C3 A1

1 123ABC -

1 1A2B3C A2

1. What is Normalization?

Ans: Normalization is the process of organizing the tables to remove the redundancy. There

are mainly 5 Normalization rules.

1st Normal Form: A table is said to be in 1st normal form when the attributes are atomic and

there is no repeating groups

2nd Normal Form: A table is said to be in 2nd Normal Form when it is in 1st normal form and all

the non-key columns are functionally dependant on the primary key. .

3rd Normal Form: A table is said to be in 3rd Normal form when it is in 2nd normal form and all

non key attributes not dependant transitively.

4th Normal Form: A table is said to be in 4th normal form when it is in 3rd normal form and has

no multi -valued dependencies.

5th Normal Form: A table is said to be in 5th normal form when it is in 4th normal forma and

every join dependency for the entity is a consequence of its candidate keys.

2. What is the Purpose of the Distinct Clause in SQL?

Distinct Clause allows you to display unique from the result set. This can be used with

only select statements.

3. What are the DDL Commands and the Purpose of these commands?

DDL (Data Definition Language) command is used for defining the structure of the

Data. DDL Statements are auto commit.

· CREATE - to create objects in the database

· ALTER - alters the structure of the database

· DROP - delete objects from the database

· TRUNCATE - remove all records from a table, including all spaces allocated for the

records are removed

· COMMENT - add comments to the data dictionary

· RENAME - rename an object

4. What are the DML commands and Use Of these commands?

DML (Data Manipulation Language) statements are used for managing data within

schema objects.

· INSERT - insert data into a table

· UPDATE - updates existing data within a table

· DELETE - deletes all records from a table, the space for the records remain

DML Statements can be roll backed.

DML Statements can’t be roll backed When DDL Statement Executed immediately

after the DML statement.

5. What are the DCL Commands and purpose of it?

DCL is Data Control Language statements.

o GRANT - gives user's access privileges to database

o REVOKE - withdraw access privileges given with the GRANT command

6. What are the TCL Commands and Purpose of it?

(Transaction Control Language) Manages the changes made by DML statements. These

commands allow statements to be grouped together into logical transactions.

COMMIT - save work done

SAVEPOINT - identify a point in a transaction to which you can later roll back

ROLLBACK - restore database to original since the last COMMIT

7. What is the Difference between TRUNCATE and DROP?

Truncate Delete the entire data from the table and keeps the structure.

TRUCATE TABLE table name

DROP drops the table structure also.

DROP TABLE Table name

8. What is the Difference between TRUNCATE and DELETE?

TRUNCATE DELETE

It’s DDL Statement It’s DML Statement

Auto commit, we can’t retrieve the data

back

we can Retrieve the data back

We can delete entire rows (No

condition)

we can delete rows conditional wise

9. What is NULL?

NULL in Oracle is an Absence of information. A NULL can be assigned but not evaluated

by it self also.

NULL not equal to null

NULL Can not be Not equal to NULL (Neither Equal Not Not Equal)

NULL Does not equal to empty String or doe not equal to ZERO.

10. How can we sort the rows in SQL?

We can Sort the rows Using ORDER By clause.

- ASC : Ascending Order is Default order

- DESC : Sorting in Descending

Null Values Displayed At last in ascending Order

11. How can we convert NULL Value?

Using NVL Function we can convert Null Value to an Actual Value.

NVL(exp1, exp2).

If exp1 is NULL then it returns the exp2.

12. Purpose and Syntax of NVL2 ?

Convert the Null values in to Actual Value.

NVL2 (Exp1, exp2, exp3). If exp1 is NULL it returns the exp3 . if exp1 is not null then it

returns the exp2.

13. When Cartesian product formed?

· A join condition Omitted

· A Join condition Invalid

To Avoid Cartesian Product, always include Valid Join condition

14. What type of joins using in SQL?

1) EQUI JOIN: The equi join is normally used to join tables with primary key foreign key

relation ships.

2) NON-EQUI JOIN:

A join condition where any relation operator other than "=" equal to operator is used.

3) OUTER JOIN:

In EQUI JOIN rows that does not satisfy specified condition would not be displayed. Example:

consider EMO and DEPT table as Example

DEPTNO 40 is not displayed in the Equi example because there are no employees in it. If we

want to diplay its detail also then we have to use OUTER JOIN.Otherwise OUTER JOIN is

imilar to EQUI JOIN except for the difference it uses outer join (+) operator. (A plus within

parenthesis) towards the side not having required data. Outer join operator will substitute null

values when there are no values available.

SQL> SELECT E.DEPTNO,ENAME,DNAME FROM EMP E , DEPT D

2 WHERE E.DEPTNO (+) = D.DEPTNO;

4) SELF JOIN:

When we join a table to itself it is called self join.To join a table itself means that each row of

the table is combined with itself and with every other row of the table. The self join can be seen

as join of two copies of the same table.

SQL> SELECT E.ENAME,M.ENAME FROM EMP E,EMP

WHERE E.MGR=M.EMPNO;

15. What are the Group Functions?

Group Functions Operate on Sets of rows to give one result per group. The types of

group functions

AVG,COUNT,MAX,MIN,SUM,STDDEV,VARIANCE

All columns in SELECT List that are not in group functions must be in the GROUP

BY clause.

16. Can you Use Group functions in the Where Clause?

NO, We Can’t.

17. How can we restrict the Group Results?

Using HAVING Clause. We can’t use WHERE for these results.

18. What is difference Between SUBQUERY and CORRELATED SUBQUERY?

SUBQUERY :

A query within another quey. A select statement whose output is substituted in the

condition of another select statement .(A query is a statement written for returning specific

data). The subquery is executed only once. A subquery is enclosed in parenthesis.

EX: SQL> SELECT ENAME FROM EMP

WHERE DEPTNO = (SELECT DEPTNO FROM EMP

WHERE ENAME = 'SMITH');

CORRELATED QUERY:

In a correlated subquery the table used in outer query refers to the table used in the

inner query. The correlated subquery is executed repeatedly once

for each row of the main query table.

Query to display name of highest salary taker.

SQL> SELECT EMPNO, ENAME FROM EMP A

WHERE 1 > (SELECT COUNT (*) FROM EMP B

WHERE A.SAL < B.SAL)

19. What are the Multiple-Row comparisons Operators?

IN : EQUAL to any member in the list.

ANY : Compare value to each value returned by the sub query

ALL : Compare Value to Every value returned by the sub query.

20. You are updating the table, you ask some another user to logon to database to check your

changes before you issue the commit command ? Can he see the changes done by you?

Another user can’t see the the changes done by you until you have given commit.

21. What is MERGE Statement do?

Provides the ability to conditionally update or insert data into a database table.

Performs update if the row exists and an insert if it is a new row.

22. What is the Use of SAVEPOINT?

A SAVEPOINT is a marker within a transaction that allows for a partial rollback. As

changes are made in a transaction, we can create SAVEPOINT to mark different points within

the transaction. If we encounter an error, we can rollback to a SAVEPOINT or all the way back

to the beginning of the transaction.

Ex : Insert Statement

SAVEPOINT A

UPDATE Statement

SAVEPOINT B

DELETE Statement

SAVEPOINT C

Roll Back to SAVEPOINT B (means it roll backs to till UPDATE statement)

23. What is the Use of ALTER Statement?

To Add new column

To modify the datatype and size of the existing column

To Drop a column

24. What are the Difference between UNION and UNION ALL?

UNION Query displays the Unique rows ( No duplicate rows)

UNION ALL Query displays the Duplicate rows also.

25. If you a Table A, You want to create table B having the same fields in TABLE A ? That

means you have to copy only structure not the data?

CREATE TABLE TABLEB AS (SELECT * FROM TABLE A where 1=2);

26. What is Synonym?

A synonym is an alternative permanent name for objects such as

tables,views,sequences,stored Procedures

27. What is view?

A View is a virtual table ,it does not physically exist rather , it is created by a query

joining one or more tables.

28. Can we Update the Data in View?

A view is created by joining one or more tables. When you update record(s) in a view, it

updates the records in the underlying tables that make up the view.

So, yes, you can update the data in a view providing you have the proper privileges to the

underlying tables.

29. What is Sequence?

Sequence is for generating auto number field. This can be useful when you need to

create a unique number to act as primary key.

Syntax:

CREATE SEQUENCE sequence_name

MINVALUE value

MAXVALUE value

START WITH value

INCREMENT BY value

CACHE value;

30. How do we set the LASTVALUE value in an Oracle Sequence?

You can change the LASTVALUE for an Oracle sequence, by executing an ALTER

SEQUENCE command.

31. What is pseudo columns ? Name them?

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can

select from pseudocolumns, but you cannot insert, update, or delete their values. This section

describes these pseudocolumns:

* CURRVAL

* NEXTVAL

* LEVEL

* ROWID

* ROWNUM

Rowid

Rowid is pseudo column that uniquely identifies a row with in the table but not with in the database.

It is possible for two rows of different tables stored in the same cluster have the same row id.

Connect By Prior

A condition that identifies the relationship between parent rows and child rows of the

heirarchy.

Level

For each row returned by heirachical query the level pseudo columns returns 1 for root row , 2

for child row of the root and so on.

32. How many columns can table have?

The number of columns in a table can range from 1 to 254.

33. How many rows and Columns in DUAL table?

One Row and One Column Only

34. What is the Use of CASE and DECODE?

CASE and DECODE statements Both perform procedural logic inside a SQL statement

without having to resort to PL/SQL.

Syntax:

DECODE (F1,E2,E3,E4) { If F1=E2 Then E3 else E4}

Syntax:

CASE

WHEN E1 THEN E2 {If E1 True E2 Else E3 CASE evaluated the Expression only

once with

ELSE E3 that result values be compared}

END

It is Best to use CASE Statement when comparing ranges or more complex logic

35. What is Inline View?

SQL Statement in the FROM clause of SQL statement called Inline View. Oracle treats the

data set that is returned from the inline view as if it were a table.

This is not a schema Object.

A common use for inline views in oracle sql is to simplify the complex queries by removing

join operations and condensinfg several separate queries into single query.

SELECT * from (SELECT * from table);

36. What is the Purpose of Index?

SQL indexes are used because they can provide the following benefits / functions:

· Rapid access of information

· Efficient access of information

· Enforcement of uniqueness constraints

CREATE INDEX (Index_name) on TABLE_NAME(Field1,field2);

37. What are Constraints? And what are the constraint Types?

Constraints Enforced rules at the table level.

Constraints prevent the deletion of a table if there are decencies.

Following are the Constraint Types

NOTNULL:

Ensures the null values are not permitted for the column. Defined at column Level.

CREATE TABLE Tablename( Empname VARCHAR2(10) NOTNULL)

UNIQUE:

Not allow already existing value

Is defined either table level or column level

CREATE TABLE T1( X1 NUMBER,

X2 VARCHAR2(10),

CONSTRAINT x2_UK UNIQUE(X2))

PRIMARY KEY:

Doesn’t allow Nulls and already existing values.

CREATE TABLE T1( X1 NUMBER,

X2 VARCHAR2(10),

CONSTRAINT x2_UK PRIMARY KEY(X2))

FOREIGN KEY:

Foreign Key defines the column in the child table at table constraint level.

CREATE TABLE T1( X1 NUMBER,

X2 VARCHAR2(10),

CONSTRAINT x2_UK FOREIGN KEY(X2)

REFERENCES TABLE2(field2))

CHECK

Defines a condition that each row must satisfy

CONSTRAINT emp_salary CHECK(SAL>0)

38. What is the Purpose ON DELETE CASCADE?

Deletes the dependent rows in the child table, when a row in parent table is deleted.

39. How can you view the constraints?

User_constraints table

40. Can we perform the DML Operations on View?

You can perform DML operations on Simple view (selecting view from one

table and also all not null columns selected).

If view is complex View and View contains the following then we can’t modify

the view

--GROUP Functions

-- A Group By clause

-- DISTINCT Keyword

-- Not null columns in Base table that are not selected by View

41. How to deny the DML operation on simple View?

CREATE a view with ‘WITH READ ONLy’ option

42. When to create an Index?

You should create an index if :

· A Column contains a wide range of values

· A Column contains a large number of null values

· One or more columns frequently used together in join condition

· Table is large and most queries expected to retrieve less than 2 to 4% of the rows.

43. When Not create an Index?

You should not create an index if:

TABLE is Small

The columns are not often used as a condition in the query

The table is updated frequently

44. What is Functional Based Index?

A functional Based index is an index based on expression

CREATE INDEX ind_name table name (UPPER (field_name))

45. What is the result of the following Command?

SELECT 1 FROM DUAL

UNION

SELECT ‘A’ FROM DUAL

Error : Expression Must have the same datatype as corresponding expression.

46. What is the difference between alias and Synonym?

Alias is temporary and used in one query. Synonym is Permanent aliasing

47. Can Dual table be deleted or dropped or altered or inserted?

YES,we can do

48. What Is the result for the following queries?

1) SELECT * from emp where rownum < 3

2) SELECT * from emp where rownum =3

1) 2 rows selected

2) No rows selected

49. Can we create index on View?

We can’t create index on view.

50. How to eliminate the duplicate rows?

Using ROWID we can delete duplicate rows.

DELETE FROM EMPMASTER A WHERE A.ROWID> (SELECT MIN(B.ROWID) FROM

EMPMASTER B WHERE A.EMPNO=B.EMPNO);

51. How can we find the nth salary in the table?

SELECT DISTINCT (A.SAL) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT

(B.SAL)) FROM EMP B WHERE A.SAL<=B.SAL)

52. What is the ROWID?

ROWID is pseudo column. It uniquely identifies a row with in a table, but not with in the

database.

53. What is the ROWNUM?

ROWNUM is pseudo column to limit the number of returned rows. This behaves like a

table column but is not actually stored in tables.

54. What is the difference between Group by and Order by?

Group by is used to group set of values based on one or more values.

Order by is to sort values either in ascending or descending order.

55. How Many CODD rules Oracle satisfies?

Out of 12 ,11 rules Oracle satisfying .

56. What is the difference Between Primary Key and Unique Key?

Primary Key Unique Key

You can have only one primary key in a

table

You can have more than one Unique key

in a table

Primary disallows the duplicates and

Nulls also

Unique key disallows only duplicates, it

accepts the Nulls.

57. What is the difference between %TYPE and %ROWTYPE?

%TYPE : It provides the datatype of a variable or database column

%ROWTYPE : It provides the record type that represents a row in a table.

58. What are the main Benefits using %TYPE and %ROWTYPE?

· You need not know the exact datatype of the field in the table.

· If you change the database definition (field size increased), datatype declared using

%TYPE or %ROWTYPE at the time of runtime it will be take changed database field.

59. What is Collection? What are the collection types using in PL/SQL?

A Collection is an ordered group of elements , all of the same type.

PL/SQL offers these Collections:

a. Nested Tables

b. VArrays

c. Index-By-tables (Associate arrays)

60. Give the Brief description on Nested Tables?

· PL/SQL Nested tables like one dimensional array. Nested tables size

unbounded ,So the size of the Nested table can increase dynamically.

· We can delete elements from Nested table using DELETE ( it might leave

gaps) and NEXT for iterate over the scripts.

Syntax:

TYPE type_name AS TABLE OF element_type

Ex:

CREATE TYPE Stulist AS TABLE OF VARCHAr2(10)

CREATE TYPE student AS OBJECT (

id NUMBER,

name VARCHAR2(10),

stuinfo stulist)

61. What is VARRAY?

Varrays allow you to associate a single identifier with an entire collection.

Varray has the maximum size which you must specify in its type definition.

Syntax:

TYPE type_name AS VARRAY(size limit) OF element_type

Ex:

CREATE TYPE Stulist AS VARRAY(50) OF VARCHAr2(10)

CREATE TYPE student AS OBJECT (

id NUMBER,

name VARCHAR2(10),

stuinfo stulist)

62. What is the Index-By-Tables (Associated Arrays)?

Associative arrays are sets of key-value pairs, where each key is unique and is used

to locate a corresponding value in the array. The key can be an integer or a string.

Syntax :

TYPE type_name IS TABLE OF element type

INDEX BY BINARY_INTEGER

Ex: TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE

INDEX BY BINARY_INTEGER;

emp_tab EmpTabTyp;

63.What is the difference between Nested tables and Varrays?

Varrays is good choice when the number of elements known in advance and all the

elements are usually accessed in sequence.

Nested tables are dynamic. You can delete arbitrary elements rather than just

removing an item from end.

64. What is the difference between Nested tables and Index-By-tables(Associated Arrays)?

Nested tables can be stored in database column, but associated arrays can not. Nested

tables are appropriate for important data relationships that must be stored persistently.

Associated Arrays are appropriate for relatively small lookup tables where the

collection can be constructed in memory each time a procedure is called or Package is

initialized. These are good for collecting information whose value is unknown before hand,

because there is no fixed limit on their size.

65. Can we Delete Individual Element from VARRAYS?

VARRAYS are dense. We can’t delete elements from VARRAYS.

66. What is bulk binding?

A DML statement can transfer all the elements of a collection in a single operation, a

process known as bulk binding.

For example If the collection has 20 elements, bulk binding lets you perform the

equivalent of 20 SELECT, INSERT, UPDATE, or DELETE statements using a single operation.

Ø This technique improves performance by minimizing the number of context switches

between the PL/SQL and SQL engines

67. Where can you use the FORALL statement?

To do bulk binds with INSERT,UPDATE and DELETE statements you enclose the SQL

statement with in a PL/SQL using FORALL

Syntax:

FORALL index IN lower_bound..upper_bound

sql_statement;

68. Where you use the BULK COLLECT?

To do bulk binds with SELECT statements, you include the BULK COLLECT clause in

the SELECT statement instead of using INTO.

69. What is a cursor? Why Use a cursor?

When a query executed in oracle, the result set is produced and stored in the memory

.Oracle allows accessing this result set in the memory through Cursor.

Need of the cursor is Many times, when a query returns more than one row. We might

Want to go through each row and process the data in different way for them.

70. What are the CURSOR types?

PL/SQL uses 2 types of Cursors

Implicit Cursor:

PL/SQL declares a cursor implicitly for all SQL data manipulation statements,

including queries that return only one row.

Explicit Cursor:

Queries that return more than one row, you must declare an explicit cursor

71. How many ways CURSOR can open?

CURSOR can open in two ways

1) OPEN ---FETCH –CLOSE

Ex:

CURSOR c1 IS

SELECT ename,empno from EMP;

OPEN C1;

LOOP

FETCH ename,empno INTO var1,var2;

EXIT WHEN C1%NOTFOUND;

END LOOP;

CLOSE C1;

2) FOR LOOP

Ex:

CURSOR c1 IS

SELECT ename,empno from EMP;

FOR c1 in C2 LOOP

Var1 =c2.ename;

Var2 = c2.empno;

END LOOP;

72. What is the difference between OPEN-FETCH-CLOSE and FOR LOOP in CURSORS?

FOR LOOP in CURSOR:

A cursor FOR loop implicitly declares its loop index as a %ROWTYPE record, opens

a cursor, repeatedly fetches rows of values from the result set into fields in the record, and

closes the cursor when all rows have been processed.

OPEN-FETCH-CLOSE:

Explicitly we have to open the query and closing the query.

73. Can we pass the parameters in CURSOR?

Yes, we can, those are called parametric cursors

74. What are the explicit cursors attributes?

%FOUND,%ISOPEN,%NOTFOUND,%ROWCOUNT

75. What are the implicit cursors attributes?

%FOUND,%NOTFOUND and %ROWCOUNT

76. What is the purpose of %ROWCOUNT?

How many rows effected we can know from the %ROWCOUNT. %ROWCOUNT

yields the number of rows affected by an INSERT, UPDATE, DELETE or SQL statement.

If INSERT,UPDATE,DELETE statements effected no rows or SELECT statement

returns no rows then the %ROWCOUNT value is ZERO.

If SELECT statement returns more than one row and PL/SQL raises the

TOO_MANY_ROWS then %ROWCOUNT VALUE is 1. it doesn’t retrieve the actual value.

77. What is the REF CURSOR?

REF CURSOR is a cursor variable. Use of the cursor variables to pass the query result

sets between PL/SQL stored subprograms, packages to client.

Syntax:

TYPE ref_cursor_name IS REF CURSOR [RETURN return_type]

Ex:

DECLARE

TYPE DeptCur IS REF CURSOR RETURN dept%ROWTYPE;

Deptcut deptmain;

78. What are the PL/SQL Exceptions?

Pre defined Exceptions:

These Exceptions are raised implicitly by the run-time system.

Ex: NO_DATA_FOUND, ZERO_DIVIDE

User defined Exceptions:

User-defined exceptions must be raised explicitly by RAISE statements.

DECLARE

SAL_check EXCEPTION;

BEGIN

IF SAL <100 THEN

RAISE SAL_CHECK

END IF;

EXCEPTION

WHEN SAL_CHECK THEN

-----

END;

79. How to define our Own Error Messages?

We can define using RAISE_APPLICATION_ERROR.

Syntax:

RAISE_APPLICATION_ERROR ( Error number,error message);

Ø When this called, it ends the subprogram and returns the user defined error message to

application.

Ø Error Number range is -20000 to-20999

80. What are SQLCODE and SQLERROR?

SQLCODE: returns the number of the Oracle Error.

SQLERRM: Associated error message for the SQLCODE

81. What are subprograms?

Subprograms are named PL/SQL blocks that can take parameters and be invoked.

PL/SQL has two types of subprograms called Procedures and functions.

82. Where can we use procedure and Function?

Procedure used for to perform an action. Function to compute Value.

83. Advantages of subprograms?

Extensibility, modularity, reusability and maintainability.

84. Give the procedure Syntax?

[CREATE [OR REPLACE]]

PROCEDURE procedure_name[(parameter[, parameter]...)]

[AUTHID {DEFINER | CURRENT_USER}] {IS | AS}

[PRAGMA AUTONOMOUS_TRANSACTION;]

[local declarations]

BEGIN

executable statements

[EXCEPTION

exception handlers]

END [name];

85. What is AUTHID? What is the Purpose of AUTHID in procedure?

The AUTHID clause determines whether a stored procedure executes with the

Privileges of its owner (the default) or current user.

86. What is AUTONOMOUS_TRANSACTION?

Ø The pragma AUTONOMOUS_TRANSACTION instructs the PL/SQL compiler to mark

procedure as autonomous (independent).

Ø Autonomous transactions let you suspend the main transaction, do SQL operations,

commit or roll back those operations, then resume the main transaction.

87. Can we call a function from SELECT Statement?

YES

88. What is forward declaration?

Forward Declaration advises PL/SQL that the body of procedure can be found later in

the block, but the specification should be declared first.

89. What are actual and Formal Parameters?

Variables or expressions referenced in the parameter list of a subprogram call are actual

parameters.

Ex: update_sal(empnum,sal);

Variables declared in subprogram specification and referenced in the subprogram body

are formal parameters.

Ex: PRCODURE update_sal(empnum number,sal number)

90. What are the types of notations?

Positional, Named and Mixed notations

Ex: PROCEDURE acc_update (acct_no NUMBER, amount NUMBER);

Positional Notation :

Acc_update(acct,amt);

Named Notation : acc_update(amount =>amt, acct_no => acct);

Acc_update(acct_no=>acct,amount=>amt);

Mixed Notation:

Positional Notation must precede named notation. And the reverse notation is not

allowed.

Acc_update(acct,amount => amt);

91. What are the Parameter Modes and what is the default parameter mode?

Parameter Modes are IN,OUT,INOUT

IN parameter is the default parameter mode.

92. In Parameter modes which are pass by Reference and Pass by Value?

Pass By Reference : IN

Pointer to the actual parameter is passed to the corresponding formal

parameters. Both Parameters use the same memory location.

Pass By Value :OUT,IN OUT

The Values of OUT Actual parameters copied into the corresponding formal

parameters.

93. What is NOCOPY? When we use it?

NOCOPY is Compiler Hint. When the Parameters hold large data structures such as

collections and records , all this time copying slows down the execution. To prevent this we ca

specify NOCPY. This allows the PL/SQL Compiler to pass OUT and INOUT parameters by

reference.

94. What is Table Functions?

Table functions are functions that produce a collection of rows (either a nested table

or a Varray) that can be queried like a physical database table or assigned to PL/SQL

collection variable. We can use the table function like the name of the database table.

95. What is PL/SQL Package? And what are the parts of the package?

Package groups logically related PL/SQL types, procedures, functions.

Package having the two parts: Package specification and Package Body.

· Package Body is optional in some cases

96. What are the advantages of the Packages?

Modularity, Easier application design, Information hiding, better performance.

· When you call the Packaged Procedure for the first time ,the whole package is

loaded in to memory. So later calls to related subprograms in the package require no disk I/O.

· Packages stop cascading dependencies and thereby avoid unnecessary

recompiling.

97. What are Private and Public variables in Package?

· Variables declared in Package Body and restricted to use with in the package those

variables are called Private Variables.

· Variables declared in Package specification and this variable is Visible outside the

package ,those variables are called public variables.

98. Which Package can we use to display the output from the PL/SQL blocks or

subprogram?

DBMS_OUTPUT

99. Which statement we have to set to display output on SQL*PLUS?

SET SERVEROUTPUT ON

100. How to read and write the text files?

Using UTL_FILE utility

101. What is Dynamic SQL?

Some Statements can, probably will change from execution to execution means change

at runtime, and they are called dynamic SQL statements.

102. What is the need for Dynamic SQL?

· You want to execute the DDL statements from the PL/SQL block

· You want to Execute the Control statements from the PL/SQL block

103. How can we execute DDL statements from PL/SQL Block?

Using EXECUTE_IMMEDIATE statement

Ex: EXECUTE_IMMEDIATE(‘TRUNCATE TABLE T1’);

104. What is Trigger? And Define the Parts of the trigger?

Trigger is stored procedure, that run implicitly when an INSERT, UPDATE Or DELETE

statement issued against the table or against the view or database system action Occurs.

Parts of the Trigger :

· Triggering Event or statement

· Trigger restriction

· Triggering action

105. What are the types of triggers?

· ROW Level Triggers

· Statement Level Triggers

· BEFORE and AFTER Triggers

· INSTEAD of Triggers

· System Event and User event Triggers

106. What is the difference Between Row Level Trigger and Statement Level Trigger?

· Row level trigger executes once for each row after (or before) the event. This is defined

By using FOR EACH ROW

· Statement Level trigger executes once after (or before) the event, independent how

many rows are affected by the event.

107. How can we access the attribute values in triggers?

Using :OLD and :NEW only with Row level trigger

108. Where can we use instead of triggers?

INSTEAD-OF triggers Provide a transparent way of modifying views, that can’t be modified

directly through SQL DML statements.

109. What are the System Event Triggers?

System events that can fire triggers are related to instances startup and shutdown and error

messages

§ STARTUP

§ SHUTDOWN

§ SERVERERROR

110. What are the User event Triggers?

User events that can fire triggers are related to user logon and logoff, DDL statements

LOG ON

LOG OFF

BEFORE CREATE and AFTER CREATE

BEFORE ALTER and AFTER ALTER

BEFORE DROP and AFTER DROP

111. Can we give the Commit and Roll back in side the Trigger?

112. What is mutating table Error?

A mutating table is a table that is currently being modified by an update, delete, or insert

statement. When a trigger tries to reference a table that is in state of flux (being changed), it is

considered "mutating" and raises an error since Oracle should not return data that has not yet reached its final state.

113. What is Optimization? What are the types of optimization?

Optimization is the process of choosing the most efficient way to execute SQL statement.

Optimization is in two approaches:

RULE Based

COST Based

114. What is Execution Plan?

Combination of steps Oracle Uses to execute a statement is called an execution plan. Execution

plan includes an access method for each table that the statement accesses and ordering of the tables

We can check the execution plan by EXPLAIN PLAN Command.

115. What are the LOCKS providing by ORACLE?

Oracle provides two different levels of locking

§ ROW LEVEL

Each row in the table locked individually

§ TABLE LEVEL

Entire Table Locked

116. What are the Modes of the LOCKING?

o Exclusive LOCK Mode

o Share Lock Mode

117. What is exclusive Lock mode?

This Lock prevents the associated resource from being shared. This Lock Mode obtained to

modify data.

Ex : LOCK TABLE table_name IN EXCLUSIVE MODE

118. What is Share Lock Mode?

This Lock allows the associated resource to be shared, depending on the operations involved.

Ex: LOCK TABLE table_name IN SHARE MODE

Plsql Tables

Plsql table is one dimentional, unbounded, collection of elements indexed by binary interger.

plsql table can have only one column,it is similar to one dimentional array.

it is onbounded there is no predefined limit of the number od rows in plsql table.

The plsql table is in this way very different from aray.

plsql table can have only single column, all rows in plsql table contain of the values of same data type.

Indexed by binary integer

Plsql tables correctly supports a single index mode by binary integer.

This number acts as primary key od plsql table.

You can’t commit information to plsql table or rollback changes from the table.

You can’t select from plsql table.

You can’t isssue DML statemts.

Materialiazed Views

It is special kind of views which physically exists inside the database,it can contain joins or aggragate

functions to improve performances .

The existances of materiliazed views is transparent to sql applications,so DBA can create or drop

materiliazed views of any time without affecting the sql applications.

Link between OM and AR

select

ooh.order_number, cust.interface_header_attribute1,

ooh.header_id, cust.interface_header_attribute6

from

oe_order_headers_all ooh,

ra_customer_trx_all cust

where

to_char(ooh.header_id) = cust.interface_header_attribute6;

select *from ra_customer_trx_lines_all where SALES_ORDER = '308223'

Link between GL and AR

http://oracleappstechguide.blogspot.com/2015/05/link-between-gl-and-ar-through-xla.html

Types of invoice:

There are 8 Types of Invoices.

1) Standard Invoice.

2) Debit Memo.

3) Credit Memo.

4) Expense Report Invoice.

5) PO Default Invoice.

6) Quick Match Invoice.

7) Fixed Invoice.

8) Pre - Payment Invoice.

What is the order of execution if there is a statement level and row level trigger on a same table?

The Order will be:
Before Statement level
Before Row level
After Row level
After Statement level

Make a table read only:

select *from salgrade;

insert into salgrade values (5, 10000, 12999);

alter table salgrade read only;

alter table salgrade read write;

delete from salgrade where losal = 10000;

Disadvantages of packages:

No version control

No synonym support

Unable to load/compile large packages into memory

Disadvantages of triggers:

Triggers are invisible to client application.

Its easy to forget triggers, if there is no documentation, it will be difficult for new developers to figure out

Instead of trigger:

https://www.mkyong.com/oracle/oracle-plsql-instead-of-trigger-example/

CREATE OR REPLACE TRIGGER trg_cust_proj_view_insert

INSTEAD OF INSERT ON customer_projects_view

DECLARE

duplicate_info EXCEPTION;

PRAGMA EXCEPTION_INIT (duplicate_info, -00001);

BEGIN

INSERT INTO customer_details

(customer_id,customer_name,country)

VALUES (:new.customer_id, :new.customer_name, :new.country);

INSERT INTO projects_details (project_id, project_name, project_start_Date, customer_id)

VALUES (

:new.project_id,

:new.project_name,

:new.project_start_Date,

:new.customer_id);

EXCEPTION

WHEN duplicate_info THEN

RAISE_APPLICATION_ERROR (

num=> -20107,

msg=> 'Duplicate customer or project id');

END trg_cust_proj_view_insert;

Bulk binding:

Bulk binds (BULK COLLECT, FORALL)

Collections in PL/SQL:

Collections are classified based on structure, subscript and storage.

Varray:

Size of the array is fixed

This collection type is always dense

Individual elements cannot be deleted, the collection must be deleted as a whole

Varrays don’t support index

Type <type_name> is varrary (<size>) of <data_type>

Nested tables:

Size of array is not fixed

This collection type is dense and sparse

Individual elements can be deleted

Nested tables support index

Type <type_name> is table of <data_type>

Index-by-table (Associative arrays):

Size of array is not fixed

Type <type_name> is table of <data_type> index by binary_integer

Type <type_name> is table of <data_type> index by varchar2(10)

Oracle blog

Labels

Saturday, August 3, 2019

PL/SQL

No comments:

Post a Comment

Link between Receivables, Payables and Projects