RE: finding the wrong datatype?

I have faint recall of debugging this error often with PeopleSoft custom SQL. I believe I had developers use “to_date” function on all their dates. They were using plain strings. Other than that 10046 trace (does anyone still do this?) with binds and plug them back in and run manually. Keep removing lines until the error goes away.  

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Martin Berger Sent: Wednesday, February 27, 2019 9:28 AM To: Oracle-L oracle-l <oracle-l_at_freelists.org> Subject: finding the wrong datatype?  

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              

 

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