Oracle Database -External Table – How to populate/ outbound file creation using External table

As you know we can load data from OS flat files into the Oracle environment,Now here showing how to create an external file / populating external table

Lets take a simple example: –

Step 1: – Create a directory

SQL> create directory my_dir as ‘/usr/abc’; —

Make sure Oracle OS user hasprivilege to write on this directory.

Step 2: – Grant permission to user SCOTT

SQL> grant read,write on directory my_dir to scott;

Step 3: – Create the External Table:

SQL> CREATE TABLE scott.extract_emp_dept



LOCATION (’emp.exp’))

reject limit unlimited


select e.ename,d.dname from emp e, dept d where


SQL> select * from scott.extract_emp_dept;

And you will find a file generated i.e. ’emp.exp’ in /usr/abc directory. Now you can take this file to the target database and create an external table and associate the file with that table.

Step 4: – to be performed on the source database

SQL> set long 2000

SQL> Select dbms_metadata.get_ddl(‘TABLE’,’EXTRACT_EMP_DEPT’) from dual;

The above command will give you the DDL command to create the table at target database.

Oracle 11g – PL SQL – New features — Reference

I found a nice article, contain reference DocID from Metalink to know latest and new features of Oracle Database 11g PL/SQL

All the new features available with 11.1 release of oracle are listed together in
Note 869084.1 : New PL/SQL Features for 11g Release 1 (11.1).
The contents of the article is pasted below.

