WIPRO

1.can we use select statement in trigger.
2. how can we retrieve last n records
3.select clause'clauses order'means where,order by, group by..
4. write the query to get maximum salary in each department.
5. table emp has 20 records
select count(8) from emp,
 select count(2) from emp,  
select count(1) from emp,
select count(*) from emp. 
what is the output.
6. can we use commit in trigger.
7.where clause and having clause difference.
8. not null vs zero.

Oracle Pipelined Table Functions

PIPELINED functions will operate like a table.
A PL/SQL function may be used in a data warehouse database to transform large amounts of data. This might also involve massaging the data in a series of transformations, each performed by different functions. Prior to Oracle Database 9, large transformations required either significant memory overhead, or storing the data in intermediate tables between each stage of the transformation. The loading process caused immense performance degradation in both cases.

Overview of Table Functions:
A table function is a user-defined PL/SQL function that returns a collection of rows (a nested table or varray). You can select from this collection as if it were a database table by invoking the table function inside the TABLE clause in a SELECT statement. For example:


To improve the performance of a table function, you can:

• Enable the function for parallel execution, with the PARALLEL_ENABLE option.
• Functions enabled for parallel execution can run concurrently.
• Stream the function results directly to the next process, with Oracle .

A pipelined table function returns a row to its invoker immediately after processing that row and continues to process rows. Response time improves because the entire collection need not be constructed and returned to the server before the query can return a single result row. (Also, the function needs less memory, because the object cache need not materialize the entire collection.)

Example 1:

We can write a PL/SQL function that will operate like a table

sql>create type array as table of number;

The PIPELINED keyword on line 4 allows this function to work as if it were a table:

create function

  gen_numbers(n in number default null)

  return array

  PIPELINED

  as

  begin

     for i in 1 .. nvl(n,999999999)

     loop

         pipe row(i);

     end loop;

     return;

  end;

/

Function created.

Suppose we needed three rows for something. We can now do that in one of two ways:

select * from TABLE(gen_numbers(3));

 COLUMN_VALUE

 ------------

           1

           2

           3

or

select * from TABLE(gen_numbers)

 where rownum <= 3;

 COLUMN_VALUE

 ------------

           1

           2

           3

Example 2:

CREATE TYPE t_tf_row AS OBJECT (

  id           NUMBER,

  description  VARCHAR2(50)

);

/

CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;

CREATE OR REPLACE FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_tf_tab PIPELINED AS

BEGIN

  FOR i IN 1 .. p_rows LOOP

    PIPE ROW(t_tf_row(i, 'Description for ' || i));   

  END LOOP;

  RETURN;

END;

/

SELECT *

FROM   TABLE(get_tab_ptf(10))

ORDER BY id DESC;

MERGE in SQL

Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view. 

• IT ENABLES US TO CONDITIONALLY UPDATE OR INSERT DATA INTO A TARGET TABLE.
• FROM 10g MERGE CAN NOW UPDATE,DELETE AND INSERT WITH SEPARATE  CONDITIONS FOR EACH.
• IT ALSO SUPPORTS UPDATE-ONLY OR INSERT-ONLY OPERATIONS.

Create Below source and target table

Source Table:

SQL>CREATE TABLE SRC  AS SELECT OBJECT_ID,OBJECT_NAME,OBJECT_TYPE  FROM ALL_OBJECTS  WHERE ROWNUM<=100;

Target Table:

SQL> CREATE TABLE TGT AS SELECT * FROM SRC WHERE ROWNUM<=20;

Simple MARGE Example:

SQL> MERGE INTO TGT  USING SRC  ON(SRC.OBJECT_ID=TGT.OBJECT_ID)  WHEN MATCHED THEN  UPDATE SET TGT.OBJECT_NAME=LOWER(SRC.OBJECT_NAME)  WHEN NOT MATCHED THEN INSERT(TGT.OBJECT_ID,TGT.OBJECT_NAME) VALUES(SRC.OBJECT_ID,SRC.OBJECT_NAME);

• FROM 10g MERGE can do UPDATE,DELETE and INSERT with separate conditions for each.

Update Using Merge:

SQL> MERGE INTO TGT  USING SRC  ON(SRC.OBJECT_ID=TGT.OBJECT_ID) WHEN  MATCHED THEN UPDATE SET TGT.OBJECT_NAME=LOWER(SRC.OBJECT_NAME);

