finding the wrong datatype?

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Wed, 27 Feb 2019 16:27:44 +0100
Message-ID: <CALH8A90g1sL0JcjT8_cJvtApPJZWv4jaXsHxZ9qvOJFNkx-8cg_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 27 2019 - 16:27:44 CET