Oracle – NuWave eSolutions DevOp Team Technical Discussion

Import Data/file using a sql script:

Big files: sqlloader or external tables & Small files: own parsing using UTL_FILE

Import: sqlloader Example

Creating a control file:

LOAD DATA
 INFILE '<path and file name of csv file>'
 INTO TABLE <your table name>
 FIELDS TERMINATED BY ','
 (feature_name CHAR OPTIONALLY ENCLOSED BY '"',
 feature_type CHAR, county CHAR,
 latitude CHAR,
 longitude CHAR,
 elevation INTEGER EXTERNAL,
 update_time DATE "YYYYMMDDHH24MI")

Remember that you need to use char instead of varchar 2 and integer for number columns save your control file as name.ctl
2. Start sql loader from the command like this: This can also be made a procedure to create a job for this to make the import process automatic.

sqlldr username/password@connect_string control=ctl_file.ctl log=log.log
you should see the rows inserted and commited as they load

Import: External tables Example

Oracle9i Database introduced external tables, which allow a formatted plain text file to be visible to the database as a table that can be selected by regular SQL.Create a directory object named dump_dir as:

create directory dump_dir as '/home/oracle/dump_dir';

Create an external table:

create table trans_ext

(   ... <columns of the table> ...)

organization external

 type oracle_loader

default directory admin

access parameters

      records delimited by newline

      badfile 'trans_ext.bad'

      discardfile 'trans_ext.dis'

      logfile 'trans_ext.log'

      fields terminated by ","  optionally enclosed by '"'

      (... <columns> ...)

   location ('trans_flat.txt')

) reject limit unlimited;

Now load the external table into the regular tables using any common method such as direct load insert and merge.

Import: Read/Import file by using UTL_FILE

CREATE OR REPLACE PROCEDURE my_app2 IS

   infile              utl_file.file_type;

   outfile             utl_file.file_type;

   buffer              VARCHAR2(30000);

   b_paragraph_started BOOLEAN := FALSE; -- flag to indicate that required paragraph is started

BEGIN

   infile := utl_file.fopen('TEST_DIR', 'mytst.txt', 'r');    -- open a file to read

   outfile := utl_file.fopen('TEST_DIR', 'out.txt', 'w');    -- open a file to write

   IF utl_file.is_open(infile)    -- check file is opened

   THEN       -- loop lines in the file

      LOOP

         BEGIN

            utl_file.get_line(infile, buffer);

            IF buffer LIKE 'foo%' OR b_paragraph_started

            THEN

               utl_file.put_line(outfile, buffer, FALSE);                --write to out.txt

               b_paragraph_started := TRUE;

            END IF;

            IF buffer LIKE '%ZEN'

            THEN

               b_paragraph_started := FALSE;

            END IF;

            utl_file.fflush(outfile);

         EXCEPTION

            WHEN no_data_found THEN EXIT;

         END; END LOOP;

   END IF;

   utl_file.fclose(infile);

   utl_file.fclose(outfile);

EXCEPTION

   WHEN OTHERS THEN

      raise_application_error(-20099, 'Unknown UTL_FILE Error');

END my_app2l;

Export: Write a file using a sql script: Example text file creation

FILEHANDLE        := UTL_FILE.FOPEN('\\10.15.1.197\d\orahome\bin','test.txt','w');
 WRITEMESSAGE  := 'This is created for testing purpose \n' || ' \n This is the second line'; 
 UTL_FILE.PUTF(filehandle,writemessage); 
 UTL_FILE.FFLUSH(filehandle);
 UTL_FILE.FCLOSE(filehandle);
 END;

Export: a file using External files

From the database, create a plain text file with the contents of the table TRANS. The file can be called trans_flat.txt in the directory /home/oracle/dump_dir. Usually this file is created with this SQL:

spool trans_flat.txt

select <column_1> ||','|| <column_2> ||','|| ...

from trans;

spool off

Automating a Process

DBMS_SCHEDULER is an internal Oracle package (since Version 10g) which provides database driven jobs.
It’s divided into 3 parts:

Time schedule part – dbms_scheduler.create_schedule
Program declaration part – dbms_scheduler.create_program
Job (conflation) part -dbms_scheduler.create_job

