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?
Best answer by Tomas Ruderfelt
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,
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.
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
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 :(
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,
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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
