Home » Developer & Programmer » Reports & Discoverer » Terminated with error: <br>REP-300: service handle not initialized (Oracle reports 10g, database 10g)
Terminated with error: <br>REP-300: service handle not initialized [message #424396] Fri, 02 October 2009 01:52 Go to next message
bechara.daccache
Messages: 14
Registered: September 2008
Location: Lebanon
Junior Member

Dear,

I created a report.
when we try to run it from the application it shows the following error:

"Terminated with error: <br>REP-300: service handle not initialized ==> SELECT payr.emp_id payr_emp_id, "

But the query where this error points works fine on "Toad", in other words, there is no syntax errors.
Anyway this is the query:
SELECT payr.emp_id  pyr_emp_id,
              payr.com_code  pyr_com_code,
              HR_ROUND(SUM(payr.amount),5)    ADD_amount,
              reco.name_e  ADD_paye_name,
              reco.sort_order 
FROM    payrolls            payr,
              pay_elements  paye,
              hr_report         rep,
              hr_report_columns reco,
              hr_column_pay_element cpe
WHERE payr.payp_id = :P_PERIOD_ID
AND   payr.com_code = :P_COMP_CODE
AND   payr.paye_code = paye.code
AND   to_number(to_char(paye.addition_flag)) =1  
AND   payr.com_code = paye.com_code
AND  :p_payroll_type = 'F'
AND   reco.repo_seq = rep.seq
AND   cpe.reco_repo_seq = reco.repo_seq
AND   cpe.reco_seq = reco.seq
AND   cpe.paye_code = paye.code
AND   rep.code='001'
and reco.code = '01'
GROUP BY payr.emp_id, payr.com_code,reco.name_e, reco.sort_order
UNION
SELECT payr.emp_id  emp_id,
              payr.com_code  com_code,
              SUM(payr.amount)  ,
              reco.name_e  paye_name,
              reco.sort_order 
FROM    trial_payrolls            payr,
               pay_elements  paye,
               hr_report         rep,
               hr_report_columns reco,
               hr_column_pay_element cpe
WHERE payr.payp_id = :P_PERIOD_ID
AND   payr.com_code = :P_COMP_CODE
AND   payr.paye_code = paye.code
AND   to_number(to_char(paye.addition_flag)) =1  
AND  payr.com_code = paye.com_code
AND  :p_payroll_type = 'T'
AND  payr.amount IS NOT NULL
AND   reco.repo_seq = rep.seq
AND   cpe.reco_repo_seq = reco.repo_seq
AND   cpe.reco_seq = reco.seq
AND   cpe.paye_code = paye.code
AND   rep.code='001'
GROUP BY payr.emp_id, payr.com_code, /*paye.code ,*/reco.name_e, reco.sort_order/*, paye.order_by*/
ORDER BY 5

Something weird about this query is that the field " paye.addition_flag" has a datatype: Number, but I was obliged to force it by "To_Number" to put the condition.( I never knew why!)

After some searches, I discovered that this is some kind of space allocation error.
Please can you help me solving this problem, because it took too much time, and I can't find any solution.
And if you needed any clarification, do not hesitate asking me anything.

Best Regards,

Bechara
Re: Terminated with error: <br>REP-300: service handle not initialized [message #424474 is a reply to message #424396] Fri, 02 October 2009 09:41 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
So what happens if you change:
AND   to_number(to_char(paye.addition_flag)) =1  

to
AND   paye.addition_flag =1  

Re: Terminated with error: <br>REP-300: service handle not initialized [message #424639 is a reply to message #424474] Mon, 05 October 2009 01:27 Go to previous messageGo to next message
bechara.daccache
Messages: 14
Registered: September 2008
Location: Lebanon
Junior Member

The query used to be without to_number, but it was taking a very long time and always timed out.
So after long searches in the query I realized that it was having problem understanding the datatype of this item, that's why I was obliged to enforce it by using To_Number.
Re: Terminated with error: <br>REP-300: service handle not initialized [message #424680 is a reply to message #424396] Mon, 05 October 2009 05:24 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
That makes absolutely no sense. If anything the version with the to_number should be slower as it'll prevent index usage.
If you're having to use to_number here that means something is wrong. So I'd concentrate on getting to the bottom of that. It might even fix your other issue.

Post explain plans for the query with and without the to_number/to_char.
Re: Terminated with error: <br>REP-300: service handle not initialized [message #425761 is a reply to message #424680] Mon, 12 October 2009 02:29 Go to previous message
bechara.daccache
Messages: 14
Registered: September 2008
Location: Lebanon
Junior Member

I know it makes absolutely no sense!!
the paye.addition_flag datatype is Number, even thought it is not understanding it without the to_number function. Confused
Previous Topic: Button on report 6i?
Next Topic: don't display on last page
Goto Forum:
  


Current Time: Fri Dec 02 18:19:03 CST 2016

Total time taken to generate the page: 0.37553 seconds