Archiv für den Monat: Juni 2015

dwh-consult

PL/SQL – Native Compilation

Schreibe einen Kommentar

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;

dwh-consult

SQL%ROWCOUNT for merge

Schreibe einen Kommentar

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.