Home » SQL & PL/SQL » SQL & PL/SQL » REP 1401
REP 1401 [message #185635] Wed, 02 August 2006 15:30 Go to next message
sweetgeegee27
Messages: 107
Registered: June 2005
Senior Member
Can anytone tell me what's wrong with the following script?

function CF_OWNER_NAMEFormula return Char is
V_OWNER_NAME VARCHAR2(60);
V_PROPERTY_NO PROPERTY.PROPERTY_NO%TYPE;

cursor c_property (B_TAX_PAYER_NO IN NUMBER) is
SELECT P.PROPERTY_NO
FROM PROP_OWNER PO,
PROP_OWNERSHIP POS,
PROPERTY P
WHERE PO.TAX_PAYER_NO = B_TAX_PAYER_NO
AND POS.TAX_PAYER_NO = PO.TAX_PAYER_NO
AND P.PROPERTY_NO = POS.PROPERTY_NO
and (SYSDATE BETWEEN POS.START_DATE AND POS.END_DATE
or (SYSDATE >= POS.START_DATE AND POS.END_DATE IS NULL))
AND FL_VOID = 'N';

CURSOR C_OWNER_NAME IS
SELECT GET_NAME_FN(:TP_MAIL)
FROM DUAL;


BEGIN
OPEN C_OWNER_NAME;
FETCH C_OWNER_NAME
INTO V_OWNER_NAME;
CLOSE C_OWNER_NAME;

OPEN C_PROPERTY(:TAX_PAYER_NO);
FETCH C_PROPERTY
INTO V_PROPERTY_NO;
CLOSE C_PROPERTY;

DETERMINE_ADDRESS(:TP_MAIL, V_PROPERTY_NO,:TAX_TYPE_NO, TO_NUMBER(''), :P_TAX_PAYER_NAME,
:P_REP_NAME, :P_MAILING_ADDRESS,:P_CITY_NAME,:P_POST_CODE_DESC, :P_COUNTRY_NAME,
:p_parish_name);

RETURN (V_OWNER_NAME);

exception
when no_data_found then
return(null);
end;






Re: REP 1401 [message #185668 is a reply to message #185635] Thu, 03 August 2006 00:16 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
REP-1401 suggests that there's an abnormal condition.

Cursors are not necessary here; all they do could be done using an ordinary SELECT statement:
FUNCTION cf_owner_nameformula
   RETURN CHAR
IS
   v_owner_name    VARCHAR2 (60);
   v_property_no   property.property_no%TYPE;
BEGIN
   SELECT p.property_no, get_name_fn (:tp_mail)
     INTO v_property_no, v_owner_name
     FROM prop_owner po, prop_ownership pos, property p
    WHERE po.tax_payer_no = :tax_payer_no
      AND pos.tax_payer_no = po.tax_payer_no
      AND p.property_no = pos.property_no
      AND (   SYSDATE BETWEEN pos.start_date AND pos.end_date
           OR (SYSDATE >= pos.start_date AND pos.end_date IS NULL)
          )
      AND fl_void = 'N';

   determine_address (:tp_mail,
                      v_property_no,
                      :tax_type_no,
                      TO_NUMBER (''),
                      :p_tax_payer_name,
                      :p_rep_name,
                      :p_mailing_address,
                      :p_city_name,
                      :p_post_code_desc,
                      :p_country_name,
                      :p_parish_name
                     );
   RETURN (v_owner_name);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN (NULL);
END;
However, I wouldn't know what might be abnormal here; what happens when you execute SELECT statement and "determine_address" function in SQL*Plus, using the same parameters as you'd use in the report? Does any of them end with an error?
Re: REP 1401 [message #186331 is a reply to message #185668] Mon, 07 August 2006 09:24 Go to previous messageGo to next message
sweetgeegee27
Messages: 107
Registered: June 2005
Senior Member
How do I test it in SQL? What changes do I have to make? Can you assist?
Re: REP 1401 [message #186341 is a reply to message #186331] Mon, 07 August 2006 10:02 Go to previous messageGo to next message
sweetgeegee27
Messages: 107
Registered: June 2005
Senior Member
I have attached another script for comparison with the one posted earlier. I think that there is a problem in the 'Determine_Address' section.

Thanks in advance
  • Attachment: script.txt
    (Size: 10.12KB, Downloaded 214 times)
Re: REP 1401 [message #186359 is a reply to message #186341] Mon, 07 August 2006 12:40 Go to previous messageGo to next message
sweetgeegee27
Messages: 107
Registered: June 2005
Senior Member
HELP ANYBODY?
Re: REP 1401 [message #186390 is a reply to message #186359] Mon, 07 August 2006 15:06 Go to previous messageGo to next message
sweetgeegee27
Messages: 107
Registered: June 2005
Senior Member
Hey everybody. I found the solution to my problem. All I did was increase the field size for the field name P_Rep_name.
Re: REP 1401 [message #186454 is a reply to message #186390] Tue, 08 August 2006 01:01 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Thanks for the feedback!

MHE
Previous Topic: execute immediate
Next Topic: What is the query to get the 4th last maximum salary
Goto Forum:
  


Current Time: Wed Dec 07 05:00:03 CST 2016

Total time taken to generate the page: 0.16334 seconds