************************
1)
set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 132
define schema=&1
set pagesize 0
set long 90000
set feedback off
set echo off
spool scott_schema.sql
connect scott/tiger;
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u;
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
FROM USER_INDEXES u;
spool off;
2) decode, case when,
3) tnsnames.ora &tnsping
4) SYS? SYSTEM>
5) who am I in oracle?
show user;
6) how to disconnect?
disc
7)Partitioned Tables And Indexes
https://oracle-base.com/articles/8i/partitioned-tables-and-indexes
8)views
9) materialized views
https://oracle-base.com/articles/misc/materialized-views
10)Difference between Views and Materialized Views in SQL
1) execution plan
2) performance and tuning
3)indexes
4)ROWNUM or ROWID
5) stored procedures
6)SOME TRUE if any of the subquery values meet the condition
7) subqueries
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
8) where not
SQL> select distinct job from emp where not job='PRESIDENT';
JOB
---------
CLERK
SALESMAN
MANAGER
ANALYST
SQL> select distinct job from emp where job!='PRESIDENT';
JOB
---------
CLERK
SALESMAN
MANAGER
ANALYST
SQL> select distinct job from emp where job<>'PRESIDENT';
JOB
---------
CLERK
SALESMAN
MANAGER
ANALYST
9)select 'D' from table (dev) or select 'P' from table (prod)
datastage variable: %ENV%
select * from x
where
'%ENV%' = 'D' and ...
or
'%ENV%' = 'P' and ...
10) limiting rows
select top 5 * from table (sqlserver)
select * from table
fetch first 5 rows only (db2)
select * from table
limit 100 (db2)
select * from table
where rownum>5 (oracle)
mardi 20 octobre 2020
10 oracle tips part 3
lundi 19 octobre 2020
10 oracle tips part2
1) comparing dates. How do I compare dates in Oracle?
char VS char
select dateaniv from test2 where to_char(dateaniv,'dd/mm/yyyy')='12/06/1977';
date VS date (better)
select dateaniv from test2 where dateaniv=to_date('12/06/1977','dd/mm/yyyy')
Comparing dates the wrong way
These Oracle date comparisons works, but no index cannot be used because date2 is invalidated with the trunc function (unless you create a function-based index on trunc(date2,'YYYY:MM').
where to_char(DATE1,'YYYY:MM') >= to_char(DATE2,'YYYY:MM')
where trunc(date1,'mm') >= trunc(date2,'mm');
2) UNION and UNION ALL
There are exhaustive notes on how the Oracle SQL UNION operator is used to merge separate SQL queries, but there is an important difference between UNION and UNION ALL.
The Oracle SQL UNION differs from the Oracle UNION ALL primarily because it does not filter out duplicate rows.
The UNION SQL operator returns only the unique rows that appear in either result, while the UNION ALL operator returns all rows in both queries, including duplicate rows.
3) WITH Clause : Subquery Factoring in Oracle
Using the SCOTT schema, for each employee we want to know how many other people are in their department. Using an inline view we might do the following.
-- Non-ANSI Syntax
SELECT e.ename AS employee_name,
dc.dept_count AS emp_dept_count
FROM emp e,
(SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno) dc
WHERE e.deptno = dc.deptno;
-- ANSI Syntax
SELECT e.ename AS employee_name,
dc.dept_count AS emp_dept_count
FROM emp e
JOIN (SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno) dc
ON e.deptno = dc.deptno;
Using a WITH clause this would look like the following.
-- Non-ANSI Syntax
WITH dept_count AS (
SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc.dept_count AS emp_dept_count
FROM emp e,
dept_count dc
WHERE e.deptno = dc.deptno;
-- ANSI Syntax
WITH dept_count AS (
SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc.dept_count AS emp_dept_count
FROM emp e
JOIN dept_count dc ON e.deptno = dc.deptno;
The difference seems rather insignificant here.
What if we also want to pull back each employees manager name and the number of people in the managers department? Using the inline view it now looks like this.
-- Non-ANSI Syntax
SELECT e.ename AS employee_name,
dc1.dept_count AS emp_dept_count,
m.ename AS manager_name,
dc2.dept_count AS mgr_dept_count
FROM emp e,
(SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno) dc1,
emp m,
(SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno) dc2
WHERE e.deptno = dc1.deptno
AND e.mgr = m.empno
AND m.deptno = dc2.deptno;
-- ANSI Syntax
SELECT e.ename AS employee_name,
dc1.dept_count AS emp_dept_count,
m.ename AS manager_name,
dc2.dept_count AS mgr_dept_count
FROM emp e
JOIN (SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno) dc1
ON e.deptno = dc1.deptno
JOIN emp m ON e.mgr = m.empno
JOIN (SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno) dc2
ON m.deptno = dc2.deptno;
Using the WITH clause this would look like the following.
-- Non-ANSI Syntax
WITH dept_count AS (
SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc1.dept_count AS emp_dept_count,
m.ename AS manager_name,
dc2.dept_count AS mgr_dept_count
FROM emp e,
dept_count dc1,
emp m,
dept_count dc2
WHERE e.deptno = dc1.deptno
AND e.mgr = m.empno
AND m.deptno = dc2.deptno;
-- ANSI Syntax
WITH dept_count AS (
SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc1.dept_count AS emp_dept_count,
m.ename AS manager_name,
dc2.dept_count AS mgr_dept_count
FROM emp e
JOIN dept_count dc1 ON e.deptno = dc1.deptno
JOIN emp m ON e.mgr = m.empno
JOIN dept_count dc2 ON m.deptno = dc2.deptno;
So we don't need to redefine the same subquery multiple times. Instead we just use the query name defined in the WITH clause, making the query much easier to read.
If the contents of the WITH clause is sufficiently complex, Oracle may decide to resolve the result of the subquery into a global temporary table. This can make multiple references to the subquery more efficient.
The MATERIALIZE and INLINE optimizer hints can be used to influence the decision.
The undocumented MATERIALIZE hint tells the optimizer to resolve the subquery as a global temporary table, while the INLINE hint tells it to process the query inline.
WITH dept_count AS (
SELECT /*+ MATERIALIZE */ deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT ...
WITH dept_count AS (
SELECT /*+ INLINE */ deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT ...
Even when there is no repetition of SQL, the WITH clause can simplify complex queries, like the following example that lists those departments with above average wages.
WITH
dept_costs AS (
SELECT dname, SUM(sal) dept_total
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY dname),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) avg
FROM dept_costs)
SELECT *
FROM dept_costs
WHERE dept_total > (SELECT avg FROM avg_cost)
ORDER BY dname;
In the previous example, the main body of the query is very simple, with the complexity hidden in the WITH clause.
4)
inner join or cross join?
cross join= cartesian product
inner join or join (regular join)
5) GROUP BY WITH VARCHAR COLUMN
select dateaniv,sum(to_number(num_users)) from test2
2 group by dateaniv;
6) differences between CROSS JOIN AND INNER JOIN
cross join = cartesian product
inner join = join
inner join uses ON + where
cross join uses WHERE
7) fake join
SQL> select * from dept d, test2 a
2 where d.deptno=to_number(num_users||'0');
DEPTNO DNAME LOC DATEANIV DATEANIV2 NUM_USERS
---------- -------------- ------------- ---------- ---------- ----------
10 ACCOUNTING NEW YORK 12/06/1977 01/02/1977 1
10 ACCOUNTING NEW YORK 01/01/1977 01/02/1977 1
10 ACCOUNTING NEW YORK 01/01/1978 1
10 ACCOUNTING NEW YORK 01/12/1977 01/02/1977 1
10 ACCOUNTING NEW YORK 01/10/1977 01/02/1977 1
10 ACCOUNTING NEW YORK 01/01/1977 01/01/1977 1
10 ACCOUNTING NEW YORK 01/01/1977 01/01/1977 1
7 rows selected.
8) null handling
SQL> select * from test2
2 where dateaniv2 is null;
DATEANIV DATEANIV2 NUM_USERS
---------- ---------- ----------
01/01/1978 1
SQL> select * from test2
2 where dateaniv2 is not null;
DATEANIV DATEANIV2 NUM_USERS
---------- ---------- ----------
12/06/1977 01/02/1977 1
01/01/1977 01/02/1977 1
01/12/1977 01/02/1977 1
01/10/1977 01/02/1977 1
01/01/1977 01/01/1977 1
01/01/1977 01/01/1977 1
6 rows selected.
SQL> select nvl(dateaniv2,'01/01/1900') from test2
2 where dateaniv2 is null;
NVL(DATEAN
----------
01/01/1900
SQL> select DECODE(dateaniv2,NULL,'01/01/1900') from test2
2 /
DECODE(DAT
----------
01/01/1900
7 rows selected.
SQL> select nvl2(dateaniv2,dateaniv2,'01/01/1900') from test2
2 /
NVL2(DATEA
----------
01/02/1977
01/02/1977
01/01/1900
01/02/1977
01/02/1977
01/01/1977
01/01/1977
7 rows selected.
The LNNVL function has been available since at least Oracle 9i, but was undocumented until Oracle 11g. It is used in a where clause to evaluate a condition. If this condition evaluates to false or unknown, it returns true. If the condition evaluates to true, it returns false.
SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col1 IS NULL) ORDER BY id;
ID COL3
---------- ----------
1 THREE
1 row selected.
SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col2 = 'TWO') ORDER BY id;
ID COL3
---------- ----------
3 THREE
4 THREE
2 rows selected.
SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col2 != 'TWO') ORDER BY id;
ID COL3
---------- ----------
1 THREE
2 THREE
3 THREE
4 THREE
4 rows selected.
NULLIF
The NULLIF function was introduced in Oracle 9i. It accepts two parameters and returns null if both parameters are equal. If they are not equal, the first parameter value is returned.
In our test table the values of COL3 and COL4 are equal in row 4, so we would only expect null returned for that row using the following query.
SQL> SELECT id, NULLIF(col3, col4) AS output FROM null_test_tab ORDER BY id;
ID OUTPUT
---------- ----------
1 THREE
2 THREE
3 THREE
4
4 rows selected.
SQL>
9)after creating a user, we need to grant roles:
GRANT CONNECT, RESOURCE, DBA TO books_admin;
GRANT UNLIMITED TABLESPACE TO books_admin;
GRANT CREATE SESSION GRANT ANY PRIVILEGE TO books_admin;
GRANT
SELECT,
INSERT,
UPDATE,
DELETE
ON
schema.books
TO
books_admin;
10) system tables
all tablespaces, all users, dba_x
select table_name, tablespace_name from sys.all_tables;
dimanche 18 octobre 2020
10 datastage tips part1
1) different env in the sql code using #pENVexecution# DS parameter
where ('#pENVexecution#' = ' D' and <condition> and <condition2>
or
('#pENVexecution#' = ' P' and <condition> and <condition2>
2) BOM
3) use one copy before sending a DS to a lookup reference to have the real numbers
bug: 11.5.0.2
number of lines of the reference link is wrong!
4) lookup file set vs dataset (for lookups)
lookup file set must have a key, the name of the column (key) must be the same column as source.
differently as per dataset.
5) Json (hierarchical data stage - made with flash)
https://www.ibm.com/support/producthub/ugi/docs/content/SSZJPZ_11.7.0/com.ibm.swg.im.iis.ds.stages.xml.core.usage.doc/topics/examples_jsonstage.html
it uses hierarchical data, examples you can find on IBM website with files provided
https://www.ibm.com/support/pages/node/603441
json_examples.zip
use the web site to validate the json file
https://json-editor.tangramjs.com/index.html
https://json-editor.tangramjs.com/editor.html#/
After opening the assembly editor, you choose Palette to:
AGGREGATE, H PIVOT, HJOIN, REGROUP, SORT, VPIVOT, SWITCH, UNION, ORDERJOIN,
WEB SERVICES:REST, XML PARSER/COMPOSER, JSON PARSER(SOURCE)/COMPOSER(CIBLE)
Output step is the most important, where Mappings tab is used to map the nodes with the columns
samedi 17 octobre 2020
db2 vs oracle part1
***dummy table
sysibm.sysdummy1 = dual
select current_date from sysibm.dual
cast function
CURRENT DATE= sysdate
first_day()
last_day()
add_days
add_months
- 1 day
-1 month
fetch first 5 rows only
limit 1000
10 oracle tips part 1
1) how define an editor in sqlplus:
define _editor=vi
2)how to see editor values:
define;
3) how to write a select:
select ename,sal
from emp
where 1=1
and sal>2000
---and ename like 'B%';
4) how to create a dummy table
create table test as
select 1 as empid,1 as deptid,'a' as name,to_date('12/12/2000','dd/mm/yyyy') as dateaniv from dual
union all
select 2 as empid,2 as deptid, 'b' as name,to_date('01/01/2001','dd/mm/yyyy') as dateaniv from dual;
5)how to create or replace table:
CREATE OR REPLACE can only be used on functions, procedures, types, views, or packages - it will not work on tables.
6) history (oracle 12 only)
SQL> set history on
SQL> show history
SQL> show history
history is ON and set to “100â€
SQL> history
SQL> history 4 run
SQL history 4 edit
SQL> clear history
7) how to change date format
permanent:
CREATE OR REPLACE TRIGGER CHANGE_DATE_FORMAT
AFTER LOGON ON DATABASE
call DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','dd/mm/yyyy');
temporary:
alter session NLS_DATE_FORMAT='dd/mm/yyyy';
8)How can I use the "up arrow" and down arrow" keys to display the command line history for all SQL*Plus commands IN LINUX?
Question: I've been told that I need to use the rlwrap utility with SQL*Plus. What is rlwrap and how does it help me in SQL*Plus?
Answer: One nice features of using SQL*Plus in Windows is that you can use the "up arrow" and down arrow" keys to display the command line history for all SQL*Plus commands.
It's also possible to do this in Linux and UNIX (AIX, Solaris, HP/UX) with the freeware rlwrap utility.
You can download the freeware rlwrap utility at this link.
The readline wrapper (rlwrap) utility uses the GNU readline library. Hence, rlwrap is not Oracle-centric, it's a standard OS utility available for all flavors of UNIX, Linux and even Windows. The rlwrap software installs easily on UNIX/Linux with these standard GNU unzip and make commands:
I downloaded: rlwrap-0.43.tar.gz
gunzip rlwrap*.gz
tar -xvf rlwrap*.tar
cd rlwrap*
./configure
make
make check
make install
Aliasing rlwrap
Following the install, you use a standard UNIX alias in your shell signon file (.cshrc for S shell, .bashrc for Bourne shell, or .profile for Korn shell) to source-in the rlwrap utility, using a different command name:
alias rl_sqlplus='rlwrap sqlplus'
alias rl_rman='rlwrap rman'
alias rl_asmcmd='rlwrap asmcmd'
Rampant author Laurent Schneider notes why you should never use an alias over-ride of the default Oracle names of "sqlplus", "asmcmd" or "rman":
Using rlwrap may affect the behavior of CTRL+C during interactive sessions. I've done a few tests on this and I can't see a difference in behavior of CTRL+C with or without rlwrap. Perhaps this was a problem with earlier versions. Note that rlwrap only supports interactive sessions, so scripts like the following may not work as expected.
sqlplus alias: From what I can see the alias doesn't seem to work from within a shell script (bash, ksh or csh), so it doesn't really present a danger. Remember, rlwrap is not an Oracle tool! I guess it's best to leave the sqlplus and rman commands clean and alias using a different name, just in case.
Using rlwrap
The rlwrap utility is a nice utility for command-line editing and an easy display of SQL*Plus command history.
9)how to add a column in a table
alter table test add dateaniv2 date;
10) not between AND between inversed
SELECT * FROM customers WHERE customer_id NOT BETWEEN 3000 AND 3500;
this works in db2 and oracle:
and '01/02/1977' between dateaniv and dateaniv2;
