Archiv der Kategorie: dwh-consult

dwh-consult, Versioning

Subversion Keywords (Revision,Author,URL,Date,ID)

1 Kommentar

It is useful if every (touched) DB-Object contains in the future the following lines:

Procedures/Functions must contain in the Header-Comment:

/*** URL: $URL$ */
/*** Revision: $Rev$ */
/*** Author: $Author$ */
/*** Date: $Date$ */
/*** ID: $Id$ */

Table-Create-Statements have to be enhanced with the following statement:

COMMENT ON TABLE IS ‚
URL: $URL$
Revision: $Rev$
Author: $Author$
Date: $Date$
ID: $Id$
‚;

—————————————————————————————————————————-

Explanation:

These variables will be replaced by every SVN-Checkin (Subversion-VersionsControll-System) with their pendants.
Example:
$URL$ => $URL: http://…/branches/SDLCV11726/svn-test.txt $
$Rev$ => $Rev: 13558 $
$Author$ => $Author: herzogg $
$Date$ => $Date: 2012-09-13 15:58:28 +0200 (Do, 13 Sep 2012) $
$Id$ => $Id: svn-test.txt 13558 2012-09-13 13:58:28Z herzogg $

So it is possible to see in the Database which version is the Table/Function/Procedure and so on,
what is very helpful.

—————————————————————————————————————————-
Configuration of Tortoise SVN

To activate this feature in SVN please open SVN-Settings ([context-menu]->[TortoiseSVN]->[Settings]
Then Click [EDIT] on the Subversion Configuration File on the General – Tab:

Add or uncomment the following line in this config-file in the [miscellany] – Section

enable-auto-props=yes

Add following lines in this config-file in the [auto-props] – Section
(Add new file-endings if you are using different…)
*.sql = svn:keywords=URL Rev Author Date Id
*.fnc = svn:keywords=URL Rev Author Date Id
*.prc = svn:keywords=URL Rev Author Date Id
*.pks = svn:keywords=URL Rev Author Date Id
*.pkb = svn:keywords=URL Rev Author Date Id
*.txt = svn:keywords=URL Rev Author Date Id

How to set these properties for existing files in a already running Project.

dwh-consult

Shellscript: PL/SQL-Block in SQLPLUS-Here-Document with Parameter

Schreibe einen Kommentar

#! /bin/bash

echo -n ORACLE_USER:
read ORACLE_USER
echo -n ORACLE_USER Password:
read -s ORACLE_PW

if [ -z „$1“ ]; then
echo Usage: Please call this Script like this: „./SQL_HERE_DOCUMENT_WITH_PARAMETER.sh [DESCRIPTION]“
exit
fi

sqlplus $ORACLE_USER/$ORACLE_PW << EOF
DEFINE DESCRIPTION=$1
SET SERVEROUTPUT ON
DECLARE
v_description varchar2(255):= ‚&DESCRIPTION‘;
v_output varchar2(255);
BEGIN

SELECT v_description
into v_output
from dual;
dbms_output.put_line(‚————————————————————–‚);
dbms_output.put_line(‚Given Description: ‚||v_output);
dbms_output.put_line(‚————————————————————–‚);
END;
/
EXIT;
EOF

exit;

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.

dwh-consult

Datapump: Clone Schema to another DB

Schreibe einen Kommentar

—————————————————————————————————
— Datapump clone schema to another DB
—————————————————————————————————

—————————————————————————————————
— EXPORT:
—————————————————————————————————

CREATE OR REPLACE DIRECTORY expimp_dir AS ‚/home/oracle/dmp‘;
GRANT READ,WRITE ON DIRECTORY expimp_dir TO myschema;
grant dba to myschema;

–shell-command:
expdp myschema/myschema schemas=myschema directory=expimp_dir dumpfile=myschema_expdp.dmp logfile=myschema_expdp.log

revoke dba from myschema;
revoke READ,WRITE ON DIRECTORY expimp_dir from myschema;

—————————————————————————————————
— IMPORT on a different machine!
—————————————————————————————————
— create schema on new DB
set serveroutput on
DECLARE CONFLICT_WITH_ANOTHER_USER EXCEPTION;
PRAGMA EXCEPTION_INIT (CONFLICT_WITH_ANOTHER_USER, -01920); — -01920: user name ‚myschema‘ conflicts with another user or role name
BEGIN
EXECUTE IMMEDIATE ‚CREATE USER myschema identified by myschema‘;
EXCEPTION WHEN CONFLICT_WITH_ANOTHER_USER THEN DBMS_OUTPUT.PUT_LINE(‚OK: User/Schema already existing‘);
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/
grant create session to myschema;

CREATE OR REPLACE DIRECTORY expimp_dir AS ‚/home/oracle/dmp‘;
GRANT READ,WRITE ON DIRECTORY expimp_dir TO myschema;
grant dba to myschema;

–shell-command:
impdp myschema/myschema schemas=myschema directory=expimp_dir dumpfile=myschema_expdp.dmp logfile=myschema_impdp.log

revoke dba from myschema;
revoke READ,WRITE ON DIRECTORY expimp_dir from myschema;

— Info:
Job „myschema“.“SYS_IMPORT_SCHEMA_01″ completed with 1 error(s) at Wed Jan 7 13:10:17 2015 elapsed 0 00:00:06
comes from:
ORA-31684: Object type USER:“myschema“ already exists.
So: everything o.k.!

dwh-consult

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

dwh-consult

Byte semantic vs length semantic

Schreibe einen Kommentar

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;