Escaping special Characters in SQL

Sonderzeichen/Special Character in SQL escaping/entwerten

Escaping in a SQL-Statement All Tables starting with ‚T_‘.

select * from user_tables
where table_name like 'T\_%' escape '\';

All tables without underscore (‚_‘)

select * from user_tables
where table_name not like '%\_%' excape '\';

Escaping in a SQL-Plus-Session: All Tables, starting with ‚T_‘.

set escape '\'
select * from user_tables
where table_name like 'T\_%';

Searching in Data-Dictionary-Views for Views user_views, all_views…

select * from user_views
where dbms_xmlgen.getxml
('select text from user_views where view_name = || view_name || ')
like '%w_spci_order_types%'

Installation ORACLE Database 11.2 on DEBIAN Linux

ORACLE-DB Installation DEBIAN Linux (Lenny)
Oracle DB 11.2 on DEBIAN
http://www.oxalis.de/Oracle11gR2aufDebianLenny.html

ORACLE-DB Installation SUSE Linux
Package-Dependencies/Abhängigkeiten/Requirements for SUSE-Linux at Paket/Package orarun.rpm.

ORACLE-DB InstallationORACLE Enterprise Linux
Package-Dependencies/Abhängigkeiten/Requirements for ORACLE Enterprise Linux in Paket/Package oracle-validated.rpm.

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; /