SQL> select deptno, ename
2 from emp
3 order by deptno
4 ;
DEPTNO ENAME
---------- ----------
10 CLARK
10 KING
10 MILLER
20 JONES
20 FORD
20 ADAMS
20 SMITH
20 SCOTT
30 WARD
30 TURNER
30 ALLEN
30 JAMES
30 BLAKE
30 MARTIN
14 rows selected.
SQL> -- We want to produce report with only new values of DEPTNO column,
SQL> -- without repetition. SQL*Plus provides BREAK ON feature.
SQL> break on deptno
SQL> select deptno, ename
2 from emp
3 order by deptno
4 ;
DEPTNO ENAME
---------- ----------
10 CLARK
KING
MILLER
20 JONES
FORD
ADAMS
SMITH
SCOTT
30 WARD
TURNER
ALLEN
JAMES
BLAKE
MARTIN
14 rows selected.
SQL> clear breaks
breaks cleared
SQL> -- Without any help from SQL*Plus I tried two approaches.
SQL> -- Both with Oracle SQL analytic functions.
SQL> select decode (
2 row_number() over (partition by deptno order by ename)
3 ,1,deptno
4 ) AS DEPTNO
5 ,ename
6 from emp
7 order by emp.deptno
8 ;
DEPTNO ENAME
---------- ----------
10 CLARK
KING
MILLER
20 ADAMS
FORD
JONES
SCOTT
SMITH
30 ALLEN
BLAKE
JAMES
MARTIN
TURNER
WARD
14 rows selected.
SQL> -- It is mandatory to use name of the table in the ORDER BY clause.
SQL> -- Next approach - to use LAG function to compare current
SQL> -- value of DEPTNO column with previous. If it is the same
SQL> -- then there is no output (null).
SQL>
SQL> select decode (
2 lag(deptno) over (order by deptno)
3 ,deptno,null,deptno
4 ) as deptno
5 ,ename
6 from emp
7 order by emp.deptno
8 ;
DEPTNO ENAME
---------- ----------
10 CLARK
KING
MILLER
20 ADAMS
FORD
JONES
SCOTT
SMITH
30 ALLEN
BLAKE
JAMES
MARTIN
TURNER
WARD
14 rows selected.
SQL> -- Use TO_NUMBER function or another way to adjust the size
SQL> -- of DEPTNO column
Sunday, October 28, 2012
How to suppress output of duplicate values - Oracle SQL
Tuesday, October 9, 2012
Palindrome detector
The string is palindrom if reverted it equals to source string. Here is simple algorithm to detect is the source string a palindrome or not.
CREATE OR REPLACE PROCEDURE isPalindrom(l_string VARCHAR2)
IS
src_string VARCHAR2(32767) := l_string;
len_src_string INTEGER := LENGTH(src_string);
j INTEGER;
FUNCTION getChar(i INTEGER) RETURN CHAR
IS
BEGIN
RETURN SUBSTR(src_string, i, 1);
END;
BEGIN
j := 1;
LOOP
EXIT WHEN (j = ROUND(len_src_string/2) + 1)
OR getChar(j) <> getChar(len_src_string-j+1);
j := j+1;
END LOOP;
IF getChar(j) = getChar(len_src_string-j+1) THEN
DBMS_OUTPUT.PUT_LINE(l_string||' IS POLYNDROM');
ELSE
DBMS_OUTPUT.PUT_LINE(l_string||' IS NOT POLYNDROM');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
END;
Friday, August 24, 2012
Multiset operations with nested tables
Daily quizzes provided by www.plsqlchallenge.com are the right way to learn PL/SQL language, to know about many features, tips and trics. The site also allows to communicate with developers and learn the best practices.
PL/SQL Challenge suggested interesting task yesterday - to develop code "to compare row counts of all tables in two schema and display the names and counts of the tables where there is a disagreement." PL/SQL and SQL are both possible. I prefered PL/SQL way and tried to play around with nested tables and multiset operations. The procedure is shown below.
PL/SQL Challenge suggested interesting task yesterday - to develop code "to compare row counts of all tables in two schema and display the names and counts of the tables where there is a disagreement." PL/SQL and SQL are both possible. I prefered PL/SQL way and tried to play around with nested tables and multiset operations. The procedure is shown below.
CREATE OR REPLACE PROCEDURE compare_table_counts(
schema1_in IN VARCHAR2,
schema2_in IN VARCHAR2,
filter_in IN VARCHAR2)
IS
TYPE type_aat IS TABLE OF VARCHAR2(100);
tab_list_1 type_aat;
tab_list_2 type_aat;
tab_except type_aat;
PROCEDURE make_tab_list(
loc_tab IN OUT type_aat,schema_name IN VARCHAR2
)
IS
sql_stmt VARCHAR2(200);
loc_cnt PLS_INTEGER;
BEGIN
SELECT table_name
BULK COLLECT INTO loc_tab
FROM all_tables
WHERE owner = schema_name
AND table_name LIKE filter_in;
FOR indx IN loc_tab.FIRST..loc_tab.LAST
LOOP
sql_stmt := 'SELECT COUNT(*) FROM '||
schema_name||'.'||
loc_tab(indx);
EXECUTE IMMEDIATE sql_stmt INTO loc_cnt;
loc_tab(indx) := loc_tab(indx) || ' (' ||
loc_cnt || ')';
END LOOP;
END;
PROCEDURE show_disagreement(
schema_name IN VARCHAR2, loc_tab IN type_aat
)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('In schema '||schema_name||
' differing tables...');
DBMS_OUTPUT.PUT_LINE('(Row counts in parentheses)');
FOR indx IN loc_tab.FIRST..loc_tab.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(' ' || loc_tab(indx));
END LOOP;
END;
BEGIN
make_tab_list(tab_list_1,schema1_in);
make_tab_list(tab_list_2,schema2_in);
tab_except := tab_list_1 MULTISET EXCEPT tab_list_2;
IF tab_except.COUNT > 0 THEN
show_disagreement(schema1_in,tab_except);
END IF;
tab_except := tab_list_2 MULTISET EXCEPT tab_list_1;
IF tab_except.COUNT > 0 THEN
show_disagreement(schema2_in,tab_except);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
END;
Monday, August 20, 2012
"The keys to Oracle" by Tom Kyte
The very useful guide "how-to" bridle Oracle was written by Tom Kyte - http://tkyte.blogspot.com/2012/08/the-keys-to-oracle.html
There are also few very interesting comments including Steven Feuerstein's.
There are also few very interesting comments including Steven Feuerstein's.
Sunday, August 19, 2012
Multiset Operations with Associative Arrays
It would be convenient to use multiset operations not only with nested tables but with associative arrays too. According to Oracle® Database SQL Language Reference multiset operations apply to nested tables only. For now. Some help would be possible to get from the package show below. The function AA_STRNDX build associative array as intersection or not intersection of two arrays. The OPER_ID parameter can take value 'INTERSECT' or 'NOT INTERSECT'.
create or replace
PACKAGE compare
IS
SUBTYPE strndx_type IS VARCHAR2(1000);
TYPE aa_strndx_type IS TABLE OF PLS_INTEGER
INDEX BY strndx_type;
FUNCTION aa_strndx(
array_in_1 IN aa_strndx_type,
array_in_2 IN aa_strndx_type,
array_out OUT aa_strndx_type,
oper_id IN VARCHAR2 := 'INTERSECT'
) RETURN PLS_INTEGER;
END;
Thursday, August 2, 2012
Saturday, July 21, 2012
Set of links for OCP Advanced PL/SQL developer
I've finished creating all-in-one training kit for 1Z0-146 Oracle Exam. It is just a list of topics which completely corresponds to the exam. 1Z0-146 is the final step to become Oracle Advanced PL/SQL Developer Certified Professional.
Straight link to the Oracle documentation is created for each topic. For some subtopics there are recommended links. In some cases the one link can match to a few topics.
This all-in-one training kit was created using only free information sources. Self-made study guide was used by me to prepare for 1Z0-146. I have passed the exam successfully. If some links are not already active please contact me - kgkuzmin@gmail.com.
Good luck to you in your endeavor to become Oracle Certified Professional!
Subscribe to:
Posts (Atom)