All Posts by Kdinesh.
Brainbench Oracle PL/SQL 11G Certification
ACCELERATE YOUR CAREER
Measure, Certify and Promote Your Skills
Brainbench certifications are used by all leading employers worldwide to support their daily hiring and promotion decisions. By partnering with Brainbench, members across the world have advanced their careers, finding better career opportunities, more interesting work, and are earning more. You can do the same.
Brainbench makes it easy to get ahead
Brainbench tests may be taken any time, anywhere online. Easily build and maintain your electronic transcript. Share your certification results with employers and see where you stand.
World's most extensive testing resource at your fingertips
Brainbench provides the most extensive skills measurement, certification, and improvement resource available on the Web today. Brainbench assessments enable members to earn certifications in more than 600 of today's leading skills, and new subjects are being added constantly.
Value: Prove your skills to increase your income
Since Brainbench tests are online, we can provide high-quality certifications at a rate lower than most other entities. Along with having a great selection of FREE tests, each paid test is only $49.95. Members find an even better value with a Subscription Plan giving them unlimited access to all 600+ tests for only $199.
Quality: Brainbench certifications are recognized worldwide
Brainbench Certifications are developed, validated and reviewed by professionals in each area being certified. As we like to say, they are "Test developed for professionals, by professionals." Utilizing Computer Adaptive Testing (CAT) technology, each test-taker is administered a different set of test questions enhancing the test's security.
Please find Brainbench Oracle PL/SQL 11G Certification- Test Tips. All most all questions are of very good standard. Scoring 4 out of 5 is Excellent score. its very difficult but not impossible. Score around 3.5 is very good score and you can celebrate your success.
Total questions : 30
Total Minutes : 90
My Suggestions:
You have only 3 mins for each question.
If you have not answered the question within 2.5 minutes, you will get an alert on timer. So click any of answer, once you get the alerts.
Some questions will be lengthier and time will be short to read the table details/code details completely.
Question will be given at the end. So read the question first and then check the required the details from code based on the question, so that you can save time.
Below are the Mock Questions, i have face in actual Exam.
Oracle PL/SQL
Questions (Asked in BB certification.)
1. What exception will be raised
by oracle when else condition is not defined in CASE statement.
--
If an undefined leg is chosen, the CASE_NOT_FOUND exception (ORA-06592: CASE not found while
executing CASE statement)) is raised
2. Output from below code
LAST_DAY(ADD_MONTHS(date1,3)),’MONDAY’)
3. If var1 = TRUE then
DBMS_OUTPUT.PUT_LINE();
ELSIF var1 = FALSE then
DBMS_OUTPUT.PUT_LINE();
What maintenance is
missing from user’s perspective where var1 is Boolean variable.
4. OUTPUT of below code at last
iteration of FOR LOOP.
Declare
Type
t1 is table of varchar2(100);
obj
t1;
begin
obj
:= t1(‘JAN’,’FEB’,…,’DEC’);
FOR
LOOP I in REVERSE 6 .. 12
DBMS_OUTPUT(obj(I));
END
LOOP;
5. Another question came asking
exact syntax FOR LOOP REVERSE option for printing numbers from 10 .. 6
6. What’s MAXIMUM buffer size of
SET SERVER OUTPUT ()
7. What’s MAXIMUM line size in
Oracle
8. Question came related to
Analyze table ..compute statistics.
Select
* from table where col1=’XX’. After you
ran this analyze statement, query is taking more time than earlier. What’s the
reason?
9. For DATA warehousing, which partition
will be suitable..options are composite partitioning, list partitioning, list
partitioning.
10. If a > 10 then
NULL;
Else
DBMS_OUTPUT.put_line(a);
What’s
the purpose of NULL statement.
11. What are the options used along
with COMMIT clause on global temporary tables.
12. There are four different select
statements are given and in which statement implicit conversion occurs during
query execution.
Select
* from table where col1=’1’
Select
* from table where col1=1
13. Sample Create trigger statement
was given and we need to specify what type of trigger like before/after update
on table/view.
14. There was a question on
procedure/function parameter type. Sample code will be given and you have to
identify the parameter type.
15. If var1 = TRUE then
DBMS_OUTPUT.PUT_LINE();
ELSIF var1 = FALSE then
DBMS_OUTPUT.PUT_LINE();
What maintenance is missing
from user’s perspective where var1 is Boolean variable.
16. OUTPUT of below code at last iteration of FOR LOOP.
Declare
Type t1 is table of
varchar2(100);
obj t1;
begin
obj := t1(‘JAN’,’FEB’,…,’DEC’);
FOR LOOP I in REVERSE 6 ..
12
DBMS_OUTPUT(obj(I));
END LOOP;
17. Another question came asking exact syntax FOR LOOP REVERSE option
for printing numbers from 10 .. 6
18. For DATA warehousing, which
partition will be suitable..options are composite partitioning, list
partitioning, list partitioning..
Oracle
PL/SQL Questions(DAY 2 -Ask To my Friend)
1)
What
type data can be stored in RAW data type?
2)
Which
are the mandatory blocks in Anonymous block?
3)
Difference
between predefined and user defined exceptions.
4)
What
is the max. size for SERVEROUTPUT ?
5)
How
to determine the number of rows deleted?
6)
Question
about DUAL.
7)
Question
about Synonym.
8)
Qusetion
on Sequence.
9)
Example of subprogram.
10) Where
is the sql execution results stored for future use ?
11) What
is the clause used to return same result for a function when the same
parameters are passed ?
12) What
exception will be raised by oracle when else condition is not defined in CASE
statement?
13) var1:= ‘VARIABLE’ What is the exception that occurs?
14) Question
on REVERSE LOOP and CONTINUE.
Oracle PL/SQL
Description:
Our Oracle PL/SQL test measures knowledge of database development using Oracle PL/SQL, based on knowledge through Oracle 11g. Designed for experienced programmers, this test covers the following topics: Architecture, Built-in Packages, DML and Transaction Management, Debugging, Exception Handling, Language Elements, Subprograms, Tuning, and Utilities.
Test Outline
|
|
|
|
|
|
|
|
|
Reference Study Material:
This is the best Reference Study Materials Collected from Oracle Documentation for Brainbech Oracle PL/SQL 11G certification. Hope it will help you lots.
Important Notes:
====================
Link To practise on-line Exam:
Interestingly, PL/SQL does have a boolean datatype. A
boolean PL/SQL variable can take the values TRUE, FALSE or NULL. It is a pity that this
handy PL/SQL variable cannot be made use of conveniently in SQL. You cannot
insert the PL/SQL boolean value into a table column, or fetch from a table
column into a PL/SQL boolean variable.
- Oracle does not have a BOOLEAN datatype.
- A NUMBER(1) column with a check constraint to allow
values 0 or 1 is a good alternative for the BOOLEAN datatype. CHAR(1) or
VARCHAR(1) are fine too, but they are not language-independent.
- PL/SQL has a BOOLEAN datatype, but you cannot insert
this value directly into a table or fetch from a table column directly
into a PL/SQL boolean variable.
=====================
Global Temporary Tables
(GTT)
Applications
often use some form of temporary data store for processes that are to
complicated to complete in a single pass. Often, these temporary stores are
defined as database tables or PL/SQL tables. From Oracle 8i onward, the
maintenance and management of temporary tables can be delegated to the server
by using Global Temporary Tables.
Creation of Global Temporary Tables
The
data in a global temporary table is private, such that data inserted by a
session can only be accessed by that session. The session-specific rows in a
global temporary table can be preserved for the whole session, or just for the
current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be
deleted at the end of the transaction.
CREATE
GLOBAL TEMPORARY TABLE my_temp_table (
column1
NUMBER,
column2
NUMBER
)
ON COMMIT DELETE ROWS;
In
contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved
until the end of the session.
CREATE
GLOBAL TEMPORARY TABLE my_temp_table (
column1
NUMBER,
column2
NUMBER
)
ON COMMIT PRESERVE ROWS;
Miscellaneous Features
- If the TRUNCATE statement is
issued against a temporary table, only the session specific data is
truncated. There is no affect on the data of other sessions.
- Data in temporary tables is
stored in temp segments in the temp tablespace.
- Data in temporary tables is
automatically deleted at the end of the database session, even if it ends
abnormally.
- Indexes can be created on
temporary tables. The content of the index and the scope of the index is
the same as the database session.
- Views can be created against temporary
tables and combinations of temporary and permanent tables.
- Temporary tables can have
triggers associated with them.
- Export and Import utilities can
be used to transfer the table definitions, but no data rows are processed.
- Statistics on temporary tables
are common to all sessions. Oracle 12c allows session specific statistics.
- There are a number of
restrictions related to temporary tables but these are version specific.
·
Preserving
GLOBAL temporary table data for transaction or session
·
You can preserve
session-scoped rows in a GLOBAL temporary table for the entire session or for
the current transaction only.
·
To preserve a temporary
table for the transaction, use the ON
COMMIT DELETE ROWS clause:
·
=> CREATE GLOBAL TEMP
TABLE temp_table1 (
·
x
NUMERIC,
·
y
NUMERIC )
·
ON
COMMIT DELETE ROWS;
·
To preserve temporary
table data until the end of the session, use the ON COMMIT PRESERVE ROWS
clause:
·
=> CREATE GLOBAL TEMP
TABLE temp_table2 (
·
x
NUMERIC,
·
y
NUMERIC )
·
ON
COMMIT PRESERVE ROWS;
The
RAW and LONG RAW datatypes are used for
data that is not to be interpreted (not converted when moving
data between different systems) by Oracle Database. These datatypes are
intended for binary data or byte strings. For example, LONG RAW can be used to store
graphics, sound, documents, or arrays of binary data. The interpretation
depends on the use.
RAW is a variable-length
datatype like the VARCHAR2 character datatype, except Oracle Net
Services (which connects user sessions to the instance) and the Import and
Export utilities do not perform character conversion when transmitting RAW or LONG RAW data. In contrast, Oracle Net Services and
Import/Export automatically convert CHAR, VARCHAR2, and LONG data between the
database character set and the user session character set, if the two character
sets are different.
When
Oracle Database automatically converts RAW or LONG RAW data to and from CHAR data, the binary data is
represented in hexadecimal form with one hexadecimal character representing
every four bits of RAW data. For example, one byte of RAW data with bits 11001011
is displayed and entered as 'CB'.
By: Er. Kdinesh || Bangalore
very informative blog and useful article thank you for sharing with us , keep posting Oracle Certification
ReplyDelete