Examples of the dbms_scheduler.create_schedule part:

 begin -- daily from Monday to Sunday at 22:00 (10:00 p.m.)
 dbms_scheduler.create_schedule
 (schedule_name => 'INTERVAL_DAILY_2200',
  start_date=> trunc(sysdate)+18/24, -- start today 18:00 (06:00 p.m.)
  repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22;',
  comments=>'Runtime: Every day (Mon-Sun) at 22:00 o'clock');

-- run every hour, every day
 dbms_scheduler.create_schedule(  
 schedule_name  => 'INTERVAL_EVERY_HOUR',  
   start_date    => trunc(sysdate)+18/24,  
   repeat_interval => 'freq=HOURLY;interval=1',  
   comments     => 'Runtime: Every day every hour');

 -- run every Sunday at 18:00 (06:00 p.m.)

dbms_scheduler.create_schedule
 (schedule_name => 'INTERVAL_EVERY_SUN_1800',
  start_date=> trunc(sysdate)+18/24,
  repeat_interval=> 'FREQ=DAILY; BYDAY=SUN; BYHOUR=18;',
  comments=>'Runtime: Run at 6pm every Sunday');
 end;

Example of the dbms_scheduler.create_program part:

Begin -- Call a procedure of a database package
 dbms_scheduler.create_program
 (program_name=> 'PROG_COLLECT_SESS_DATA',
  program_type=> 'STORED_PROCEDURE',
  program_action=> 'pkg_collect_data.prc_session_data',
  enabled=>true,
  comments=>'Procedure to collect session information'
  );
 end;

Example of the dbms_scheduler.create_job part:

begin
 -- Connect both dbms_scheduler parts by creating the final job
 dbms_scheduler.create_job
  (job_name => 'JOB_COLLECT_SESS_DATA',
   program_name=> 'PROG_COLLECT_SESS_DATA',
   schedule_name=>'INTERVAL_EVERY_5_MINUTES',
   enabled=>true,
   auto_drop=>false,
   comments=>'Job to collect data about session values every 5 minutes');
 end;

Example to run job immediate:

dbms_scheduler.run_job(‘JOB_COLLECT_SESS_DATA’,TRUE);

UTL_FILE

Export/write a text file from pl sql block using UTL_FILE and then create a procedure for this and then create a job and then schedule the job to make this automatic.

UTL_FILE: With the UTL_FILE package, PL/SQL programs can read and write operating system text files. UTL_FILE provides a restricted version of operating system stream file I/O.

Subprogram	Description
FCLOSE	Closes a file
FCOPY	Copies a contiguous portion of a file to a newly created file
FFLUSH	Physically writes all pending output to a file
FGETATTR	Reads and returns the attributes of a disk file
FOPEN Function	Opens a file for input or output
FREMOVE	Deletes a disk file, assuming that you have sufficient privileges
FRENAME	Renames an existing file to a new name, similar to the UNIX mv function
FSEEK	Adjusts the file pointer forward or backward within the file by the number of bytes specified
GET_LINE	Reads text from an open file
GET_LINE_NCHAR	Reads text in Unicode from an open file
GET_RAW Function	Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read
NEW_LINE	Writes one or more operating system-specific line terminators to a file
PUT	Writes a string to a file
PUT_LINE	Writes a line to a file, and so appends an operating system-specific line terminator
PUT_LINE_NCHAR	Writes a Unicode line to a file
PUT_NCHAR	Writes a Unicode string to a file
PUTF	A PUT with formatting
PUT_RAW Function	Accepts as input a RAW data value and writes the value to the output buffer

Collections:

Collections are Oracle's version of arrays; collections are single-dimensioned lists. To create a collection or record variable, you first define a collection or record type, and then you declare a variable of that type. In a collection, the internal components are always of the same data type, and are called elements.

Nested Tables, they are the most common form of collection and so represent a useful basis of comparison. A nested table is a variable which can hold more than one instance of something, often a record from a database table. They might be declared like this:

type emp_nt is table of emp%rowtype;

emp_rec_nt emp_nt;

Use: to store multiple instances of data against which we want to do the same thing. The classic example is using BULK COLLECT to store multiple records:

select * bulk collect into emp_rec_nt from employees;

 An Index By table / Associative Array: These are simple collections of single attributes with an index. Nested tables also have indexes but their indexes are just row counts. With an associative array the index can be meaningful, i.e. sourced from a data value. So they are useful for caching data values for later use. The index can be a number, or (since 9iR2) a string which can be very useful. For instance, here is an associative array of salaries which is indexed by the employee identifier.

type emp_sal_aa is table of emp.sql%type index by emp.empno%type;

l_emp_sales emp_sal_aa;

Elements of an array can identified by an index value, in this case EMPNO:

l_emp_sals(l_emp_no) := l_emp_sal;

Other than caching reference tables or similar look-up values there aren't many use cases for associative arrays.

Variable arrays are just nested tables with a pre-defined limit on the number of elements. So perhaps the name is misleading: they are actually fixed arrays. They are declared like this:

type emp_va is varray(14) of emp%rowtype;

emp_rec_va emp_va;

We can use bulk collect to populate a VArray ...

select * bulk collect into emp_rec_va from employees;

The query will return the no of elements specified in the VArray's declaration otherwise ORA-22165: given index [string] must be in the range of [string] to [string]  will be thrown.

Use: same as Nested Table, here we can set the limit and another one big advantage of VArrays over nested tables is that they guarantee the order of the elements. So if you must get elements out in the same order as you inserted them use a VArray.

Autonomous Transaction

An autonomous transaction is an independent transaction that is initiated by another transaction, and executes without interfering with the parent transaction. When an autonomous transaction is called, the originating transaction gets suspended. Control is returned when the autonomous transaction does a COMMIT or ROLLBACK.

A Routine can be marked as autonomous by declaring it as PRAGMA AUTONOMOUS_TRANSACTION. You may need to increase the TRANSACTIONS parameter to allow for the extra concurrent transactions. Top-level (not nested) anonymous PL/SQL block. The routine can be Standalone, packaged, or nested subprogram Method of a SQL object type or Database trigger

Example

CREATE OR REPLACE TRIGGER tab1_trig

AFTER insert ON tab1

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');

  COMMIT; -- only allowed in autonomous triggers

 END; /

Use: that with the above example will insert and commit log entries – even if the main transaction is rolled-back! One situation in which autonomous transactions can prove extremely useful is with auditing and debugging PL/SQL.

Materialized View

Materialized views are disk based and are updated periodically based upon the query definition. Normal Views are virtual only and run the query definition each time they are accessed.

CREATE MATERIALIZED VIEW view-name

BUILD [IMMEDIATE | DEFERRED]

REFRESH [FAST | COMPLETE | FORCE ]

ON [COMMIT | DEMAND ]

[[ENABLE | DISABLE] QUERY REWRITE]

[ON PREBUILT TABLE]

AS

SELECT ...;

The QUERY REWRITE clause tells the optimizer if the materialized view should be consider for query rewrite operations. The ON PREBUILT TABLE clause tells the database to use an existing table segment, which must have the same name as the materialized view and support the same column structure as the query.

Use: Performance, on queries performing aggregations and transformations of the data. This allows the work to be done once and used repeatedly by multiple sessions, reducing the total load on the server

Queries to large tables using joins. These operations are very expensive in terms of time and processing power. The type of materialized view that is created determines how it can be refreshed and used by query rewrite.

Limitations: must define column names explicitly; you cannot include a SELECT * .

Do not include columns defined as TIMESTAMP WITH TIME ZONE in the materialized view. The value of the time_zone_adjustment option varies between connections based on their location and the time of year, resulting in incorrect results and unexpected behavior.

When creating a materialized view, the definition for the materialized view cannot contain, references to other views, materialized or not , references to remote or temporary tables, variables such as CURRENT USER; all expressions must be deterministic, calls to stored procedures, user-defined functions, or external functions

Mutating Exception

ORA-04091 (table xxx is mutating. Trigger/function might not see it): The error is encountered when a row-level trigger accesses the same table on which it is based, while executing. The table is said to be mutating.

CREATE OR REPLACE TRIGGER TUA_TEST
 AFTER UPDATE OF STATUS ON TEST
 FOR EACH ROW – This is the culprit here
 DECLARE
 v_Count NUMBER;
 BEGIN

SELECT count(*) INTO v_count FROM TEST WHERE status = ‘INVALID’;

dbms_output.put_line(‘Total Invalid Objects are ‘ || v_count);
 END;
 /

Throwing the exception by updating the status to ‘INVALID’

update test set status = 'INVALID'  where object_name = 'TEST1';

ERROR at line 1:

 ORA-04091: table SCOTT.TEST is mutating, trigger/function may not see it
 
 Different ways to handle mutating table errors

First one is to create statement level trigger instead of row level. If we omit the ‘for each row’ clause from above trigger, it will become statement level trigger. Let us create a new statement level trigger.

CREATE OR REPLACE TRIGGER TUA_TEST
 AFTER UPDATE OF STATUS ON TEST
 DECLARE

v_Count NUMBER;
 BEGIN

SELECT count(*) INTO v_count FROM TEST WHERE status = ‘INVALID’;

dbms_output.put_line(‘Total Invalid Objects are ‘ || v_count);
 END;

 2. Second way of dealing with the mutating table issue is to declare row level trigger as an autonomous transaction so that it is not in the same scope of the session issuing DML statement. Following is the row level trigger defined as pragma autonomous transaction. By defining row level trigger as an autonomous transaction, we got rid of mutating table error but result is not correct. The latest updates are not getting reflected in our result set as oppose to statement level trigger. So one has to be very careful when using this approach.

In version 11g, Oracle made it much easier with introduction of compound triggers. Let us see in this case how a compound trigger can resolve mutating table error. Let’s create a compound trigger first:

CREATE OR REPLACE TRIGGER TEST_TRIG_COMPOUND

FOR UPDATE ON TEST

COMPOUND TRIGGER

/* Declaration Section*/

v_count NUMBER;

AFTER EACH ROW IS

BEGIN

dbms_output.put_line(‘Update is done’);

END AFTER EACH ROW;

AFTER STATEMENT IS

BEGIN

SELECT count(*) INTO v_count FROM TEST WHERE status = ‘INVALID’;

dbms_output.put_line(‘Total Invalid Objects are ‘ || v_count);

END AFTER STATEMENT;

END TEST_TRIG_COMPOUND;

SET Operator:  UNION, INTERSECT, MINUS, UNION ALL, used on complex queries

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not. There is a performance hit when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports). The implication of this, is that union is much less performant as it must scan the result for duplicates

