Home » SQL & PL/SQL » SQL & PL/SQL » FRM 40735 Pre Query trigger raised unhandled exception ORA 04067
FRM 40735 Pre Query trigger raised unhandled exception ORA 04067 [message #188439] Fri, 18 August 2006 10:13 Go to next message
sweetgeegee27
Messages: 107
Registered: June 2005
Senior Member
declare
	cursor c_exist is
	  select count(*) 
	  from tax_payer_balance_tmp
	  where tax_payer_no = :taxac.tax_payer_no;
	  
	v_count   number;
begin
	open c_exist;
	fetch c_exist into v_count;
	close c_exist;
	
	if v_count > 0 then
		null;
	else
		INSERT_TP_BALANCE(:TAXAC.TAX_PAYER_NO);
    :tax_total := NULL;
    :pen_total := NULL;
    :int_total := NULL;
    :big_total := NULL;
	end if;
end;


Can anyone tell me what is wrong with this code? I think this error is in the else statement.
Re: FRM 40735 Pre Query trigger raised unhandled exception ORA 04067 [message #188446 is a reply to message #188439] Fri, 18 August 2006 10:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, 30 seconds with the documentation would have revealed that the text for your error message is
Quote:

ORA-04067 not executed, string does not exist
Cause: Attempt to execute a non-existent stored procedure.
Action: Make sure that a correct name is given.

Given that you only make one call to a SP, I'd guess that either INSERT_TP_BALANCE doesn't exist, or it calls something that doesn't exist.
Re: FRM 40735 Pre Query trigger raised unhandled exception ORA 04067 [message #188452 is a reply to message #188439] Fri, 18 August 2006 11:12 Go to previous messageGo to next message
sweetgeegee27
Messages: 107
Registered: June 2005
Senior Member
The procedure does exist. Here it is


PROCEDURE          INSERT_TP_BALANCE
 (P_TAX_PAYER_NO IN TAX_PAYER.TAX_PAYER_NO%TYPE
 )
 IS

CURSOR C_PTAX_OWNER
 (B_TAX_PAYER_NO IN NUMBER
 )
 IS
select tax_payer_no_own owner_no
	  from  prop_owner_detail pod,
	        prop_ownership po
	  where pod.tax_payer_no = b_tax_payer_no
	  and   pod.tax_payer_no_own = po.tax_payer_no
	  and   end_date is null
	  and   fl_void = 'N';
BEGIN
    INSERT INTO TAX_PAYER_BALANCE_TMP  (
    SELECT TAX_PAYER_NO,
           TAX_TYPE_NO,
           TAX_TYPE_DESC,
           TAX_PERIOD_NO,
           TPER_MONTH,
           TPER_YEAR,
           ARREARS_FLAG,
           TOTAL_AMOUNT,
           NULL
    FROM TAX_PAYER_BALANCE
    WHERE TAX_PAYER_NO = P_TAX_PAYER_NO);

    for x in c_ptax_owner(p_tax_payer_no) loop
      insert into tax_payer_balance_tmp (
      SELECT P_TAX_PAYER_NO,
             TAX_TYPE_NO,
             TAX_TYPE_DESC,
             TAX_PERIOD_NO,
             TPER_MONTH,
             TPER_YEAR,
             ARREARS_FLAG,
             TOTAL_AMOUNT,
             x.owner_no
      FROM TAX_PAYER_BALANCE
      WHERE TAX_PAYER_NO = x.owner_no);
    end loop;

END;
Re: FRM 40735 Pre Query trigger raised unhandled exception ORA 04067 [message #188509 is a reply to message #188452] Fri, 18 August 2006 17:06 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
A general tip: you open, fetch and close a cursor to see whether one record exists. Why the count? It will simply kill your performance. You can open, fetch and check cursor%FOUND or cursor%NOTFOUND.

MHE
Re: FRM 40735 Pre Query trigger raised unhandled exception ORA 04067 [message #188761 is a reply to message #188439] Mon, 21 August 2006 09:37 Go to previous messageGo to next message
sweetgeegee27
Messages: 107
Registered: June 2005
Senior Member
so how do i amend the script to remove the count part of it?
Re: FRM 40735 Pre Query trigger raised unhandled exception ORA 04067 [message #188767 is a reply to message #188439] Mon, 21 August 2006 10:06 Go to previous message
sweetgeegee27
Messages: 107
Registered: June 2005
Senior Member
I found the solution under another post by me. Since the procedure did not belong to me, I had to refer to it by putting the schema name: schema_name.procedure_name.

Thanks for all your assistance.
Previous Topic: Calling procedures
Next Topic: update using group by
Goto Forum:
  


Current Time: Thu Dec 08 00:35:56 CST 2016

Total time taken to generate the page: 0.10487 seconds