tag:blogger.com,1999:blog-71457815319505571932024-03-12T19:46:36.584-07:00Project WowNowSoftware code snippets and other stuffUnknownnoreply@blogger.comBlogger95125tag:blogger.com,1999:blog-7145781531950557193.post-1897849946768576482019-09-13T07:41:00.000-07:002019-09-13T07:41:35.583-07:00Python: Which is Quicker to Search a String, Find or In? There are many ways to do the same task in Python, as with most things. Here I wanted to know which was quicker to see if a string contained a space:
<div class="snippet">if x.find(' ') != -1:</div>
or
<div class="snippet">if ' ' in x:</div>
So a little test...
<div class="snippet">import datetime
x = 'abc def'
r = 100000000
def test_a():
if x.find(' ') != -1:
pass
def test_b():
if ' ' in x:
pass
start = datetime.datetime.now()
for a in range(0, r):
test_a()
print('Test A: ' + str(datetime.datetime.now()-start))
start = datetime.datetime.now()
for a in range(0, r):
test_b()
print('Test B: ' + str(datetime.datetime.now()-start))
</div>
... found that ...
<div class="snippet">Test A: 0:00:27.240583
Test B: 0:00:13.469368
</div>
"in" being the clear winner here and no real surprise as "find" has to do more work to return the character's position.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-70059694531052826102019-02-21T04:41:00.001-08:002019-09-13T07:41:49.362-07:00HTML Character CodesThere are several websites which list the popular HTML Character Codes but if you need to view them all, this short HTML Javascript snippet will list all the numbered codes in your browser.<br />
<br />
<div class="snippet"><html>
<head>
<style>
table {
border-collapse: collapse;
}
th {
width: 3em;
padding: 0.6em;
background: lightgray;
color: darkslategray;
font-size: 0.6em;
}
td {
width: 3em;
padding: 0.6em;
display: inline-block;
border: 1px solid lightgray;
text-align: center;
}
</style>
</head>
<body>
<table>
<script>
for (i=0; i<80000; i++) {
document.write('<tr><th>&#' + i + ';</th><td> &#' + i + '; </td></tr>');
}
</script>
</table>
</body>
</html>
</div>
<div style="font-size:3em">✋</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-27208250358937731362019-01-22T02:45:00.002-08:002019-01-22T02:46:25.053-08:00Adding Floating Point Numbers in PythonI've recently been doing some work in Python and noticed that it can't add floating point numbers correctly! I Googled it and found that this isn't a bug, it's expected because the C code underneath converts the floating point into a fraction to add the numbers and not all numbers can be converted to fractions and vice-versa!!<br/>
<br/>It may be expected but it is definitely in no way correct.<br/><br/>
With my limited knowledge I've created this function to convert the floating point numbers to decimals, perform the addition, then convert back to floating point.
<br/><br/>
<div class="snippet">def add_floats(v):
multiplier = 1
for i in v:
multiplier = max(len(str(i)) - str(i).find('.'), multiplier)
multiplier = 10**multiplier
total = 0
for i in v:
total += (i * multiplier)
total = total / multiplier
return total
x = float('1.41')
y = float('1.4')
z = x+y
print(x+y)
print(add_floats([x, y]))
</div>
Output:<br/>
<br/>
<div class="snippet">2.8099999999999996
2.81
</div>
My Python knowledge is limited, so if anyone wishes to improve this, please let me know.
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-43665560949880529492016-03-07T02:22:00.001-08:002019-09-13T07:42:04.840-07: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> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7145781531950557193.post-91899336437184536852016-01-26T07:05:00.002-08:002019-01-22T02:45:37.852-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/>
:-)
Unknownnoreply@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
NULL;
END LOOP;
END LOOP;
END;
/</div>
<br />
Done.Unknownnoreply@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.
Unknownnoreply@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>
Unknownnoreply@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.Unknownnoreply@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.
Unknownnoreply@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.Unknownnoreply@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.
Unknownnoreply@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.
Unknownnoreply@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 <br />
<br />Unknownnoreply@blogger.com1tag: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: <i>"RENAME table-Name TO new-Table-Name"</i><br />
<br />
This now gave me error <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.Unknownnoreply@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",
'<< WAITING | HOLDING >>' "|",
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 > 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>
Unknownnoreply@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 & 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 & Associates</i>Unknownnoreply@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>
Unknownnoreply@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,
'<?xml version="1.0" encoding="iso-8859-1"?>
<mydata MyAttribute="Fuzzy" xmlns:="http://www.abc.com">
<dude>
<id>100</id>
<name>John</name>
<address>
<line1>10 High Road</line1>
<line2>London</line2>
</address>
<phone>123456789</phone>
</dude>
<dude>
<id>101</id>
<name>Frank</name>
<address>
<line1>11 High Road</line1>
<line2>London</line2>
</address>
<phone>987654321</phone>
</dude>
</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>
Unknownnoreply@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,
'<?xml version="1.0" encoding="iso-8859-1"?>
<mydata>
<id>100</id>
<name>John</name>
<address>
<line1>10 High Road</line1>
<line2>London</line2>
</address>
<phone>123456789</phone>
</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>Unknownnoreply@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>Unknownnoreply@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>
Unknownnoreply@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>
Unknownnoreply@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>Unknownnoreply@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.Unknownnoreply@blogger.com2