tag:blogger.com,1999:blog-71457815319505571932016-11-02T06:50:40.706-07:00Project WowNowSoftware code snippets and other stuffWowNownoreply@blogger.comBlogger92125tag:blogger.com,1999:blog-7145781531950557193.post-43665560949880529492016-03-07T02:22:00.001-08:002016-03-07T08:10:11.579-08:00Installing SQLPlus on Windows 10 and "MSVCP100.dll Missing"Firstly to install SQLPlus on Windows 10 I downloaded two packages from Oracle.<br /><br /><a href="http://www.oracle.com/technetwork/topics/winx64soft-089540.html">http://www.oracle.com/technetwork/topics/winx64soft-089540.html</a><br /><br />The first "Instant Client Package - Basic Lite" has the main connection packages.<br /><br />The second "Instant Client Package - SQL*Plus" has the SQLPlus elements.<br /><br />Unzip these to a folder. I used "C:\Oracle\instantclient_12_1".<br /><br />I then tried to run SQLPLUS.exe and got the error "The program can't start because MSVCR100.dll is missing from your computer. try reinstalling the program to fix this problem"<br /><br />A quick Google search found that this is some kind of C++ library. I downloaded the necessary files from <a href="http://www.microsoft.com/en-us/download/details.aspx?id=14632">http://www.microsoft.com/en-us/download/details.aspx?id=14632</a>.<br /><br />I dropped my tnsnames.ora file into the instant client directory, ran SQLPlus again and voila.<br /><br />SQL&gt; WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-91899336437184536852016-01-26T07:05:00.002-08:002016-01-26T07:08:02.590-08:00Oracle Flashback Source PLSQL CodeAaaaarrgh! Just modified some PLSQL in the database and lost what the previous version, with lots of cool stuff in I did this morning! :-(<br/><br/>Luckily, using the AS OF flashback clause I can rewind time and get the code back.<br/>(NB: To get this to work I had to SSH to the server and connect as SYS via SqlPlus)<br/><br/><div class="snippet">SELECT text FROM sys.dba_source AS OF TIMESTAMP TO_TIMESTAMP('2016-01-26 12:34:00', 'YYYY-MM-DD HH24:MI:SS') WHERE owner = 'schema' AND name = 'object name' AND type = 'PACKAGE BODY' ORDER BY line ; </div>Change as required and carry on.<br/>:-) WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-69620617428250976102015-11-24T04:17:00.001-08:002015-11-24T04:19:42.675-08:00Oracle SQL Tuning: Quick Test TemplateSometime using explain plan doesn't clearly give a answer to which way to write a query. This template allows a query to be run several times to give a idea of which way is faster.<br /><br /><div class="snippet">BEGIN FOR a IN 1..1000 LOOP FOR b IN ( ... paste query here ... ) LOOP &nbsp; NULL; END LOOP; END LOOP; END; /</div><br />Done.WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-24616925782796782852015-04-24T08:44:00.000-07:002016-01-26T07:08:13.374-08:00Remove Oracle Job ProcessTrying to kill a database job can be frustrating sometimes and they just won't die if you don;t do it right, especially if the job is owned by a different user.<br/><br/>So first identify the job you are looking for using:<br/><br/><div class="snippet">SELECT * FROM dba_jobs ; </div>Then use DBMS_IJOB to remove the job. Use the value from the JOB column above as the parameter value.<br/><br/><div class="snippet">EXEC SYS.DBMS_IJOB.REMOVE(?); </div>I'm using Oracle 11 but I believe that, although this is undocumented, this has been around for several years now.<br/><br/>Removed. WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-16123893829779370782015-04-21T02:33:00.000-07:002015-04-21T02:33:40.696-07:00Oracle REGEXP to Strip Numbers From a StringThis 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. <div class="snippet">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 ; </div>Gives the output: <div class="snippet">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. </div>WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-84227452893410112352015-03-24T04:54:00.002-07:002016-01-26T07:08:23.523-08:00Oracle Queries to See Executing SQLA couple of queries to see what is executing on the database.<br/><br/>The first uses v$sqltext to query executing SQL:<br/><br/><div class="snippet">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 ; </div>This one uses v$sqlarea and will get PL/SQL and SQL:<br/><br/><div class="snippet">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 ; </div>Done.WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-3470086649174787872015-02-19T04:05:00.000-08:002015-02-19T04:05:29.282-08:00Oracle OWA_UTL ErrorUsing 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.<br/><br/><div class="snippet">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 </div>To solve this we need a wrapper around our code to get the owa package to work.<br/><br/><div class="snippet">DECLARE l_vcarr OWA.VC_ARR; BEGIN l_vcarr(1) := 1; OWA.INIT_CGI_ENV(l_vcarr); -- Call my code here END; / </div>anonymous block completed. WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-32779210402874284122015-02-18T06:25:00.001-08:002015-02-18T06:25:41.728-08:00Linux: Convert text files from Windows to LinuxThe 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.<br/><br/><div class="snippet">tr -d "\r" < source.txt > target.txt </div>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.<br/><br/>Converted.WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-92058269634508527232015-01-29T02:21:00.002-08:002015-01-29T02:23:42.738-08:00Another Oracle Lock QueryThe 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.<br/><br/><div class="snippet">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 </div>Done. WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-55256214123108981612015-01-28T02:01:00.000-08:002015-01-29T02:18:10.877-08:00Linux: Find the Top 40 Largest Files in DirectoryThis command finds the largest 40 files in and under the current directory.<br /><br /><div class="snippet">find . -type f -print0 | xargs -0 du -h | sort -hr | head -40</div>Found. WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-68683551425304647902014-09-08T03:24:00.002-07:002014-09-08T03:34:16.523-07:00Convert LONG to VARCHAR2 in an SQL Statement<i>"Why are you still using Long column types, they've been extinct for ages?"</i><br />I'm not but the data dictionary is...<br /><br />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.<br /><br /><div class="snippet">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%' ;</div><br />This particular query was to check my triggers were ok for Apex. Referencing APP_USER if they referenced USER&nbsp; <br /><br />WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-52806596531791396222014-06-09T07:09:00.004-07:002016-01-26T07:08:37.834-08:00ORA-00903, ORA-03001 and ORA-01765 When Renaming a TableUsing Oracle 11.2.0.3.0, I was trying to rename a table. Not something I do everyday.<br /><br />On searching Google for the syntax I found Oracle documentation for <i>"RENAME TABLE table-Name TO new-Table-Name"</i> which gave me the error "<i>ORA-00903: invalid table name</i>".<br />This turns out to be a mistake in the documentation and the command should not have the TABLE keyword in it. IE:&nbsp; <i>"RENAME table-Name TO new-Table-Name"</i><br /><br />This now gave me error&nbsp; <i></i>"<i>ORA-03001: unimplemented feature</i>". Now I am not logged on as the table owner but do have the owner set as my current schema. <i>"ALTER SESSION SET CURRENT_SCHEMA=USERNAME;"</i><br /><br />Next logical step was to include the username in the command. "<i>RENAME owner.table-Name TO new-Table-Name</i>", which gave "<i>ORA-01765: specifying owner's name of the table is not allowed"</i><br /><br />A bit more searching and I found the following syntax which did work.<br /><br /><div class="snippet">ALTER TABLE owner.table-Name RENAME TO new-Table-Name;</div><br />Table altered.... Hurray.WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-27985542629942624252013-11-12T04:30:00.001-08:002013-12-17T03:39:10.452-08:00Oracle. Who Is Waiting, Who Is LockingThis Oracle (tested on 11g) SQL script shows who is waiting on locked tables. <br /><br /><div class="snippet">SELECT SUBSTR(TO_CHAR(w.session_id),1,5) "W.SID", p1.spid "W.PID", SUBSTR(s1.username,1,12) "W.User", SUBSTR(s1.osuser,1,8) "W.OS User", SUBSTR(s1.program,1,20) "W.Program", s1.client_info "W.Client", '&lt;&lt; WAITING | HOLDING &gt;&gt;' "|", SUBSTR(TO_CHAR(h.session_id),1,5) "H.SID", p2.spid "H.PID", SUBSTR(s2.username,1,12) "H.User", SUBSTR(s2.osuser,1,8) "H.OS User", SUBSTR(s2.program,1,20) "H.Program", s2.client_info "H.Client", o.object_name "H.Object" FROM gv$process p1, gv$process p2, gv$session s1, gv$session s2, dba_locks w, dba_locks h, dba_objects o WHERE w.last_convert &gt; 60 AND h.mode_held != 'None' AND h.mode_held != 'Null' AND w.mode_requested != 'None' AND s1.row_wait_obj# = o.object_id AND w.lock_type(+) = h.lock_type AND w.lock_id1(+) = h.lock_id1 AND w.lock_id2 (+) = h.lock_id2 AND w.session_id = s1.sid (+) AND h.session_id = s2.sid (+) AND s1.paddr = p1.addr (+) AND s2.paddr = p2.addr (+) ORDER BY w.last_convert DESC ; </div><i>Waiting......</i>WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-36296887511582603012013-06-18T07:11:00.000-07:002013-06-18T07:13:41.563-07:00Unprotecting Excel WorksheetsAfter being given an Excel spreadsheet and being asked to modify it and finding the Worksheets were protected, I came across this website:<br/><br/><a href="http://mcgimpsey.com/excel/removepwords.html">McGimpsey &amp; Associates</a><br/><br/>All the explaination is on their website, but I took the macro code below, ran it and within a few seconds my worksheet was unprotected.<br/><br/><div class="snippet">Public Sub AllInternalPasswords() ' Breaks worksheet and workbook structure passwords. Bob McCormick ' probably originator of base code algorithm modified for coverage ' of workbook structure / windows passwords and for multiple passwords ' ' Norman Harker and JE McGimpsey 27-Dec-2002 (Version 1.1) ' Modified 2003-Apr-04 by JEM: All msgs to constants, and ' eliminate one Exit Sub (Version 1.1.1) ' Reveals hashed passwords NOT original passwords Const DBLSPACE As String = vbNewLine & vbNewLine Const AUTHORS As String = DBLSPACE & vbNewLine & _ "Adapted from Bob McCormick base code by" & _ "Norman Harker and JE McGimpsey" Const HEADER As String = "AllInternalPasswords User Message" Const VERSION As String = DBLSPACE & "Version 1.1.1 2003-Apr-04" Const REPBACK As String = DBLSPACE & "Please report failure " & _ "to the microsoft.public.excel.programming newsgroup." Const ALLCLEAR As String = DBLSPACE & "The workbook should " & _ "now be free of all password protection, so make sure you:" & _ DBLSPACE & "SAVE IT NOW!" & DBLSPACE & "and also" & _ DBLSPACE & "BACKUP!, BACKUP!!, BACKUP!!!" & _ DBLSPACE & "Also, remember that the password was " & _ "put there for a reason. Don't stuff up crucial formulas " & _ "or data." & DBLSPACE & "Access and use of some data " & _ "may be an offense. If in doubt, don't." Const MSGNOPWORDS1 As String = "There were no passwords on " & _ "sheets, or workbook structure or windows." & AUTHORS & VERSION Const MSGNOPWORDS2 As String = "There was no protection to " & _ "workbook structure or windows." & DBLSPACE & _ "Proceeding to unprotect sheets." & AUTHORS & VERSION Const MSGTAKETIME As String = "After pressing OK button this " & _ "will take some time." & DBLSPACE & "Amount of time " & _ "depends on how many different passwords, the " & _ "passwords, and your computer's specification." & DBLSPACE & _ "Just be patient! Make me a coffee!" & AUTHORS & VERSION Const MSGPWORDFOUND1 As String = "You had a Worksheet " & _ "Structure or Windows Password set." & DBLSPACE & _ "The password found was: " & DBLSPACE & "$$" & DBLSPACE & _ "Note it down for potential future use in other workbooks by " & _ "the same person who set this password." & DBLSPACE & _ "Now to check and clear other passwords." & AUTHORS & VERSION Const MSGPWORDFOUND2 As String = "You had a Worksheet " & _ "password set." & DBLSPACE & "The password found was: " & _ DBLSPACE & "$$" & DBLSPACE & "Note it down for potential " & _ "future use in other workbooks by same person who " & _ "set this password." & DBLSPACE & "Now to check and clear " & _ "other passwords." & AUTHORS & VERSION Const MSGONLYONE As String = "Only structure / windows " & _ "protected with the password that was just found." & _ ALLCLEAR & AUTHORS & VERSION & REPBACK Dim w1 As Worksheet, w2 As Worksheet Dim i As Integer, j As Integer, k As Integer, l As Integer Dim m As Integer, n As Integer, i1 As Integer, i2 As Integer Dim i3 As Integer, i4 As Integer, i5 As Integer, i6 As Integer Dim PWord1 As String Dim ShTag As Boolean, WinTag As Boolean Application.ScreenUpdating = False With ActiveWorkbook WinTag = .ProtectStructure Or .ProtectWindows End With ShTag = False For Each w1 In Worksheets ShTag = ShTag Or w1.ProtectContents Next w1 If Not ShTag And Not WinTag Then MsgBox MSGNOPWORDS1, vbInformation, HEADER Exit Sub End If MsgBox MSGTAKETIME, vbInformation, HEADER If Not WinTag Then MsgBox MSGNOPWORDS2, vbInformation, HEADER Else On Error Resume Next Do 'dummy do loop For i = 65 To 66: For j = 65 To 66: For k = 65 To 66 For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66 For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66 For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126 With ActiveWorkbook .Unprotect Chr(i) & Chr(j) & Chr(k) & _ Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _ Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) If .ProtectStructure = False And _ .ProtectWindows = False Then PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _ Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) MsgBox Application.Substitute(MSGPWORDFOUND1, _ "$$", PWord1), vbInformation, HEADER Exit Do 'Bypass all for...nexts End If End With Next: Next: Next: Next: Next: Next Next: Next: Next: Next: Next: Next Loop Until True On Error GoTo 0 End If If WinTag And Not ShTag Then MsgBox MSGONLYONE, vbInformation, HEADER Exit Sub End If On Error Resume Next For Each w1 In Worksheets 'Attempt clearance with PWord1 w1.Unprotect PWord1 Next w1 On Error GoTo 0 ShTag = False For Each w1 In Worksheets 'Checks for all clear ShTag triggered to 1 if not. ShTag = ShTag Or w1.ProtectContents Next w1 If ShTag Then For Each w1 In Worksheets With w1 If .ProtectContents Then On Error Resume Next Do 'Dummy do loop For i = 65 To 66: For j = 65 To 66: For k = 65 To 66 For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66 For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66 For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126 .Unprotect Chr(i) & Chr(j) & Chr(k) & _ Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) If Not .ProtectContents Then PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _ Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) MsgBox Application.Substitute(MSGPWORDFOUND2, _ "$$", PWord1), vbInformation, HEADER 'leverage finding Pword by trying on other sheets For Each w2 In Worksheets w2.Unprotect PWord1 Next w2 Exit Do 'Bypass all for...nexts End If Next: Next: Next: Next: Next: Next Next: Next: Next: Next: Next: Next Loop Until True On Error GoTo 0 End If End With Next w1 End If MsgBox ALLCLEAR & AUTHORS & VERSION & REPBACK, vbInformation, HEADER End Sub </div><br/><i>Many Thanks McGimpsey &amp; Associates</i>WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-34907550859416575232013-05-29T04:50:00.001-07:002013-05-29T04:51:50.051-07:00Css ... But only for ChromeWe all know browsers like to apply css differently. So to only apply certain css rules to chrome, wrap the chrome specific code as so.<br/><br/><div class="snippet">@media screen and (-webkit-min-device-pixel-ratio:0) { .myclass{ padding-top:1px; } /* Correct alignment */ } </div><i>Easy.</i>WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-49276420955782661052013-02-20T03:23:00.000-08:002013-02-20T03:23:46.593-08:00Oracle: Querying XML from a CLOB (Part 2)Using Oracle 11g again...Moving on from yesterdays XML examples, I added some more complexity and things started to go wrong!<br/><ul><li>Why am I getting null values back?</li><li>How do I get attribute values?</li><li>Does this method scale to multiple rows?</li></ul>After some investigation...<br/>I'm getting null values back because my new XML had a default namespace. A default namespace needs to be passed into the extract function, otherwise it returns null. I could just hard code this in, or have a variable, but I think that would be a pain, so I've added a new inline query to get the default namespace value dynamically<br/><br/>Getting attribute values is straight forward XPath stuff, just use the @ symbol. Easy.<br/><br/>But all of my data is being concatinated together in one row! Well of course is it, the table it's selecting from only has one row. So this needs the XML to be converted into a table.<br/><br/>OK Here we go.<br/><br/>1. Create the table (if not done so on the previous post)<br/><br/><div class="snippet">CREATE TABLE xml_test ( id NUMBER(9,0), xml CLOB ); </div> 2. Insert some XML with 2 records, a default namespace and an attribute.<br/><br/><div class="snippet">INSERT INTO xml_test VALUES ( 2, '&lt;?xml version="1.0" encoding="iso-8859-1"?>&lt;mydata MyAttribute="Fuzzy" xmlns:="http://www.abc.com"> &lt;dude> &lt;id>100&lt;/id> &lt;name>John&lt;/name> &lt;address> &lt;line1>10 High Road&lt;/line1> &lt;line2>London&lt;/line2> &lt;/address> &lt;phone>123456789&lt;/phone> &lt;/dude> &lt;dude> &lt;id>101&lt;/id> &lt;name>Frank&lt;/name> &lt;address> &lt;line1>11 High Road&lt;/line1> &lt;line2>London&lt;/line2> &lt;/address> &lt;phone>987654321&lt;/phone> &lt;/dude>&lt;/mydata>'); </div>3. Query the data with the new query<br/><br/><div class="snippet">WITH xmlData AS ( SELECT xt.id, XMLTYPE(xt.xml) xml FROM xml_test xt WHERE xt.id = 2 ), namespace AS ( SELECT xd.id, 'xmlns="'||XMLCAST(XMLQUERY('namespace-uri(.)' PASSING xd.xml RETURNING CONTENT) AS VARCHAR2(4000))||'"' defaultNs FROM xmlData xd ) SELECT xd.id, xd.xml.EXTRACT('//mydata/@MyAttribute',ns.defaultNs).GETSTRINGVAL() myAttribute, x.COLUMN_VALUE.EXTRACT('//dude/name/text()',ns.defaultNs).GETSTRINGVAL() dudeName, x.COLUMN_VALUE.EXTRACT('//dude/address/line1/text()',ns.defaultNs).GETSTRINGVAL() dudeAddress FROM xmlData xd, namespace ns, TABLE(XMLSEQUENCE(EXTRACT(xd.xml,'/mydata/dude',ns.defaultNs))) x WHERE xd.id = ns.id ORDER BY 1 ; </div>And you should get...<br/><br/><div class="snippet">ID MYATTRIBUTE DUDENAME DUDEADDRESS -- ----------- -------- ------------ 2 Fuzzy John 10 High Road 2 Fuzzy Frank 11 High Road </div><i>Phew</i> WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-53853646371735974792013-02-19T08:01:00.001-08:002013-02-19T08:01:20.580-08:00Oracle: Querying XML from a CLOBUsing Oracle 11g, a quick example of one way to query XML data from a CLOB.<br/><br/>1. Create the table.<br/><br/><div class="snippet">CREATE TABLE xml_test ( id NUMBER(9,0), xml CLOB ); </div>2. Insert some xml data<br/><br/><div class="snippet">INSERT INTO xml_test VALUES ( 1, '&lt;?xml version="1.0" encoding="iso-8859-1"?>&lt;mydata> &lt;id>100&lt;/id> &lt;name>John&lt;/name> &lt;address> &lt;line1>10 High Road&lt;/line1> &lt;line2>London&lt;/line2> &lt;/address> &lt;phone>123456789&lt;/phone>&lt;/mydata>'); </div>3. Query it<br/><br/><div class="snippet">SELECT XMLTYPE(t.xml).EXTRACT('//name/text()').getStringVal(), XMLTYPE(t.xml).EXTRACT('//address/line1/text()').getStringVal() FROM xml_test t; </div>4. Alternative query<br/><br/><div class="snippet">WITH xmlData AS ( SELECT XMLTYPE(t.xml) xml FROM xml_test t WHERE t.id = 1 ) SELECT x.xml.EXTRACT('//name/text()').getStringVal(), x.xml.EXTRACT('//address/line1/text()').getStringVal() FROM xmlData x ; </div>Next step: <a href="http://projectwownow.blogspot.co.uk/2013/02/oracle-reading-text-file-into-clob.html">Load the XML data into the table from a file.</a>WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-21823024714444175692013-02-19T07:55:00.000-08:002013-02-19T08:02:55.413-08:00Oracle: Reading a Text File into a CLOBUsing Oracle11g, this procedure reads a text file, in this case an xml file, into a CLOB.<br/><br/><div class="snippet">DECLARE l_clob CLOB; l_bfile BFILE := BFILENAME('SCRATCH','test.xml'); BEGIN INSERT INTO xml_test ( id, xml ) VALUES ( 2, EMPTY_CLOB() ) RETURNING xml INTO l_clob; DBMS_LOB.OPEN(l_bfile, DBMS_LOB.LOB_READONLY); DBMS_LOB.LOADFROMFILE ( dest_lob => l_clob, src_lob => l_bfile, amount => DBMS_LOB.GETLENGTH(l_bfile) ); DBMS_LOB.CLOSE(l_bfile); END; / </div>If you need to worry about different character sets etc, then use DBMS_LOB.LOADCLOBFROMFILE, which has several more parameters.<br/><br/><i>File Read and Inserted.</i>See also: <a href="http://projectwownow.blogspot.co.uk/2013/02/oracle-querying-xml-from-clob_19.html">Querying XML from a CLOB</a>WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-7018521126622505402013-02-15T07:23:00.000-08:002013-12-17T03:40:59.726-08:00Timing Sections of PLSQL CodeYour PLSQL routine is running like a dog with three legs? Don't know where to start looking as it could be any of a number of issues? Then adapt this timing code and drop it in for testing. Wrap the start and end calls around suspected sections of code to find where the hold up is occuring.<br/><br/><div class="snippet">DECLARE ----------------------------- -- Timing Declaration Start TYPE tt_timingStart IS TABLE OF TIMESTAMP(9) INDEX BY VARCHAR2(30); l_timingStart tt_timingStart; TYPE tt_timingTotal IS TABLE OF NUMBER INDEX BY VARCHAR2(30); l_timingCount tt_timingTotal; l_timingTotal tt_timingTotal; -- PROCEDURE timingStart( p_timingIndex IN VARCHAR2 ) IS BEGIN BEGIN IF l_timingTotal(p_timingIndex) IS NULL THEN NULL; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN l_timingCount(p_timingIndex) := 0; l_timingTotal(p_timingIndex) := 0; END; l_timingStart(p_timingIndex) := SYSTIMESTAMP; END timingStart; -- PROCEDURE timingEnd( p_timingIndex IN VARCHAR2 ) IS l_duration INTERVAL DAY(9) TO SECOND(5) := SYSTIMESTAMP - l_timingStart(p_timingIndex); BEGIN l_timingCount(p_timingIndex) := l_timingCount(p_timingIndex) + 1; l_timingTotal(p_timingIndex) := l_timingTotal(p_timingIndex) + (EXTRACT(SECOND FROM l_duration)*1000) + (EXTRACT(MINUTE FROM l_duration)*60000) + (EXTRACT(HOUR FROM l_duration)*3600000) + (EXTRACT(DAY FROM l_duration)*86400000); END timingEnd; -- PROCEDURE timingOutput IS l_file UTL_FILE.FILE_TYPE; l_index VARCHAR2(30); BEGIN l_file := UTL_FILE.FOPEN('SCRATCH_DIR','TimingOutput.txt','W'); l_index := l_timingTotal.FIRST; WHILE l_index IS NOT NULL LOOP UTL_FILE.PUT_LINE(l_file,LPAD(l_index,20,'_')||': ' ||LPAD(ROUND(l_timingCount(l_index)),10)||'iterations' ||LPAD(ROUND(l_timingTotal(l_index)),10)||'ms' ||LPAD(ROUND(l_timingTotal(l_index)/1000),10)||'s' ||LPAD(ROUND(l_timingTotal(l_index)/60000),10)||'m'); l_index := l_timingTotal.NEXT(l_index); END LOOP; UTL_FILE.FCLOSE(l_file); END timingOutput; -- Timing Declaration End ----------------------------- BEGIN timingStart('Procedure'); FOR i IN 1..20 LOOP timingStart('First Sleep'); DBMS_LOCK.SLEEP(0.3); timingEnd('First Sleep'); timingStart('Second Sleep'); DBMS_LOCK.SLEEP(0.5); timingEnd('Second Sleep'); timingStart('Third Sleep'); DBMS_LOCK.SLEEP(0.1); timingEnd('Third Sleep'); END LOOP; timingEnd('Procedure'); timingOutput; END; / </div>This does assume there is a SCRATCH directory to output the file to. If you haven't got one it's easily modified to DBMS_OUTPUT.PUT_LINE.<br/><br/><i>Timed</i>WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-24236112824995996422013-02-08T02:21:00.000-08:002013-02-15T07:23:23.733-08:00Oracle Processes CPU UsageIs that long running job doing anything? This SQL query (Oracle 11) will list user processes and the amount of CPU usage the current job has used so far. Re-query to see how the CPU usage progesses.<br/><br/><div class="snippet">SELECT vs.username, vs.sid, vs.serial#, vp.spid, vs.machine, vs.module, vs.program, vs.action, vs.status, vs.last_call_et, ROUND((vss_sess.value-vss_call.value)/100) "CPU Usage" FROM gv$session vs, gv$process vp, v$sesstat vss_sess, v$sesstat vss_call WHERE vs.paddr = vp.addr(+) AND vs.inst_id = vp.inst_id(+) AND vs.sid = vss_sess.sid AND vs.sid = vss_call.sid AND vss_call.statistic# = 13 -- CPU used when call started AND vss_sess.statistic# = 14 -- CPU used by this session AND vs.username IS NOT NULL ORDER BY ( CASE vs.status WHEN 'INACTIVE' THEN 0 ELSE vss_sess.value-vss_call.value END ) DESC, vs.status, vs.last_call_et; </div>Maybe useful here...<br/><br/><b>Several ways to kill a process.</b><br/><br/>The kill session syntax. The immediate option will not kill the session faster, however it will return the prompt immediately after the statement has been issued. <br/><br/><div class="snippet">ALTER SYSTEM KILL SESSION 'sid,serial#'; ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; </div>The kill session statement asks the session to end, whereas the disconnect session statement cuts the session process.<br/><br/><div class="snippet">ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION; ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE; </div>The post transaction option is equivilent to kill session.<br/><br/>For those even more stubborn processes, you need to move to the operating system, using the spid value from the query above.<br/><br/>In Windows:<br/><div class=snippet>orakill ORACLE_SID spid </div>In UNIX:<br/><div class=snippet>kill -9 spid </div><br/><i>Done and Killed (hopefully).</i> WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-25528188876178143912013-01-28T06:29:00.001-08:002013-02-15T07:23:39.176-08:00Solving ORA-28001 Without Changing the PasswordUnexpectedly, several months after upgrading to Oracle 11g, our SYSMAN and DBSNMP account passwords expired, creating lots of errors in our audit logs. We never had this with 10g, they never expired.<br/><br/>Not wanting to dive into lots of unfamilar config files, we looked at unexpiring the accounts, keeping the existing passwords.<br/><br/>A little googling and tweaking later, the following SQL command outputs alter user statements to "unexpire" the accounts.<br/><br/>I'm not sure if this method is supported by Oracle or not but it worked for us.<br/><br/><div class="snippet">SELECT 'ALTER USER '||u.name||' IDENTIFIED BY VALUES '''||u.spare4||';'||u.password||''';' cmd FROM sys.user$ u WHERE u.name IN ('SYSMAN','DBSNMP') ; </div><i>User altered</i>WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-34750630580964327262012-10-09T08:20:00.000-07:002012-10-09T08:22:10.937-07:00Oracle Function to Split String into an ArrayThis deterministic function creates a general function for converting a comma seperated string into an array.<br/><br/>First of all you need to create a varchar2 array type. I've found it very useful to create a generic varchar2 array I can use anywhere.<br/><br/><div class="snippet">CREATE TYPE "VARCHAR2_ARRAY" AS TABLE OF VARCHAR2(32767) </div>Now the function...<br/><br/><div class="snippet">CREATE OR REPLACE FUNCTION string_to_array ( p_string IN VARCHAR2, p_seperator IN VARCHAR2 DEFAULT ',' ) RETURN VARCHAR2_ARRAY DETERMINISTIC IS l_string VARCHAR2(4000) := p_seperator||p_string||p_seperator; l_array VARCHAR2_ARRAY := VARCHAR2_ARRAY(); BEGIN IF LENGTH(p_seperator) != 1 THEN RAISE_APPLICATION_ERROR(-20101,'Seperator should only be one character'); END IF; SELECT SUBSTR(l_string, INSTR(l_string,p_seperator,1,LEVEL)+1, INSTR(l_string,p_seperator,1,LEVEL+1) - INSTR(l_string,p_seperator,1,LEVEL)-1 ) token BULK COLLECT INTO l_array FROM DUAL CONNECT BY LEVEL < LENGTH(l_string)-LENGTH(REPLACE(l_string,p_seperator,'')); RETURN(l_array); END; / </div><br/>String Split...Done.WowNownoreply@blogger.com2tag:blogger.com,1999:blog-7145781531950557193.post-37689879330065974802012-09-20T06:44:00.001-07:002012-09-20T07:29:09.794-07:00PLS-00201: identifier 'NVL2' must be declaredHaving just tried to use NVL2 inside PLSQL I have discovered it isn't there! This is one of those functions that only exist for SQL not PLSQL. Like DECODE.<br/><br/>NVL2 is quite simple and easily rewritten with IF or CASE statements but these can sometimes make code messy. NVL2 in SQL can just be concatinated within a string and is one command. (Not always a good reason for using it but in some cases...)<br/><br/>Anyway, to get around the problem in these few circumstances, you can create your own function.<br/><br/><div class="snippet">CREATE OR REPLACE FUNCTION NVL2 ( p_value IN VARCHAR2, p_newValueIfNotNull IN VARCHAR2, p_newValueIfNull IN VARCHAR2 ) RETURN VARCHAR2 DETERMINISTIC IS BEGIN IF p_value IS NOT NULL THEN RETURN(p_newValueIfNotNull); ELSE RETURN(p_newValueIfNull); END IF; END; / </div><br/>The function will be used when called from PLSQL, the built-in will be used when called from SQL.<br/><br/><i>Sorted</i>WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-33652180921087888512012-09-17T06:09:00.002-07:002012-09-17T06:11:37.689-07:00Diagnose ORA-02292 Errors When Deleting Records in OracleWorking on an undocumented system and got the error “ORA-02292: integrity constraint (…) violated - child record found” when trying to delete records? This solution provides an anonymous procedure for running in SQL*Plus, Developer, etc, to get the child relationships for a specific table, so you can easily understand the constraints preventing the record being deleted.<br/><br/>The procedure needs two parameters, the start table and the rowid of the record. The following code can be prefixed to the procedure in the script to set up these variables. <br/><br/><div class="snippet">VAR vRowId VARCHAR2(20) VAR vSchema VARCHAR2(30) VAR vStartTable VARCHAR2(30) BEGIN :vSchema := USER; :vStartTable := 'MY_TABLE'; SELECT ROWID INTO :vRowId FROM my_table WHERE my_table_id = 5333; END; / </div>Adjust the query or just add the rowid reference directly, as required. The procedure then uses these values to produce the output, which will be specifically for the record with the rowid selected. Relationships with no data will not be displayed, so you can track down the cause of the ORA-02292 error.<br/><br/><div class="snippet">DECLARE --//------------------------------ c_schema CONSTANT VARCHAR2(30) := :vSchema; c_startTable CONSTANT VARCHAR2(30) := :vStartTable; c_startRowId CONSTANT VARCHAR2(20) := :vRowId; --//------------------------------ TYPE t_consColumns IS TABLE OF user_cons_columns.column_name%TYPE; TYPE t_results IS TABLE OF ROWID; TYPE t_processed IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(50); g_processed t_processed; --//------------------------------ FUNCTION getChildRowids ( p_parentTable IN VARCHAR2, p_childTable IN VARCHAR2, p_parentColumns IN t_consColumns, p_childColumns IN t_consColumns, p_rowid IN ROWID ) RETURN t_results IS l_query VARCHAR2(4000); l_results t_results; BEGIN l_query := 'SELECT c.ROWID' || ' FROM '||c_schema||'.'||p_parentTable||' p,' || c_schema||'.'||p_childTable ||' c' ||' WHERE p.ROWID = '''||p_rowid||''''; FOR i IN 1..p_parentColumns.COUNT LOOP l_query := l_query||' AND p.'||p_parentColumns(i)||' = c.'||p_childColumns(i); END LOOP; EXECUTE IMMEDIATE l_query BULK COLLECT INTO l_results; RETURN(l_results); END getChildRowids; --//------------------------------ FUNCTION getConstraintColumns ( p_constraintName IN VARCHAR2 ) RETURN t_consColumns IS l_columnNames t_consColumns; BEGIN SELECT column_name BULK COLLECT INTO l_columnNames FROM dba_cons_columns WHERE owner = c_schema AND constraint_name = p_constraintName; RETURN(l_columnNames); END getConstraintColumns; --//------------------------------ FUNCTION alreadyProcessed ( p_tableName IN VARCHAR2, p_rowid IN ROWID ) RETURN BOOLEAN IS l_return BOOLEAN := FALSE; BEGIN IF g_processed(p_tableName||'.'||p_rowid)=1 THEN NULL; END IF; RETURN(TRUE); EXCEPTION WHEN NO_DATA_FOUND THEN g_processed(p_tableName||'.'||p_rowid) := 1; RETURN(FALSE); END; --//------------------------------ PROCEDURE processTable ( p_tableName IN VARCHAR2, p_rowid IN ROWID ) IS l_parentColumns t_consColumns; l_childColumns t_consColumns; l_results t_results; BEGIN IF NOT alreadyProcessed(p_tableName,p_rowid) THEN FOR c IN ( SELECT parent.constraint_name parentConstraint, child.constraint_name childConstraint, child.table_name childTable FROM dba_constraints parent, dba_constraints child WHERE parent.table_name = p_tableName AND parent.constraint_name = child.r_constraint_name AND child.r_owner = c_schema AND parent.owner = c_schema ) LOOP l_parentColumns := getConstraintColumns(c.parentConstraint); l_childColumns := getConstraintColumns(c.childConstraint); l_results := getChildRowids(p_tableName, c.childTable, l_parentColumns, l_childColumns, p_rowid); FOR r IN 1..l_results.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Found child records'); DBMS_OUTPUT.PUT_LINE('- for: '||p_tableName||' ['||p_rowid||']'); DBMS_OUTPUT.PUT_LINE('- at: '||c.childTable||' ['||l_results(r)||']'); DBMS_OUTPUT.PUT_LINE('- via: '||c.childConstraint||' -> '||c.parentConstraint); processTable(c.childTable, l_results(r)); END LOOP; END LOOP; END IF; END processTable; --//------------------------------ BEGIN DBMS_OUTPUT.PUT_LINE('Processing '||c_startTable||' ['||c_startRowId||']'); processTable(c_startTable, c_startRowId); END; / </div><br/>The main sources of data are the views dba_constraints and dba_cons_columns. The procedure uses these to build a dynamic query to check for related data in the child tables. If data is found it outputs the result, then checks down that branch for further child tables.<br/><br/>The procedure only checks one schema, so relationships between schemas will not be included in the search.<br/><br/><i>Child Records Found</i> WowNownoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-20377219229794109572012-09-11T02:59:00.002-07:002012-09-11T03:03:18.255-07:00Oracle Procedure to Write a CLOB to a FileI've seen several ways to do this but this way works nicely for me. (Using Oracle 11g)<br/><br/><div class="snippet">CREATE OR REPLACE PROCEDURE clob_to_file ( p_directory IN VARCHAR2, p_filename IN VARCHAR2, p_clob IN CLOB ) IS c_chunk CONSTANT PLS_INTEGER := 32767; l_fHandler UTL_FILE.FILE_TYPE; l_pos PLS_INTEGER := 1; BEGIN l_fHandler := UTL_FILE.FOPEN(p_directory, p_filename, 'W', c_chunk); WHILE l_pos < DBMS_LOB.GETLENGTH(p_clob) LOOP UTL_FILE.PUT(l_fHandler, DBMS_LOB.SUBSTR(p_clob, c_chunk, l_pos)); l_pos := l_pos + c_chunk; UTL_FILE.FFLUSH(l_fHandler); END LOOP; UTL_FILE.FCLOSE(l_fHandler); END; / </div>The UTL_FILE package writes the clob in 32767 byte chunks. This has a limitation that each 32K chunk has to have a carriage return (I've not come across an output without one, so not captured and processed the error here but you could easily), otherwise you get the following error:<br/><br/><div class="snippet">ORA-29285: file write error ORA-06512: at "SYS.UTL_FILE", line 183 ORA-06512: at "SYS.UTL_FILE", line 1169 ORA-06512: at "MYSCHEMA.CLOB_TO_FILE", line 16 ORA-06512: at line 8 </div><br/><i>Written.</i>WowNownoreply@blogger.com0