Insert Using Merge:

SQL> MERGE INTO TGT  USING SRC  ON(SRC.OBJECT_ID=TGT.OBJECT_ID)  WHEN  NOT MATCHED THEN  INSERT(TGT.OBJECT_ID,TGT.OBJECT_NAME) VALUES(SRC.OBJECT_ID,SRC.OBJECT_NAME);

Conditional DML Using Merge:

SQL> MERGE INTO TGT  USING SRC  ON(SRC.OBJECT_ID=TGT.OBJECT_ID)  WHEN MATCHED THEN  UPDATE SET TGT.OBJECT_NAME=LOWER(SRC.OBJECT_NAME)  WHERE SRC.OBJECT_TYPE='SYNONYM'  WHEN NOT MATCHED THEN  INSERT(TGT.OBJECT_ID,TGT.OBJECT_NAME) VALUES(SRC.OBJECT_ID,SRC.OBJECT_NAME) WHERE SRC.OBJECT_TYPE='VIEW';

Deleting During Merge:

SQL> MERGE INTO TGT  USING SRC  ON(SRC.OBJECT_ID=TGT.OBJECT_ID) WHEN MATCHED THEN  UPDATE SET TGT.OBJECT_NAME=LOWER(SRC.OBJECT_NAME)  DELETE WHERE SRC.OBJECT_TYPE='SYNONYM'  WHEN NOT MATCHED THEN  INSERT(TGT.OBJECT_ID,TGT.OBJECT_NAME)VALUES(SRC.OBJECT_ID,SRC.OBJECT_NAME) WHERE SRC.OBJECT_TYPE='VIEW';

• The delete works against condition on the data, not the source target.
• Delete works only on rows that have been updated as a result of the merge. any rows in the target table that are not  touched by the merge are not deleted, even if they satisfy the delete criteria.

SAVE EXCEPTIONS

Handling FORALL Exceptions

Since version 9 it is possible to do bulk DML using FORALL and use the SAVE EXCEPTIONS clause. It makes sure that all invalid rows - the exceptions - are saved into the sql%bulk_exceptions array, while all valid rows are still processed. This array stores a record for each invalid row, containing an ERROR_INDEX which is the iteration number during the FORALL statement and an ERROR_CODE which corresponds with the Oracle error code.

The error text is not stored. The documentation says:

The individual error messages, or any substitution arguments, are not saved, but the error message text can looked up using ERROR_CODE with SQLERRM ...

Looks reasonable, but in our shop we validate lots of business rules with triggers. When a business rule is violated we do a RAISE_APPLICATION_ERROR(-20000,'APP-12345');

At client side (Webforms) the error message is looked up in the messages table and a friendly message is given. When coding a FORALL with SAVE EXCEPTIONS in such an environment, the error messages become useless as can be seen in the next example:

rwijk@ORA11G> create table mytable
  2  ( id number(4)
  3  , name varchar2(30)
  4  )
  5  /

Tabel is aangemaakt.

rwijk@ORA11G> create trigger mytable_bri
  2  before insert on mytable
  3  for each row
  4  begin
  5    if :new.id = 2
  6    then
  7      raise_application_error(-20000,'APP-12345');
  8    elsif :new.id = 9
  9    then
 10      raise_application_error(-20000,'APP-98765');
 11    end if;
 12  end;
 13  /

Trigger is aangemaakt.

rwijk@ORA11G> alter table mytable add constraint mytable_ck1 check (id <> 6)
  2  /

Tabel is gewijzigd.

rwijk@ORA11G> declare
  2    e_forall_error exception;
  3    pragma exception_init(e_forall_error,-24381)
  4    ;
  5    type t_numbers is table of mytable.id%type;
  6    l_numbers t_numbers := t_numbers(1,2,3,4,5,6,7,8,9,10)
  7    ;
  8  begin
  9    forall i in 1..l_numbers.count save exceptions
 10      insert into mytable
 11      ( id
 12      , name
 13      )
 14      values
 15      ( l_numbers(i)
 16      , 'Name' || to_char(l_numbers(i))
 17      )
 18    ;
 19  exception
 20  when e_forall_error then
 21    for i in 1..sql%bulk_exceptions.count
 22    loop
 23      dbms_output.put_line('SQLCODE: ' || sql%bulk_exceptions(i).error_code);
 24      dbms_output.put_line('SQLERRM: ' || sqlerrm(-sql%bulk_exceptions(i).error_code));
 25      dbms_output.new_line;
 26    end loop;
 27  end;
 28  /
