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> ...)
default directory admin
records delimited by newline
fields terminated by "," optionally enclosed by '"'
(... <columns> ...)
) 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
b_paragraph_started BOOLEAN := FALSE; -- flag to indicate that required paragraph is started
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
IF buffer LIKE 'foo%' OR b_paragraph_started
utl_file.put_line(outfile, buffer, FALSE); --write to out.txt
b_paragraph_started := TRUE;
IF buffer LIKE '%ZEN'
b_paragraph_started := FALSE;
WHEN no_data_found THEN EXIT;
END; END LOOP;
WHEN OTHERS THEN
raise_application_error(-20099, 'Unknown UTL_FILE Error');
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:
select <column_1> ||','|| <column_2> ||','|| ...
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:
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.
|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 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;
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;
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;
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.
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
CREATE OR REPLACE TRIGGER tab1_trig
AFTER insert ON tab1
INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');
COMMIT; -- only allowed in autonomous triggers
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 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]
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
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
/* Declaration Section*/
AFTER EACH ROW IS
dbms_output.put_line(‘Update is done’);
END AFTER EACH ROW;
AFTER STATEMENT IS
SELECT count(*) INTO v_count FROM TEST WHERE status = ‘INVALID’;
dbms_output.put_line(‘Total Invalid Objects are ‘ || v_count);
END AFTER STATEMENT;
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
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.
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.
l_total INTEGER := 10000;
SELECT employee_id FROM plch_employees ORDER BY salary ASC;
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;
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:
FOR employee_rec IN (SELECT * FROM employeesWHERE department_id = 10)
LOOP DBMS_OUTPUT.put_line (employee_rec.last_name);
You can also use a cursor FOR loop with an explicitly declared cursor:
SELECT * FROM employees WHERE department_id = 10;
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)
OPEN l_return FOR
SELECT last_name FROM employees ORDER BY employee_id;
OPEN l_return FOR
SELECT department_name FROM departments ORDER BY department_id;
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:
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:
EXECUTE IMMEDIATE 'CREATE SEQUENCE S_TEST START WITH 1 INCREMENT BY 1';
WHEN OTHERS THEN
IF SQLCODE = -955 THEN
NULL; -- suppresses ORA-00955 exception
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)
TYPE employee_ids_t IS TABLE OF employees.employee_id%TYPE
INDEX BY PLS_INTEGER;
BULK COLLECT INTO l_employee_ids
WHERE department_id = increase_salary.department_id_in;
FOR indx IN 1 .. l_employee_ids.COUNT
check_eligibility (l_employee_ids (indx),
l_eligible_ids (l_eligible_ids.COUNT + 1) :=
FORALL indx IN 1 .. l_eligible_ids.COUNT
UPDATE employees emp
SET emp.salary =
+ emp.salary * increase_salary.increase_pct_in
WHERE emp.employee_id = l_eligible_ids (indx);
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;
owa_util.mime_header( 'application/vnd.ms-excel', False ); -- Here change the content-type to PDF Format accordingly
for v_emp_cur in p_emp
htp.p (v_emp_cur.ename || ',' || v_emp_cur.deptno || chr(13));
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.
Packages an email message into the appropriate format, locates SMTP information, and delivers the message to the SMTP server for forwarding to the recipients
Represents the SEND Procedure overloaded for RAW attachments
Represents the SEND Procedure overloaded for VARCHAR2 attachments
Most used functions: Merge, NVL, Decode