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.

http://www.oracle.com/technetwork/topics/winx64soft-089540.html

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 http://www.microsoft.com/en-us/download/details.aspx?id=14632.

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

SQL>

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; /

Done.

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.

EXEC SYS.DBMS_IJOB.REMOVE(?);
I'm using Oracle 11 but I believe that, although this is undocumented, this has been around for several years now.

Removed.

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 ;
Done.

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.