Oracle Spatial, dynamic SQL, a view and the strange problem of a SDO_GEOMETRY bind variable

The title sound like an old italian joke and indeed I wasted 4 hours on this joke before having the epiphany.

Scenario:

A set of functions accepting a few parameters, in some cases one of the parameters can be a geometry object, while in other cases it's a CLOB containing a geometry in WKT format.

These functions must execute dynamic SQL queries built on some of these parameters.

The data suitable for testing lies in a different schema and the table's columns would need to be renamed, so I decided instead to create a view in that schema with the expected column names, then I granted SELECT to the schema containing the functions.

I ran the first test with functions working on CLOBs and they worked fine.

Then I ran a test on a function taking the SDO_GEOMETRY parameter whose result should be identical to the result of the previous test.

It wasn't, it returned nothing.

Among the dozens of experiments I made to sort out what was wrong, I altered the function such that it converts the SDO_GEOMETRY object back and forth within the OPEN-FOR-USING statement and to my surprise the function worked correctly.

But even if it worked well, I didn't like the fact I had to do this unnecessary step, so I continued investigating.

At a certain point, I decided to try bypassing the view, so I copied some data into a test table and repeated the test.

Now it worked

Then came the epiphany.

I granted SELECT on the base table of the view and repeated the test using the view.

It worked.

I revoked the SELECT  from the base table and, again, it stopped working.

Bingo.

Is it some sort of bug or is it expected behavior?
I am still undecided.

Certainly an error message would have helped to point me in the right direction much earlier.

At any rate the problem affects only the dynamic SQL containing a view in a different schema when passing a SDO_GEOMETRY value to the bind variable which is later used as argument to SDO_FILTER or any other spatial operator in the WHERE clause. If that bind variable is used as argument elsewhere, it seems to work regardless of the privilege on the base table.

I think I'll take a day off tomorrow, I have had enough for this week...

PLS-00457: expressions have to be of SQL types

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

PLS-00457: expressions have to be of SQL types

This error can be seen when attempting to compile a PL/SQL unit containing a FORALL statement in combination with EXECUTE IMMEDIATE, as follows:

create table tab_pls_435 (
col_a number,
col_b varchar2(30));

create or replace
procedure test_pls_435
is

type tab_type     is table of tab_pls_435%rowtype;
plsql_rec_tab     tab_type := tab_type();

begin
plsql_rec_tab.extend;
plsql_rec_tab(1).col_a           := 100;
plsql_rec_tab(1).col_b           := 'TEST';

forall i in 1..plsql_rec_tab.count
execute immediate
'insert into tab_pls_435
values :1'
using
plsql_rec_tab(i);
end;

The PL/SQL parser doesn't like the syntax of the FORALL statement in combination with EXECUTE IMMEDIATE and a collection of RECORD data type.

Note however that the program can be successfully compiled and executed if we make the FORALL statement static (as opposed to native dynamic):

create or replace
procedure test_pls_435
is

type tab_type     is table of tab_pls_435%rowtype;
plsql_rec_tab     tab_type := tab_type();

begin
plsql_rec_tab.extend;
plsql_rec_tab(1).col_a           := 100;
plsql_rec_tab(1).col_b           := 'TEST';

forall i in 1..plsql_rec_tab.count
insert into tab_pls_435
 values plsql_rec_tab(i);
end;

See also PLS-00436 for a list of other possibilities with native dynamic SQL.

See message translations for PLS-00457 and search additional resources.

PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

You may see this error when you attempt to compile a PL/SQL unit containing a FORALL statement combined with an EXECUTE IMMEDIATE statement like, as follows:

create or replace
procedure test_pls_435
is

type tab_type  is table of tab_pls_435%rowtype; -- defines a RECORD data type
plsql_rec_tab  tab_type := tab_type();          -- defines a collection of RECORDs

begin
plsql_rec_tab.extend;
plsql_rec_tab(1).col_a           := 100;
plsql_rec_tab(1).col_b           := 'TEST';