Triggers

A trigger is a named program unit that is stored in the database and fired (executed) in response to a specified event. The specified event is associated with either a table, a view, a schema, or the database, and it is one of the following:

A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)

A database definition (DDL) statement (CREATE, ALTER, or DROP)

A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)

Use:Automatically generate derived column values, Enforce referential integrity across nodes in a distributed database, Enforce complex business rules, Provide transparent event logging, Provide auditing, Maintain synchronous table replicates, Gather statistics on table access, Modify table data when DML statements are issued against views, Publish information about database events, user events, and SQL statements to subscribing applications, Restrict DML operations against a table to those issued during regular business hours, Enforce security authorizations and Prevent invalid transactions

Limitations/points to be considered when creating trigger

A SQL statement within its trigger action potentially can fire other triggers, resulting in cascading triggers,

It should not be generate mutating behavior.

Analyze to using the autonomous transaction?

Trigger Size Restriction, The size of the trigger cannot exceed 32K. In this case create an procedure.

A trigger cannot declare a variable of the LONG or LONG RAW data type. A SQL statement in a trigger can reference a LONG or LONG RAW column only if the column data can be converted to the data type CHAR or VARCHAR2. A trigger cannot use the correlation name NEW or PARENT with a LONG or LONG RAW column.

One of the most dangerous attributes of a database trigger is its hidden behavior, we do not know it was fired or not, if it was disabled due to change in a table.

