using simple loop in plsql

begin
for i in 1 .. 5
loop
dbms_output.put_line('******TABLE'||' '||i||'******');
 for j in 1 .. 10
 loop
 dbms_output.put_line('    '|| i||'*'||j||'='||i*j);
 end loop;

end loop;
end;
/        

using case in plsql part3

DECLARE
l_maths number;
l_eng number;
l_hindi number;
l_sci number;
l_soc number;
l_san number;
l_tot_mars number;
mark_percent number;
grade number :=100;
BEGIN

select maths,eng,hindi,sci,soc,san,tot_mars into l_maths,l_eng,l_hindi,l_sci,l_soc,l_san,l_tot_mars
from student where stud_id = :v_stud_id;

mark_percent := l_tot_mars/ 600;

 CASE
 
   when (mark_percent >= 75) then             dbms_output.put_line('Excellent');
   when mark_percent <=75 and mark_percent >=60 then dbms_output.put_line('Very good');
   when mark_percent <=60 and mark_percent >=50 then dbms_output.put_line('Well done');
   when mark_percent <=50 and mark_percent >=40 then dbms_output.put_line('You passed');
   when mark_percent <=40 and mark_percent >=35 then dbms_output.put_line('Better try again');
   else dbms_output.put_line('No such grade');
   END CASE;

END stud_pro;

using case in plsql part2

DECLARE
grade number :=100;
BEGIN

 CASE
 
   when (grade >= 75) then             dbms_output.put_line('Excellent');
   when grade <=75 and grade >=60 then dbms_output.put_line('Very good');
   when grade <=60 and grade >=50 then dbms_output.put_line('Well done');
   when grade <=50 and grade >=40 then dbms_output.put_line('You passed');
   when grade <=40 and grade >=35 then dbms_output.put_line('Better try again');
   else dbms_output.put_line('No such grade');
   END CASE;

END;

using case in plsql

DECLARE
grade char(1) := 'A';
BEGIN
 CASE
   grade
   when 'A' then dbms_output.put_line('Excellent');
   when 'B' then dbms_output.put_line('Very good');
   when 'C' then dbms_output.put_line('Well done');
   when 'D' then dbms_output.put_line('You passed');
   when 'E' then dbms_output.put_line('Better try again');
   else dbms_output.put_line('No such grade');
   END CASE;

END;

using if else with logical operators in plsql

DECLARE
a boolean := true;
 b boolean := false;

 BEGIN

 IF (a AND b) THEN
 dbms_output.put_line('Line 1 - Condition is true');
 END IF;

 IF (a OR b) THEN
 dbms_output.put_line('Line 2 - Condition is true');
 END IF;

 IF (NOT a) THEN
 dbms_output.put_line('Line 3 - a is not true');
 ELSE
 dbms_output.put_line('Line 3 - a is true');
 END IF;
 
     IF (NOT b) THEN
     dbms_output.put_line('Line 4 - b is not true');
     ELSE
     dbms_output.put_line('Line 4 - b is true');
     END IF;
END;

Using if else condition in plsql 2

DECLARE
letter varchar2(1) := 'm';
BEGIN

    IF (letter in ('a', 'b', 'c')) THEN
    dbms_output.put_line('True');
    ELSE
    dbms_output.put_line('False');
    END IF;
 
    IF (letter in ('m', 'n', 'o')) THEN
    dbms_output.put_line('True');
    ELSE
    dbms_output.put_line('False');
    END IF;
   
    IF (letter is null) THEN
    dbms_output.put_line('True');
    ELSE
    dbms_output.put_line('False');
    END IF;
END;
/

Using IF ELSE Condition in plsql

 DECLARE
x number(2) := 10;

BEGIN

IF (x between 5 and 20) THEN
 dbms_output.put_line('True');
  ELSE
  dbms_output.put_line('False');
END IF;
 
 IF (x BETWEEN 5 AND 10) THEN
    dbms_output.put_line('True');
 ELSE
 dbms_output.put_line('False');
 END IF;

 IF (x BETWEEN 11 AND 20) THEN
 dbms_output.put_line('True');
 ELSE
 dbms_output.put_line('False');
 END IF;