SQLCODE: 20000
SQLERRM: ORA-20000:

SQLCODE: 2290
SQLERRM: ORA-02290: CHECK-beperking (.) is geschonden.

SQLCODE: 20000
SQLERRM: ORA-20000:


PL/SQL-procedure is geslaagd.

rwijk@ORA11G> select id, name from mytable
  2  /

        ID NAME
---------- ------------------------------
         1 Name1
         3 Name3
         4 Name4
         5 Name5
         7 Name7
         8 Name8
        10 Name10

7 rijen zijn geselecteerd.

Note how the SQLERRM message doesn't return anything useful and that the name of the check constraint has disappeared. This is really annoying and can't be circumvented easily in 9i. For better error messages we would have to go back to row by row processing. And that means: very slow.

However, version 10gR2 introduced a feature called DML error logging. I remember reading about it more than two years ago here on Tom Kyte's blog. In this entry and in the documentation you only see examples using SQL, not PL/SQL examples using FORALL. But luckily this works as well:

rwijk@ORA11G> rollback
  2  /

Rollback is voltooid.

rwijk@ORA11G> exec dbms_errlog.create_error_log('mytable')

PL/SQL-procedure is geslaagd.

rwijk@ORA11G> declare
  2    type t_numbers is table of mytable.id%type;
  3    l_numbers t_numbers := t_numbers(1,2,3,4,5,6,7,8,9,10)
  4    ;
  5  begin
  6    forall i in 1..l_numbers.count
  7      insert into mytable
  8      ( id
  9      , name
 10      )
 11      values
 12      ( l_numbers(i)
 13      , 'Name' || to_char(l_numbers(i))
 14      )
 15      log errors reject limit unlimited
 16    ;
 17    for r in
 18    ( select ora_err_number$
 19           , ora_err_mesg$
 20        from err$_mytable
 21    )
 22    loop
 23      dbms_output.put_line('SQLCODE: ' || to_char(r.ora_err_number$));
 24      dbms_output.put_line('SQLERRM: ' || r.ora_err_mesg$);
 25      dbms_output.new_line;
 26    end loop
 27    ;
 28  end;
 29  /
SQLCODE: 20000
SQLERRM: ORA-20000: APP-12345
ORA-06512: in "RWIJK.MYTABLE_BRI", regel 4
ORA-04088: Fout bij uitvoering van trigger 'RWIJK.MYTABLE_BRI'
.


SQLCODE: 2290
SQLERRM: ORA-02290: CHECK-beperking (RWIJK.MYTABLE_CK1) is geschonden.


SQLCODE: 20000
SQLERRM: ORA-20000: APP-98765
ORA-06512: in "RWIJK.MYTABLE_BRI", regel 7
ORA-04088: Fout bij uitvoering van trigger 'RWIJK.MYTABLE_BRI'
.



PL/SQL-procedure is geslaagd.

rwijk@ORA11G> select id, name from mytable
  2  /

        ID NAME
---------- ------------------------------
         1 Name1
         3 Name3
         4 Name4
         5 Name5
         7 Name7
         8 Name8
        10 Name10

7 rijen zijn geselecteerd.

And you do get to see the error messages (APP-12345 and APP-98765) and the name of the check constraint. Unfortunately, our shop still uses 9.2.0.7...

---

UPDATE

It is worth knowing that there are some restrictions when working with the DML error logging clause. From the 11g documentation (same as in 10gR2 documentation):

Restrictions on DML Error Logging

* The following conditions cause the statement to fail and roll back without invoking the error logging capability:

o Violated deferred constraints.

o Any direct-path INSERT or MERGE operation that raises a unique constraint or index violation.

o Any update operation UPDATE or MERGE that raises a unique constraint or index violation.

* You cannot track errors in the error logging table for LONG, LOB, or object type columns. However, the table that is the target of the DML operation can contain these types of columns.

o If you create or modify the corresponding error logging table so that it contains a column of an unsupported type, and if the name of that column corresponds to an unsupported column in the target DML table, then the DML statement fails at parse time.

o If the error logging table does not contain any unsupported column types, then all DML errors are logged until the reject limit of errors is reached. For rows on which errors occur, column values with corresponding columns in the error logging table are logged along with the control information.