Cursors

Oracle creates a memory area, known as context area, for processing an SQL statement, which contains all information needed for processing the statement, for example, number of rows processed, etc.

A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement

Using the SELECT-INTO statement: implicit cursor

Fetching from an explicit cursor: A SELECT-INTO is also referred to as an implicit query, because Oracle Database implicitly opens a cursor for the SELECT statement, fetches the row, and then closes the cursor when it finishes doing that (or when an exception is raised). You can, alternatively, explicitly declare a cursor and then perform the open, fetch, and close operations yourself.

DECLARE

 l_total       INTEGER := 10000;

      CURSOR employee_id_cur

IS

           SELECT employee_id FROM plch_employees ORDER BY salary ASC;

      l_employee_id   employee_id_cur%ROWTYPE;

  BEGIN

     OPEN employee_id_cur;

     LOOP

        FETCH employee_id_cur INTO l_employee_id;

        EXIT WHEN employee_id_cur%NOTFOUND;

        assign_bonus (l_employee_id, l_total);

        EXIT WHEN l_total <= 0;

     END LOOP;

     CLOSE employees_cur;

  END;

Using a cursor FOR loop: the cursor FOR loop is an elegant and natural extension of the numeric FOR loop in PL/SQL. With a numeric FOR loop, the body of the loop executes once for every integer value between the low and high values specified in the range. With an implicit cursor FOR loop, the body of the loop is executed for each row returned by the query.