forall i in 1..plsql_rec_tab.count
execute immediate
'insert into tab_pls_435
 values (:1, :2)'
using
plsql_rec_tab(i).col_a
 ,plsql_rec_tab(i).col_b;
end;

Whilst it is possible to specify bulk FORALL statements, in practice there are limitations when RECORD data type collections are involved. If PLS-00436 is present in the list of parsing errors, then i suggest you to read that entry for a detailed explanation and try out alternative solutions.

See message translations for PLS-00435 and search additional resources.

PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You may get the following error along with PLS-00435.

PLS-00436: implementation restriction:
cannot reference fields of BULK In-BIND table of records

if you attempt to compile a PL/SQL procedure containing native dynamic SQL like this:

create table tab_pls_435 (
col_a number,
col_b varchar2(30));

create or replace
procedure test_pls_435
is

type tab_type     is table of tab_pls_435%rowtype;
plsql_rec_tab     tab_type := tab_type();

begin
plsql_rec_tab.extend;
plsql_rec_tab(1).col_a           := 100;
plsql_rec_tab(1).col_b           := 'TEST';

forall i in 1..plsql_rec_tab.count
execute immediate
'insert into tab_pls_435
values (:1,:2)'
using
plsql_rec_tab(i).col_a
,plsql_rec_tab(i).col_b;

end;

The problem here is that i defined a PL/SQL table of records and i am trying to bulk bind the individual fields of the record. The main problem however is with the EXECUTE IMMEDIATE type of native dynamic SQL that is also considered invalid thus returning PLS-00435.

The solution is to completely rewrite the code, depending on the specific case, in one of these ways:

1) by eliminating the bulk FORALL (slower solution)

 ...
for i in 1..plsql_rec_tab.count
loop
execute immediate
'insert into tab_pls_435
values (:1,:2)'
using
plsql_rec_tab(i).col_a
,plsql_rec_tab(i).col_b;
end loop;
...

2) by splitting the individual record fields into different collections, thus eliminating the RECORD data type collection, while keeping the bulk FORALL:

create or replace
procedure test_pls_435
is

type col_a_tab_type is table of tab_pls_435.col_a%type;
type col_b_tab_type is table of tab_pls_435.col_b%type;
plsql_col_a_tab     col_a_tab_type := col_a_tab_type();
plsql_col_b_tab     col_b_tab_type := col_b_tab_type();

begin
plsql_col_a_tab.extend;
plsql_col_b_tab.extend;
plsql_col_a_tab(1)           := 100;
plsql_col_b_tab(1)           := 'TEST';

forall i in 1..plsql_col_a_tab.count
execute immediate
'insert into tab_pls_435
values (:1, :2)'
using
plsql_col_a_tab(i), plsql_col_b_tab(i);

end;

3) It's worth noting that using native dynamic SQL makes things more complex.
Indeed if there isn't a valid reason for using native dynamic SQL, we could rewrite the code getting rid of EXECUTE IMMEDIATE and using a shorter syntax,:

create or replace
procedure test_pls_435
is

type tab_type     is table of tab_pls_435%rowtype;
plsql_rec_tab     tab_type := tab_type();

begin
plsql_rec_tab.extend;
plsql_rec_tab(1).col_a           := 100;
plsql_rec_tab(1).col_b           := 'TEST';

forall i in 1..plsql_rec_tab.count
insert into tab_pls_435
values plsql_rec_tab(i);

end;

The shorter syntax requires that the PL/SQL RECORD structure matches exactly the table structure. Trying to perform the insert on a subset of columns may lead to ORA-03001.

See message translations for PLS-00436 and search additional resources.

ORA-03001: unimplemented feature

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

ORA-03001: unimplemented feature

This error can be returned attempting to compile a PL/SQL unit containing a FORALL statement, as follows:

create table tab_pls_435 (
col_a number,
col_b varchar2(30));

create or replace
procedure test_pls_435
is

