Table column-names in Assoziative Array

Here a Example for the usage of Assoziative Array to handle the column-names of a table automated…

set serveroutput on

create table column_list_test
( myid number
,mydesc varchar2(20)
,myname varchar2(100)
,mydate date
);

DECLARE
v_table_name varchar2(30) := 'column_list_test';
TYPE v_column_names_type IS TABLE OF varchar2(30) INDEX BY BINARY_INTEGER;
v_column_names v_column_names_type;
v_index BINARY_INTEGER;
v_col_list varchar2(4000);
BEGIN
--initialize table-names-array
FOR currow in (select column_id,column_name
from user_tab_columns
where 1=1
and table_name = upper(v_table_name)
order by table_name,column_id)
LOOP
v_column_names(currow.column_id) := currow.column_name;
END LOOP;

-- loop through the names-array
v_index := v_column_names.first;
while v_index is not NULL loop

dbms_output.put_line(v_index || ' - ' ||v_column_names(v_index));
v_col_list := v_col_list || v_column_names(v_index);

-- next index:
v_index := v_column_names.next (v_index);

-- add a comma only if it is not the last attribute.
if nvl(v_index,0) <> 0 then
v_col_list := v_col_list ||',';
end if;
end loop;

--complete col-list comma separated for further dynamic-sql...
dbms_output.put_line(v_col_list);

EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;

Output:
table COLUMN_LIST_TEST created.
anonymous block completed
1 - MYID
2 - MYDESC
3 - MYNAME
4 - MYDATE
MYID,MYDESC,MYNAME,MYDATE

Byte semantic vs length semantic

Byte semantic vs length semantic

The database-parameter NLS_LENGTH_SEMANTIC shows starting with ORACLE 9i the default value of the used semantic for the lenth of table-attributes.

SQL> show parameter NLS_LENGTH_SEMANTICS

NAME TYPE VALUE
-------------------- ----------- ------
nls_length_semantics string BYTE

Byte means: The reserved storage per char is here in Byte as a default. This default-value is used for at least varchar2- and char-fields.

varchar2(1)

Here explicitly used:

varchar2(1 BYTE)

So if the attribute has an length of 1 (Byte) and you use Character-Set AL32UTF8 a Char has a length of 1,2 3 or 4 byte and so you will never be able to insert a special-character having a length more than 1 Byte in your table…

But if you use CHAR as length-semantic the available storage is big enough for storing any char.

varchar2(1 CHAR)

Important
Excerpt ORACLE-Docu:
Sessions logged in as SYS do not use the NLS_LENGTH_SEMANTICS parameter. They use BYTE length semantics for all created objects unless overridden by the explicit BYTE and CHAR qualifiers in object definitions (SQL DDL statements).

Here all the examples:

Used the default-value of NLS_LENGTH_SEMANTICS:

create table table_attribute_default (field1 varchar2(10));
SELECT column_name,
data_type,
data_length,
char_used,
decode(char_used,'B','BYTE','C','CHAR') semantic
FROM all_tab_columns
WHERE table_name = 'TABLE_ATTRIBUTE_DEFAULT';

COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_USED SEMANTIC
----------- --------- ----------- --------- --------
FIELD1 VARCHAR2 10 B BYTE

Used BYTE as length-semantic:

create table table_attribute_byte (field1 varchar2(10 BYTE));
SELECT column_name,
data_type,
data_length,
char_used,
decode(char_used,'B','BYTE','C','CHAR') semantic
FROM all_tab_columns
WHERE table_name = 'TABLE_ATTRIBUTE_BYTE';

COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_USED SEMANTIC
----------- --------- ----------- --------- --------
FIELD1 VARCHAR2 10 B BYTE

Used CHAR as length-semantic:

create table table_attribute_char (field1 varchar2(10 CHAR));
SELECT column_name,
data_type,
data_length,
char_used,
decode(char_used,'B','BYTE','C','CHAR') semantic
FROM all_tab_columns
WHERE table_name = 'TABLE_ATTRIBUTE_CHAR';

COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_USED SEMANTIC
----------- --------- ----------- --------- --------
FIELD1 VARCHAR2 40 C CHAR

DROP TABLE table_attribute_default;
DROP TABLE table_attribute_byte;
DROP TABLE table_attribute_char;

Sqldeveloper Language

Sqldeveloper Language
To change the GUI-Language without NLS-Parameters to english add the following lines to

[sqldev-install-dir]/sqldeveloper/bin/sqldeveloper.conf:

AddVMOption -Duser.language=en
AddVMOption -Duser.country=US

Tested for SQL-Developer 1.5x, 2.1.x, 3.0.x, 3.1.x, 4.0.x, 4.1.0, 4.1.1

Entries in sqldeveloper/ide/bin/ide.conf will also change NLS-Parameter!

INTERVAL-LIST Composite Partitioning

ORACLE 11g: INTERVAL-LIST Composite Partitioning

For the automatic creation of new Partitions in the Intervalpartitioning there are two options:
NUMTOYMINTERVAL converts number n to an INTERVAL YEAR TO MONTH literal. (Use ‚YEAR‘ or ‚MONTH‘)
NUMTODSINTERVAL converts n to an INTERVAL DAY TO SECOND literal. (Use ‚DAY‘)

EXAMPLE

CREATE TABLE AUDIT_partitioned
(
TICKETID NUMBER(11, 0)
, CHANGETO VARCHAR2(200 BYTE)
, CHANGEDT DATE
, CHANGEUSER VARCHAR2(200 BYTE)
, ACTIONNO NUMBER(6, 0)
, ACTIONTEXT VARCHAR2(4000 BYTE)
, BANKNO VARCHAR2(10 BYTE)
)
PARTITION BY RANGE (CHANGEDT) INTERVAL(NUMTODSINTERVAL (1, 'DAY')) -- daily partitions
--PARTITION BY RANGE (CHANGEDT) INTERVAL(NUMTOYMINTERVAL (1, 'MONTH')) -- monthly partitions
SUBPARTITION BY LIST (BANKNO)
SUBPARTITION TEMPLATE
(
SUBPARTITION bank001 VALUES ('001'),
SUBPARTITION bank011 VALUES ('011'),
SUBPARTITION bank014 VALUES ('014'),
SUBPARTITION bankdef VALUES (DEFAULT)
)
( PARTITION p_genaudit_20140101 VALUES LESS THAN (TO_DATE('20140102', 'YYYYMMDD'))
);