The following block uses a cursor FOR loop to display the last names of all employees in department 10:

BEGIN

   FOR employee_rec IN (SELECT * FROM employeesWHERE department_id = 10)

   LOOP       DBMS_OUTPUT.put_line (employee_rec.last_name);

 END LOOP;

END;

You can also use a cursor FOR loop with an explicitly declared cursor:

DECLARE

   CURSOR employees_in_10_cur

IS

      SELECT * FROM employees WHERE department_id = 10;

BEGIN

   FOR employee_rec

   IN employees_in_10_cur

   LOOP

      DBMS_OUTPUT.put_line (employee_rec.last_name);

   END LOOP;

END;

The nice thing about the cursor FOR loop is that Oracle Database opens the cursor, declares a record by using %ROWTYPE against the cursor, fetches each row into a record, and then closes the loop when all the rows have been fetched (or the loop terminates for any other reason).

Using cursor variables: A cursor variable is, as you might guess from its name, a variable that points to a cursor or a result set. Unlike with an explicit cursor, you can pass a cursor variable as an argument to a procedure or a function. There are several excellent use cases for cursor variables, including the following:

Construct a result set inside a function, and return a cursor variable to that set. This is especially handy when you need to use PL/SQL, in addition to SQL, to build the result set.

Pass a cursor variable to a pipelined table function—a powerful but quite advanced optimization technique. A full explanation of cursor variables, including the differences between strong and weak REF CURSOR types, is beyond the scope of this article.

Cursor variables can be used with either embedded (static) or dynamic SQL.

CREATE OR REPLACE FUNCTION names_for (

         name_type_in IN VARCHAR2)

      RETURN SYS_REFCURSOR

IS

      l_return   SYS_REFCURSOR;

   BEGIN

      CASE name_type_in

         WHEN 'EMP'

         THEN

           OPEN l_return FOR

                SELECT last_name FROM employees ORDER BY employee_id;

        WHEN 'DEPT'

        THEN

           OPEN l_return FOR

                SELECT department_name FROM departments ORDER BY department_id;

     END CASE;

     RETURN l_return;

  END names_for;

Strong and weak REF CURSOR types

Ref cursor is a cursor variable which acts as a pointer to the sql memory area. Ref cursor can be asssociated with multiple sql statements where as a cursor can be associated with only one sql statement. Refcursor is dynamic where as cursor is static. Ref cursors can be typed/strong and untyped/weak:

A strongly typed ref cursor always returns a known type, usually from a declared TYPE object. The compiler can find problems in a PL/SQL block by comparing the types returned to how they are used.

A weakly typed ref cursor has a return type that is dependent on the SQL statement it executes, i.e. only once the cursor is opened is the type known (at runtime). The compiler cannot determine the types until it is ran, so care must be taken to ensure that the cursor result set is handled properly to avoid runtime errors.

User Defined Exception

Is it possible to create user-defined exceptions and be able to change the SQLERRM? Yes, You could use RAISE_APPLICATION_ERROR like this:

DECLARE

    ex_custom       EXCEPTION;

BEGIN

    RAISE ex_custom;

EXCEPTION

    WHEN ex_custom THEN

        RAISE_APPLICATION_ERROR(-20001,'My exception was raised');

END;
That will raise an exception that looks like: ORA-20001: My exception was raised, The error number can be anything between -20001 and -20999.

How to catch and handle only specific Oracle exceptions? Refer to the exception directly by number:

BEGIN

    EXECUTE IMMEDIATE 'CREATE SEQUENCE S_TEST START WITH 1 INCREMENT BY 1';

EXCEPTION

    WHEN OTHERS THEN

      IF SQLCODE = -955 THEN

        NULL; -- suppresses ORA-00955 exception

      ELSE

         RAISE;

      END IF;

END;

BULK COLLECT

BULK COLLECT: SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval

The bulk processing features of PL/SQL are designed specifically to reduce the number of context switches required to communicate from the PL/SQL engine to the SQL engine. Use the BULK COLLECT clause to fetch multiple rows into one or more collections with a single context switch. Use the FORALL statement when you need to execute the same DML statement repeatedly for different bind variable values. The UPDATE statement in the increase_salary procedure fits this scenario; the only thing that changes with each new execution of the statement is the employee ID. PL/SQL collections are essentially arrays in memory, so massive collections can have a detrimental effect on system performance due to the amount of memory they require.  In some situations, it may be necessary to split the data being processed into chunks to make the code more memory-friendly.   This “chunking” can be achieved using the LIMIT clause of the BULK COLLECT syntax.

The bulk_collect_limit.sql script uses the LIMIT clause to split the collection into chunks of 10,000; processing each chunk in turn.  Notice the use of the explicit cursor for this operation.

FORALL: INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data very quickly

CREATE OR REPLACE PROCEDURE increase_salary (

      department_id_in   IN employees.department_id%TYPE,

      increase_pct_in    IN NUMBER)

IS

      TYPE employee_ids_t IS TABLE OF employees.employee_id%TYPE

              INDEX BY PLS_INTEGER;

      l_employee_ids   employee_ids_t;

      l_eligible_ids   employee_ids_t;

     l_eligible       BOOLEAN;

  BEGIN

     SELECT employee_id

       BULK COLLECT INTO l_employee_ids

       FROM employees

      WHERE department_id = increase_salary.department_id_in;

     FOR indx IN 1 .. l_employee_ids.COUNT

     LOOP

        check_eligibility (l_employee_ids (indx),

                           increase_pct_in,

                           l_eligible);

        IF l_eligible

        THEN

           l_eligible_ids (l_eligible_ids.COUNT + 1) :=

              l_employee_ids (indx);

        END IF;

     END LOOP;

     FORALL indx IN 1 .. l_eligible_ids.COUNT

        UPDATE employees emp

           SET emp.salary =

                    emp.salary

                  + emp.salary * increase_salary.increase_pct_in

         WHERE emp.employee_id = l_eligible_ids (indx);

  END increase_salary;

Steps to generate Excel sheet output

Create a custom DAD if required using Enterprise Manager Console of MidTier for HTTP Server or use Portal DAD itself to implement the solution.

 Create a new procedure (a webdb solution) to stream the HTML for the excel sheet report which will be downloaded.

 CREATE OR REPLACE  PROCEDURE generateCsv as

 cursor p_emp is select * from PORTAL_DEMO.EMP;

 begin

 owa_util.mime_header( 'application/vnd.ms-excel', False ); -- Here change the content-type to PDF Format accordingly

 htp.print('Content-Disposition: attachment;filename="pvasista.csv"');

 owa_Util.Http_Header_Close;

 for v_emp_cur in p_emp

 LOOP

 htp.p (v_emp_cur.ename || ',' || v_emp_cur.deptno || chr(13));

 END LOOP;

 end;

Probably we can apply appropriate content-type for PDF in the PL/SQL written below, to get the same in PDF. i suppose it should be application/pdf

Sending email: UTL_MAIL: The UTL_MAIL package is a utility for managing email which includes commonly used email features, such as attachments, CC, BCC, and return receipt.

SEND Procedure

Packages an email message into the appropriate format, locates SMTP information, and delivers the message to the SMTP server for forwarding to the recipients

SEND_ATTACH_RAW Procedure

Represents the SEND Procedure overloaded for RAW attachments

SEND_ATTACH_VARCHAR2 Procedure

Represents the SEND Procedure overloaded for VARCHAR2 attachments

Most used functions: Merge, NVL, Decode

Category: Oracle

Writing Code is fun, see here :)

About CAE Solutions

Pl/SQL Concepts: How to with an example?