How to write an OEM report in Oracle using PL/SQL.

• 1). Write a declaration to define the variables you need. A declaration is where you will assign characters to a variable. In Oracle, for example, FIRST CHARACTER will be written as title_area.FIRSTCHARACTER%TYPE;. Title_area will be used in place of FIRSTCHARACTER in your command procedure. This will save time and space in your procedure.
  
  Your first step should look like this:
  
  DECLARE -- all other variables will be listed here as well
  
  title_idtitle_area.FIRSTCHARACTER%TYPE
  
  title_nametitle_area.title_name%TYPE;
  
  
• 2). Write your executable statements. The BEGIN keyword defines the start of your executable statements in your procedure. This is where you will write your commands in the procedure, instructing Oracle to give you the output you will be expecting.
  
  Your procedure should now look like this:
  
  DECLARE
  
  title_idtitle_area.FIRSTCHARACTER%TYPE
  
  title_nametitle_area.title_name%TYPE;
  
  BEGIN
  
  
• 3). Four clauses and two statements follow the BEGIN statement. The first is SELECT. The SELECT clause instructs Oracle what you want from your datamart tables. You must list each declaration in the order in which you want the columns returned because that is the order in which you will receive them. Next, the INTO clause is written to instruct Oracle in what columns to put the retrieved data. Then the FROM clause specifies from where (what file in the datamart) you want to grab the data you need. The last clause, the WHERE clause, specifies from what client or DID (distribution id) you want to grab the data from. The next two statements follow directly below the clauses, within the same BEGIN statement. The INSERT INTO statement is where you actually instruct Oracle to insert the data you are asking to retrieve. The last statement, VALUES, is added to assign any values to your declarations.
  
  Your procedure should now look like this:
  
  DECLARE
  
  title_idtitle_area.FIRSTCHARACTER%TYPE
  
  title_nametitle_area.title_name%TYPE;
  
  BEGIN
  
  SELECT title_id, title_name
  
  INTO title_id, title_name
  
  FROM title_area
  
  WHERE ; title_id = (SELECT MAX (title_id) FROM title_area);
  
  INSERT INTO title_area (title_id, title_name)
  
  VALUES ; (title_id + 1)
  
  
• 4). Add the END statement to end your executable statements. In order to execute your procedure, you must end with a semicolon (;).
  
  Your procedure should now look like this:
  
  DECLARE
  
  title_idtitle_area.FIRSTCHARACTER%TYPE
  
  title_nametitle_area.title_name%TYPE;
  
  BEGIN
  
  SELECT title_id, title_name
  
  INTO title_id, title_name
  
  FROM title_area
  
  WHERE ; title_id = (SELECT MAX (title_id) FROM title_area);
  
  INSERT INTO title_area (title_id, title_name)
  
  VALUES ; (title_id + 1)
  
  END;