type rec_type     is record (col_b tab_pls_435.col_b%type);
type tab_type     is table of rec_type;
plsql_rec_tab     tab_type;

begin
plsql_rec_tab.extend;
plsql_rec_tab(1).col_b           := 'TEST';

forall i in 1..plsql_rec_tab.count
insert into tab_pls_435 (col_b)
values plsql_rec_tab(i);

end;

My current understanding is that currently is not possible to specify a subset of columns in an bulk INSERT statement that bulk binds a RECORD type collection inside FORALL.
Note that in the code above, if we get rid of column specification, we incur in ORA-00947, however this is caused by the mismatching number of columns between the table structure and the PL/SQL RECORD data type.
If you can redefine the RECORD definition to match the table structure, then the code can be compiled and executed successfully, as follows:

create or replace
procedure test_pls_435
is

type tab_type     is table of tab_pls_435%rowtype;
plsql_rec_tab     tab_type := tab_type();

begin
plsql_rec_tab.extend;
plsql_rec_tab(1).col_a           := 100;
plsql_rec_tab(1).col_b           := 'TEST';

forall i in 1..plsql_rec_tab.count
insert into tab_pls_435
values plsql_rec_tab(i);

end;

I ignore if ORA-03001 can be returned also in different situations.

See message translations for ORA-03001 and search additional resources.

Updated January 4, 2008:
If you are getting this error when using a CAST/MULTISELECT construct, check out the comments section!

ORA-01006: bind variable does not exist

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Here is a common scenario for this error:
i specified two variables in the USING clause of the EXECUTE IMMEDIATE statement, however i didn't specify two bind variables in the PL/SQL string to be executed, there is only one variable (:1).

declare
a varchar2(50) := 'TEST PROCEDURE';
b varchar2(50) := 'DYNAMIC SQL';
c varchar2(50);
d varchar2(50);
begin
execute immediate 'begin dbms_application_info.set_module(:1); end;' using a, b;
dbms_application_info.read_module(c,d);
dbms_output.put_line(c);
dbms_output.put_line(d);
end;

ORA-01006: bind variable does not exist

After adding the bind variable as a second parameter for SET_MODULE, the program works correctly:

declare
a varchar2(50) := 'TEST PROCEDURE';
b varchar2(50) := 'DYNAMIC SQL';
c varchar2(50);
d varchar2(50);
begin
execute immediate 'begin dbms_application_info.set_module(:1,:2); end;' using a, b;
dbms_application_info.read_module(c,d);
dbms_output.put_line(c);
dbms_output.put_line(d);
end;

TEST PROCEDURE
DYNAMIC SQL

You can easily try out the code inside Apex SQL Workshop.

Another situation where you might get this error is when you attempt to execute this unsupported single-row native dynamic SQL statement:

declare
a varchar2(10);
b varchar2(10) := 'Y';
begin
execute immediate 'select dummy into :a from dual where dummy != :b' using out a, in b;
dbms_output.put_line(' a:'||a);
end;

ORA-01006: bind variable does not exist

however in this case the problem is with the syntax, not with the missing bind variable, because single-row dynamic SELECTs must be written with the special INTO clause as already explained in a previous posting (see ORA-01745).

See message translations for ORA-01006 and search additional resources

SQL Error: ORA-01745: invalid host/bind variable name

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This error can be returned in at least two distinct situations (and possibly more) involving native dynamic SQL.

The first situation occurs when you pick a reserved word for use as a bind variable name, for instance :USER or :SYDATE, just to name a couple (by the way, the letter casing is irrelevant).

Let's look instead at the following PL/SQL anonymous block for curious occurrence of this error:

declare
a varchar2(10);
b varchar2(10) := 'Y';
begin
execute immediate 'select dummy into :1 from dual where dummy != :2' using in b;
dbms_output.put_line(' a:'||a);
end;

SQL Error: ORA-01745: invalid host/bind variable name

