Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00382: expression is of wrong type
| PLS-00382: expression is of wrong type [message #555307] |
Wed, 23 May 2012 02:55  |
scottwmackey
Messages: 515
Registered: March 2005
|
Senior Member |
|
|
Hi all,
I have an issue I just can't seem to figure out. I am running some FORALL...UPDATE statements in a dynamic anonymous blocks and I am seeing this intermittently
ORA-06550: line 10, column 28:
PLS-00382: expression is of wrong type
ORA-06550: line 10, column 17:
PL/SQL: Statement ignored
ORA-06550: line 10, column 55:
PLS-00382: expression is of wrong type
ORA-06550: line 10, column 36:
PL/SQL: Statement ignored
ORA-06550: line 18, column 32:
PLS-00382: expression is of wrong type
ORA-06550: line 18, column 21:
PL/SQL: Statement ignored
This is one of the failures. (The code is not completely formatted so the lines and columns from the error stack are still accurate.)
DECLARE
v_tz_id string4000_nt_t; v_net_device_id number_nt_t;
v_errors string4000_nt_t := string4000_nt_t();
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
v_tz_id := :tz_id; v_net_device_id := :net_device_id;
FORALL i IN INDICES OF v_net_device_id SAVE EXCEPTIONS UPDATE device SET tz_id = v_tz_id(i), update_ts = systimestamp WHERE net_device_id = v_net_device_id(i);
EXCEPTION
WHEN dml_errors THEN
FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
v_errors.extend;
v_errors(v_errors.count) := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX || '':'' || -SQL%BULK_EXCEPTIONS(i).ERROR_CODE;
END LOOP;
:errors := v_errors;
END;';
Here it is formatted, though I am not sure how much more useful it is.
DECLARE
v_tz_id string4000_nt_t;
v_net_device_id number_nt_t;
v_errors string4000_nt_t := string4000_nt_t();
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
v_tz_id := :tz_id;
v_net_device_id := :net_device_id;
FORALL i IN INDICES OF v_net_device_id SAVE EXCEPTIONS
UPDATE device
SET tz_id = v_tz_id(i)
,update_ts = systimestamp
WHERE net_device_id = v_net_device_id(i);
EXCEPTION
WHEN dml_errors THEN
FOR i IN 1 .. SQL%bulk_exceptions.count
LOOP
v_errors.extend;
v_errors(v_errors.count) := SQL%BULK_EXCEPTIONS(i).error_index || ':' || -sql%BULK_EXCEPTIONS(i).error_code;
END LOOP;
:errors := v_errors;
END;
For which, I am binding
--Type definitions
TYPE string4000_nt_t IS TABLE OF VARCHAR2(4000);
TYPE number_nt_t IS TABLE OF NUMBER;
--Variable declarations
v_value string4000_nt_t
v_ids number_nt_t;
--Assign values to variables
v_value(1) := 20003 -- will be converted to a varchar2 at this time, but we don't care
v_ids(1) := 330
--Bind
v_value => :tz_id
v_ids => :net_device_id
Here's the part that is really baffling me. It hardly ever fails, maybe one out of a hundred, the exact same dyncamic SQL, the exact same bind values with different IDs. I have unit tests that call this nearly 1000 times, each with slightly different statements. It never fails. Never. The only time we have seen failures is during our hourly automated testing using Bamboo in which this DB API is called by JDBC, but the actual failure is always several layers of PL/SQL removed from the original call. Moreover, we just run the tests again that failed, i.e. same exact dynamic SQL with a different ID, and they pass. We cannot get the error to happen so we can debug it. The only thing that is common about the failures is that they happen in tests that take place shortly after the database schema has been created, but I can't, for the life of me, see how that could matter.
So that's really all I have in terms of describing the situation. If anybody has any idea what could possibly be happening, I'd love to hear it. I understand there is a tendency in these fora not to provide sufficient information to answer the question. I also know there is a tendency for the experts around here to assume the OP is not always the sharpest tack in the drawer. To address the first problem, I am going to way overshoot in terms of the information I can provide. Below, you will find the complete procedure that I am using. I don't know how useful it will be, and I doubt if most people will want to read it. Moreover, I really don't think there are any clues in there as to what is going on. Below that, you will find an explanation of why I am attempting this. Once again, reading it is optional and probably not useful. I'm not just making work, I don't think. If there is an easier way to do what I need to do and somebody can suggest a better path, I am all ears (actually eyes, but you know what I mean). The obvious alternative would be to bind_array to a simple dynamic update statement, without the FORALL and the anonymous block, but I can't figure out how to save the exceptions. If anybody knows how to do that, this problem may become moot.
Here is the procedure that this creating the dynamic sql and binding.
TYPE table_of_string4000_nt_t IS TABLE OF string4000_nt_t;
TYPE update_rec_typ IS RECORD(vals table_of_string4000_nt_t
,ids number_nt_t
,mac_ids mac_id_nt_t
,pointers number_nt_t);
TYPE update_rec_tab_typ IS TABLE OF update_rec_typ INDEX BY VARCHAR2(32000);
TYPE update_rec_tab_tab_typ IS TABLE OF update_rec_tab_typ INDEX BY VARCHAR2(30);
TYPE network_device_hst_tab_typ IS TABLE OF network_device_hst%ROWTYPE;
TYPE ints_x_varchar32000_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(32000);
TYPE update_ptr_tab_typ IS TABLE OF ints_x_varchar32000_typ;
TYPE number_nt_t_x_varchar32000_typ IS TABLE OF number_nt_t INDEX BY VARCHAR2(32000);
TYPE update_errors_typ IS TABLE OF number_nt_t_x_varchar32000_typ INDEX BY VARCHAR2(30);
PROCEDURE bulk_update(
p_update_tab IN OUT NOCOPY update_rec_tab_tab_typ
,p_table_name VARCHAR2
,p_id_name VARCHAR2
,p_error_code IN OUT NOCOPY number_nt_t
,p_errored_indexes IN OUT NOCOPY update_errors_typ
,p_update_ptrs IN OUT NOCOPY update_ptr_tab_typ
,p_raise BOOLEAN
)
IS
v_update_index VARCHAR2(32000);
v_update update_rec_typ;
v_declarations VARCHAR2(32000);
v_assignments VARCHAR2(32000);
v_forall_sql VARCHAR2(32000);
v_update_sql VARCHAR2(32000);
v_handle NUMBER;
v_forall_errors string4000_nt_t := string4000_nt_t();
v_dummy NUMBER;
v_type_name VARCHAR2(30);
v_new_errored_indexes update_errors_typ;
v_error_index NUMBER;
v_error_code NUMBER;
v_last NUMBER;
v_bind_index NUMBER;
v_rec_index NUMBER;
v_ptrs_index VARCHAR2(32000);
v_tmp_error_indexes number_nt_t;
v_cols dbms_utility.lname_array;
v_len BINARY_INTEGER;
v_error_tab_tab number_nt_t_x_varchar32000_typ;
v_ptrs_table VARCHAR2(30);
v_ptrs_update VARCHAR2(32000);
BEGIN
IF NOT p_update_tab.exists(p_table_name) THEN
RETURN;
END IF;
v_update_index := p_update_tab(p_table_name).first;
IF p_id_name = 'nic_mac_id' THEN
v_type_name := 'mac_id_nt_t';
ELSE
v_type_name := 'number_nt_t';
END IF;
LOOP
EXIT WHEN v_update_index IS NULL;
v_update := p_update_tab(p_table_name)(v_update_index);
dbms_utility.comma_to_table(v_update_index, v_len, v_cols);
v_declarations := NULL;
FOR i IN 1 .. v_update.vals.count LOOP
v_declarations := v_declarations || 'v_' || v_cols(i) || ' string4000_nt_t; ';
END LOOP;
v_declarations := v_declarations || 'v_' || p_id_name || ' ' || v_type_name || ';';
v_assignments := NULL;
FOR i IN 1 .. v_update.vals.count LOOP
v_assignments := v_assignments || 'v_' || v_cols(i) || ' := :' || v_cols(i) ||'; ';
END LOOP;
v_assignments := v_assignments || 'v_' || p_id_name || ' := :' || p_id_name ||';';
v_forall_sql := 'FORALL i IN INDICES OF v_' || p_id_name || CASE WHEN NOT p_raise THEN' SAVE EXCEPTIONS ' END;
v_forall_sql := v_forall_sql || ' UPDATE ' || p_table_name || ' SET ';
FOR i IN 1 .. v_update.vals.count LOOP
v_forall_sql := v_forall_sql || v_cols(i) || ' = v_' || v_cols(i) || '(i)' || CASE WHEN i < v_update.vals.count THEN ', ' END;
END LOOP;
IF p_table_name != 'device_data_timestamps' THEN
v_forall_sql := v_forall_sql || ', update_ts = systimestamp ';
END IF;
v_forall_sql := v_forall_sql || 'WHERE ' || p_id_name ||' = v_' || p_id_name ||'(i);';
v_update_sql := '
DECLARE
#DECLARATIONS#
v_errors string4000_nt_t := string4000_nt_t();
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
#ASSIGNMENTS#
#SQL#
EXCEPTION
WHEN dml_errors THEN
FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
v_errors.extend;
v_errors(v_errors.count) := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX || '':'' || -SQL%BULK_EXCEPTIONS(i).ERROR_CODE;
END LOOP;
:errors := v_errors;
END;';
v_update_sql := REPLACE(REPLACE(REPLACE(v_update_sql, '#DECLARATIONS#', v_declarations), '#ASSIGNMENTS#', v_assignments), '#SQL#', v_forall_sql);
v_handle := dbms_sql.open_cursor;
dbms_sql.parse(v_handle, v_update_sql, dbms_sql.native);
--Print the statement to dbms_output
util_pkg.p(v_update_sql);
FOR i IN 1 .. v_update.vals.count LOOP
-- ********* BIND *****************
dbms_sql.bind_variable(v_handle, ':'||v_cols(i), v_update.vals(i));
END LOOP;
IF p_id_name = 'nic_mac_id' THEN
-- ********* BIND *****************
dbms_sql.bind_variable(v_handle, ':' || p_id_name ||'', v_update.mac_ids);
ELSE
-- ********* BIND *****************
dbms_sql.bind_variable(v_handle, ':' || p_id_name ||'', v_update.ids);
END IF;
-- ********* BIND *****************
dbms_sql.bind_variable(v_handle, ':errors', v_forall_errors);
v_dummy := dbms_sql.execute(v_handle);
END IF;
v_update_index := p_update_tab(p_table_name).next(v_update_index);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
-- Write the failed statement to the error_log table
error_log_pkg.log_error(p_pkg_name => G_PKG_NAME
,p_proc_name => 'p_update_tab'
,p_param_name_nt => string30_nt_t(' ')
,p_param_value_nt => string4000_nt_t(substr(regexp_replace(REPLACE(v_update_sql, CHR(10)), ' +', ' '), 1, 3500))
,p_ora_error_num => SQLCODE
,p_ora_error_msg => SQLERRM);
-- Write the bind variables to the error_log table
print_update_tab(p_update_tab, SQLCODE);
RAISE;
END bulk_update;
This is why I am doing it. We have network_device record potentially updates three tables: device, nic, and device_data_timestamps. For performance reasons, we want to update only the tables that have columns that are being changed. The set_update procedure determines which columns change for each row passed. Once that is done it passes columns and the new values to set_update_tab which builds and update structure, a update_rec_tab_tab_typ type. That structure is then passed to bulk_update which dynamically creates and executes the necessary updates. I think the best way to explain this is with a concrete example.
network_device_ins_upd takes a table of network_device%ROWTYPE. Let's say that the columns you want to update are as follows:
--Set the IDs to identify the records to be updated
v_network_device_tab(1).net_device_id := 101;
v_network_device_tab(2).net_device_id := 102;
v_network_device_tab(3).net_device_id := 103;
v_network_device_tab(4).net_device_id := 104;
v_network_device_tab(5).net_device_id := 105;
v_network_device_tab(6).net_device_id := 106;
v_network_device_tab(7).net_device_id := 107;
v_network_device_tab(8).net_device_id := 108;
v_network_device_tab(9).net_device_id := 109;
v_network_device_tab(10).net_device_id := 110;
--Set the columns to be updated
--device columns
v_network_device_tab(1).serial_num := 'tsn1';
v_network_device_tab(2).util_device_id := 'tud2';
v_network_device_tab(3).serial_num := 'tsn3';
v_network_device_tab(3).util_device_id := 'tud3';
v_network_device_tab(4).serial_num := 'tsn3';
v_network_device_tab(4).util_device_id := 'tud4';
v_network_device_tab(5).serial_num := 'tsn5';
v_network_device_tab(5).util_device_id := 'tud5';
v_network_device_tab(6).serial_num := 'tsn6';
v_network_device_tab(7).serial_num := v_network_device_ref_tab(10).serial_num;
v_network_device_tab(8).util_device_id := 'tud8';
--nic columns
v_network_device_tab(3).nic_attribute_1 := 'tna13';
v_network_device_tab(3).nic_attribute_2 := 'tna23';
v_network_device_tab(4).nic_attribute_1 := 'tna14';
v_network_device_tab(6).nic_attribute_1 := 'tna16';
v_network_device_tab(7).nic_attribute_1 := 'tna17';
v_network_device_tab(8).nic_attribute_2 := 'tna28';
v_network_device_tab(9).nic_attribute_2 := 'tna29';
v_network_device_tab(10).nic_attribute_2 := 'tna210';
--device_data_timestamps columns
v_network_device_tab(3).last_comm_ts := TIMESTAMP '2000-01-03 00:00:00';
v_network_device_tab(4).last_comm_ts := TIMESTAMP '2000-01-04 00:00:00';
v_network_device_tab(5).last_comm_ts := TIMESTAMP '2000-01-05 00:00:00';
This table of network_device%ROWTYPE is then passed to the network_device_ins_upd API, which creates the following structure, where v_update_tab is a update_rec_tab_tab_typ.
v_update_tab('device')('serial_num').vals(1)(1) = 'tsn1'
(2) = 'tsn6'
(3) = 'SN0010' --will fail
.ids(1) = 101
(2) = 106
(3) = 107
.mac_ids = NULL
('util_device_id').vals(1)(1) = 'tud2'
(2) = 'tud8'
.ids(1) = ??
(2) = ??
.mac_ids = NULL
('util_device_id,serial_num').vals(1)(1) = 'tud3'
(2) = 'tud4'
(3) = 'tud5'
(2)(1) = 'tsn3'
(2) = 'tsn3' --will fail
(3) = 'tsn5'
.ids(1) = 103
(2) = 104
(3) = 105
.mac_ids = NULL
('nic')('nic_attribute_1,nic_attribute_2').vals(1)(1) = 'tna13'
(2)(1) = 'tna23'
.ids = NULL
.mac_ids(1) = 003
('nic_attribute_1').vals(1)(1) = 'tna14'
(2) = 'tna16'
(3) = 'tna17'
.ids = NULL
.mac_ids(1) = 004
(2) = 006
(3) = 007
('nic_attribute_2').vals(1)(1) = 'tna18'
(2) = 'tna19'
(3) = 'tna110'
.ids = NULL
.mac_ids(1) = 008
(2) = 009
(3) = 010
('device_data_timestamps')('last_comm_ts').vals(1)(1) = '2000-01-03 00:00:00'
(2) = '2000-01-04 00:00:00'
(3) = '2000-01-05 00:00:00'
.ids(1) = 103
(2) = 104
(3) = 105
.mac_ids = NULL
The bulk_update procedure will then create the following seven update statements. (I will include here the full statement for only the first, even though each FORALL is wrapped in identical anonymous with the exception of the variable declarations.
DECLARE
v_serial_num string4000_nt_t;
v_net_device_id number_nt_t;
v_errors string4000_nt_t := string4000_nt_t();
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
v_serial_num := :serial_num;
v_net_device_id := :net_device_id;
FORALL i IN INDICES OF v_net_device_id SAVE EXCEPTIONS
UPDATE device
SET serial_num = v_serial_num(i)
,update_ts = systimestamp
WHERE net_device_id = v_net_device_id(i);
EXCEPTION
WHEN dml_errors THEN
FOR i IN 1 .. SQL%bulk_exceptions.count
LOOP
v_errors.extend;
v_errors(v_errors.count) := SQL%BULK_EXCEPTIONS(i).error_index || ':' || SQL%BULK_EXCEPTIONS(i).error_code;
END LOOP;
:errors := v_errors;
END;
Update part of the other six
FORALL i IN INDICES OF v_net_device_id SAVE EXCEPTIONS
UPDATE device
SET util_device_id = v_util_device_id(i)
,update_ts = systimestamp
WHERE net_device_id = v_net_device_id(i);
FORALL i IN INDICES OF v_net_device_id SAVE EXCEPTIONS
UPDATE device
SET util_device_id = v_util_device_id(i)
,serial_num = v_serial_num(i)
,update_ts = systimestamp
WHERE net_device_id = v_net_device_id(i);
FORALL i IN INDICES OF v_nic_mac_id SAVE EXCEPTIONS
UPDATE nic
SET attribute_1 = v_attribute_1(i)
,update_ts = systimestamp
WHERE nic_mac_id = v_nic_mac_id(i);
FORALL i IN INDICES OF v_nic_mac_id SAVE EXCEPTIONS
UPDATE nic
SET attribute_1 = v_attribute_1(i)
,attribute_2 = v_attribute_2(i)
,update_ts = systimestamp
WHERE nic_mac_id = v_nic_mac_id(i);
FORALL i IN INDICES OF v_nic_mac_id SAVE EXCEPTIONS
UPDATE nic
SET attribute_2 = v_attribute_2(i)
,update_ts = systimestamp
WHERE nic_mac_id = v_nic_mac_id(i);
FORALL i IN INDICES OF v_net_device_id SAVE EXCEPTIONS
UPDATE device_data_timestamps
SET last_comm_ts = v_last_comm_ts(i)
WHERE net_device_id = v_net_device_id(i);
|
|
|
|
|
|
| Re: PLS-00382: expression is of wrong type [message #555385 is a reply to message #555309] |
Wed, 23 May 2012 12:13  |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Hi Scott,
I know you put a lot of effort into this, but there are some things that are missing, out of order, unclear, etc. For example, when you say something like:
TYPE string4000_nt_t IS TABLE OF VARCHAR2(4000);
this is obviously not complete SQL or PL/SQL, so, although it may be obvious to you, I can't tell if you mean SQL:
CREATE OR REPLACE TYPE string4000_nt_t IS TABLE OF VARCHAR2(4000);
or PL/SQL:
DECLARE
TYPE string4000_nt_t IS TABLE OF VARCHAR2(4000);
BEGIN
...
END;
/
and it can matter.
For another example, where is MAC_ID_NT_T declared?
After encountering those two, I stopped trying to complete the pieces and put them in order.
I am guessing that you have something like:
CREATE OR REPLACE TYPE ...
CREATE OR REPLACE PACKAGE ...
TYPE ..
PROCEDURE ...
CREATE OR REPLACE PACKAGE BODY ...
PROCEDURE ...
and execution of this dynamically creates and runs a PL/SQL block that results in the errors, that are captured in an exception section, which makes it show that the error is coming from the line of code that is the exception section, obscuring the actual origin.
I suggest that you provide a single complete compilable code with all of the pieces in the proper order, including execution, that we can copy and paste and run on our systems to understand and test the process. I also suggest that you try commenting out the exception section, so that it does not obscure the origin of the error.
[Updated on: Wed, 23 May 2012 12:16] Report message to a moderator |
|
|
|
| Re: PLS-00382: expression is of wrong type [message #555394 is a reply to message #555385] |
Wed, 23 May 2012 15:47 |
scottwmackey
Messages: 515
Registered: March 2005
|
Senior Member |
|
|
Barbara,
Sorry about that. They are compiled types that get created when the DB schema is installed.
CREATE OR REPLACE TYPE number_nt_t AS TABLE OF NUMBER;
CREATE OR REPLACE TYPE string4000_nt_t IS TABLE OF VARCHAR2(4000);
CREATE OR REPLACE TYPE mac_id_nt_t AS TABLE OF RAW(8);
They are the first objects we create, before tables, packages, etc. There is absolutely no chance they cannot be defined before the failing tests run. Unless I am misreading your response, it seems like you are thinking that I do everything dynamically, including creating packages. Once again, I am sorry I wasn't clear about that. That is not the case. The automated test creates a new schema, creates all the DB objects, validates all objects, starts two middle tier Java based applications, waits for them to start, then runs tests against them which are essentially DML. The only thing I am doing dynamically is the anonymous block that I posted.
As for posting working code, I could. The problem though is that it will work. I could post it. You could run it. It will work. You will ask, What's the problem? But that's my problem. But in an effort to be as cooperative as possible with people offering me free help, I have posted it below. I completely understand the strangeness of what I am asking. How can you possibly suggest a solution when I can't even give you the problem? Like I said in the problem statement, I can't get this to fail on my unit tests that I run on my development environment. I can run on my DB instance all the same tests that our automated test runs and they never fail. The only difference is that I do not rebuild my schema every time. If we simply hit the button on the web page for Bamboo to rerun the automated tests, they pass. The same tests on the same application running against the same DB schema that failed only minutes before complaining that the expression is of the wrong type now have no problem with the expression type. Nobody seems to be able to get it to fail on their development environments. It's only on the one automated testing environment, only intermittently, and only inconsistently. So I don't know what I can possibly expect from anybody here. Maybe somebody has seen something like this before. Maybe somebody really knows the internals of Oracle's dynamic SQL parser and binding and knows something about states. Maybe there's a limit on the number of concurrent dynamic SQL that can be run and this is the error that it gives. I am completely grasping at straws. This post is my last shot before my manager makes me rollback three weeks worth of work because of some bizarre unreproducible error.
Finally, the exception clause is not masking the error. I get the same error stack with or without it. If you look at the example I posted.
This is line 10
v_tz_id := :tz_id; v_net_device_id := :net_device_id;
^ ^ ^ ^
| | | |
column 17 28 36 55
This is line 18
:errors := v_errors;
^ ^
| |
column 21 32
Here is the working example.
CREATE TABLE t(
t_id NUMBER,
var_col VARCHAR2(30),
num_col NUMBER,
ts TIMESTAMP
);
Table created
INSERT INTO t
SELECT LEVEL
,CHR(LEVEL + 96)
,LEVEL * 10
,SYSTIMESTAMP
FROM dual
CONNECT BY LEVEL <= 5;
5 rows inserted
COMMIT;
Commit complete
CREATE OR REPLACE TYPE string4000_nt_t IS TABLE OF VARCHAR2(4000);
/
Type created
CREATE OR REPLACE TYPE number_nt_t IS TABLE OF NUMBER;
/
Type created
SELECT *
FROM t;
T_ID VAR_COL NUM_COL TS
---- ------- ------- ----------------------------
1 a 10 23-MAY-12 08.19.29.298415 PM
2 b 20 23-MAY-12 08.19.29.298415 PM
3 c 30 23-MAY-12 08.19.29.298415 PM
4 d 40 23-MAY-12 08.19.29.298415 PM
5 e 50 23-MAY-12 08.19.29.298415 PM
DECLARE
p_ids_tab number_nt_t := number_nt_t(1, 3, 5);
p_vars_tab string4000_nt_t := string4000_nt_t('q', 'r', 's');
p_nums_tab string4000_nt_t := string4000_nt_t(10, 11, 12);
v_update_sql VARCHAR2(32000);
v_handle NUMBER;
v_dummy NUMBER;
BEGIN
v_update_sql := '
DECLARE
v_vars string4000_nt_t;
v_nums string4000_nt_t;
v_ids number_nt_t;
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
v_vars := :v_vars;
v_nums := :v_nums;
v_ids := :v_ids;
FORALL i IN INDICES OF v_ids
UPDATE t
SET var_col = v_vars(i)
,num_col = v_nums(i)
,ts = systimestamp
WHERE t_id = v_ids(i);
EXCEPTION
WHEN dml_errors THEN
NULL;
END;';
v_handle := dbms_sql.open_cursor;
dbms_sql.parse(v_handle, v_update_sql, dbms_sql.native);
dbms_sql.bind_variable(v_handle, ':v_vars', p_vars_tab);
dbms_sql.bind_variable(v_handle, ':v_nums', p_nums_tab);
dbms_sql.bind_variable(v_handle, ':v_ids', p_ids_tab);
v_dummy := dbms_sql.execute(v_handle);
END;
/
PL/SQL procedure successfully completed
SELECT *
FROM t;
T_ID VAR_COL NUM_COL TS
---- ------- ------- ----------------------------
1 q 10 23-MAY-12 08.19.29.981878 PM
2 b 20 23-MAY-12 08.19.29.298415 PM
3 r 11 23-MAY-12 08.19.29.981878 PM
4 d 40 23-MAY-12 08.19.29.298415 PM
5 s 12 23-MAY-12 08.19.29.981878 PM
I can easily get it to yield the error I am seeing by crossing up the bind variable with the definition in the dynamic SQL. If I change p_nums_tab to a number_nt_t and try to bind it to :v_nums which is still a string4000_nt_t in the dynamic SQL, I get the error.
DECLARE
p_ids_tab number_nt_t := number_nt_t(1, 3, 5);
p_vars_tab string4000_nt_t := string4000_nt_t('q', 'r', 's');
p_nums_tab number_nt_t := number_nt_t(10, 11, 12);
v_update_sql VARCHAR2(32000);
v_handle NUMBER;
v_dummy NUMBER;
BEGIN
v_update_sql := '
DECLARE
v_vars string4000_nt_t;
v_nums string4000_nt_t;
v_ids number_nt_t;
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
v_vars := :v_vars;
v_nums := :v_nums;
v_ids := :v_ids;
FORALL i IN INDICES OF v_ids
UPDATE t
SET var_col = v_vars(i)
,num_col = v_nums(i)
,ts = systimestamp
WHERE t_id = v_ids(i);
EXCEPTION
WHEN dml_errors THEN
NULL;
END;';
v_handle := dbms_sql.open_cursor;
dbms_sql.parse(v_handle, v_update_sql, dbms_sql.native);
dbms_sql.bind_variable(v_handle, ':v_vars', p_vars_tab);
dbms_sql.bind_variable(v_handle, ':v_nums', p_nums_tab);
dbms_sql.bind_variable(v_handle, ':v_ids', p_ids_tab);
v_dummy := dbms_sql.execute(v_handle);
END;
ORA-06550: line 11, column 19:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 9:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_SQL", line 1825
ORA-06512: at line 43
That, however, is not what is happening in the errors I am seeing as far as I can tell. In my actual code, I am binding v_update.vals(i) and v_update.ids, which according to this definition
TYPE table_of_string4000_nt_t IS TABLE OF string4000_nt_t;
TYPE update_rec_typ IS RECORD(vals table_of_string4000_nt_t
,ids number_nt_t
,mac_ids mac_id_nt_t
,pointers number_nt_t);
resolve to string4000_nt_t and number_nt_t which I bind to v_tz_id and v_net_device_id in this dynamic SQL
DECLARE
v_tz_id string4000_nt_t;
v_net_device_id number_nt_t;
v_errors string4000_nt_t := string4000_nt_t();
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
v_tz_id := :tz_id;
v_net_device_id := :net_device_id;
I hope somebody can pull a rabbit out of the hat for me on this one.
|
|
|
|
| Re: PLS-00382: expression is of wrong type [message #555400 is a reply to message #555394] |
Wed, 23 May 2012 18:00 |
|
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I believe I understand the process. I think you are asking for trouble by relying on implicit data conversions and, for some strange reason, this only causes a problem at certain times. I would change the following two lines:
change:
p_nums_tab string4000_nt_t := string4000_nt_t(10, 11, 12);
to:
p_nums_tab number_nt_t := number_nt_t(10, 11, 12);
and change:
v_nums string4000_nt_t;
to:
v_nums number_nt_t;
as demonstrated below:
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE string4000_nt_t IS TABLE OF VARCHAR2(4000);
2 /
Type created.
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE number_nt_t IS TABLE OF NUMBER;
2 /
Type created.
SCOTT@orcl_11gR2> CREATE TABLE t(
2 t_id NUMBER,
3 var_col VARCHAR2(30),
4 num_col NUMBER,
5 ts TIMESTAMP
6 )
7 /
Table created.
SCOTT@orcl_11gR2> INSERT INTO t
2 SELECT LEVEL
3 ,CHR(LEVEL + 96)
4 ,LEVEL * 10
5 ,SYSTIMESTAMP
6 FROM dual
7 CONNECT BY LEVEL <= 5
8 /
5 rows created.
SCOTT@orcl_11gR2> COLUMN var_col FORMAT A7
SCOTT@orcl_11gR2> COLUMN ts FORMAT A28
SCOTT@orcl_11gR2> SELECT * FROM t
2 /
T_ID VAR_COL NUM_COL TS
---------- ------- ---------- ----------------------------
1 a 10 23-MAY-12 03.59.18.194000 PM
2 b 20 23-MAY-12 03.59.18.194000 PM
3 c 30 23-MAY-12 03.59.18.194000 PM
4 d 40 23-MAY-12 03.59.18.194000 PM
5 e 50 23-MAY-12 03.59.18.194000 PM
5 rows selected.
SCOTT@orcl_11gR2> DECLARE
2 p_ids_tab number_nt_t := number_nt_t(1, 3, 5);
3 p_vars_tab string4000_nt_t := string4000_nt_t('q', 'r', 's');
4 -- p_nums_tab string4000_nt_t := string4000_nt_t(10, 11, 12);
5 p_nums_tab number_nt_t := number_nt_t(10, 11, 12);
6 v_update_sql VARCHAR2(32000);
7 v_handle NUMBER;
8 v_dummy NUMBER;
9 BEGIN
10 v_update_sql := '
11 DECLARE
12 v_vars string4000_nt_t;
13 -- v_nums string4000_nt_t;
14 v_nums number_nt_t;
15 v_ids number_nt_t;
16 BEGIN
17 v_vars := :v_vars;
18 v_nums := :v_nums;
19 v_ids := :v_ids;
20 FORALL i IN INDICES OF v_ids
21 UPDATE t
22 SET var_col = v_vars(i)
23 ,num_col = v_nums(i)
24 ,ts = systimestamp
25 WHERE t_id = v_ids(i);
26 END;';
27 v_handle := dbms_sql.open_cursor;
28 dbms_sql.parse(v_handle, v_update_sql, dbms_sql.native);
29 dbms_sql.bind_variable(v_handle, ':v_vars', p_vars_tab);
30 dbms_sql.bind_variable(v_handle, ':v_nums', p_nums_tab);
31 dbms_sql.bind_variable(v_handle, ':v_ids', p_ids_tab);
32 v_dummy := dbms_sql.execute(v_handle);
33 END;
34 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> SELECT * FROM t
2 /
T_ID VAR_COL NUM_COL TS
---------- ------- ---------- ----------------------------
1 q 10 23-MAY-12 03.59.18.387000 PM
2 b 20 23-MAY-12 03.59.18.194000 PM
3 r 11 23-MAY-12 03.59.18.387000 PM
4 d 40 23-MAY-12 03.59.18.194000 PM
5 s 12 23-MAY-12 03.59.18.387000 PM
5 rows selected.
|
|
|
|
|
|
|
|
| Re: PLS-00382: expression is of wrong type [message #555415 is a reply to message #555401] |
Thu, 24 May 2012 01:04 |
scottwmackey
Messages: 515
Registered: March 2005
|
Senior Member |
|
|
Barbara,
Thanks much for taking a stab at this, but I think you are missing this one. The error message says:
ORA-06550: line 10, column 28:
PLS-00382: expression is of wrong type
ORA-06550: line 10, column 17:
PL/SQL: Statement ignored
ORA-06550: line 10, column 55:
PLS-00382: expression is of wrong type
ORA-06550: line 10, column 36:
PL/SQL: Statement ignored
ORA-06550: line 18, column 32:
PLS-00382: expression is of wrong type
ORA-06550: line 18, column 21:
PL/SQL: Statement ignored
line 10 -> v_tz_id := :tz_id; v_net_device_id := :net_device_id;
^ ^ ^ ^
| | | |
column 17 28 36 55
line 18 -> :errors := v_errors;
^ ^
| |
column 21 32ORA-06550: line 10, column 28:
PLS-00382: expression is of wrong type
ORA-06550: line 10, column 17:
PL/SQL: Statement ignored
ORA-06550: line 11, column 36:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 17:
PL/SQL: Statement ignored
ORA-06550: line 19, column 32:
PLS-00382: expression is of wrong type
ORA-06550: line 19, column 21:
PL/SQL: Statement ignored
v_tz_id := :tz_id; v_net_device_id := :net_device_id;
:errors := v_errors;
p_nums_tab string4000_nt_t := string4000_nt_t(10, 11, 12);
UPDATE t
SET var_col = v_vars(i)
,num_col = v_nums(i)
Once again, I understand that what I am explaining doesn't make any sense. I wouldn't believe it myself if I weren't seeing it first hand. I have been doing this Oracle stuff for 17 years now and I have never seen anything like it. It's the closest thing I have ever seen to indeterministic behavior, but we know that's not possible. We have managed to get the error sometimes on one particular database instance by simply running UTPLSQL tests. The strange thing is that it only happens after a schema is rebuilt, i.e. dropping all the objects and reloading them. Of the couple of hundred tests in the test suite that exercises this procedure, six failed the first time. When we ran the suite again, two failed. Then one, then none. Then we ran it about ten more times and couldn't get any failures. We rebuilt the schema, ran the tests, and voila, the errors were back. On the other hand, I have rebuilt my schema dozens of times and ran the same tests and have never seen the error. I am, however, on an instance with only 42 schemas. The instance on which we saw the error has 832 schemas. (Each developer has multiple schemas of their very own.) I have no idea if or how that could possibly matter, but this issue is beginning to make me doubt my own grasp on reality, so I will consider anything at this point.
|
|
|
|
|
|
| Re: PLS-00382: expression is of wrong type [message #555493 is a reply to message #555416] |
Thu, 24 May 2012 11:10 |
|
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Can you add some code that will display or log the data types and values of the things that it thinks it is assigning, just before before it assigns them. For example, if it has
v_tz_id := :tz_id;
display the data types and values of those two variables.
|
|
|
|
Goto Forum:
Current Time: Wed Aug 13 01:10:39 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
