.NET/ASP.NET/C#/VB.NET PDF Document SDK

You ll examine the main Oracle data transformation techniques in more detail in the following sections.

create barcode in excel 2016, barcode formula excel 2010, how to install barcode font in excel 2010, barcode formula for excel 2007, barcode font excel 2010 free, free barcode generator excel 2007, how to create barcode in microsoft excel 2013, barcode maker excel 2007, barcode excel, barcode excel 2010 microsoft,

It s common to derive your new transformed data from existing tables in your database or other databases. You have two basic methods you can use to derive data from another table. If you re creating the table for the very first time, you can use the CTAS method to create new tables that meet your specifications. If the table already exists in your database or another database, you can use the INSERT /* APPEND */ INTO . . . SELECT method. If the tables are in external databases, you can still use the CTAS method by using database links. Using the CTAS method simply means that you create a new table from an existing table. While you re creating the new table, you can apply certain SQL functions to the source table s columns, thereby transforming the data in the process. The following is a simple example showing the use of the CTAS method: SQL> CREATE TABLE new_employees AS SELECT e.empno, INITCAP(e.ename), e.sal*1.1, e.mgr, d.deptno, d.loc, d.dname FROM emp e, dept d WHERE e.deptno=d.deptno; Table created. SQL> The data transformations in this example state that the employee names will all start with a capital letter (INITCAP) and the salary column will be raised by a uniform 10 percent (sal * 1.1).

The HOWOLD variable is used as the maximum age, in days, for saved core files. Any files older than this will be removed. The FSLIST variable contains the list of file systems that will be checked for core files. The list will vary from system to system. You could set FSLIST dynamically by using the df command to determine the locally mounted file systems. The command might look something like this: FSLIST=`df -l | grep '^/dev' | awk '{print $6}'`, which gathers the lines starting with /dev and then prints the field containing the file-system name. The other four variables contain the name of the system that the script is running on (UNAME), the current date (DATE), the directory to which the core files will be saved (DATADIR), and the name of the log file (LOGFILE).

The next example shows how to load data into an existing table from another table. The use of the NOLOGGING and PARALLEL options in the example make the bulk insert run extremely fast. SQL> INSERT /*APPEND NOLOGGING PARALLEL */ INTO sales_data SELECT product_id, customer_id, TRUNC(sales_date), discount_rate, sales_quantity, sale_price FROM sales_history; SQL> Note that even though you used the PARALLEL hint in the preceding INSERT statement, Oracle may not execute your INSERT statement in parallel because, by default, parallel mode is disabled. You first must use the following statement so any DML statements you issue can be considered for parallel execution: SQL> ALTER SESSION ENABLE PARALLEL DML; Session altered. SQL> Once you have enabled parallel DML in your session, you can use the PARALLEL hint in your DML statements, and Oracle will parallelize its execution.

Caution There are several restrictions on the use of parallel DML. For example, you can t use parallel DML on a table that has triggers. Refer to Oracle s documentation for more information about conditions that may preclude the use of the parallel DML feature.

It s common to use SQL statements to perform various kinds of data transformations. You can transform data by using simple UPDATE statements, although they could take a considerable time to execute in large tables. For smaller transactions in OLTP databases, the UPDATE statement is adequate when you need to transform data in a column based on some criteria. In the following sections, you ll explore some of the other common ways of using SQL to transform your data before loading: the MERGE statement, multitable inserts, and table functions.