Re: finding the wrong datatype?

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
>
>

-- 





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