What is the difference between PL SQL and SQL?
| Comparison | SQL | PL/SQL |
|
Execution |
Single command at a time |
Block of code |
|
Application |
Source of data to be displayed |
Application created by data acquired by SQL |
|
Structures include |
DDL and DML based queries and commands |
Includes procedures, functions, etc |
|
Recommended while |
Performing CRUD operations on data |
Creating applications to display data obtained using SQL |
| Compatibility with each other |
SQL can be embedded into PL/SQL |
PL/SQL cant be embedded in SQL |
What is an alias in SQL statements?
Alias is a user-defined alternative name given to the column or table. By default column, alias headings appear in upper case. Enclose the alias in double quotation marks (“ “) to make it case-sensitive. “AS” Keyword before the alias name makes the SELECT clause easier to read.
For example Select emp_name AS name from employee; (Here AS is a keyword and “name” is an alias).
What is a Dual Table?
The dual table is owned by the user SYS and can be accessed by all users. It contains one columnDummy and one row with the value X. The Dual Table is useful when you want to return a value only once. The value can be a constant, pseudocolumn, or expression that is not derived from a table with user data.
What is a MERGE statement?
The MERGE statement inserts or updates rows in one table, using data from another table. It is useful in data warehousing applications.
Write a PL/SQL Program that raises a user-defined exception on Thursday?
declare
a exception
begin
If to_char(sysdate, ‘DY)=’THU’
then
raise a;
end if;
exception
when a then
dbms_output.put_line(‘my exception raised on thursday’);
end
;
What are Triggering Events (or) Trigger Predicate Clauses?
If we want to perform multiple operations in different tables then we must use triggering events within the trigger body. These are inserting, updating, deleting clauses. These clauses are used in the statement, row-level triggers. These triggers are also called trigger predicate clauses.
What is Invalid_number, Value_Error?
In oracle when we try to convert “string type to number type” or” data string into data type” then the oracle server returns two types of errors.
- Invalid_number
- Value_error (or) numeric_error
Invalid_number:
When PL/SQL block has a SQL statement and also those SQL statements try to convert string type to number type or data string into data type then oracle server returns an error: ora-1722-Invalid Number
For handling this error oracle provides number exception Invalid_number exception name.
Example:
begin
Insert
intoemp(empno, ename, sal)
values(1,’gokul’, ‘abc’)
exception when invalid_number then dbms_output.put_line(‘insert proper data only’);
end;/
value_error:
Whenever PL/SQL block having procedural statements and also those statements find to convert string type to number type then oracle servers return an error: ora-6502: numeric or value error: character to a number conversion error
For handling, this error oracle provided exception value_error exception name
Example:
begin
declare z number(10);
begin
z:= ‘&x’ + ‘&y’;
dbms_output.put_line(z);
exception when value_error then dbms_output.put_line(‘enter numeric data value for x & y only’);
end;/
Output:
Enter value for x:3
Enter value for y:2
z:=5
Enter value for x:a
Enter value for y:b
Error:enter numeric data value for x & y only.
What is Flashback Query?
- Flashback query is handled by the Database Administrator the only flashback queries along to allow the content of the table to be retrieved with reference to the specific point of time by using as of clause that is flashback queries retrieves accidental data after committing the transaction also.
- Flashback queries generally use undo file that is flashback queries retrieve old data before committing the transaction oracle to provide two methods for flashback queries
Method1: using the timestamp
Method2: using scn number
Explain different methods to trace the PL/SQL code?
Tracing code is a necessary technique to test the performance of the code during runtime. We have different methods in PL/SQL to trace the code, which are,
- DBMS_ TRACE
- DBMS_ APPLICATION_INFO
- Tkproof utilities and trcsess
- DBMS_SESSION and DBMS_MONITOR
What does it mean by PL/SQL Cursors?
In PL/SQL to retrieve and process more, it requires a special resource, and that resource is known as Cursor. A cursor is defined as a pointer to the context area. The context area is an area of memory that contains information and SQL statements for processing the statements.
