Schlagwort-Archive: SQL

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;

Rerunable deploy-scripts

Rerunable alter-Scripts which catches the exceptions for deployment to avoid delta-deployment-scripts

In a lot of Projects I see a Testdatabase which gets the new Deployment and afterwards the developer have to make delta-deployments to get the testmachine to the most current version.
There are several solutions:

  • The better one of course is to get always a fresh copy of the current-production-version to be able to deploy the new version the first time.
  • A Solution can be to check for the last version and to deploy all necessary delta-deployments serial to get to the latest version automatically.
  • Another Solution is to run the same scripts again and again and catch all expected exceptions

— create table
DECLARE OBJECTNAME_ALEREADY_EXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT (OBJECTNAME_ALEREADY_EXISTS, -00955); -- -00955: name is already being used by existing object
BEGIN
EXECUTE IMMEDIATE q'[create table [TABLENAME]
([ATTRNAME] [TYPE],
[ATTRNAME] [TYPE]
)]';
EXCEPTION WHEN OBJECTNAME_ALEREADY_EXISTS THEN DBMS_OUTPUT.PUT_LINE('OK: Objekt(name) already existing');
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/

— drop column
DECLARE COLUMN_ALREADY_EXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT (COLUMN_DOES_NOT_EXIST, -00904); --ORA-01430: ORA-00904: "DESCR": invalid identifier
BEGIN
EXECUTE IMMEDIATE 'alter table [table] drop [column] colname';
EXCEPTION WHEN COLUMN_DOES_NOT_EXIST THEN DBMS_OUTPUT.PUT_LINE('OK: column does not exist in table, already dropped?');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

— add column
DECLARE COLUMN_ALREADY_EXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT (COLUMN_ALREADY_EXISTS, -01430); --ORA-01430: column being added already exists in table
BEGIN
EXECUTE IMMEDIATE 'alter table [table] add [column] [coltype]';
EXCEPTION WHEN COLUMN_ALREADY_EXISTS THEN DBMS_OUTPUT.PUT_LINE('OK: column being added already exists in table');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

— make column not null
DECLARE COLUMN_ALREADY_NOT_NULL EXCEPTION;
--ORA-01442: column to be modified to NOT NULL is already NOT NULL
PRAGMA EXCEPTION_INIT (COLUMN_ALREADY_NOT_NULL, -01442);
BEGIN
EXECUTE IMMEDIATE 'alter table [table] modify [column] not null';
EXCEPTION
WHEN COLUMN_ALREADY_NOT_NULL THEN DBMS_OUTPUT.PUT_LINE('OK: column to be modified to NOT NULL is already NOT NULL');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

–Insert new row
BEGIN
insert into [table] ([column], [column]) values([colvalue1],[colvalue2]);
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('OK: Row already inserted (DUP_VAL_ON_INDEX)');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

— add Constraint Unique Key
DECLARE UNIQUE_KEY_ALREADY_EXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT (UNIQUE_KEY_ALREADY_EXISTS, -02261); -- -02261: such unique or primary key already exists in the table
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE [table] ADD CONSTRAINT [CONSTRAINT_NAME] UNIQUE ( [column])';
EXCEPTION WHEN UNIQUE_KEY_ALREADY_EXISTS THEN DBMS_OUTPUT.PUT_LINE('OK: unique or primary key already exists');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

— add Unique Index
DECLARE UNIQUE_KEY_ALREADY_EXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT (UNIQUE_KEY_ALREADY_EXISTS, -02261); -- -02261: such unique or primary key already exists in the table
NAME_ALREADY_EXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT (NAME_ALREADY_EXISTS, -00955); -- -00955: name is already used by an existing object
BEGIN
EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX UI_WFID ON TBGLOBALSTATUS (WFID)';
EXCEPTION WHEN UNIQUE_KEY_ALREADY_EXISTS THEN DBMS_OUTPUT.PUT_LINE('OK: unique or primary key already exists');
WHEN NAME_ALREADY_EXISTS THEN DBMS_OUTPUT.PUT_LINE('OK: unique or primary key already exists');
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/

