Which two are true about the PLSQL_CODE_TYPE parameter? (Choose two.)
AChanging the parameter setting automatically changes the setting for existing PL/SQL library units.
BThe default value is NATIVE.
CIf set to NATIVE, programs are stored in platform dependent machine code.
DIt can use the REUSE SETTINGS clause to recompile a program unit without changing to the current session settings.
EIf set to NATIVE, programs are stored in a PL/SQL bytecode format.
Which three statements are true about passing parameters to subprograms? (Choose three.)
APL/SQL assigns values to actual parameters in subprograms with unhandled exceptions.
BIN parameters passed to subprograms act like constants, to which values cannot be assigned by the subprogram.
CIN OUT parameters pass initial values to subprograms and return values updated by subprograms to the caller.
DThe actual parameter must be a variable when calling a subprogram with an OUT parameter.
EIN parameters passed to subprograms act like variables, to which values can be assigned by the subprogram.
FOUT parameters returning values to calling subprograms act like constants in the called subprogram.
Which three are true about PL/SQL subprograms? (Choose three.)
AResults of a subprogram can be cached in the SGA such that sessions connected to the same instance can reuse these results when available.
BUsers granted execute privilege on a procedure compiled with definer's rights require grants to access objects belonging to the definer that are referenced in the procedure.
CSubprograms are cached by default and shared among users, thereby reducing memory requirements.
DReuse of parsed PL/SQL code from the shared SQL area reduces parsing overhead.
EA subprogram's session state is retained even if any of the session’s instantiated subprograms are invalidated and revalidated.
FHost variables can be referenced inside any PL/SQL subprogram.
Examine these facts:
Table EMP exists in schema USERA with columns SALARY and EMP_ID.
EMP_ID is the primary key with values ranging from 1 to 100.
USERA now executes these statements successfully:
USERA then grants execute privilege on procedure MYPROC to USERB.
USERB exists in the database identified by pdb1 but does not have select privilege on USERA.EMP.
USERB now executes these statements:
conn userB/userB@pdb1
execute userA.myproc;
Which is true?
AIt results in an error because Authid Current_User is missing from MYPROC.
BIt results in an error because Authid Definer is missing from MYPROC.
CIt results in an error because USERB doesn't have select privilege on USERA.EMP.
DIt executes successfully.
Which three are true regarding code based access control (CBAC)? (Choose three.)
AIn a multitenant environment, the DELEGATE option of CBAC cannot be used.
BCBAC roles can be granted to a program unit only if they are directly granted to its owner.
CCBAC roles can be granted to a program unit only if they are the predefined roles automatically defined by the standard scripts as part of database creation.
DYou can use CBAC to attach database roles to a PL/SQL function or procedure only.
EIn CBAC, the ADMIN and DELEGATE options cannot both be granted to the same user.
FYou can use CBAC to attach database roles to a PL/SQL function, procedure, or package.
Which two blocks of code display a numerical zero? (Choose two.)
A
B
C
D
Examine this statement which is submitted for compilation:
Which three are true? (Choose three.)
AThis is a PACKAGE specification. A PACKAGE BODY is needed to use this.
BThis will not compile successfully because the loan_amount variable is declared NOT NULL but lacks an initialization assignment.
CThis program unit will compile successfully.
DInitialization of min_bal can be done while using this packaged constant in another program.
EInitialization of loan_amount can be done while using this packaged variable in another program.
FThis will not compile successfully because the min_bal constant must be initialized.
Examine these statements which execute successfully:
Which anonymous block executes successfully?
A
B
C
D
Which two are valid MODIFIER values for the PLSQL_WARNINGS parameter? (Choose two.)
ADISABLE
BENABLE
CERROR
DALL
ESEVERE
Which code will successfully create a BODILESS PACKAGE to standardize CONSTANTS and EXCEPTIONS declarations?
A
B
C
D
Examine this statement which executes successfully:
SQL> SET SERVEROUTPUT ON;
Now, examine this code which is executed:
What is true about the result?
AIt returns an error in line 2.
BIt returns an error in line 4.
CIt returns an error in line 9.
DIt executes and displays output.
Which three PL/SQL-only data types can be used in queries and native dynamic SQL issued from PL/SQL in the server? (Choose three.)
Aa record declared in an anonymous block
Ba record declared in a procedure
Can associative array indexed by PLS_INTEGER
Da record declared in a package specification
Ea predefined PL/SQL-only data type like BOOLEAN
Fan associative array indexed by VARCHAR2
Which three SQL statements, embedded in PL/SQL, typically benefit from using variables declared with %ROWTYPE? (Choose three.)
ACREATE
BDROP
CUPDATE
DSELECT
EDELETE
FALTER
Which three statements can process a dynamic multi-row query? (Choose three.)
ADECLARE
BOPEN
CINTO
DOPEN-FOR
EFETCH
FWHEN
Examine these statements from a block of code:
Which two are true? (Choose two.)
AThe lock acquired when executing the OPEN c1 command will be released only after a COMMIT or ROLLBACK statement is issued.
BOnly the PRICE column can be updated in the PRODUCTS table.
CThe FOR UPDATE OF clause can be used only if the WHERE CURRENT OF clause is used in the executable part of the block.
DThe WHERE CURRENT OF clause can be used only if the FOR UPDATE clause is used in the cursor definition.
EAn OPEN c1 command will acquire a lock only on the PRICE column in the PRODUCTS table.
Which two PL/SQL elements can be deprecated using the DEPRECATE pragma? (Choose two.)
APACKAGES
BVARIABLES
CANONYMOUS BLOCK
DTRIGGER BODY
EDATABASE LINKS
Which three are true about functions and procedures? (Choose three.)
AThe ACCESSIBLE BY clause can be used only for procedures.
BIn a function, every execution path must lead to a RETURN statement.
CBoth can have only constants as actual parameters for IN mode parameters.
DBoth can be invoked from within SQL statements.
EIn a procedure the RETURN statement cannot specify an expression.
FIn a function every RETURN statement must specify an expression.
Which two are true about Conditional Compilation in PL/SQL using $IF, $ELSE, $END, and $ERROR? (Choose two.)
APL/SQL code can be compiled and executed based on different versions of the operating system.
BPL/SQL code can be compiled and executed based on different versions of Oracle.
CIt is newer syntax that works the same way as 'IF , ELSEIF , ELSE, and END IF'.
DConditional compilation is disabled by default.
EThe PL/SQL compiler can conditionally include selected parts of a program.
Which three are true about the NOCOPY hint, the PARALLEL ENABLE hint, and the DETERMINISTIC clause? (Choose three.)
AThe PARALLEL_ENABLE clause can be used only in the CREATE FUNCTION statement.
BThe NOCOPY hint asks the compiler to pass the actual parameters by reference.
CA deterministic function's results always depend on the state of session variables.
DThe NOCOPY hint asks the compiler to pass the actual parameters by value.
EA function is deterministic if it always returns the same result for a specific combination of input values.
FThe PARALLEL_ENABLE clause can be specified for a nested function.
Which two are true about INDEX-BY tables? (Choose two.)
AThe index can be integer or string.
BThe index can be integer only.
CINDEX-BY table types can be created in PL/SQL blocks only.
DINDEX-BY table types can be created both with the CREATE TYPE statement and in PL/SQL blocks.
EINDEX-BY table types can be created with the CREATE TYPE statement.
Which statement is true about user-defined records?
AThey can be returned from a function.
BField types must match column types.
CThe number of fields must match the number of columns in a table.
DField names must match selected column names.
Examine the structure of the ora1.depts table:
Now, examine these statements issued by user ora1 which execute successfully:
Create or replace view dep_vu as select * from depts;
Alter table depts add dep_email varchar2(20);
Finally, examine this block of code executed by user ora1:
Which is true?
ADEP_VU must be manually recompiled to successfully run this code.
BIt will run successfully producing a result of 4.
CIt will result in an error because table depts has been altered.
DIt will run successfully producing a result of 5.
Which two statements are true about the RETURNING clause when used with DML? (Choose two.)
AWhen using the RETURNING INTO clause, the data returned can be only single column or expression.
BThe RETURNING INTO clause and bulk binds may not be used together.
CThe RETURNING INTO clause can be used with DML that affects multiple rows, if the BULK COLLECT clause is used.
DThe RETURNING INTO clause returns column values for rows affected by DML statements.
EThe RETURNING clause can be used for remote or parallel deletes.
SERVEROUTPUT is enabled.
Which is the correct method to use a PACKAGED CONSTANT in SELECT statements?
A
B
C
D
Which three are true about anonymous blocks and subprograms? (Choose three.)
ANamed subprograms cannot be called from other packages.
BPROCEDURE subprograms can accept parameters.
CA FUNCTION subprogram must return one or more values.
DAnonymous blocks cannot use packaged variables.
ENamed subprograms are stored in the database server.
FAnonymous blocks must always start with the Declare keyword.
Preview
