Solved

IAL Error - Cannot convert JDBC type -101 to PlsqlType

  • 14 July 2023
  • 5 replies
  • 68 views
B
Rank
Userlevel 2
Badge +5

Hi

I created a very simple IAL (Apps 9 SP11) with the following SQL...

select * FROM DBA_SCHEDULER_JOBS

This view is SYS owned and is set as a public synonym. The view has been granted privs to SELECT to IFSINFO. The IAL is named MT_SYS_DBA_JOBS

When I execute the following SQL from SQL Developer all is well…

select * from IFSINFO.MT_SYS_DBA_JOBS

Yet, when I execute from a Quick Report or the SQL Query Tool I get this error…

Cannot convert JDBC type -101 to PlsqlType

 

Any ideas?

icon

Best answer by Tomas Ruderfelt 14 July 2023, 14:02

View original

5 replies

K
Rank
Userlevel 5
Badge +15

Hi

 

Try to specify explicit list of columns rather than asterisk on Your query SELECT * FROM DBA_SCHEDULER_JOBS. Maybe there are CLOB, LONG columns and this is a problem. Omit them.

Piotr --> https://www.linkedin.com/in/piotr-skowronek-8425aa6/
T
Rank
Userlevel 7
Badge +19

Hi

 

Try to specify explicit list of columns rather than asterisk on Your query SELECT * FROM DBA_SCHEDULER_JOBS. Maybe there are CLOB, LONG columns and this is a problem. Omit them.

Probably is like that.

Seems like it can be the columns with datatype INTERVAL that is not supported by JDBC.

Try to remove these three columns and see if it works:

last_run_duration
schedule_limit
max_run_duration

B
Rank
Userlevel 2
Badge +5

Hi

Thanks for your responses.

I have updated the IAL as follows..

select 
OWNER,
  SCHEDULE_OWNER,
  PROGRAM_OWNER,
  JOB_NAME,
  STATE,
  LAST_START_DATE,
  
  NEXT_RUN_DATE,
  JOB_TYPE,
  COMMENTS,
  JOB_ACTION,
  SCHEDULE_NAME,
  RUN_COUNT,
  FAILURE_COUNT
FROM DBA_SCHEDULER_JOBS

That gap was the only INTERVAL type column used. There are no BLOB columns

 

I’m getting the same error :(

T
Rank
Userlevel 7
Badge +19

Maybe try to remove the TIMESTAMP columns also?

  LAST_START_DATE,
  NEXT_RUN_DATE,
 

Or if you need them convert it to character or something else, like this example:

  to_char(LAST_START_DATE,'YYYY-MM-DD HH24:MI:SS') last_start_date,
  to_char(NEXT_RUN_DATE,'YYYY-MM-DD HH24:MI:SS') next_run_date,
 

B
Rank
Userlevel 2
Badge +5

Hi Tomas

Thanks for this, you are on the right track. I removed all but a couple of text columns and it started working. I’ll re-dress the code along the lines of what you have suggested (TO_CHAR). Hopefully I can re-convert them to dates and numbers within the Quick Report 😀

 

Thanks

Reply


Terms & ConditionsCookie settings