— add check constraint
DECLARE CONSTRAINT_NAME_ALREADY_EXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT (CONSTRAINT_NAME_ALREADY_EXISTS, -02264); -- -02264: name already used by an existing constraint
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE [table] ADD CONSTRAINT CHECK ([column] IS NOT NULL) ENABLE';
EXCEPTION
WHEN CONSTRAINT_NAME_ALREADY_EXISTS THEN DBMS_OUTPUT.PUT_LINE('OK: name already used by an existing constraint');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

–add REF-CONSTRAINT / FOREIGN KEY
DECLARE REFCONSTRAINT_ALEREADY_EXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT (REFCONSTRAINT_ALEREADY_EXISTS, -02275); -- -02275: such a referential constraint already exists in the table
PARENT_KEY_NOT_FOUND_1 EXCEPTION;
PRAGMA EXCEPTION_INIT (PARENT_KEY_NOT_FOUND_1, -02291); -- -02291: integrity constraint (string.string) violated - parent key not found
PARENT_KEY_NOT_FOUND_8 EXCEPTION;
PRAGMA EXCEPTION_INIT (PARENT_KEY_NOT_FOUND_8, -02298); -- -02298: cannot validate (string.string) - parent keys not found
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE [table] ADD CONSTRAINT FOREIGN KEY ( [columns] ) REFERENCES

( [column] )';
EXCEPTION WHEN REFCONSTRAINT_ALEREADY_EXISTS THEN DBMS_OUTPUT.PUT_LINE('OK: REF-Constraint (FK) already exists');
WHEN PARENT_KEY_NOT_FOUND_1 OR PARENT_KEY_NOT_FOUND_8 THEN
DBMS_OUTPUT.PUT_LINE('Not able to create foreign-key-constraint: 02291: parent key not found. Following rows in parent missing:');
FOR currow in ( select [columns] from [table] minus select [columns] from [table]) --child minus parent
LOOP
dbms_output.put_line('xml_blobno: '||currow.[columns]);
END LOOP;
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/

— create INDEX
DECLARE NAME_ALREADY_USED EXCEPTION;
PRAGMA EXCEPTION_INIT (NAME_ALREADY_USED, -00955); -- -00955: name is already used by an existing object
BEGIN
EXECUTE IMMEDIATE 'CREATE INDEX [INDEX] ON [TABLE]([column]) ';
EXCEPTION WHEN NAME_ALREADY_USED THEN DBMS_OUTPUT.PUT_LINE('OK: name is already used by an existing object');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

–Insert new Job
DECLARE
v_job_id number := 99;
v_jobtask VARCHAR2(500) := 'myschema.PKG_MYPACKAGE.myprocedure(p_mypara => ''NO'');';
v_next_date date := trunc(SYSDATE)+1 + 1/24; /* 01:00*/
v_interval varchar2(50) := 'SYSDATE + 1 /* daily */';
v_count number;
BEGIN
select count(*)
into v_count
from user_jobs
where what = v_jobtask
or job = v_job_id;

if v_count = 0 then

DBMS_JOB.isubmit (
job => v_job_id,
what => v_jobtask,
next_date => v_next_date,
interval => v_interval
);
COMMIT;
dbms_output.put_line('job '||v_job_id||': '||v_jobtask||' inserted and commited!');
else
DBMS_JOB.change (
job => v_job_id,
what => v_jobtask,
next_date => v_next_date,
interval => v_interval
);
COMMIT;
bms_output.put_line('job '||v_job_id||': '||v_jobtask||' changed already inserted job!');
end if;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
end;
/

Published on: Jul 4, 2014 @ 9:08
Updated on: Nov 20, 2014 @ 9:48
Updated on: Nov 26, 2014 @ 12:31
Updated on: Dec 02, 2014 @ 14:48
Updated on: Dec 09, 2014 @ 17:12

Dml error logging

Dml error logging

DML-Error-Logging is a feature available since ORACLE DB 10.2.

Creating the error-log-table:

exec dbms_errlog.create_error_log('tablename','tablename_err') ;

Logging errors at insert/update/merge/delete-statement:

Structure of the Error-Log-Table

