- What is PL/SQL ?
- Differentiate between % ROWTYPE and TYPE RECORD.
TYPE RECORD, on the other hand, is used when a query returns column of different tables or views.
Eg. TYPE r_emp is RECORD (sno smp.smpno%type,sname smp sname %type)
e_rec smp %ROWTYPE
Cursor c1 is select smpno,dept from smp;
e_rec c1 %ROWTYPE
- Explain uses of cursor.
- Show code of a cursor for loop.
Eg. FOR smp_rec IN C1 LOOP
totalsal=totalsal+smp_recsal;
ENDLOOP;
- Explain the uses of database trigger.
1)Audit data modifications.
2)Log events transparently.
3)Enforce complex business rules.
4)Maintain replica tables
5)Derive column values
6)Implement Complex security authorizations
- What are the two types of exceptions.
- Show some predefined exceptions.
ZERO_DIVIDE
NO_DATA_FOUND
TOO_MANY_ROWS
CURSOR_ALREADY_OPEN
INVALID_NUMBER
INVALID_CURSOR
PROGRAM_ERROR
TIMEOUT _ON_RESOURCE
STORAGE_ERROR
LOGON_DENIED
VALUE_ERROR
etc.
- Explain Raise_application_error.
9.Show how functions and procedures are called in a PL SQL block.
Function is called as a part of an expression.
total:=calculate_sal(‘b644’)
Procedure is called as a statement in PL/SQL.
calculate_bonus(‘b644’);
- Explain two virtual tables available at the time of database trigger execution.
For INSERT related triggers, NOW.column_name values are available only.
For DELETE related triggers, THEN.column_name values are available only.
For UPDATE related triggers, both Table columns are available.
- What are the rules to be applied to NULLs whilst doing comparisons?
2) NULL cannot be equal or unequal to other values
3) If a value in an expression is NULL, then the expression itself evaluates to NULL except for concatenation operator (||)
- How is a process of PL SQL compiled?
Syntax checking checks the PL SQL codes for compilation errors. When all errors are corrected, a storage address is assigned to the variables that hold data. It is called Binding. P-code is a list of instructions for the PL SQL engine. P-code is stored in the database for named blocks and is used the next time it is executed.
- Differentiate between Syntax and runtime errors.
A runtime error is handled with the help of exception-handling section in an PL/SQL block. For eg, SELECT INTO statement, which does not return any rows.
- Explain Commit, Rollback and Savepoint.
- Other users can see the data changes made by the transaction.
- The locks acquired by the transaction are released.
- The work done by the transaction becomes permanent.
- The work done in a transition is undone as if it was never issued.
- All locks acquired by transaction are released.
- Define Implicit and Explicit Cursors.
If a query returns multiple rows of data, the program defines an explicit cursor. This allows the application to process each row sequentially as the cursor returns it.
- Explain mutating table error.
- When is a declare statement required?
- How many triggers can be applied to a table?
- What is the importance of SQLCODE and SQLERRM?
- If a cursor is open, how can we find in a PL SQL Block?
- Show the two PL/SQL cursor exceptions.
Invaid_cursor
- What operators deal with NULL?
var:=NVL(var2,’Hi’);
IS NULL and IS NOT NULL can be used to check specifically to see whether the value of a variable is NULL or not.
- Does SQL*Plus also have a PL/SQL Engine?
- What packages are available to PL SQL developers?
- Explain 3 basic parts of a trigger.
- A triggering statement or event.
- A restriction
- An action
- What are character functions?
- Explain TTITLE and BTITLE.
- Show the cursor attributes of PL/SQL.
%ROWCOUNT : The number of rows that are updated, deleted or fetched.
%FOUND : Checks if the cursor has fetched any row. It is true if rows are fetched
%NOT FOUND : Checks if the cursor has fetched any row. It is True if rows are not fetched.
- What is an Intersect?
- What are sequences?
- How would you reference column values BEFORE and AFTER you have inserted and deleted triggers?
Using the keyword “new.column name”, the triggers can reference column values by new collection. By using the keyword “old.column name”, they can reference column vaues by old collection.
- What are the uses of SYSDATE and USER keywords?
- How does ROWID help in running a query faster?
- What are database links used for?
- What does fetching a cursor do?
- What does closing a cursor do?
- Explain the uses of Control File.
- Explain Consistency
- Differ between Anonymous blocks and sub-programs.
- Differ between DECODE and CASE.
select decode(totalsal=12000,’high’,10000,’medium’) as decode_tesr from smp where smpno in (10,12,14,16);
This statement returns an error.
CASE is directly used in PL SQL, but DECODE is used in PL SQL through SQL only.
- Explain autonomous transaction.
There are several situations to use autonomous transactions like event logging and auditing.
- Differentiate between SGA and PGA.
- What is the location of Pre_defined_functions.
- Explain polymorphism in PL SQL.
- What are the uses of MERGE?
Syntax : merge into tablename
using(query)
on(join condition)
when not matched then
[insert/update/delete] command
when matched then
[insert/update/delete] command
- Can 2 queries be executed simultaneously in a Distributed Database System?
- Explain Raise_application_error.
- What is out parameter used for eventhough return statement can also be used in pl/sql?
- How would you convert date into Julian date format?
SQL > select to_char(to_date(‘29-Mar-2013’,’dd-mon-yyyy’),’J’) as julian from dual;
JULIAN
- Explain SPOOL
spool/tmp/sql_outtxt
select smp_name, smp_id from smp where dept=’accounts’;
spool off;
- Mention what PL/SQL package consists of?
- PL/SQL table and record TYPE statements
- Procedures and Functions
- Cursors
- Variables ( tables, scalars, records, etc.) and constants
- Exception names and pragmas for relating an error number with an exception
- Cursors
- Mention what are the benefits of PL/SQL packages?
It provides several benefits like
- Enforced Information Hiding: It offers the liberty to choose whether to keep data private or public
- Top-down design: You can design the interface to the code hidden in the package before you actually implemented the modules themselves
- Object persistence: Objects declared in a package specification behaves like a global data for all PL/SQL objects in the application. You can modify the package in one module and then reference those changes to another module
- Object oriented design: The package gives developers strong hold over how the modules and data structures inside the package can be used
- Guaranteeing transaction integrity: It provides a level of transaction integrity
- Performance improvement: The RDBMS automatically tracks the validity of all program objects stored in the database and enhance the performance of packages.
- Mention what are different methods to trace the PL/SQL code?
Tracing code is a crucial technique to measure the code performance during the runtime. Different methods for tracing includes
- DBMS_APPLICATION_INFO
- DBMS_TRACE
- DBMS_SESSION and DBMS_MONITOR
- trcsess and tkproof utilities
- Mention what does the hierarchical profiler does?
The hierarchical profiler could profile the calls made in PL/SQL, apart from filling the gap between the loopholes and the expectations of performance tracing. The efficiencies of the hierarchical profiler includes
- Distinct reporting for SQL and PL/SQL time consumption
- Reports count of distinct sub-programs calls made in the PL/SQL, and the time spent with each subprogram call
- Multiple interactive analytics reports in HTML format by using the command line utility
- More effective than conventional profiler and other tracing utilities
- Mention what does PLV msg allows you to do?
The PLV msg enables you to
- Assign individual text message to specified row in the PL/SQL table
- It retrieves the message text by number
- It substitutes automatically your own messages for standard Oracle error messages with restrict toggle
- Batch load message numbers and text from a database table directly PLV msg PL/SQL table
- Mention what is the PLV (PL/Vision) package offers?
- Null substitution value
- Set of assertion routines
- Miscellaneous utilities
- Set of constants used throughout PL vision
- Pre-defined datatypes
- Mention what is the use of PLVprs and PLVprsps?
- PLVprs: It is an extension for string parsing for PL/SQL, and it is the lowest level of string parsing functionality
- PLVprsps: It is the highest level package to parse PL/SQL source code into separate atomics. It relies on other parsing packages to get work done.
- Explain how you can copy a file to file content and file to PL/SQL table in advance PL/SQL?
With a single program call – “fcopy procedure”, you can copy the complete contents of one file into another file. While to copy the contents of a file directly into a PL/SQL table, you can use the program “file2pstab”.
- Explain how exception handling is done in advance PL/SQL?
For exception handling PL/SQl provides an effective plugin PLVexc. PLVexc supports four different exception handling actions.
- Continue processing
- Record and then continue
- Halt processing
- Record and then halt processing
- Mention what problem one might face while writing log information to a data-base table in PL/SQL?
While writing log information to a database table, the problem you face is that the information is only available only once the new rows are committed to the database. This might be a problem as such PLVlog is usually deployed to track errors and in many such instances the current transaction would fail or otherwise needed a rollback.
- Mention what is the function that is used to transfer a PL/SQL table log to a database table?
To transfer a PL/SQL table log a database log table function “PROCEDURE ps2db” is used.
- When you have to use a default “rollback to” savepoint of PLVlog?
The default “rollback to” savepoint of PLVlog is used when the users has turned on the rollback activity and has not provided an alternative savepoint in the call to put_line. The default savepoint is initialized to the c none constant.
- Why PLVtab is considered as the easiest way to access the PL/SQL table?
The PL/SQL table are the closest to arrays in PL/SQL, and in order to access this table you have to first declare a table type, and then you have to declare PL/SQL table itself. But by using PLVtab, you can avoid defining your own PL/SQL table type and make PL/SQL data-table access easy.
- Mention what does PLVtab enables you to do when you showthe contents of PL/SQL tables?
PLVtab enables you to do following things when you show the contents of PL/SQL tables
- Display or suppress a header for the table
- Display or suppress the row numbers for the table values
- Show a prefix before each row of the table
- Explain how can you save or place your msg in a table?
To save msg in a table, you can do it in two ways
- Load individual messages with calls to the add_text procedure
- Load sets of messages from a database table with the load_from_dbms procedure
- Mention what is the use of function “module procedure” in PL/SQL?
The “module procedure” enables to convert all the lines of code in a definite program unit with one procedure call. There are three arguments for modules
- module_in
- cor_in
- Last_module_in
- Mention what PLVcmt and PLVrb does in PL/SQL?
- PLVcmt: PLVcmt package wraps logic and complexity for dealing with commit processing
- PLVrb: It provides a programmatic interface to roll-back activity in PL/SQL
0 Comments:
Post a Comment