Hi Martin,
for type Timestamp you must use ANYDATA.AccessTimestamp(value_anydata)
in your query.
Regards
Lothar
Am 27.02.2019 um 16:27 schrieb Martin Berger:
Hi List,
I try to create a testcase to speed up a SR at Oracle a little bit.
The problem at first place is a failing cjq0 process. But it doesn't crash. One statement fails with
ORA-12850: Could not allocate slaves on all specified instances: needed, allocated
and so it just don't run jobs on a specific instance of a 4-node RAC. (12.2.0.1 RU Jan19c + some one-offs)
The statement itself ( sql_id='5j7xryzqzqcbk' ) has more than 7000 characters.
To get proper INTO binds I did a "create table xxx as select ...." and this table has these columns:
NAME DATA TYPE NULL DEFAULT COMMENTS
CON_ID NUMBER Yes
OBJOID NUMBER Yes
CLSOID NUMBER Yes
RUN_TIME TIMESTAMP(6) WITH TIME ZONE Yes
STATUS NUMBER Yes
JOBTYPE NUMBER Yes
SCHLIM INTERVAL DAY(3) TO SECOND(0) Yes
WT NUMBER Yes
INST NUMBER Yes
RUNNOW NUMBER Yes
ENQ_SCHLIM NUMBER Yes
INST_ID NUMBER Yes
For the USING part I queried gv$sql_bind_capture and got the (ordered) list:
NUMBER
TIMESTAMP (TZ)
NUMBER
VARCHAR2(32)
NUMBER
VARCHAR2(32)
VARCHAR2(32)
VARCHAR2(32)
TIMESTAMP (TZ)
NUMBER
VARCHAR2(32)
NUMBER
NUMBER
NUMBER
So I'm quite sure I did everything right, but still I get
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 34
line 34 is
EXECUTE IMMEDIATE s_query_stmt ...
does anyone has a clue how I can identify my error?
the script in it's full glory:
declare
V_NUMBER_1 number := 0;
V_NEXT_RUN_DATE_2 TIMESTAMP(6) WITH TIME ZONE := to_timestamp('2020-01-01 01:01:01', 'YYYY-MM-DD HH24:MI:SS');
V_FLAGS_3 number := 1;
V_AFFINITY_4 VARCHAR2(32 BYTE) := 'bla';
V_INSTANCE_ID_5 number := 3;
V_DATABASE_ROLE_6 VARCHAR2(32 BYTE) := 'ALL'; -- ALL or PRIMARY
V_DATABASE_ROLE_7 VARCHAR2(32 BYTE) := 'PRIMARY'; -- ALL or PRIMARY
V_DATABASE_ROLE_8 VARCHAR2(32 BYTE) := 'ALL'; -- ALL or PRIMARY
V_NEXT_RUN_DATE_9 TIMESTAMP(6) WITH TIME ZONE := to_timestamp('2020-01-01 01:01:01', 'YYYY-MM-DD HH24:MI:SS');
V_FLAGS_10 number := 1;
V_INSTANCE_ID_11 varchar2(32 BYTE) := 1;
V_INSTANCE_ID_12 number := 3;
V_NUMBER_13 number := 0;
V_INSTANCE_ID_14 number := 3;
s_query_stmt CLOB;
V_CON_ID NUMBER ;
V_OBJOID NUMBER ;
V_CLSOID NUMBER ;
V_RUN_TIME TIMESTAMP(6) WITH TIME ZONE ;
V_STATUS NUMBER ;
V_JOBTYPE NUMBER ;
V_SCHLIM INTERVAL DAY(3) TO SECOND(0) ;
V_WT NUMBER ;
V_INST NUMBER ;
V_RUNNOW NUMBER ;
V_ENQ_SCHLIM NUMBER ;
V_INST_ID NUMBER ;
begin
select SQL_FULLTEXT into s_query_stmt from gv$sql where sql_id='5j7xryzqzqcbk' and rownum=1;
dbms_output.put_line(dbms_lob.substr(s_query_stmt, 40, 1));
EXECUTE IMMEDIATE s_query_stmt INTO V_CON_ID ,
V_OBJOID ,
V_CLSOID ,
V_RUN_TIME ,
V_STATUS ,
V_JOBTYPE ,
V_SCHLIM ,
V_WT ,
V_INST ,
V_RUNNOW ,
V_ENQ_SCHLIM ,
V_INST_ID
USING
V_NUMBER_1 , V_NEXT_RUN_DATE_2 , V_FLAGS_3 , V_AFFINITY_4 ,
V_INSTANCE_ID_5 , V_DATABASE_ROLE_6 , V_DATABASE_ROLE_7 ,
V_DATABASE_ROLE_8 , V_NEXT_RUN_DATE_9 , V_FLAGS_10 , V_INSTANCE_ID_11 ,
V_INSTANCE_ID_12 , V_NUMBER_13 , V_INSTANCE_ID_14
;
end;
/
thank you for any suggestion,
Martin