Pipelined Table Functions

Pipelined Table Functions for Multiple Transformations

Chaining pipelined table functions is an efficient way to perform multiple transformations on data.

             You cannot run a pipelined table function over a database link. The reason is that the return type of a pipelined table function is a SQL user-defined type, which can be used only in a single database Although the return type of a pipelined table function might appear to be a PL/SQL type, the database actually converts that PL/SQL type to a corresponding SQL user-defined type.

Overview of Table Functions

A table function is a user-defined PL/SQL function that returns a collection of rows (a nested table or varray). You can select from this collection as if it were a database table by invoking the table function inside the TABLE clause in a SELECT statement. For example:

SELECT * FROM TABLE(table_function_name(parameter_list))

To improve the performance of a table function, you can:


Enable the function for parallel execution, with the PARALLEL_ENABLE option.

Functions enabled for parallel execution can run concurrently.


Stream the function results directly to the next process, with Oracle .



A pipelined table function returns a row to its invoker immediately after processing that row and continues to process rows. Response time improves because the entire collection need not be constructed and returned to the server before the query can return a single result row. (Also, the function needs less memory, because the object cache need not materialize the entire collection.)




Creating and Invoking Pipelined Table Function


CREATE OR REPLACE PACKAGE pkg1 AS
  TYPE numset_t IS TABLE OF NUMBER;
  FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;
END pkg1;
/

CREATE PACKAGE BODY pkg1 AS
  -- FUNCTION f1 returns a collection of elements (1,2,3,... x)
  FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS
  BEGIN
    FOR i IN 1..x LOOP
      PIPE ROW(i);
    END LOOP;
    RETURN;
  END f1;
END pkg1;
/

SELECT * FROM TABLE(pkg1.f1(5));
Result:-
COLUMN_VALUE
------------
           1
           2
           3
           4
           5




For Details Reference

'&' vs '&&' in ORACLE

“&” is used to create a temporary substitution variable. You will be prompted to enter the value every time the variable is referenced.

“&&” is used to create a permanent substitution variable. You need to enter the value only once.

1. Using ‘&’

SELECT ename FROM emp

    WHERE empno ='&emp'

Every time, you execute this select statement, you will have to pass the value of emp.

2. Using ‘&&’

Consider the following procedure.

SQL> set serveroutput on;

SQL> CREATE OR REPLACE PROCEDURE test_substitution AS

   v_ename VARCHAR2(10);

   v_job VARCHAR2(10);

   BEGIN

   SELECT ename INTO v_ename FROM emp

   WHERE empno ='&&emp';

   dbms_output.put_line(v_ename);

   SELECT job INTO v_job FROM emp

   WHERE empno='&emp';

   dbms_output.put_line(v_job);

   END;

 /

Procedure created

This procedure fetches the employee name(ename) and employee designation(job)  from the emp table for a particular empno.

It will prompt for the empno only once as ‘&emp’ is used. In the next select statement,although a substitution variable is used, you will not be prompted to enter a value for emp.

The next time you execute the procedure,it will not prompt for the value to be passed.

If you need to pass a different value each time you run the procedure, then you will have to undefine the variable emp.

SQL> undefine emp;

AND then compile the procedure again. This will prompt for a new value to be passed.

If you just undefine the variable and execute the procedure, it will not prompt for a new value.

It is important to undefine and then recompile the procedure.

COLLECTION

A collection is a list of elements of the same type. Each element in the list has a unique position number or label, called the "subscript".

To give a logical analogy, consider a list of colors = {red, blue, green, yellow}. This list has four elements, all names of colors. There is a unique position of each element in the list. If we number the positions sequentially starting from one, we could say:
color[1] = red, color[2] = blue, and so on.

Here, color is the name of the collection, and the numbers within [] are the subscripts.

PL/SQL has three collection types. In this article, we’ll look at a chart for comparing the three, their advantages and limitations, and which one to use for your needs.

To introduce the three collection types:

• Index by tables: Also called associative arrays.
• Nested tables
• Varrays: Also called variable arrays

The chart below lists the properties of the three collection types on a set of parameters such as size, ease of modification, persistence, etc.

	Index By Tables	Nested Tables	Varrays
