Bypassing ORA-00942: table or view does not exist

The infamous ORA-00942: table or view does not exist error is a constant source of frustration with Oracle.

Ususally, the cause for this error is that someone is granted access to a table via a role rather than directly. So, the user can select from that_table but as soon as he uses the statement in a compiled PL/SQL source, it won't work anymore, erroring out with this ORA-00942.

I can demonstrate this easily: First, a fresh user is created and given a few privileges:

connect / as sysdba create user ipc_user identified by ipc_user; grant create procedure, create session, select_catalog_role to ipc_user;

The user has been granted select_catalog_role so he can do

select count(*) from v$process;

and

select count(*) from v$session;

Now, the user wants to create a stored procedure that shows him the amount of memory used for his process:

create or replace function tq84_proc_memory return varchar2 as v_result varchar2(200); begin select 'Used: ' || round(pga_used_mem /1024/1024)||', '|| 'Alloc: ' || round(pga_alloc_mem /1024/1024)||', '|| 'Freeable: ' || round(pga_freeable_mem/1024/1024)||', '|| 'PGA Max: ' || round(pga_max_mem /1024/1024) into v_result from v$process where addr = (select paddr from v$session where sid = sys_context('USERENV','SID')); return v_result; end tq84_proc_memory; /

This procedure won't compile for the reasons outlined in the beginning of this post:

-- 5/5 PL/SQL: SQL Statement ignored -- 15/33 PL/SQL: ORA-00942: table or view does not exist

So, what to do? For such reasons, I have created the IPC PL/SQL package. The package's function exec_plsql_in_other_session uses dbms_job to create another session that can make full use of the granted privileges and also uses dbms_pipe to return a value to the caller.

Of course, I need to grant the required privileges also:

grant execute on dbms_job to ipc_user; grant execute on dbms_pipe to ipc_user; grant create job to ipc_user;

After installing the packages, I can rewrite my function like so:

create or replace function tq84_proc_memory return varchar2 as v_result varchar2(200); begin select 'Used: ' || round(pga_used_mem /1024/1024)||', '|| 'Alloc: ' || round(pga_alloc_mem /1024/1024)||', '|| 'Freeable: ' || round(pga_freeable_mem/1024/1024)||', '|| 'PGA Max: ' || round(pga_max_mem /1024/1024) into v_result from v$process where addr = (select paddr from v$session where sid = sys_context('USERENV','SID')); return v_result; end tq84_proc_memory; /

Now, I can use the function to report some memory figures

select tq84_proc_memory from dual;

Links

Github:

SQL demonstration script

A PL/SQL bug

Today, I discovered what seems to be a PL/SQL bug. Here's the relevant part:

if guid_ is null then dbms_output.put_line('guid_ is null: ' || guid_); end if;

Under some special circumstances, it prints

guid_ is null: 07D242FCC55000FCE0530A30D4928A21

Of course, this is impossible if PL/SQL were executing correctly. Either guid_ is null or it has a value. Since the if statement executes the dbms_output line, I should assume that guid_ is indeed null. Yet, a value for guid_ is printed.

This behaviour can be reproduced, at least on Oracle 11R2, with the following code:

create type tq84_t as table of varchar2(32); / create type tq84_o as object ( dummy number(1), not final member procedure clear ) not final; / show errors create type tq84_d under tq84_o ( g varchar2(32), constructor function tq84_d return self as result, overriding member procedure clear ); / show errors create package tq84_h as t tq84_t; end tq84_h; / show errors create package body tq84_h as begin t := tq84_t(); end; / show errors create type body tq84_o as member procedure clear is begin null; end clear; end; / create type body tq84_d as constructor function tq84_d return self as result is begin g := sys_guid; return; end tq84_d; overriding member procedure clear is begin tq84_h.t.extend; tq84_h.t(tq84_h.t.count) := g; g := null; end clear; end; / show errors declare b tq84_o; -- Change to tq84_d ... guid_ varchar2(32); begin b := new tq84_d; guid_ := treat(b as tq84_d).g; b.clear; if guid_ is null then dbms_output.put_line('guid_ is null: ' || guid_); end if; end; / drop type tq84_t; drop type tq84_d; drop type tq84_o; drop package tq84_h;

I have also asked a question on stackoverflow.

The power of k-nearest-neighbor searches

I came across the k-nearest-neighbor (k-NN) algorithm recently. Altough it's a relatively simple algorithm, its power still amazed me.

K-NN can be used to classify sets of data when the algorithm is fed with some examples of classifications.

In order to demonstrate that, I have written a perl script. The script creates two csv files (known.csv and unknown) and a third file: correct.txt. The k-NN algorithm will use known.csv to train its understanding of a classification. Then, it tries to guess a classification for each record in unknown.csv. For comparing purposes, correct.txt contains the classification for each record in unknown.csv.

Structure of known.csv

known.csv is a csv file in which each record consists of 11 numbers. The first number is the classifaction for the record. It is a integer between 1 and 4 inclusively. The remaining 10 numbers are floats between 0 and 1.

Structure of unknown.csv

In unknown.csv, each record consists of 10 floats between 0 and 1. They correspond to the remaining 10 numbers in known.csv. The classification for the records in unkown.csv is missing in the file - it is the task of the k-NN algorithm to determine this classification. However, for each record in unknown.csv, the correct classification is found in correct.txt

Values for the floats

A record's classification determines the value-ranges for the floats in the record, according to the following graphic:
The four classifications are represented by the four colors red, green, blue and orange. The Perl script generated eight floats. For range for the first float for the red classification is [0.15,0.45], the range for the first float for the green classification is [0.35,0.65] etc. Similarly, the range for the second value of the red classification is [0.55,0.85] and so on.

To make things a bit more complicated, two random values in the range [0,1] are added to the eight values resulting in 10 values. These two values can either be at the beginning, at the end or one at the beginning and the other at the end.

Result

When I let the Perl script create 1000 unknown and known records, the following r script guesses more than 985 classifications right, most of the time.

library(FNN) known <- read.csv("known.csv" , header=FALSE) unknown <- read.csv("unknown.csv", header=FALSE) labels <- known[, 1] known <- known[,-1] results <- (0:4)[knn(known, unknown, labels, k = 10, algorithm="cover_tree")] write(results, file="guessed.txt", ncolumns=1)

Links

Source code on github

I find that rather impressive

Introductory Scripts for Active Session History and Automatic Workload Repository

A client of mine asked me to provide a short introductory talk on Oracle's Active Session History (ASH) and Automatic Workload Repository (AWR). I now have put the demonstration scripts on github at https://github.com/ReneNyffenegger/oracle_scriptlets/tree/master/ash-awr/Introduction-Nov-3rd-14. Maybe, someone can profit from the scripts?

Little things that make live easier #3: javascripts' Number.toFixed() for a fixed number of digits after the decimal point.

Javascript's Number object has the toFixed method that allows display a number with a fixed amount of digits after the decimal point. Unfortunately, it doesn't round the number.

function tf(n) { out.innerHTML += n.toFixed(3) + '<br>'; } tf(4 ); // 4.000 tf(4.2 ); // 4.200 tf(4.1004); // 4.100 tf(4.1009); // 4.100

Links

Source code on github