Cast Table

[Dagon]

I am trying to cast an object to a table type in a dynamic cursor and keep getting a not very helpful error message of the form:

ORA-00932:inconsistent datatypes: expected - got -

As you can see, it doesn't actually tell me what it expected and what it got.

The code is quite complex but it is basically as follows.

I've declared a type t_security on the database and another type t_security_table, which is a table of that.

I have a variable:

V_pass_sec_info t_security_table := t_security_table();

I initialise and populate v_pass_sec_info from a cursor (this appears to work ok). I then attempt to declare and open a cursor using it:

lv_sql := 'WITH tmp_sec As (SELECT
b.external_system_id,
b.external_system_name,
b.data_access_rule_desc,
b.data_class_name,
b.ps_gl_comp_id,
b.book_global_id,
b.location_cd,
b.source_feed_cd,
b.err_msg
FROM
TABLE(castb_sec_info AS T_SECURITY_Table)) B
)
SELECT
...';

OPEN l_Cursor for lv_SQL USING v_PASS_sec_info;

It is at this point that I get the error. The code works with a proper table instead of the table cast, so it appears to be a problem with the binding of the t_security_table variable.

 

[sem]

SQL engine that executes your dynamic statement doesn't see your local pl/sql variable. Try to make it "global" (define in package specification), but I'm not sure even this helps to bind a collection.

Regards, Dima

http://seminihin.narod.ru

 

[Dagon]

The general principle seems to work. I have proven it with a number of smaller examples. It appears to be a problem with this particular piece of SQL.

 

[sem]

So have you tried to "externalize" V_pass_sec_info definition?

Regards, Dima

http://seminihin.narod.ru

 

[Dagon]

I'm not sure what you mean by "externalize".

 

[sem]

Forget it
I meant that variables declared within pl/sql block are not seen outside that block, thus to give SQL engine access to a variable it should be declared in package specification. I missed that you bind, not substitute.

Sad to say I'm not familiar with WITH construct, but if your statement is valid for that version of Oracle you use, and l_Cursor is declared as weakly typed then it should work.

Regards, Dima

http://seminihin.narod.ru