Tag Archives: PLSQL

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;

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)

PLDOC

PLDOC is a generator which generates HTML-Docs in the format of JAVA-Doc for PL/SQL or PLSQL-Code like Packages, Procedures an Functions.
It is possible to add comments and explanations for parameter, returnvalues, exceptions and variables.

Projectsite: http://pldoc.sourceforge.net/maven-site/
Download: http://pldoc.sourceforge.net/maven-site/downloads.html
Documentation/User Guide: http://pldoc.sourceforge.net/maven-site/docs/Users_Guide/index.html


 

Example for Adding Comment to a Package(Header or Body)
CREATE OR REPLACE PACKAGE myschema.packagename
IS
/**
* <pre>Dieses Package steuert den Ablauf der Befüllung des DWHs.
*
* URL: $UR$
* Revision: $Rev$
* Author: $Author$
* Date: $Date$
* ID: $Id$
*
* MODIFICATION HISTORY
* Person Date Comments<br/>
* --------- ---------- ----------------------------------------
* Name001 01.01.2016 - Neuerstellung.
* Name002 01.02.2016 - PLDOC-Formattierung der Kommentare
* - SVN-Keywords
* </pre>
* @headcom
*/


 

Inside of the tag <pre></pre> there is no need for the line-break-tags (<br/>)

Example for Adding Comment to a Procedure/Function
/** This is a comment shown til the dot at the end of the comment or an At Sign in the next line.
* @param id This is the description of parameter "id"
* @return This is the description of the returnvalue
* @throws NO_PARTNER_FOUND This is the description of the Exception thrown
*/
FUNCTION check_object(
id IN VARCHAR2
) RETURN NUMBER;

There must be no colon (‘:’) after the name of the parameter behind @param!
This is wrong:
* @param id: This is the description of parameter "id"

You can remove the colons for example with notepad++ like described here.


 

Example-Call on Windows to generate the PL-DOC:
call pldoc.bat -doctitle 'Special Database Project Name' -d DirectoryInWhichTheDocWillBeStored -inputencoding ISO-8859-15 --overview OverviewToBeIncludedAutomatically.html SourceDirectory/*.sql SourceDirectory/*.pkb SourceDirectory/*.pks


 

Alternative doc-generators for PLSQL to evaluate can be:

 

PL/SQL – Native Compilation

Show configuration concerning native compilation of PL/SQL-Code:

SQL> show parameter PLSQL_CODE_TYPE;
NAME TYPE VALUE
--------------- ------ -----------
plsql_code_type string INTERPRETED

INTERPRETED : Default-Value, the Database will interpret the code at runtime and will not compile it into native code.
NATIVE : Native-Compilation before running the code

Change Configuration on session- or system-level:

SQL> alter system set PLSQL_CODE_TYPE=native scope=both;
System altered.

From now on, all plsql-code is compiled into native code.
scope=both : Affects MEMORY and SPFILE

Check compilation-method on PL/SQL-Object-Level:

select OWNER,NAME,TYPE,PLSQL_CODE_TYPE from all_plsql_object_settings;
select NAME,TYPE,PLSQL_CODE_TYPE from user_plsql_object_settings;

change compilation-method for one procedure/function/package
PLSQL_CODE_TYPE= native or interpreted

SQL> alter <procedure|function|package> <objectname> compile PLSQL_CODE_TYPE=native;

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

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

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.

PLSQL-Case

CASE-Ausdruck
Gibt einen Wert zurück/returns a value

set serveroutput on
declare
myid NUMBER;
myresult VARCHAR2(10);
begin
myresult :=
case
when myid=null then 'true'
when myid is null then 'is null'
else 'false'
end;
dbms_output.put_line(myresult);
END;
/

CASE-Anweisung
Führt eine Aktion aus / Processes an action

set serveroutput on
declare
myid NUMBER;
myresult VARCHAR2(10);
begin
case
when myid=null then
myresult :='true';
when myid is null then
myresult :='is null';
else myresult :='false';
end case;
dbms_output.put_line(myresult);
END;
/

Bulk Processing

CREATE OR REPLACE PROCEDURE COPY_EMPS(limit_in IN PLS_INTEGER DEFAULT 100) is
-- EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('emp_bulk_test', 'emp_bulk_errors');
-- create table emp_bulk_test as select * from employees;
-- truncate table emp_bulk_test;
CURSOR employees_cur
IS
SELECT * FROM employees;
TYPE employees_rec IS TABLE OF employees_cur%ROWTYPE
INDEX BY PLS_INTEGER;
l_rec employees_rec;
BEGIN
OPEN employees_cur;
LOOP
FETCH employees_cur
BULK COLLECT INTO l_rec LIMIT limit_in;
FOR indx IN 1 .. l_rec.COUNT
LOOP
insert into emp_bulk_test
(employee_id
,first_name
,last_name
,email
,phone_number
,hire_date
,job_id
,salary
,commission_pct
,manager_id
,department_id)
VALUES (l_rec(indx).employee_id
,l_rec(indx).first_name
,l_rec(indx).last_name
,l_rec(indx).email
,l_rec(indx).phone_number
,l_rec(indx).hire_date
,l_rec(indx).job_id
,l_rec(indx).salary
,l_rec(indx).commission_pct
,l_rec(indx).manager_id
,l_rec(indx).department_id)
LOG ERRORS INTO emp_bulk_errors
;
END LOOP;
commit;
dbms_output.put_line('commit!');
EXIT WHEN l_rec.COUNT < limit_in; END LOOP; CLOSE employees_cur; end copy_emps; /