Oracle Nested Table - Create, Insert and Select

Hi all

Today i want to speak about oracle nested table.
Basically you can define in oracle a field that is a table inserted in other table. In Oracle system tables there are two tables used to manage this particular type of columns :

• USER_NESTED_TABLES              : Nested Table List
• USER_NESTED_TABLE_COLS    : Nested Table List Columns

Now an example to test it.

1) Creation a Type :

CREATE OR REPLACE TYPE VALUELIST AS TABLE OF VARCHAR2(20);

2) Creation Table :

CREATE TABLE NESTED_TABLE
(  ID            NUMBER           NOT NULL,
   FIELD1   VALUELIST)
NESTED TABLE FIELD1 STORE AS field1_tab;

3) Inserting values in table :

INSERT INTO nested_table VALUES (1, valuelist('Red'));

INSERT INTO nested_table VALUES (2, valuelist('White', 'Brown'));

INSERT INTO nested_table VALUES (3, valuelist('Blue', 'Orange','Yellow'));

4) Select values :

SELECT id, COLUMN_VALUE FROM nested_table t1, TABLE(t1.field1) t2;

5) Result :

ID    COLUMN_VALUE

1 Red

2 White

2 Brown

3 Blue

3 Orange

3 Yellow

In select statement you must reference a pseudo-column called "COLUMN_VALUE". To select values from a nested table, always you must explain mother table too.

Stay tuned.

Oracle Function to get a specific field from a String contains fields characters delimited

Hi All

Today a simple function to extract a field from a String contains characters delimited fields.
I want use regexp_substr function but there are some problems for null fields to extract, so i develop this simple function.

CREATE OR REPLACE FUNCTION
  get_field (iString IN VARCHAR2, iSeparator IN VARCHAR2, field_num IN NUMBER)
RETURN VARCHAR2
IS
   oField VARCHAR2(1024);
   position_field NUMBER;
   position_field_start NUMBER;
   position_field_end NUMBER;
BEGIN

  IF field_num = 1 THEN
     position_field :=  INSTR(iString,iSeparator);
     IF position_field > 0 THEN
        SELECT SUBSTR(iString,1,INSTR(iString,iSeparator)-1) INTO oField FROM DUAL;
     ELSE
        oField := iString;
     END IF;
  ELSE
      position_field_start :=  INSTR(iString,iSeparator,1,field_num-1);
      position_field_end :=  INSTR(iString,iSeparator,1,field_num);

      IF position_field_start > 0 THEN
      
            IF position_field_end = 0 THEN
               position_field_end := length(iString)+1;
            END IF;
           SELECT SUBSTR(iString,position_field_start+1,position_field_end-position_field_start-1) INTO oField FROM DUAL;
      ELSE
         oField := null;
      END IF; 
  END IF;

  RETURN (oField);
END;

For example you have this string '10;12;34;233;12' delimited by ';' character and you wont get third field.
You can call this oracle function in this way :

SELECT GET_FIELD('10;12;34;233;12',';',3) FROM DUAL;

Results will be : '34'.

I hope can help someone with same problem.

Stay tuned

Script SQL to calculate averange row size and total table size

Today an sql script to calculate averange row len and total table size (Is extracted from the Knowledge Xpert for Oracle Administration library).

