41. Explain autonomous transaction.
An autonomous transaction is an independent transaction of the main or parent transaction. It is not nested if it is started by another transaction.
There are several situations to use autonomous transactions like event logging and auditing.
42. Differentiate between SGA and PGA.
SGA stands for System Global Area whereas PGA stands for Program or Process Global Area. PGA is only allocated 10% RAM size, but SGA is given 40% RAM size.
43. What is the location of Pre_defined_functions.
They are stored in the standard package called “Functions, Procedures and Packages”
44. Explain polymorphism in PL/SQL.
Polymorphism is a feature of OOP. It is the ability to create a variable, an object or function with multiple forms. PL/SQL supports Polymorphism in the form of program unit overloading inside a member function or package..Unambiguous logic must be avoided whilst overloading is being done.
45. What are the uses of MERGE?
MERGE is used to combine multiple DML statements into one.
Syntax : merge into tablename
using(query)
on(join condition)
when not matched then
[insert/update/delete] command
when matched then
[insert/update/delete] command
46. Can 2 queries be executed simultaneously in a Distributed Database System?
Yes, they can be executed simultaneously. One query is always independent of the second query in a distributed database system based on the 2 phase commit.
47. Explain Raise_application_error.
It is a procedure of the package DBMS_STANDARD that allow issuing a user_defined error messages from the database trigger or stored sub-program.
48. What is out parameter used for eventhough return statement can also be used in pl/sql?
Out parameters allows more than one value in the calling program. Out parameter is not recommended in functions. Procedures can be used instead of functions if multiple values are required. Thus, these procedures are used to execute Out parameters.
49. How would you convert date into Julian date format?
We can use the J format string :
SQL > select to_char(to_date(‘29-Mar-2013’,’dd-mon-yyyy’),’J’) as julian from dual;
JULIAN
50. Explain SPOOL?
Spool command can print the output of sql statements in a file.
spool/tmp/sql_outtxt
select smp_name, smp_id from smp where dept=’accounts’;
spool off;
