Tag Archives: ORACLE

EXECUTE IMMEDIATE with USING and OUT Parameter

If you want to use an OUT – Parameter in an EXECUTE IMMEDIATE – Statement including an anonymous PL/SQL-Block: here is an example:

create table in_out_test ( num_ber number);


set serveroutput on;
DECLARE
vc_sql CLOB;
p_insert_count NUMBER;
BEGIN
vc_sql := '
DECLARE
cursor curData is
with resNmb(iNmb) as
(select 1 as iNmb from dual
union all
select iNmb + 1 from resNmb where iNmb < 1005
)
select iNmb as num_ber from resNmb;
type typeTblData is table of curData%ROWTYPE;
tblData typeTblData;
iterator NUMBER := 0;
insert_count NUMBER;
begin
open curData;
loop
fetch curData bulk collect into tblData limit 100;
exit when tblData.COUNT = 0;
forall ii in tblData.FIRST..tblData.LAST
INSERT INTO in_out_test
values tblData(ii) ;
insert_count := (iterator*100)+Sql%Rowcount;
:1 := insert_count;
commit;
iterator := iterator +1;
end loop;
close curData;
exception
when others then
rollback;
if curData%ISOPEN then close curData; end if;
raise;
end;
';
execute immediate vc_sql using out p_insert_count;
dbms_output.put_line('IN OUT PARAMETER: '||p_insert_count||' rows inserted');
END;
/

>> PL/SQL procedure successfully completed.
>> IN OUT PARAMETER: 1005 rows inserted

drop table in_out_test purge;

SQL Developer 4.2 Early Adopter

Since a few days it is possible to download the new SQL Developer 4.2 in the Early Adopter Edition.

Infos about the new features can be found here by ORACLE or by ThatJeffSmith.

Here the list of new features coming from ORACLE:

  • In-Line Editing: simply arrow around your currrent statement or script without needing to invoke an external editor
  • Automatic Formatting: SQL output can be formatted to fit your screen or to CSV, JSON, Delimited, HTML, Insert statements, or XML
  • New Commands: DDL, CTAS, REPEAT, INFO (an updated version of DESC), and many more. Use the HELP command for a full list.
  • Client-Side Scripting: Use JavaScript in combination with SQL and PL/SQL
  • SQL History: Records previous 100 executed statements and scripts and can be recalled with up and down arrow keys
  • Tab Completion: Get assitance with object names, keywords, and more with the TAB key
  • Oracle Database Exadata Express Cloud Service support
  • New command, SET ENCODING. Example, SET ENCODING UTF-8, allows for reading a file for a certain encoding
  • SSHTUNNEL now allows user to change default SSH port 22. Example, username@hotname:port
  • INFO and CTAS commands now support TAB completion for object names.
  • SET WALLET no longer requires a password for the wallet.

PL/SQL-Function for SVN-Revision

If you are using Subversion-Keywords in you PLSQL-Project you can automatically get the info for all these Keywords for Packages, Procedures and Functions.

Just use a function get_svn_info to find out, which version, date and so on are located actually in you database:

Funcion-Declaration for the Package-Header:

/**
*
* Gibt die Subversion-Informationen des Package-Bodies zurueck
* @is_type mögliche Ausprägungen:
* 'version','url','autor','datum','all'
* default: 'version'
* @return angeforderte Subversion-Info des Package Bodies
*/
FUNCTION  get_svn_Info
(is_type                     IN VARCHAR2 DEFAULT 'version')
RETURN VARCHAR2;

 

Record-declaration in the Package-Body:

TYPE typ_svn_info IS RECORD (
version  VARCHAR2 (256) := '$Rev: 209690 $',
autor    VARCHAR2 (256) := '$Author: authorname $',
datum    VARCHAR2 (256) := '$Date: 2016-02-09 09:32:47 +0100 (Di, 09. Feb 2016) $',
url      VARCHAR2 (256) := '$URL: svn://dir1/oracle/trunk/myschema/MYSCHEMA.MYPACKAGE.packbody $'
);

And the function itself in the Package-Body:

/**
*
* Gibt die Subversion-Informationen des Package-Bodies zurueck
* @is_type mögliche Ausprägungen:
* 'version' oder 'revision','url','autor','datum','name','all'
* default: 'version'
* @return angeforderte Subversion-Info des Package Bodies
*/
FUNCTION  get_svn_Info
(is_type                     IN VARCHAR2 DEFAULT 'version')
RETURN VARCHAR2
is

c_convert   varchar2 (4000);
infos       typ_svn_info;

begin

case lower(is_type)
when 'version' then     c_convert := infos.version;
when 'revision' then    c_convert := infos.version;
when 'url' then         c_convert := infos.url;
when 'autor' then       c_convert := infos.autor;
when 'datum' then       c_convert := infos.datum;
when 'name'  then       c_convert := substr (infos.url,instr(infos.url,'/',-1)+1); -- name = part after the last slash
when 'all' then
c_convert := get_svn_Info('url')
||' Ver.'||get_svn_Info('version')
||' von:'||upper(get_svn_Info('autor'))
||' letzte Aenderung:'||get_svn_Info('datum')
;
return c_convert;
else
return 'Parameter existiert nicht! (all, autor, url, datum, version, name)';
end case;

c_convert := replace(c_convert,'$','');
c_convert := trim(substr(c_convert,instr(c_convert,':',1,1)+1));
return c_convert;
exception
when others then return '-1';
end get_svn_info;

Examplecall:

set serveroutput on
exec dbms_output.put_line(myschema.mypackage.get_svn_Info('name')||' '||myschema.mypackage.get_svn_Info('Revision')||' vom '||myschema.mypackage.get_svn_Info('datum')||' ('||myschema.mypackage.get_svn_Info('autor')||')');

Result:

myschema.my_package.packbody 209690 vom 2016-02-09 09:32:47 +0100 (Di, 09. Feb 2016) (authorname)

SQL%ROWCOUNT for merge

create table merge_sql_rowcount(
dummy VARCHAR2(1),
x number,
y number
);
truncate table merge_sql_rowcount;
select * from merge_sql_rowcount;

insert into merge_sql_rowcount values ('X',1,0);
commit;

set serveroutput on
begin
merge into merge_sql_rowcount using dual on (merge_sql_rowcount.dummy = dual.dummy)
when matched then update set y = y+1
when not matched then insert (x,y) values ( dual.dummy, 0 );

if sql%rowcount > 0 then
dbms_output.put_line( sql%rowcount || ' rows affected...' );
end if;
end;
/
select * from merge_sql_rowcount;

drop table merge_sql_rowcount;

>Table MERGE_SQL_ROWCOUNT created.
>Table MERGE_SQL_ROWCOUNT truncated.
>no rows selected
>1 row inserted.
>Commit complete.
>PL/SQL procedure successfully completed.
>1 rows affected…

D X Y
– ———- ———-
X 1 1

>Table MERGE_SQL_ROWCOUNT dropped.

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'))
);

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