1.What is the difference between SQL and PL/SQL?
Both SQL and PL/SQL are languages used to access data within Oracle databases.
SQL is a limited language that allows you to directly interact with the database. You can write queries (SELECT), manipulate objects (DDL) and data (DML) with SQL. However, SQL doesn't include all the things that normal programming languages have, such as loops and IF...THEN...ELSE statements.
PL/SQL is a normal programming language that includes all the features of most other programming languages. But, it has one thing that other programming languages don't have: the ability to easily integrate with SQL.
Some of the differences:
* SQL is executed one statement at a time. PL/SQL is executed as a block of code.
* SQL tells the database what to do (declarative), not how to do it. In contrast, PL/SQL tell the database how to do things (procedural).
* SQL is used to code queries, DML and DDL statements. PL/SQL is used to code program blocks, triggers, functions, procedures and packages.
* You can embed SQL in a PL/SQL program, but you cannot embed PL/SQL within a SQL statement.
2. What is the maximum number of handlers processed before the PL/SQL block is exited when an exception occurs?
Only one
3.What are the PL/SQL compiler limits for block, record, subquery and label nesting?
The following limits apply:
Level of Block Nesting: 255
Level of Record Nesting: 32
Level of Subquery Nesting: 254
Level of Label Nesting: 98
4. How can I protect my PL/SQL source code?
Oracle provides a binary wrapper utility that can be used to scramble PL/SQL source code. This utility was introduced in Oracle7.2 (PL/SQL V2.2) and is located in the ORACLE_HOME/bin directory.
The utility use human-readable PL/SQL source code as input, and writes out portable binary object code (somewhat larger than the original). The binary code can be distributed without fear of exposing your proprietary algorithms and methods. Oracle will still understand and know how to execute the code. Just be careful, there is no "decode" command available. So, don't lose your source!
The syntax is:
wrap iname=myscript.pls oname=xxxx.plb
Please note: there is no way to unwrap a *.plb binary file. You are supposed to backup and keep your *.pls source files after wrapping them.
How can one search PL/SQL code for a string/ key value?
The following query is handy if you want to know where certain tables, columns and expressions are referenced in your PL/SQL source code.
SELECT type, name, line
FROM user_source
WHERE UPPER(text) LIKE UPPER('%&KEYWORD%');
If you run the above query from SQL*Plus, enter the string you are searching for when prompted for KEYWORD. If not, replace &KEYWORD with the string you are searching for.
5. Should one use PL/SQL or Java to code procedures and triggers?
Both PL/SQL and Java can be used to create Oracle stored procedures and triggers. This often leads to questions like "Which of the two is the best?" and "Will Oracle ever desupport PL/SQL in favour of Java?".
Many Oracle applications are based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL. For example, Oracle 9i supports native compilation of Pl/SQL code to binaries. Not to mention the numerous PL/SQL enhancements made in Oracle 10g and 11g.
PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the similarities and difference between these two language environments:
PL/SQL:
* Can be used to create Oracle packages, procedures and triggers
* Data centric and tightly integrated into the database
* Proprietary to Oracle and difficult to port to other database systems
* Data manipulation is slightly faster in PL/SQL than in Java
* PL/SQL is a traditional procedural programming language
Java:
* Can be used to create Oracle packages, procedures and triggers
* Open standard, not proprietary to Oracle
* Incurs some data conversion overhead between the Database and Java type
* Java is an Object Orientated language, and modules are structured into classes
* Java can be used to produce complete applications
PS: Starting with Oracle 10g, .NET procedures can also be stored within the database (Windows only). Nevertheless, unlike PL/SQL and JAVA, .NET code is not usable on non-Windows systems.
PS: In earlier releases of Oracle it was better to put as much code as possible in procedures rather than triggers. At that stage procedures executed faster than triggers as triggers had to be re-compiled every time before executed (unless cached). In more recent releases both triggers and procedures are compiled when created (stored p-code) and one can add as much code as one likes in either procedures or triggers.
6. Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ?
From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements.
Eg: CREATE OR REPLACE PROCEDURE DYNSQL
AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
7. Can one read/write files from PL/SQL?
Included in Oracle 7.3 is a UTL_FILE package that can read and write files. The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=... parameter). Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput','W');
UTL_FILE.PUTF(fileHandler, 'Value of func1 is %sn', func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;
8. Is there a limit on the size of a PL/SQL block?
Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K. You can run the following select statement to query the size of an existing package or procedure.
SQL> select * from dba_object_size where name = 'procedure_name'
9. Explain UNION, MINUS, UNION ALL and INTERSECT?
INTERSECT - returns all distinct rows selected by both queries.
MINUS - returns all distinct rows selected by the first query but not by the second.
UNION - returns all distinct rows selected by either query
UNION ALL - returns all rows selected by either query, including all duplicates.
MINUS - returns all distinct rows selected by the first query but not by the second.
UNION - returns all distinct rows selected by either query
UNION ALL - returns all rows selected by either query, including all duplicates.
10. What is ROWID?
ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, rownumber are the components of ROWID.
11. Difference between SUBSTR and INSTR?
INSTR (String1, String2 (n, (m)),
INSTR returns the position of the m-th occurrence of the string 2 in string1. The search begins from nth position of string1.
SUBSTR (String1 n, m)
SUBSTR returns a character string of size m in string1, starting from n-th position of string1.
INSTR (String1, String2 (n, (m)),
INSTR returns the position of the m-th occurrence of the string 2 in string1. The search begins from nth position of string1.
SUBSTR (String1 n, m)
SUBSTR returns a character string of size m in string1, starting from n-th position of string1.
12. Explain CONNECT BY PRIOR?
Retrieves rows in hierarchical order eg.
select empno, ename from emp where.
13. What is correlated sub-query?
Correlated sub-query is a sub-query, which has reference to the main query.
14. What is the sub-query?
Sub-query is a query whose return values are used in filtering conditions of the main query.
subquery; aquery inside a query is called a subquery the result of the outer query depends upon inner query
ex; select * from emp where deptno =(select deptno from dept where loc='NEW YORK') this example gives the details of the employ's who loc is new york where loc is dept table
15. What is difference between TRUNCATE & DELETE?
TRUNCATE commits after deleting entire table i.e., cannot be rolled back. Database triggers do not fire on TRUNCATE
DELETE allows the filtered deletion. Deleted records can be rolled back or committed. Database triggers fire on DELETE.
TRUNCATE:
truncate = delete + commit;
after truncating a table, we loss table space. so we cant rollback date.
if any constraints are available on table first we need to disable that constraint before truncating table.
conditional restrictions are not allowed in truncate command.
when u truncating a table at that time triggers are not fired.
it is a special ddl command.
DELETE;
when you use delete command on any table. table space is not lost. so we can rollback data.
triggers are fires when you go for delete.
delete command allows conditional restrictions on table (means we can delete selected data from table). It is a dml command.
16. What's an SQL injection?
SQL Injection is when form data contains an SQL escape sequence and injects a new SQL query to be run.
17. What is a join? Explain the different types of joins?
Join is a query, which retrieves related columns or rows from multiple tables.
Self Join - Joining the table with itself.
Equi Join - Joining two tables by equating two common columns.
Non-Equi Join - Joining two tables by equating two common columns.
Outer Join - Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.
Joins are used to join one or more tables if the data belonging to the same field and in different tables we use joins the types join in sql are equijoin or innerjoin; this join get the data from two tables which matches the values by using equality operator.
ex; select e.* from emp e,dept d where e.deptno=d.deptno and dname='ACCOUNTING'
This example selects the employee details who are in accounting where the dname column is in dep table
non equijoin; this join is used to bring the certain records which have range between by using between operator
ex;select e.*,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal
outerjoin; This join brings the values forcefully which do not match also for
ex; select e.ename.d.dname from emp e ,dept d where e.deptno(+)=d.deptno
This is an example of outer join this may called as also left outerjoin.
ex; select e.* from emp e,dept d where e.deptno=d.deptno and dname='ACCOUNTING'
This example selects the employee details who are in accounting where the dname column is in dep table
non equijoin; this join is used to bring the certain records which have range between by using between operator
ex;select e.*,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal
outerjoin; This join brings the values forcefully which do not match also for
ex; select e.ename.d.dname from emp e ,dept d where e.deptno(+)=d.deptno
This is an example of outer join this may called as also left outerjoin.
18. What is the parameter substitution symbol used with INSERT INTO command?
& (amperhand) is the substitution variable
19. What are the wildcards used for pattern matching.?
_ for single character substitution and % for multi-character substitution.
Great collection of question and answers thank you for sharing. Follow the link if you want OBIEE Online Training
ReplyDeletetechnically your blog is very useful oracle Online Training
ReplyDelete