END;
/

using IF Condition in plsql

DECLARE
PROCEDURE compare (value varchar2, pattern varchar2 )
 is
BEGIN

 IF value LIKE pattern THEN
 dbms_output.put_line ('True');
 ELSE
 dbms_output.put_line ('False');
 END IF;

END;


 BEGIN
 compare('Zara Ali', 'Z%A_i');
 compare('Nuha Ali', 'Z%A_i');
 END;

 /

how to comparison operators in plsql

DECLARE
a number (2) := 21;
b number (2) := 10;
BEGIN
 IF (a = b) then
  dbms_output.put_line('Line 1 - a is equal to b');
  ELSE
  dbms_output.put_line('Line 1 - a is not equal to b');
 END IF;
 
 IF (a < b) then
  dbms_output.put_line('Line 2 - a is less than b');
  ELSE
  dbms_output.put_line('Line 2 - a is not less than b');
 END IF;

 IF ( a > b ) THEN
 dbms_output.put_line('Line 3 - a is greater than b');
 ELSE
 dbms_output.put_line('Line 3 - a is not greater than b');
 END IF;
 -- Lets change value of a and b
 a := 5;
 b := 20;

 IF ( a <= b ) THEN
  dbms_output.put_line('Line 4 - a is either equal or less than b');
 END IF;

    IF ( b >= a ) THEN
     dbms_output.put_line('Line 5 - b is either equal or greater than a');
      END IF;
 
     IF ( a <> b ) THEN
       dbms_output.put_line('Line 6 - a is not equal to b');
       ELSE
       dbms_output.put_line('Line 6 - a is equal to b');
     END IF;
 END;
/

plsql inner block 2

DECLARE
-- Global variables
num1 number := 95;
num2 number := 85;
num3 number;
BEGIN
dbms_output.put_line('Outer Variable num1: ' || num1);
dbms_output.put_line('Outer Variable num2: ' || num2);
dbms_output.put_line('Inner Variable num1: ' || num3);
   DECLARE
  -- Local variables
   num3 number := 195;
   num4 number := 185;
   BEGIN
     dbms_output.put_line('Inner Variable num1: ' || num3);
     dbms_output.put_line('Inner Variable num2: ' || num4);
     dbms_output.put_line('Outer Variable num1: ' || num1);
    END;
END;

Simple plsql program 3

DECLARE
a integer := 10;
b integer := 20;
c integer;
f number(10,2);
f1 number;
BEGIN
c := a + b;
dbms_output.put_line('Value of c: ' || c);
f := 70.0/3.0;
dbms_output.put_line('Value of f: ' || f);
f1 := a*b;
dbms_output.put_line('Value of f: ' || f1);

END;

plsql inner blocks

DECLARE
SUBTYPE name IS char(20);
SUBTYPE message IS varchar2(100);
today date:=sysdate;
salutation name;
greetings message;
counter binary_integer := 0;
greetings1 varchar2(20) DEFAULT 'Have a Good Day';

BEGIN
salutation := 'Reader ';
greetings := 'Welcome to my  Blog';

 dbms_output.put_line('Hello ' || salutation || greetings||today);
 dbms_output.put_line('Hello ' ||'Counter :'||counter||'  '||'Greet:'||greetings1);
END;
/

Simple plsql program 2

   
BEGIN
  salutation := 'Reader ';
  greetings := 'Welcome to my blog';
   
  dbms_output.put_line('Hello ' || salutation || greetings);
       
EXCEPTION
when others then
null;
--<exception handling Statements>
END;

Simple PLSQL Program

DECLARE

  -- single line ccmments
   --
 
   --multi line comments
   /*
   --- n number of statements
 
   */

   --<declarations section>
   --variables
   --char type
   --declare variables using %TYPE,%ROWTYPE
   --declare records as PLSQL TABLE TYPE
   -- declare global variables
   --number type
   --date type
   -- cursor
   -- exceptions
   --subprograms
 --
stud_id  number;
stud_name varchar2(15) :='SATHISH';
join_date date := sysdate;
SUBTYPE name IS char(20);
SUBTYPE message IS varchar2(100);
salutation name; greetings message;