REM LOCATION: Object Management\Tables\Utilities
REM FUNCTION: Display the average row size of table and total table
REM size
REM TESTED ON: 10.2.0.3, 11.1.0.6 (should work with Oracle 9iR2)
REM PLATFORM: non-specific
REM REQUIRES: dba_tables, dba_extents
REM
REM INPUTS: Owner of table and name of table to report on
REM NOTE: This report requires that the table be analyzed.
REM
REM
REM This is a part of the Knowledge Xpert for Oracle Administration library.
REM Copyright (C) 2008 Quest Software
REM All rights reserved.
REM
REM ******************** Knowledge Xpert for Oracle Administration ********************
UNDEF ENTER_OWNER_NAME
UNDEF ENTER_TABLE_NAME
SET PAGESIZE 66 HEADING ON VERIFY OFF
SET FEEDBACK OFF SQLCASE UPPER NEWPAGE 3
UNDEF ENTER_OWNER_NAME
UNDEF ENTER_TABLE_NAME
COLUMN table_name format a30 wrap
COLUMN avg_row_len format 9,999,999,999 heading "Average|Row|Length"
COLUMN actual_size_of_data format 9,999,999,999 heading "Total|Data|Size"
COLUMN total_size format 9,999,999,999 heading "Total|Size|Of|Table"
TTITLE left _date center "Table Average Row Length and Total Size Report"
WITH table_size AS
(SELECT owner, segment_name, SUM (BYTES) total_size
FROM dba_extents
WHERE segment_type = 'TABLE'
GROUP BY owner, segment_name)
SELECT table_name, avg_row_len, num_rows * avg_row_len actual_size_of_data,
b.total_size
FROM dba_tables a, table_size b
WHERE a.owner = UPPER ('&INSERT_OWNER_NAME')
AND a.table_name = UPPER ('&INSERT_TABLE_NAME')
AND a.owner = b.owner
AND a.table_name = b.segment_name;

Stay tuned.

Script sql for sqlldr

Hi

This is a script sql to create a dat and a ctl file to load data on an oracle table from a user to another user.

I searched this on internet without success so i had wrote it.

Enjoy :

--

-- Created by Giovanni Palleschi - Ver. 1.0 - 10/10/2011

--

-- This script sql permits to create files dat and ctl for sqlldr oracle utility

-- Script accepts in input two parameters :

--

-- 1) Table Name

-- 2) Condition to extract

--

-- Ej. sqlplus pippo/pluto @gpgen_sqlloader.sql TABLE1 "1=1"

--

-- In this mode will be extracted all rows from table TABLE1.

-- Script will produce two files :

--

-- ./TABLE1.dat

-- ./TABLE1.ctl

--

--

set echo off

set termout off

set feedback off

SET serverout ON size unlimited

set linesize 8192

set pagesize 0

set verify off

set heading off

host echo ' Start Generacion sqlloader files for table &1 and condition &2'

host echo ' '

host echo ' '

host echo ' ......... Working ......... '

host echo ' '

host echo ' '

spool ./gen_dat.wrk

DECLARE

-- TO MODIFY

Separator VARCHAR2(1) := CHR(29);

DateFormat VARCHAR2(20) := 'YYYYMMDD HHMISS';

CURSOR UserTabColumns_cursor ( TableName IN VARCHAR2 )

IS

SELECT

COLUMN_ID,

COLUMN_NAME,

DATA_TYPE,

DATA_LENGTH

FROM USER_TAB_COLUMNS

WHERE TABLE_NAME = TableName ORDER BY COLUMN_ID;

recUserTabColumns UserTabColumns_cursor%ROWTYPE;

BEGIN

DBMS_OUTPUT.PUT_LINE('###QUERY###SET LINESIZE 8192');

DBMS_OUTPUT.PUT_LINE('###QUERY###SET PAGESIZE 0');

DBMS_OUTPUT.PUT_LINE('###QUERY###SET HEADING OFF');

DBMS_OUTPUT.PUT_LINE('###QUERY###SELECT ');

DBMS_OUTPUT.PUT_LINE('###CTRL###LOAD DATA');

DBMS_OUTPUT.PUT_LINE('###CTRL###INFILE ''./&1..dat''');

DBMS_OUTPUT.PUT_LINE('###CTRL###BADFILE ''./LOG/&1..bad''');

DBMS_OUTPUT.PUT_LINE('###CTRL###DISCARDFILE ''./LOG/&1..dsc''');

DBMS_OUTPUT.PUT_LINE('###CTRL###APPEND');