Name Null? Type
----------------------------------------- -------- ----------------------------
ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$ VARCHAR2(2000)
ORA_ERR_ROWID$ ROWID
ORA_ERR_OPTYP$ VARCHAR2(2)
ORA_ERR_TAG$ VARCHAR2(2000)
VARCHAR2(4000)
VARCHAR2(4000)
VARCHAR2(4000)
<...>

Example:

insert into tablename (att1, att2, att3)
values (i, j, k)
LOG ERRORS INTO tablename_err REJECT LIMIT 1;

REJECT LIMIT: threshold amount of errors the statement shall stop. That means how many errors are allowed… Possible values: Integer/“UNLIMITED“

Default Value is 0! So it is always necessary to add this for a real error-logging. By default the first error is logged into the error-table and the dml-action is aborted.

Comment or Tagging of the error

insert into tablename (att1, att2, att3)
values (i, j, k)
LOG ERRORS INTO tablename_err
(TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS')) REJECT LIMIT 50;

There is the possibility to have an comment on the logged error in brackets after the error-logging-table (here filled with sysdate). This comment is inserted in the error-logging-table-attribute ORA_ERR_TAG$ VARCHAR2(2000 BYTE). It is needed to convert the content into varchar2!

Oracle-Docu 10g:
http://docs.oracle.com/cd/B19306_01/server.102/b14231/tables.htm#sthref2223
Oracle-Docu 11gR2:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN10261
Oracle-Docu 12gR1:
http://docs.oracle.com/database/121/ADMIN/tables.htm#ADMIN-GUID-36DB026B-4702-477A-92C4-EA2795D2B37F

Compiling Objects When to use a Slash

Compiling Objects When to use a slash ‚/‘

There was always a diffuse toppic for me in sqlscripts for sqlplus:
When to use a forwardslash (‚/‘) after a code-block and when not.

A simple marker:

Use a slash after every PL/SQL -block:

– Package
– Procedure
– Function

Example:
create or replace procedure procname as
null;
end procname;
/

Use NO slash after all DML -blocks:

– Table
– Index
– Constraint
– Sequence
– View
– Materialized View
– …

If there is an ‚Create or replace‘ in the first line of the code to compile there is no problem, but the object will be recreated unnecessarily.
But in the case of an index for example there will be an exception.

Deleting Data depending on second Table

Deleting Data depending on second Table

The task was to delete data in a table dependend on a join to another table. At first I got the Error: ORA-01752 (cannot delete from view without exactly one key-preserved table). My solution was to do the deletion with an korrelated select:

drop table DELETE_TEST;
CREATE TABLE DELETE_TEST
(
id number,
REPORTINGPERIOD NUMBER,
BRANCHCODE VARCHAR2(10 BYTE)
);
drop table DELETE_CONFIG;
CREATE TABLE DELETE_CONFIG
(
REPORTINGPERIOD NUMBER,
BRANCHCODE VARCHAR2(10 BYTE)
);

INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(1,201201,'BC1');
insert into DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) values(2,201202,'BC1');
insert into DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) values(3,201203,'BC1');
insert into DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) values(4,201204,'BC1');
insert into DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) values(5,201205,'BC1');
insert into DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) values(6,201206,'BC1');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(7,201207,'BC1');

INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(21,201201,'BC2');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(22,201202,'BC2');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(23,201203,'BC2');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(24,201204,'BC2');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(25,201205,'BC2');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(26,201206,'BC2');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(27,201207,'BC2');

INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(31,201201,'BC3');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(32,201202,'BC3');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(33,201203,'BC3');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(34,201204,'BC3');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(35,201205,'BC3');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(36,201206,'BC3');
INSERT INTO DELETE_TEST(ID,REPORTINGPERIOD,BRANCHCODE) VALUES(37,201207,'BC3');
COMMIT;

-- BranchCode 1 - Data has to be deleted before 201206
INSERT INTO DELETE_CONFIG(REPORTINGPERIOD,BRANCHCODE) VALUES(201206,'BC1');
-- BranchCode 2 - Data has to be deleted before 201205
INSERT INTO DELETE_CONFIG(REPORTINGPERIOD,BRANCHCODE) VALUES(201205,'BC2');
-- BranchCode 3 - Data has to be deleted before 201204
INSERT INTO DELETE_CONFIG(REPORTINGPERIOD,BRANCHCODE) VALUES(201204,'BC3');
commit;