BEGIN
--<executable command(s)>
  --we can use dml statements
     -- insert ,update,delete,select....
     -- we can use operations
     -- we can write all loop conditions
     --here we raise exceptions
     -- we can open the cursor, fetch,close
   
     /* dbms_output.put_line('OUTER BEGIN');
        dbms_output.put_line('Student ID:'||stud_id);
     */
     null;
    stud_id :=10;
--    stud_name := 'SATHISH';  
     dbms_output.put_line('OUTER BEGIN');
     dbms_output.put_line('Student ID:'||stud_id);
     dbms_output.put_line('Student Name:'||stud_name);
     dbms_output.put_line('Student Join Date:'||join_date);
     begin
      null;
      stud_id :=20;
       stud_name :='RAM';
      dbms_output.put_line('INNER BEGIN');
      dbms_output.put_line('Student ID:'||stud_id);
      dbms_output.put_line('Student ID:'||stud_name);
      dbms_output.put_line('Student ID:'||join_date);
     -- all executable statements
     exception
     when others then
     null;
     end;

tkprof command in oracle

tkprof orcl102_ora_3064.trc output.prf EXPLAIN=scott/tiger SYS=NO

Purging Oracle Workflow Data Questions

Which Workflow Tables Need To Be Checked?

Master workflow runtime table is WF_ITEMS.
All other runtime table records are associated to record in WF_ITEMS.
If records are purged in WF_ITEMS, records ar purged in all other workflow runtime tables.
Checking the workflow runtime table master table for progress purging is the same as check any other workflow runtime table.

This query will help you to determine volume of wf process classified by Status, item Type, and permanency.

select wi.item_type ITEM_TYPE,

       wit.persistence_type P_TYPE, 

       decode (wi.end_date, NULL, 'OPEN', 'CLOSED') Status,

       count(*) COUNT

from wf_items wi,

     wf_item_types wit

where wit.name = wi.item_type

group by item_type, 

         wit.persistence_type, 

         WIT.PERSISTENCE_DAYS,

         decode (wi.end_date, NULL, 'OPEN', 'CLOSED') 

order by decode (wi.end_date, NULL, 'OPEN', 'CLOSED'), 4 desc;

This query displays the parameter arguments in order for a Concurrent Program:

SELECT p.USER_CONCURRENT_PROGRAM_NAME "NAME", 

c.CONCURRENT_PROGRAM_NAME "INTERNAL",

f.END_USER_COLUMN_NAME "PARAMETER",

f.ENABLED_FLAG "ON_OFF",

f.DEFAULT_VALUE,

f.REQUIRED_FLAG, f.DESCRIPTION

FROM FND_DESCR_FLEX_COL_USAGE_VL f, FND_CONCURRENT_PROGRAMS_TL p, fnd_concurrent_programs c

WHERE substr(f.DESCRIPTIVE_FLEXFIELD_NAME,7,8)=c.CONCURRENT_PROGRAM_NAME

and c.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID

--and p.USER_CONCURRENT_PROGRAM_NAME LIKE '%Workflow%' 

and (f.DESCRIPTIVE_FLEXFIELD_NAME like '$SRS$.FNDWFPR%') 

AND p.LANGUAGE = 'US'

order by f.DESCRIPTIVE_FLEXFIELD_NAME, f.COLUMN_SEQ_NUM;

Records in these tables are purged only if the records in the workflow master table (WF_ITEMS) qualifies to be purged.

These queries give the volume of data in the tables.

select count(*) from WF_ITEM_ATTRIBUTE_VALUES;
select count(*) from WF_ITEM_ACTIVITY_STATUSES;
select count(*) from WF_NOTIFICATION_ATTRIBUTES;

The above Workflow tables contain data which is used for Workflow background processing. If these tables grow too large the Workflow performance level becomes slower. You should be purging these tables should be purged on a regular basis.

What Are The APIs Provided For Purging Workflow Data?

WF_PURGE is the database defined package which contain the APIs to purge workflow runtime tables. There are also APIs to purge design data like workflow directory services data.  Workflow design data are  tables where workflow process definitions are stored and are not considered runtime data.

The most commonly used procedures are:
Wf_Purge.Items:

Purge all runtime data associated with completed items, their processes, and
notifications sent by them.
Deletes from the tables: WF_NOTIFICATIONS, WF_ITEM_ACTIVITY_STATUSES,
WF_ITEM_ATTRIBUTE_VALUES AND WF_ITEMS/
Parameters:

itemtype : Item type to delete, or null for all item types
itemkey : Item key to delete, or null for all item keys
enddate : Purges wf processes closed after this date
force : Forces to purge closed wf processes even if it has wf slibing
processes open.
docommit : TRUE does commit, FALSE deleted but does not commit.
Core Workflow Only

Wf_Purge.Activities :

Purges wf process definition versions that are not used and that are obsolete.
Deletes from tables:
WF_ACTIVITY_ATTR_VALUES,
WF_ACTIVITY_TRANSITIONS,
WF_PROCESS_ACTIVITIES,
WF_ACTIVITY_ATTRIBUTES_TL,
WF_ACTIVITY_ATTRIBUTES,
WF_ACTIVITIES_TL and
WF_ACTIVITIES that are associated with the specified item type,
have an END_DATE less than or equal to the specified end date and
are not referenced by an existing item as either a process or activity.

Wf_Purge.Total : Purge both item data and activity data.

Wf_Purge.AdHocDirectory :

Purge users and roles in the WF_LOCAL_* tables
whose expiration date has elapsed and that are not referenced in any notification.

Note:

The WF_PURGE_APIS only purge data associated with Temporary item type whose
persistence, in days, has expired. A persistence type PL/SQL variable is set
to 'TEMP' (Temporary) by default and should not be changed. Use the WF_PURGE.
TotalPERM API to delete all eligible obsolete runtime data associated with
item types of with a persistence type of 'Permanent'.

What Parameters Are Passed To The Purge APIs?

Many of the Purge APIs accept the following parameters:

• Item Type: The item type associated with the obsolete runtime data you want to delete. Leave this parameter null to delete obsolete data for all item types
• Item Key: A string generated from the application object's primary key. The string uniquely identifies the item within an item type. Leave this parameter null to purge all items in the specified item type.
• End Date: A specified date to delete up to.

Which Concurrent Program Is Used For Purging?

Answer
In Oracle E-Business Suite, use the concurrent program 'Purge Obsolete Workflow
Runtime Data' (short name FNDWFPR) which calls database package WF_Purge.Total.

This program deletes old Workflow runtime data for completed Workflows.
It is recommended that this program or package is run regularly as required.

What's The Recommended Frequency For Running The 'Purge Obsolete Workflow Runtime Data' Concurrent Program?

In short, Once Every 12 Hours for ALL item types daily.  For maintaining a healthy workflow environment, the Purge Obsolete Workflow Runtime Data request should be scheduled regularly.  Below is from the Workflow Administrator's Guide which reflects as a guideline, but the volumes differ for each instance:

The Oracle Applications Manager console helps you easily maintain the Oracle Workflow and Oracle XML Gateway database tables. Oracle Workflow and Oracle XML Gateway access several tables that can grow quite large with obsolete workflow information that is stored for all completed workflow processes, as well as obsolete information for XML transactions. The size of these tables and indexes can adversely affect performance. These tables should be purged on a regular basis, using the Purge Obsolete Workflow Runtime Data concurrent program.

What Are The Parameter Values To Define To Run The 'Purge Obsolete Workflow Runtime Data' Concurrent Program?

• Item Type : The item type to purge. Leaving this field blank defaults to purging the runtime data for all item types.
• Item Key : The item key to purge. Leaving is field blank defaults to purging the runtime data for all item keys.
• Age : Minimum age of data to purge, in days.
• Persistence Type : The persistence type to be purged, either for Temporary or for Permanent. The default is temp.
• Core Workflow Only - Enter 'Y' to purge only obsolete runtime data associated
  with work items, or 'N' to purge all obsolete runtime data as well obsolete design
  data. The default is 'N'.
• Commit Frequency - Enter the number of records to purge before the program
  commits data. To reduce rollback size and improve performance, set this parameter
  to commit data after a smaller number of records. The default is 500 records.