Open Questions

When do we use " instead of '?
A PL/SQL program is terminated and executed by a slash (/) on a line by itself.


The NOT NULL constraint prevents nulls from being assigned to the PL/ SQL table of that type. Do not
initialize the INDEX BY table.

INDEX-BY tables are initially sparse. That enables you, for example, to store reference data in an INDEX-BY
table using a numeric primary key as the index.

Is the default must in Decode? what if no match to the selector and there is no default?
Ans: Not must. It then returns NULL.

Global variable of a Package has scope of session? what abt visibility? for ex. lets we changed values of a global variable, till when the new values be.. ?

Note: In the slide, the private function is shown above the public procedure. When you are coding the package body, the definition of the private function has to be above the definition of the public procedure. ?

Only subprograms and cursors declarations without body in a package specification have an underlying implementation in the package body. So if a specification declares only types, constants, variables, exceptions, and call specifications, the package body is unnecessary. However, the body can still be used to initialize items declared in the package specification.?

You cannot use SQLCODE or SQLERRM directly in a SQL statement. Instead, you must assign their values to local variables, then use the variables in the SQL statement, as shown in the following example: ? [why so? how to make such functions? ; Verify if it is the case}

For example, declare a variable called NUMBER_EMPLOYED as a private variable, if each call to a procedure that uses the variable needs to be maintained. When declared as a global variable in the package specification, the value of that global variable gets initialized in a session the first time a construct from the package is invoked.?

Predefined exceptions in which package.?

{
If you redeclare a built-in subprogram in another PL/SQL program, your local declaration overrides the standard or built-in subprogram. To be able to access the built-in subprogram, you need to qualify it with its package name. For example, if you redeclare the TO_CHAR function, to access the built-in function you
refer it as: STANDARD.TO_CHAR.
If you redeclare a built-in subprogram as a stand-alone subprogram, to be able to access your subprogram you need to qualify it with your schema name, for example, SCOTT.TO_CHAR.
} ?

A situation where forward declaration is inevitable?

No END Keyword in the end of One-Time-Only Procedure.

{
You cannot overload:
• Two subprograms if their formal parameters differ only in datatype and the different datatypes are in the
same family (NUMBER and DECIMAL belong to the same family)
• Two subprograms if their formal parameters differ only in subtype and the different subtypes are based
on types in the same family (VARCHAR and STRING are PL/SQL subtypes of VARCHAR2)
• Two functions that differ only in return type, even if the types are in different families
You get a run-time error when you overload subprograms with the above features.
Note: The above restrictions apply if the names of the parameters are also the same. If you use different names for the parameters, then you can invoke the subprograms by using named notation for the parameters.
} ?

pg 52 of pdf pl/sql 2?

• Functions called from UPDATE/DELETE statements on a table T cannot contain DML on the same table T. • Functions called from a DML statement on a table T cannot query the same table.?

The transactions in the submitted job are not committed until either COMMIT is issued, or DBMS_JOB.RUN is executed to run the job. The COMMIT in the slide commits the transaction.

The CHECK option for views is not enforced when insertions or updates to the view are performed by using INSTEAD OF triggers. The INSTEAD OF trigger body must enforce the check.?

The whole Begin-End reaches oracle server at one-go?

Restriction When clause can be used in Row Triggers. Can it be in Statement Triggers as well?

Read consistency ensures that on the same data:
– Readers do not wait for writers.
– Writers do not wait for readers. ?

PL/SQL PDF-2, 16-33 [175] ?

USER?