There are a couple of things worth noting here:
first of all, the correct way of specifying a dynamic single-row select query is the following:

declare
a varchar2(10);
b varchar2(10) := 'Y';
begin
execute immediate 'select dummy from dual where dummy != :2' INTO a using b;
dbms_output.put_line(' a:'||a);
end;

a:X

So, get rid of the INTO clause inside the SELECT and relocate it outside.
Secondly and funnily enough, if we write the original statement with :a instead of :1, Oracle doesn't complain at all:

declare
a varchar2(10);
b varchar2(10) := 'Y';
begin
execute immediate 'select dummy into :a from dual where dummy != :2' using b;
dbms_output.put_line(' a:'||a);
end;

a:

Even if oracle doesn't complain, this statement isn't of any practical use, because the bind variable :a is ignored altogether.
Note also that according to the documentation of 10R1 (it says the same thing in the docs of other versions) "you can place all bind arguments in the USING clause", but this isn't true for single-row SELECT queries (see ORA-01006) that require the special INTO clause shown above.

See message translations for ORA-01745 and search additional resources

A practical example of using global temporary tables within Apex: displaying dbms_output messages

It's late so i am not going to make it longer than necessary.

Some days ago i read an interesting question in the OTN forum and i threw my 50 cent advice (well, honestly it may have been worth 60 or 70 cents as i did some homework before shooting...).

In short, a user was asking for a method for display the messages sent to the DBMS_OUTPUT buffer.

This question eventually aroused my fantasy, so i wrote a generic function for storing dbms_output messages into a generic table (a table in the same schema or in another one, provided the user has INSERT privilege).
Having used up all my fantasy in the development, i decided to call the function STORE_DBMS_OUTPUT.

In my test apex application the staging table is defined as follows:

create global temporary table
staging_table(
id number(5,0),
text varchar2(255)
) on commit delete rows
/

The source code of the function can be downloaded from Yocoya.com.

The function takes 4 parameters and returns a number:

p_owner owner of the staging table, default to current user.
p_table name of the table, mandatory
p_txt_col name of the column for storing messages, typically VARCHAR2(255)
p_cnt_col optional name of the column for storing message sequence number

The last parameter may be omitted if you already have a before-insert trigger on the table where you stuff an oracle sequence.

The function retrieves all the content in the buffer and the value it returns is the number of messages it found.

Although i wrote the function for using within an Apex page, it's completely independent from it and it can be used in different situations.
The table where the messages are going to be stored doesn't necessarily have to be a Global Temporary Table (GTT), although in that case you must devise a method for keeping it clean without interfering with other sessions and/or users.

Users who are interested in looking at a working example of dynamic SQL in combination with FORALL and bulk binds may be interested in this function too.
Note also the n-tier usage of DUAL i talked about some time ago that i use here for initializing a pl/sql table containing the subscripts, i'll probably write something in another posting on this subject.

In case you are interested in using this function inside apex, here is how i did it (please note that this techniques are still under testing).

before header processes
step 10: truncate staging table (just in case) and call the desired procedure returning dbms_output messages (conditional on request=OUTPUT)
step 20: get the dbms_output and store it in a global temporary table (conditional on request=OUTPUT), as follows:

declare
n integer;
begin
n := STORE_DBMS_OUTPUT(
p_owner => :OWNER,
p_table => 'STAGING_TABLE',
p_txt_col => 'TEXT',
p_cnt_col => 'ID');
end;

regions
sequence 10: some region with a button branching to the same page, setting request = OUTPUT
sequence 20: report region on staging table (conditional on request=OUTPUT)
sequence 30: PL/SQL type region truncating the table (conditional on request=OUTPUT), may be omitted altogether if GTT is defined as on commit delete rows.

As you see here i have up to two truncates statements. This is just to avoid having an idle session holding some temporary segment as i related in my previous posting.
If the GTT is defined as on commit delete rows, you can probably omit both truncates, i just prefer to leave a clean table after using it.

That's it.