Identify and count Characters this Tip Comes from Aui de la VEGA, DBA, IN MAKATI, Philippines. This function provides the number of times a pattern occurs in a string (varchar2).
SQL> CREATE FUNCTION NUM_CHARS (Instring Varchar2, Inpattern varchar2)
Return Number
IS
Counter Number;
Next_index number;
String varchar2 (2000);
Pattern varchar2 (2000);
Begin
COUNTER: = 0;
NEXT_INDEX: = 1;
String: = loWer (Instring);
Pattern: = LOWER (Inputtern);
For i in 1 .. Length (string) loop
IF (length (pattern) <= length (string) -next_index 1)
AND (SUBSTR (SUBSTR (String, Next_index, Length (Pattern)) = Pattern) THEN
Counter: = Counter 1;
END IF;
Next_index: = next_index 1;
End loop;
Return Counter;
END;
Function created.
How Many Seconds calculated after a number of seconds This tip comes from Mir Mirhashimaali, Oracle Systems Manager, Rice University in Houston, Texas. Here's a function to calculate the number of seconds elapsed in a given period of time. Create or replace function how_many_seconds (
p_startdate date,
p_ENDDATE DATE,
P_StartTime Varchar2,
P_endtime varchar2
)
Return Number
IS
-
MV_SYSDATE VARCHAR (8): = to_CHAR (Sysdate, 'rrrrmmdd');
MN_RETURN_VALUE NUMBER;
MN_SECONDS_PER_DAY NUMBER;
MN_TOTAL_DAYS NUMBER;
MN_SECONDS_IN_START_DAY NUMBER;
MN_SECONDS_IN_END_DAY NUMBER;
-
Begin
-
MN_SECONDS_PER_DAY: = to_CHAR (to_date (mv_sysdate || p_endtime,
'rrrrmmddhh24mi'), 'sssss') -
TO_CHAR (to_date (mv_sysdate || p_starttime, 'rrrrmmddhh24mi),
'sssss');
Mn_total_days: = (Trunc (p_enddate) - Trunc (p_startdate)) - 1;
MN_SECONDS_IN_START_DAY: = to_CHAR (to_date (mv_sysdate ||
P_ENDTIME, 'RRRMMDDHH24MI'), 'SSSSS') -to_char (p_startdate, 'sssss');
Mn_seconds_in_end_day: = to_CHAR (P_ENDDATE, 'SSSSS') -
TO_CHAR (to_date (mv_sysdate || p_starttime, 'rrrrmmddhh24mi),
'sssss');
-
IF mn_total_days <0 THEN
Mn_total_days: = 0;
MN_SECONDS_IN_START_DAY: = 0;
END IF;
-
IF mn_seconds_in_start_day <0 THEN
MN_SECONDS_IN_START_DAY: = 0;
END IF;
-
IF mn_seconds_in_nd_day <0 THEN
MN_SECONDS_IN_END_DAY: = 0;
END IF;
-
MN_RETURN_VALUE: = mn_seconds_in_start_day (mn_total_days *
MN_SECONDS_PER_DAY) MN_SECONDS_IN_END_DAY;
-
RETURN MN_RETURN_VALUE;
-
END;
Find Numeric and Non-Numeric Records identify the numeric and non-numeric fields This tip comes from Ilya Petrenko, Senior Oracle DBA, Open Distributed Solutions, Inc., in Jamison, Pennsylvania. This script uses two Oracle functions-TRANSLATE and Length-to identify IF data incrudes Numeric or Non-Numeric Characters.
Create Table TMP_DATA
(Row_Seq # Number,
String # varchar2 (100)
);
INSERT INTO TMP_DATA
SELECT ROWNUM,
Object_name
|| Decode (Mod (Rownum, 4), 0, NULL, TO_CHAR (ROWNUM))
|| Object_Type
From user_Objects
Where object_name like 't%'
Union all
SELECT 100, '87' from DUAL
Union all
SELECT 100, '0123' from DUAL
Union all
SELECT 100, '911' from DUAL
;
Col String # for a60
SELECT * from Tmp_DATA;
Find All Rows Where You Have Numeric Characters ONLY:
SELECT ROW_SEQ #, String #
From TMP_DATA
WHERE Length (String #)
- Length (string #, chr (1) || Translate (String #, chr (1) || '1234567890', chr (1)), chr (1)) = 0
;
Row_seq # string #
---------- --------
100 0123
100 911
Find all rows where you have non-numeric characters Only: Select Row_seq #, string #
From TMP_DATA
WHERE Length (String #)
- Length (string #, chr (1) || Translate (String #, chr (1) || '1234567890', chr (1)), chr (1)))> 0
;
Row_seq # string #
---------- ------------------------------------
1 T0011Table
2 Test22Table
3 TMP_DATA3TABLE
5 Trans5Table
6 TRANS216TABLE
7 Triv7Trigger
100 87
Conversion This tip comes Row to Column Function rows to columns from Krishna Kondavadi, Consultant, RAPIDIGM Inc in East Hartford, Connecticut. The objective of this function is to transpose rows to columns. This RowToCol function is built with invoker-rights AUTHID CURRENT_USER. The function works on tables / views of the user who invokes the function, not on the owner of this function RowToCol takes two parameters:. 1. SQL Statement: You can pass any valid SQL statement to this function.2 Delimiter:. You can Pass any character as a delimiter. Default value is`
Select a.deptno, a.dname, a.loc,
RowTocol ('Select Distinct Job from Emp where deptno =' || a.deptno) as jobs
From dept a;
EXAMPLE 2: WHERE THE CONTENT IN The WHERE CLAUSE CHARACTERS, PUT IT IN Sting Format. NOTORY SOURY IS SAME TABLE (EMP). SO, Use Distinct Clause In The Main Query.
Select Distinct A.Job
, ROWTOCOL ('SELECT Ename from Emp where Job =' || '' '|| a.job ||' '' || 'Order By Ename'
As Employees
From EMP A;
Code Listing:
Create or Replace
Function RowTocol (p_slct in varcha2, p_dlmtr in varchar2 default ',') Return varchar2
Authi Current_User AS
/ *
1) Column should be character type.2) If it is non-character type, column has to be converted into character type. 3) If the returned rows should in a specified order, put that ORDER BY CLASS in the SELECT statement argument. 4) IF The SQL Statement Happened to Return Duplicate Values, And if you don't want That to happen, put distinct in the select statement arch.
TYPE C_REFCUR IS REF CURSOR;
LC_STR VARCHAR2 (4000);
LC_Colval Varchar2 (4000);
C_Dummy c_refcur;
l Number;
Begin
Open c_dummy for p_slct;
Loop
FETCH C_Dummy Into LC_COLVAL;
EXIT WHEN C_DUMMY% NOTFOUND;
LC_STR: = LC_STR || P_dlmtr || LC_COLVAL;
End loop;
Close c_dummy;
Return Substr (LC_STR, 2);
/ *
EXCEPTION
When Others Then
LC_STR: = SQlerRM;
IF c_dummy% isopen Then
Close c_dummy;
END IF;
Return LC_STR;
* / END;
/
Suppress Repeating Groups Using RANK (not ROWNUM) This tip comes from Mohammad Anwar, Oracle Developer, Tuskerdirect Ltd, in London, United Kingdom. This tip achieves the same output as the Tip for Week of August 23, 2004. In that tip, the Author Uses Rownum and Inline Views, WHEREAS I Use the Analytic Function Rank () to do the job. Here is the completion script:
CREATE TABLE EMP
Empno Number (3),
DEPT NUMBER (3),
Name varchar2 (15)
);
INSERT INTO EMP VALUES (1,100, 'Tracey Turner');
INSERT INTO EMP VALUES (10,100, 'Peter Watson');
INSERT INTO EMP VALUES (99, 200, 'David Truman');
INSERT INTO EMP VALUES (80, 200, 'William Fender');
INSERT INTO EMP VALUES (2,300, 'Sara Joshua);
INSERT INTO EMP VALUES (3,200, 'Joseph Derner');
INSERT INTO EMP VALUES (60, 100, 'Sandra Harper'); Original Statement:
Select decode (Rownum-min_sno, 0, a.dept, null) DEPT,
Decode (Rownum-min_sno, 0, 1, rownum 1-min_sno) SNO,
A.empno, Name
From (Select * from Emp ORDER BY DEPT, EMPNO) A,
(SELECT DEPT, MIN (ROWNUM) min_sno
From (Select * from Emp ORDER BY DEPT, EMPNO)
GROUP BY DEPT) B
Where a.dept = b.dept
/
Modified Statement Using Rank ():
SELECT CASE WHEN RANK () OVER (Partition By Dept Order by Empno) = 1 THEN Dept Else Null End As Dept,
Rank () over (Partition by Dept Order By Empno) AS SNO,
Empno,
Name
From EMP;
/
OUTPUT:
DEPT SNO Empno Name
---------- ---------------------------------
100 1 1 TRACEY TURNER
2 10 Peter Watson
3 60 Sandra Harper
200 1 3 Joseph Derner
2 80 William Fender
3 99 David Truman
300 1 2 Sara Joshua
Comma, Pipe, or Tab Delimited Output This tip comes from Galina Petrenko, Sr. IT Specialist, TPAS Systems Development in Voorhees, New Jersey. To generate comma or pipe delimited output for tools such as Excel, Access, and so on while spooling from SQL * Plus, Use settings from sql * plus such as:
Set colsep,
oral
Set colsep |
EXAMPLE:
SET PAGES 0 feed OFF
Set colsep,
SELECT
Object_name,
Object_type,
STATUS,
created
From all_Objects
WHERE OWNER = 'public'
And Rownum <10 / * 9 Rows for Sample Output * /
;
OUTPUT:
All_All_Tables, Synonym, Valid, 01-APR-2004
All_arguments, Synynym, Valid, 01-APR-2004
All_associations, Synonym, Valid, 01-APR-2004
All_catalog, Synynym, Valid, 01-APR-2004
All_Clusters, Synynym, Valid, 01-APR-2004
All_cluster_hash_expressions, synonym, valid, 01-APR-2004all_coll_types, synonym, valid, 01-APR-2004
ALL_COL_COMMENTS, SYNONYM, VALID, 01-APR-2004
All_col_privs, Synonym, Valid, 01-APR-2004
For Pipe Delimited Output:
Set colsep |
SELECT
Object_name,
Object_type,
STATUS,
created
From all_Objects
WHERE OWNER = 'public'
And Rownum <10 / * 9 Rows for Sample Output * /
;
OUTPUT:
All_all_tables | SYNONYM | VALID | 01-APR-2004
All_arguments | Synonym | Valid | 01-APR-2004
ALL_ASSOCIATIONS | SYNONYM | VALID | 01-APR-2004
All_catalog | SYNONYM | VALID | 01-APR-2004
All_clusters | Synonym | Valid | 01-APR-2004
All_cluster_hash_expressions | Synonym | Valid | 01-APR-2004
All_coll_types | Synonym | Valid | 01-APR-2004
ALL_COL_COMMENTS | SYNONYM | VALID | 01-APR-2004
All_col_privs | Synonym | Valid | 01-APR-2004
For Tab Delimited Output, You CAN Use The Following:
Col Tab # new_value Tab Noprint
Select chr (9) tab # from dual;
Set colsep "& tab"
SELECT
Object_name,
STATUS,
created
From all_Objects
WHERE OWNER = 'public'
And Rownum <10 / * 9 Rows for Sample Output * /
;
OUTPUT:
ALL_ALL_TABLES VALID 01-APR-2004
All_arguments Valid 01-APR-2004
All_Associations Valid 01-APR-2004
All_catalog valid 01-APR-2004
All_clusters Valid 01-APR-2004
All_cluster_hash_expressions valid 01-APR-2004
All_coll_types valid 01-APR-2004
ALL_COL_COMMENTS VALID 01-APR-2004
All_col_privs valid 01-APR-2004