DELETE
FROM (SELECT *
FROM DELETE_TEST T
WHERE t.REPORTINGPERIOD < (select c.REPORTINGPERIOD from DELETE_CONFIG C where C.BRANCHCODE = t.branchcode) ); COMMIT;

Date Logic

Tagessekunde, Monatssekunde…
Second of the Day, Second of the Months …

select sysdate
, TO_CHAR(SYSDATE,'HH24')*3600 AS WHOLE_HOURS_IN_S
, TO_CHAR(SYSDATE,'MI')*60 AS WHOLE_MINUTES_IN_S
, TO_CHAR(SYSDATE,'SS') AS CURRENT_SECOND
, TO_CHAR(SYSDATE,'HH24')*3600 + TO_CHAR(SYSDATE,'MI')*60 + TO_CHAR(SYSDATE,'SS') AS SECOND_OF_THE_DAY
, TO_CHAR(SYSDATE,'DD') AS WHOLE_DAY_OF_THE_MONTH
, TO_CHAR(SYSDATE,'DD') * 86400 AS SECONDS_WHOLE_DAYS_OF_MONTHS
, (TO_CHAR(SYSDATE,'DD') * 86400) + (TO_CHAR(SYSDATE,'HH24')*3600 + TO_CHAR(SYSDATE,'MI')*60 + TO_CHAR(SYSDATE,'SS')) AS SECONDS_OF_THE_MONTH
from dual;

Temporary tables

Prinzip der temporären Tabellen

Die Daten werden automatisch vom Datenbanksystem gelöscht.
Die Lebensdauer ist einstellbar: bis zum Ende der laufenden Transaktion oder bis zum Ende der Session.
Die Daten der parallel laufenden Sessions sind automatisch vollständig voneinander getrennt. Es ist nicht notwendig, die Daten über zusätzliche Spalten den einzelnen Sessions zuzuordnen.
Aufgrund der automatischen Trennung der Daten zwischen den einzelnen Sessions sind keine Sperren notwendig und auch nicht verfügbar.
Typische Anwendung: komplexe Auswertungen, die Zwischendaten halten müssen.
Es werden keine Redo-Informationen geschrieben.
Die Verwendung von temporären Tabellen bietet deutliche Performance-Vorteile gegenüber „normalen“ Tabellen.

Wichtigste Einschränkungen

Es sind keine foreign key constraints mit temporärer Tabelle als Ziel erlaubt.
Eine temporäre Tabelle ist nicht als index-organized table definierbar.

Beispiele

Daten werden bis Transaktionsende gehalten

create global temporary table tmp_artikel (
artikel_nr number(5),
umsatz number(10,2))
on commit delete rows;

Die Daten können mit den üblichen Befehlen (insert, update, delete, select) bearbeitet werden.
Nach dem Ende der Transaktion ist die Tabelle automatisch leer.

Daten werden bis Sessionende gehalten

create global temporary table tmp_artikel (
artikel_nr number(5),
umsatz number(10,2))
on commit preserve rows;

Die Daten dieser temporären Tabelle sind so lange verfügbar, bis die Datenbank-Session beendet wird.

Escaping special Characters in SQL

Sonderzeichen/Special Character in SQL escaping/entwerten

Escaping in a SQL-Statement All Tables starting with ‚T_‘.

select * from user_tables
where table_name like 'T\_%' escape '\';

All tables without underscore (‚_‘)

select * from user_tables
where table_name not like '%\_%' excape '\';

Escaping in a SQL-Plus-Session: All Tables, starting with ‚T_‘.

set escape '\'
select * from user_tables
where table_name like 'T\_%';

Searching in Data-Dictionary-Views for Views user_views, all_views…

select * from user_views
where dbms_xmlgen.getxml
('select text from user_views where view_name = || view_name || ')
like '%w_spci_order_types%'