DBMS_OUTPUT.PUT_LINE('###CTRL###INTO TABLE &1');

DBMS_OUTPUT.PUT_LINE('###CTRL###FIELDS TERMINATED BY '''|| Separator || '''');

DBMS_OUTPUT.PUT_LINE('###CTRL###TRAILING NULLCOLS');

DBMS_OUTPUT.PUT_LINE('###CTRL###(');

OPEN UserTabColumns_cursor('&1');

LOOP

FETCH UserTabColumns_cursor INTO recUserTabColumns;

EXIT WHEN UserTabColumns_cursor%NOTFOUND;

IF recUserTabColumns.COLUMN_ID > 1 THEN

DBMS_OUTPUT.PUT_LINE('###QUERY###||''' || Separator || '''||');

DBMS_OUTPUT.PUT_LINE('###CTRL###,');

END IF;

IF recUserTabColumns.DATA_TYPE = 'DATE' THEN

DBMS_OUTPUT.PUT_LINE('###QUERY### to_char(' || recUserTabColumns.COLUMN_NAME || ',''' || DateFormat || ''')');

DBMS_OUTPUT.PUT_LINE('###CTRL###' || recUserTabColumns.COLUMN_NAME || ' DATE ' || '"' || DateFormat ||'"');

ELSIF recUserTabColumns.DATA_TYPE IN ('LONG RAW','LONG','RAW') THEN

DBMS_OUTPUT.PUT_LINE('###CTRL### ' || recUserTabColumns.COLUMN_NAME);

DBMS_OUTPUT.PUT_LINE('###QUERY### ''''');

ELSIF recUserTabColumns.DATA_TYPE in ('VARCHAR2','NVARCHAR2','NCHAR','CHAR') THEN

DBMS_OUTPUT.PUT_LINE('###QUERY### REPLACE(' || recUserTabColumns.COLUMN_NAME || ',CHR(10),'' '')');

DBMS_OUTPUT.PUT_LINE('###CTRL### ' || recUserTabColumns.COLUMN_NAME || ' CHAR(' || recUserTabColumns.DATA_LENGTH || ')');

ELSE

DBMS_OUTPUT.PUT_LINE('###QUERY### ' || recUserTabColumns.COLUMN_NAME);

DBMS_OUTPUT.PUT_LINE('###CTRL### ' || recUserTabColumns.COLUMN_NAME);

END IF;

END LOOP;

CLOSE UserTabColumns_cursor;

DBMS_OUTPUT.PUT_LINE('###QUERY###FROM &1 WHERE &2;');

DBMS_OUTPUT.PUT_LINE('###CTRL###)');

EXCEPTION

WHEN OTHERS THEN

RAISE_APPLICATION_ERROR(-20001,

'Oracle Error MSG: >' || SQLERRM(SQLCODE) || '<');

END;

/

spool off

host grep "###QUERY###" ./gen_dat.wrk > ./gen_dat.wrk2

host sed -e "s/###QUERY###//" -e "s/ *$//" ./gen_dat.wrk2 > ./gen_dat.sql

host grep "###CTRL###" ./gen_dat.wrk > ./gen_dat.wrk2

host sed -e "s/###CTRL###//" -e "s/ *$//" ./gen_dat.wrk2 > ./&1..ctl

spool ./gen_dat.wrk

@./gen_dat.sql

spool off

host sed -e "s/ *$//" ./gen_dat.wrk > ./&1..dat

--host rm -fr ./gen_dat.wrk

--host rm -fr ./gen_dat.wrk2

--host rm -fr ./gen_dat.sql

host echo ' '

host echo ' '

host echo ' End Generacion sqlloader files for table &1 and codition &2 '

host echo ' '

host echo ' sqlldr userid=user/password control=./&1..ctl log=./&1..log'

host echo ' '

host echo ' '

quit;

Stay tuned.