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%'
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.
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;
/
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;
/
Eine weitere WordPress-Website