ORA-1858 -Proc issue - runs ok on Client - does not run via Sch tool
Version :Oracle Database - 220.127.116.11.0
Project: Moving to new environment, copied existing fully functional database from prod at a point in time to test.
Objective: Get all procs, functions jobs etc running on new environment that are running on prod environment.
Re - ORA-01858: a non-numeric character was found where a numeric was expected
Have issue with procedure, when executing from client no issues, however when proc called via 3rd party scheduling tool, from server issue is coming
Proc completes ok when executed on client via Toad - completes ok.
Procedure executed from client via sqlplus - proc completes ok.
Statement below executed asis from toad - ok
Statement in code where proc kicks out error when submitted from server.
INSERT INTO Local.G_RATES(TO_CURRENCY, CONVERSION_YEAR, CONVERSION_RATE, CONVERSION_PERIOD)
select a.to_currency, to_char(a.conversion_date, 'YYYY') Year, max(a.conversion_rate) conversion_rate,
from gl.gl_daily_rates a, local.dates b
AND CONVERSION_TYPE = '1000'
AND trunc(b.period_last_date) = to_char(a.conversion_date, 'DD-MON-YYYY')
group by a.to_currency, to_char(a.conversion_date, 'YYYY'),TO_char(CONVERSION_DATE,'MON-YY')
My general question is how come (when i am setting no parameters on client) is my proc is completing ok and kicking out with errors when same proc run by scheduling job?
Are there any specific settings that i should look into on the profile of the account that is running the job via sqlplus on the server?
Any suggestions / reccomendations on steps to test out appreciated.
Click Here to Expand Forum to Full Width