Oracle REGEXP to Strip Numbers From a String

This example uses the Oracle REGEXP functions to check if a string contains numbers and also strip away unwanted characters. Unful to prevent errors when converting strings to numbers with TO_NUMBER.

WITH testData AS ( SELECT '12ab34' theString FROM DUAL UNION ALL SELECT 'a12b34c' FROM DUAL UNION ALL SELECT '%1$AB2.34' FROM DUAL UNION ALL SELECT 'abcd' FROM DUAL UNION ALL SELECT '12.34' FROM DUAL UNION ALL SELECT '1234' FROM DUAL ) SELECT theString, REGEXP_REPLACE(theString,'[^0-9]') justNumbers, REGEXP_REPLACE(theString,'[^0-9\.]') numbersAndDecimal, (CASE REGEXP_INSTR(theString,'[0-9]') WHEN 0 THEN 0 ELSE 1 END) existsCheck, (CASE REGEXP_INSTR(theString,'\D') WHEN 0 THEN 1 ELSE 0 END) onlyNumbersCheck FROM testData ;

Gives the output:

THESTRING JUSTNUMBE NUMBERSAN EXISTSCHECK ONLYNUMBERSCHECK --------- --------- --------- ----------- ---------------- 12ab34 1234 1234 1 0 a12b34c 1234 1234 1 0 %1$AB2.34 1234 12.34 1 0 abcd 0 0 12.34 1234 12.34 1 0 1234 1234 1234 1 1 6 rows selected.

Oracle Queries to See Executing SQL

A couple of queries to see what is executing on the database.

The first uses v$sqltext to query executing SQL:

SELECT vs.username, vs.sid, vs.serial#, vs.machine, vst.sql_text, LTRIM(TO_CHAR(FLOOR(vs.last_call_et/3600),'00'))||':' ||LTRIM(TO_CHAR(FLOOR(MOD(vs.last_call_et,3600)/60),'00'))||':' ||LTRIM(TO_CHAR(MOD(vs.last_call_et,60),'00')) runtime FROM v$session vs, v$sqltext vst WHERE vs.status = 'ACTIVE' AND vs.username IS NOT NULL AND vs.sql_address = vst.address AND vs.sql_hash_value = vst.hash_value AND vs.audsid != USERENV('SESSIONID') ORDER BY vs.last_call_et DESC, vs.username, vst.piece ;

This one uses v$sqlarea and will get PL/SQL and SQL:

SELECT vs.username, vs.sid, vs.serial#, vs.machine, vsa.optimizer_mode, vsa.sql_text, LTRIM(TO_CHAR(FLOOR(vs.last_call_et/3600),'00'))||':' ||LTRIM(TO_CHAR(FLOOR(MOD(vs.last_call_et,3600)/60),'00'))||':' ||LTRIM(TO_CHAR(MOD(vs.last_call_et,60),'00')) last_call FROM v$session vs, v$sqlarea vsa WHERE vs.sql_hash_value = vsa.hash_value AND vs.sql_address = vsa.address AND vs.audsid != USERENV('SESSIONID') ORDER BY vs.last_call_et DESC, vs.username ;

Done.

Oracle OWA_UTL Error

Using Oracle 11, I'm just trying to debug some code that uses the owa package and I'm getting the following error because I'm not testing it in a web environment.

ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SYS.OWA_UTIL", line 328 ORA-06512: at "SYS.OWA_UTIL", line 2172 ORA-06512: at "XXXXXXXXXXXXXXXXXXX", line 383

To solve this we need a wrapper around our code to get the owa package to work.

DECLARE l_vcarr OWA.VC_ARR; BEGIN l_vcarr(1) := 1; OWA.INIT_CGI_ENV(l_vcarr); -- Call my code here END; /

anonymous block completed.

Linux: Convert text files from Windows to Linux

The age old question, why are line endings different between Windows and Linux files? Anyway not got time to ponder why, I just want to convert Windows files to Linux so I can get on.

tr -d "\r" < source.txt > target.txt

This will remove the carriage return (CR) from the source.txt file and just leave the line feed (LF) in the target.txt. Obviously if you use source.txt instead of target.txt you don't get a second file.

Converted.

Another Oracle Lock Query

The lock queries already posted show the oldest lock and who is waiting on a lock. However often you just want a plain who has locks on a table...so.

SELECT do.object_name, do.object_type, vs.username, vs.osuser, vs.sid||','||vs.serial# sid_serial, ( CASE glo.locked_mode WHEN 0 THEN 'None' WHEN 1 THEN 'Null' WHEN 2 THEN 'Row-S (SS)' WHEN 3 THEN 'Row-X (SX)' WHEN 4 THEN 'Share' WHEN 5 THEN 'S/Row-X (SSX)' WHEN 6 THEN 'Exclusive' ELSE TO_CHAR(glo.locked_mode) END ) lock_mode, do.status object_status FROM dba_objects do, gv$locked_object glo, v$session vs WHERE do.object_id = glo.object_id AND glo.session_id = vs.sid ORDER BY 1,3

Done.

Linux: Find the Top 40 Largest Files in Directory

This command finds the largest 40 files in and under the current directory.

find . -type f -print0 | xargs -0 du -h | sort -hr | head -40

Found.

Convert LONG to VARCHAR2 in an SQL Statement

"Why are you still using Long column types, they've been extinct for ages?"
I'm not but the data dictionary is...

Anyway, I struggled for ages to find a solution to quickly convert Long column types to Varchar2 on the fly in an SQL statement without creating some kind of PLSQL function to do the job, which I didn't want to do. So I came up with converting it to XML then querying the XML. Still not ideal but got the job done without me having to create PLSQL.

WITH xml AS ( SELECT DBMS_XMLGEN.GETXMLTYPE( 'SELECT * FROM dba_triggers where trigger_name like ''%MODTRG''' ) AS xml FROM DUAL ), dbaTriggersData AS ( SELECT xs.trigger_name, xs.trigger_body FROM xml x, XMLTABLE('/ROWSET/ROW' PASSING x.xml COLUMNS trigger_name VARCHAR2(30) PATH 'TRIGGER_NAME', trigger_body VARCHAR2(4000) PATH 'TRIGGER_BODY') xs ) SELECT * FROM dbaTriggersData WHERE trigger_body LIKE '%USER%' AND trigger_body NOT LIKE '%APP_USER%' ;

This particular query was to check my triggers were ok for Apex. Referencing APP_USER if they referenced USER