Size	Unbounded i.e. the number of elements it can hold is not pre-defined	Unbounded i.e. the number of elements it can hold is not pre-defined	Bounded i.e. holds a declared number of elements, though this number can be changed at runtime
Subscript Characteristics	Can be arbitrary numbers or strings. Need not be sequential.	Sequential numbers, starting from one	Sequential numbers, starting from one
Database Storage	Index by tables can be used in PL/SQL programs only, cannot be stored in the database.	Can be stored in the database using equivalent SQL types, and manipulated through SQL.	Can be stored in the database using equivalent SQL types, and manipulated through SQL (but with less ease than nested tables)
Referencing and lookups	Works as key-value pairs. e.g. Salaries of employees can be stored with unique employee numbers used as subscripts sal(102) := 2000;	Similar to one-column database tables. Oracle stores the  nested table data in no particular order. But when you retrieve the nested table into a PL/SQL variable, the rows are given consecutive subscripts starting at 1.	Standard subscripting syntax e.g.  color(3) is the 3rd color in varray color
Flexibility to changes	Most flexible. Size can increase/ decrease dynamically.   Elements can be added to any position in the list and deleted from any position.	Almost like index-by tables, except that subscript values are not as flexible. Deletions are possible from non-contiguous positions.	Not very flexible. You must retrieve and update all the elements of the varray at the same time.
Mapping with other programming languages	Hash tables	Sets and bags	Arrays

Which Collection Type To Use?

You have all the details about index by tables, nested tables and varrays now. Given a situation, will one should you use for your list data?

Here are some guidelines.

Use index by tables when:

• Your program needs small lookups
• The collection can be made at runtime in the memory when the package/ procedure is initialized
• The data volume is unknown beforehand
• The subscript values are flexible (e.g. strings, negative numbers, non-sequential)
• You do not need to store the collection in the database

Use nested tables when:

• The data needs to be stored in the database
• The number of elements in the collection is not known in advance
• The elements of the collection may need to be retrieved out of sequence
• Updates and deletions affect only some elements, at arbitrary locations
• Your program does not expect to rely on the subscript remaining stable, as their order may change when nested tables are stored in the database.

Use varrays when:

• The data needs to be stored in the database
• The number of elements of the varray is known in advance
• The data from the varray is accessed in sequence
• Updates and deletions happen on the varray as a whole and not on arbitrarily located elements in the varray

Example Declaring Nested Tables, Varrays, and Associative Arrays

DECLARE
   TYPE nested_type IS TABLE OF VARCHAR2(30);
   TYPE varray_type IS VARRAY(5) OF INTEGER;
   TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
   TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
   TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
   v1 nested_type;
   v2 varray_type;
   v3 assoc_array_num_type;
   v4 assoc_array_str_type;
   v5 assoc_array_str_type2;
BEGIN
-- an arbitrary number of strings can be inserted v1
   v1 := nested_type('Shipping','Sales','Finance','Payroll'); 
   v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
   v3(99) := 10; -- Just start assigning to elements
   v3(7) := 100; -- Subscripts can be any integer values
   v4(42) := 'Smith'; -- Just start assigning to elements
   v4(54) := 'Jones'; -- Subscripts can be any integer values
   v5('Canada') := 'North America'; -- Just start assigning to elements
   v5('Greece') := 'Europe';        -- Subscripts can be string values
END;
/

PRAGMA

Definition:
In Oracle PL/SQL, PRAGMA refers to a compiler directive or "hint" it is used to provide an instruction to the compiler. The directive restricts member subprograms to query or modify database tables and packaged variables. Pragma directives are processed at compile time where they pass necessary information to the compiler; they are not processed at runtime.

The 5 types of Pragma directives available in Oracle are listed below:
PRAGMA AUTONOMOUS_TRANSACTION: This pragma can perform an autonomous transaction within a PL/SQL block between a BEGIN and END statement without affecting the entire transaction.
PRAGMA SERIALLY_REUSABLE: This directive tels Oracle that the package state is needed only for the duration of one call to the server. After the call is made the package may be unloaded to reclaim memory.
PRAGMA RESTRICT_REFRENCES: Defines the purity level of a packaged program. After Oracle8i this is no longer required.
PRAGMA EXCEPTION_INIT: This directive binds a user defined exception to a particular error number.
PRAGMA INLINE: (Introduced in Oracle 11g) This directive specifies that a subprogram call either is or is not to be inlined. Inlining replaces a subprogram call with a copy of the called subprogram.
Example Syntax:

