Alle Beiträge von ghg

dwh-consult

Dml error logging

Schreibe einen Kommentar

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

dwh-consult

Compiling Objects When to use a Slash

Schreibe einen Kommentar

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.

dwh-consult

SQL Developer Standardpath for open and close file dialog box

Schreibe einen Kommentar

SQL Developer Standardpath for open and close file dialog box

It is very time consuming to click a long path to the directory to want to because sql-developer does not save the last path in the open/save-file dialog-box. I didn’t find infos about how and when a favorite-icon appears in the file-dialog.box. A workaround is to use a favorite-icon which appears in the file-dialog-box under the name ‚SQL‘.

This can be done as follows:

German: Menü: [Extras]->[Voreinstellungen]->[Datenbank]->[Arbeitsplatt]
English: Menu: [Tools]->[Preferences]->[Database]->[Worksheet]

here is the formular-field which can bee filled by opening a file-dialog-box to choose a directory.

German: 'Standardpfad zur Suche nach Skripten wählen'
English: 'Select default path to look for scripts'

Please select a directory in the directory you want to be in by opening file save/open – dialog box.

For example:
To open the path: 'C:\sql'
you have to select a directory as for example 'C:\sql\tmp'.

Now you can jump to your needed path by clicking on the ’sql‘-favorite-icon in the open/save-file-box.

Testet with SQL-Developer 3.2.09

dwh-consult

Deleting Data depending on second Table

Schreibe einen Kommentar

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;