New PL/SQL Features for 11g Release 1 (11.1)

  • PL/SQL Native Compilation
    In Oracle 11g, PL/SQL native compilation requires no C compiler, no DBA intervention and is fully supported in a RAC environment. By setting the PLSQL_CODE_TYPE to a value of NATIVE , rather than the default value of INTERPRETED , code is compiled directly to machine code . When the code is called, it is loaded into shared memory, making it accessible for all sessions in that instance. The %_PLSQL_OBJECT_SETTINGS views include the current PLSQL_CODE_TYPE setting for each PL/SQL object.
    Note 462449.1 : PL/SQL Native Compilation on Oracle 11G


  • PL/SQL Function Result Cache
    The cross-session PL/SQL function result cache provides a simple way to boost the performance of PL/SQL functions by saving the results of function calls for specific combinations of input parameters in the SGA. These results can be reused by any session calling the same function with the same parameters. This can result in a significant performance boost when functions are called for each row in an SQL query, or within a loop in PL/SQL. This article describes the usage and administration of the function result cache.
    Note 430887.1  : 11g New Feature PL/SQL Function Result Cache


  • PL/SQL Fine-Grained Dependencies
    Prior to Oracle11g, modifying the structure of a table would make dependent views invalid even thought the change to table has nothing to do with view.That is why starting from Oracle11g a change to table that does not effect view logically, will NOT make view invalid. So, if you add a column to table T1 then because of fine grained dependency tracking system applied by Oracle 11g, it doesn’t make view invalid.
    Note 430725.1 : 11g New Feature Fine-Grained Dependencies


  • PL/SQL Continue Statement
    In Oracle Database 11g PL/SQL has a new construct called CONTINUE , which is used in a loop. The statement moves the logic to the end of the loop and then to the beginning of the loop.The CONTINUE statement jumps out of the current loop iteration and starts the next one. It can be used on its own, or as part of a CONTINUE WHEN statement. This type of processing has always been possible using IF statements either on their own or with exceptions or GOTO statements, but the CONTINUE statement is neater and brings PL/SQL in line with other languages.
    Note 469584.1 : 11g New Feature PLSQL CONTINUE Statement


  • PL/SQL Automatic Subprogram Inlining
    Every call to a procedure or function causes a slight, but measurable, performance overhead, which is especially noticeable when the subprogram is called within a loop. Avoiding procedures and functions is not an option, as it goes against the concept of modular programming, making programs bulky and difficult to manage. Automatic subprogram inlining can reduce the overheads associated with calling subprograms, whilst leaving your original source code in its normal modular state. This is done by replacing the subprogram calls with a copy of the code in the subprogram at compile time.
    The process of subprogram inlining is controlled by the PLSQL_OPTIMIZE_LEVEL parameter and the INLINE pragma. When PLSQL_OPTIMIZE_LEVEL=2 (the default), the INLINE pragma determines whether the following statement or declaration should be inlined or not. When PLSQL_OPTIMIZE_LEVEL=3, the optimizer may inline code automatically. In this case the INLINE pragma can turn it off inlining for a statement, or increase the likelihood that the optimizer will choose to inline a statement.


    The SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE data types are predefined subtypes of PLS_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE, respectively. Each subtype has the same range as its base type and has a NOT NULL constraint.
    SIMPLE_INTEGER differs significantly from PLS_INTEGER in its overflow semantics, but SIMPLE_FLOAT and SIMPLE_DOUBLE are identical to their base types, except for their NOT NULL constraint.
    You can use SIMPLE_INTEGER when the value will never be NULL and overflow checking is unnecessary. You can use SIMPLE_FLOAT and SIMPLE_DOUBLE when the value will never be NULL. Without the overhead of checking for nullness and overflow, these subtypes provide significantly better performance than their base types when PLSQL_CODE_TYPE=’NATIVE’, because arithmetic operations on SIMPLE_INTEGER values are done directly in the hardware. When PLSQL_CODE_TYPE=’INTERPRETED’, the performance improvement is smaller.
    Note 433091.1 : 11g New Features SIMPLE_INTEGER Datatype

  • Named and Mixed Notation in PL/SQL Subprogram Invocations
    Before Release 11.1, a SQL statement that invoked a PL/SQL subprogram had to specify the actual parameters in positional notation. As of Release 11.1, named and mixed notation are also allowed. This improves usability when a SQL statement invokes a PL/SQL subprogram that has many defaulted parameters, and few of the actual parameters must differ from their default values.
    Note 433070.1 : 11g New Feature Using Named and Mixed notation in PL/SQL subprograms


  • Enhancements to Regular Expression Built-in SQL Functions
    The regular expression built-in functions REGEXP_INSTR and REGEXP_SUBSTR have increased functionality. A new regular expression built-in function, REGEXP_COUNT, returns the number of times a pattern appears in a string. These functions act the same in SQL and PL/SQL.


  • Sequences in PL/SQL Expressions
    The pseudocolumns CURRVAL and NEXTVAL make writing PL/SQL source code easier for you and improve run-time performance and scalability. You can use sequence_name.CURRVAL and sequence_name.NEXTVAL wherever you can use a NUMBER expression.
    Note 433068.1 : Sequences in PL/SQL Expressions
  • Dynamic SQL Enhancements
    Both native dynamic SQL and the DBMS_SQL package have been enhanced.Native dynamic SQL now supports a dynamic SQL statement larger than 32 KB by allowing it to be a CLOB.
    In the DBMS_SQL package:
    • All data types that native dynamic SQL supports are supported.
    • The DBMS_SQL.PARSE function accepts a CLOB argument, allowing dynamic SQL statements larger than 32 KB.
    • The new DBMS_SQL.TO_REFCURSOR Function enables you to switch from the DBMS_SQL package to native dynamic SQL.
    • The new DBMS_SQL.TO_CURSOR_NUMBER Function enables you to switch from native dynamic SQL to the DBMS_SQL package.
    Note 435251.1   : How Native Dynamic Sql Supports Statements > 32K Characters
    Note 567856.1 : New Feature in 11g , DBMS_SQL.TO_REFCURSOR and DBMS_SQL.TO_CURSOR_NUMBER.


  • Compound Trigger
    A compound trigger allows different blocks within a trigger to be executed at different timing points. It has a declaration section and a section for each of its timing points. All of these sections can access a common PL/SQL state.
    Note 430847.1 : 11g NEW PL/SQL COMPOUND Trigger