CREATE OR REPLACE [FUNCTION | PROCEDURE] [NAME] IS
IS
[PRAGMA];
BEGIN
 ...
 ...
END;


Note that PRAGMA resides in the Declarative section of a PL/SQL block.

PRAGMA AUTONOMOUS_TRANSACTION:
we create a test table and populate it with two rows notice that the data is not commited.
1>>CREATE TABLE TEST
      (ID NUMBER NOT NULL,
       DESC VARCHAR2(10) NOT NULL
      );
2>>INSERT INTO TEST VALUES(1,'DESC01');
      INSERT INTO TEST VALUES(2,'DESC02');
3>>SELECT  * FROM TEST;
Next, we insert 8 rows using an anonymous block declare an PRAGMA AUTONOMOUS_TRANSACTION
which contain a commit statement.
4>>
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
FOR I IN 3..10
LOOP
INSERT INTO TEST
VALUES(I,'DESC'||I);
END LOOP;
COMMIT;
END;
--(IT COMMIT's ONLY 3 TO 10 RECORDS NOT PREVIOUS 2 RECORDS)

PRAGMA INLINE:
The only PRAGMA recently added (in Oracle11g) is PRAGMA INLINE.
In Oracle11g has been added a new feature that optimizer can use to get better performances, it’s called Subprogram Inlining. Optimizer can (autonomously or on demand) choose to replace a subprogram call with a local copy of the subprogram.

For example, assume the following code:

declare
total number;
begin
 total := calculate_nominal + calculate_interests;
end;

Where calculate_nominal and calculate_interests are two functions defined as follows:

function calculate_nominal return number is
s number;
begin
  select sum(nominal)
    into s
    from deals; 
  return s;
end;

function calculate_interests return number is
s number;
begin
  select sum(interest)
    into s
    from deals;
     
  return s;
end;

Optimizer can change the code to something like this:

declare
total number;
v_calculate_nominal number;
v_calculate_interests number;
begin
  select sum(nominal)
    into v_calculate_nominal
    from deals;

  select sum(interest)
    into v_calculate_interests
    from deals;

 total := v_calculate_nominal + v_calculate_interests;
end;

Including a copy of the subprograms into the calling program.

PRAGMA INLINE is the tool that we own to drive this new feature. If we don’t want such an optimization we can do:

declare
total number;
begin
 PRAGMA INLINE(calculate_nominal,'NO');
 PRAGMA INLINE(calculate_interests,'NO');
 total := calculate_nominal + calculate_interests;
end;

If we do want subprogram inlining on calculate_nominal we do:

declare
total number;
begin
 PRAGMA INLINE(calculate_nominal,'YES');
 total := calculate_nominal + calculate_interests;
end;

Subprogram inlining behave differently depending on the level of optimization defined through the db initialization variable PLSQL_OPTIMIZE_LEVEL. If this variable is set to 2 (that’s the default value) optimizer never uses subprogram inlining unless the programmer requests it using PRAGMA INLINE YES. If PLSQL_OPTIMIZE_LEVEL=3 optimizer can autonomously decide whether to use subprogram inlining or not. In this case PRAGMA INLINE YES does not force the optimizer, it’s just an hint.

PRAGMA SERIALLY_REUSABLE:

PRAGMA SERIALLY_REUSABLE tells to the compiler that the package’s variables are needed for a single use. After this single use Oracle can free the associated memory. It’s really useful to save memory when a packages uses large temporary space just once in the session.
Let’s see an example.
Let’s define a package with a single numeric variable “var” not initialized:
SQL> create or replace package pack is
  2  var number;
  3  end;
  4  /
If we assign a value to var, this will preserve that value for the whole session:
SQL> begin
  2  pack.var := 1;
  3  end;
  4  /

SQL> exec dbms_output.put_line('Var='||pack.var);
Var=1
If we use the PRAGMA SERIALLY_REUSABLE, var will preserve the value just inside the program that initializes it, but is null in the following calls:
SQL> create or replace package pack is
  2  PRAGMA SERIALLY_REUSABLE;
  3  var number;
  4  end;
  5  /

SQL> begin
  2  pack.var := 1;
  3  dbms_output.put_line('Var='||pack.var);
  4  end;
  5  /
Var=1

SQL> exec dbms_output.put_line('Var='||pack.var);
Var=
PRAGMA SERIALLY_REUSABLE is a way to change the default behavior of package variables that is as useful as heavy for memory.