Monday, 7 March 2016

Installing SQLPlus on Windows 10 and "MSVCP100.dll Missing"

Firstly to install SQLPlus on Windows 10 I downloaded two packages from Oracle.

The first "Instant Client Package - Basic Lite" has the main connection packages.

The second "Instant Client Package - SQL*Plus" has the SQLPlus elements.

Unzip these to a folder. I used "C:\Oracle\instantclient_12_1".

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"

A quick Google search found that this is some kind of C++ library. I downloaded the necessary files from

I dropped my tnsnames.ora file into the instant client directory, ran SQLPlus again and voila.


Tuesday, 26 January 2016

Oracle Flashback Source PLSQL Code

Aaaaarrgh! Just modified some PLSQL in the database and lost what the previous version, with lots of cool stuff in I did this morning! :-(

Luckily, using the AS OF flashback clause I can rewind time and get the code back.
(NB: To get this to work I had to SSH to the server and connect as SYS via SqlPlus)

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 ;
Change as required and carry on.

Tuesday, 24 November 2015

Oracle SQL Tuning: Quick Test Template

Sometime 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.

BEGIN FOR a IN 1..1000 LOOP FOR b IN ( ... paste query here ... ) LOOP   NULL; END LOOP; END LOOP; END; /


Friday, 24 April 2015

Remove Oracle Job Process

Trying 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.

So first identify the job you are looking for using:

SELECT * FROM dba_jobs ;
Then use DBMS_IJOB to remove the job. Use the value from the JOB column above as the parameter value.

I'm using Oracle 11 but I believe that, although this is undocumented, this has been around for several years now.


Tuesday, 21 April 2015

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.

Tuesday, 24 March 2015

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 ;

Thursday, 19 February 2015

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.