DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Help to remove error ORA-01481

  1. #1
    Join Date
    May 2008
    Posts
    34

    Question Help to remove error ORA-01481

    I have gone through my code for about 10 times and i am not finding the actual error... i need someother eyes to say me where i went wrong


    SQL> desc SAP_CERTIFICATES_EXTRACT_VIEW;
    Name Null? Type
    ----------------------------------------- -------- ---------------
    CERTIFICATE_NBR VARCHAR2(40)
    ORG_ID NOT NULL VARCHAR2(4)
    CUSTOMER_NBR_SUFX NOT NULL VARCHAR2(2)
    CUSTOMER_NBR_BASE NOT NULL VARCHAR2(8)
    PART171 NOT NULL VARCHAR2(40)
    HTS_CDE VARCHAR2(12)
    ORIGINATING_IND NOT NULL VARCHAR2(1)
    ISO_COUNTRY_CDE NOT NULL VARCHAR2(2)
    BASIS_CDE VARCHAR2(1)
    PRODUCER_CDE VARCHAR2(1)
    CERT_CDE VARCHAR2(1)
    REGIONAL_VALUE_CONTENT_CDE VARCHAR2(1)
    PART_NBR NOT NULL VARCHAR2(40)
    EFFECTIVE_DATE VARCHAR2(6)
    EFFECTIVE_FROM_DATE VARCHAR2(6)
    EFFECTIVE_TO_DATE VARCHAR2(6)
    TRANSACTION_DATE VARCHAR2(6)
    INACTIVE_IND VARCHAR(1)

    Procedure

    SQL> create or replace
    2 procedure MEXICO_NAFTA_CERTIFICATE_EXT (
    3 f_org_id varchar2,
    4 f_customer_nbr_base varchar2,
    5 f_customer_nbr_sufx varchar2,
    6 f_year VARCHAR2)
    7 is
    8 output_file utl_file.file_type;
    9 o_filename VARCHAR2(50):= 'MEXICO_NAFTA_CERTIFICATE_EXT.txt';
    10 o_DataDir CONSTANT VARCHAR2 (30) := '/d014/oradata/temp';
    11 v_CERTIFICATE_NBR fta.SAP_CERTIFICATES_EXTRACT_VIEW.CERTIFICATE_NBR%type;
    12 v_PART171 fta.SAP_CERTIFICATES_EXTRACT_VIEW.PART171%type;
    13 v_INACTIVE_IND fta.SAP_CERTIFICATES_EXTRACT_VIEW.INACTIVE_IND%type;
    14 v_HTS_CDE fta.SAP_CERTIFICATES_EXTRACT_VIEW.HTS_CDE%type;
    15 v_ORIGINATING_IND fta.SAP_CERTIFICATES_EXTRACT_VIEW.ORIGINATING_IND%type;
    16 v_ISO_COUNTRY_CDE fta.SAP_CERTIFICATES_EXTRACT_VIEW.ISO_COUNTRY_CDE%type;
    17 v_BASIS_CDE fta.SAP_CERTIFICATES_EXTRACT_VIEW.BASIS_CDE%type;
    18 v_PRODUCER_CDE fta.SAP_CERTIFICATES_EXTRACT_VIEW.PRODUCER_CDE%type;
    19 v_CERT_CDE fta.SAP_CERTIFICATES_EXTRACT_VIEW.CERT_CDE%type;
    20 v_REGIONAL_VALUE_CONTENT_CDE fta.SAP_CERTIFICATES_EXTRACT_VIEW.REGIONAL_VALUE_CONTENT_CDE%type;
    21 v_PART_NBR fta.SAP_CERTIFICATES_EXTRACT_VIEW.PART_NBR%type;
    22 v_EFFECTIVE_DATE fta.SAP_CERTIFICATES_EXTRACT_VIEW.EFFECTIVE_DATE%type;
    23 v_EFFECTIVE_FROM_DATE fta.SAP_CERTIFICATES_EXTRACT_VIEW.EFFECTIVE_FROM_DATE%type;
    24 v_EFFECTIVE_TO_DATE fta.SAP_CERTIFICATES_EXTRACT_VIEW.EFFECTIVE_TO_DATE%type;
    25 v_TRANSACTION_DATE fta.SAP_CERTIFICATES_EXTRACT_VIEW.TRANSACTION_DATE%type;
    26
    27
    28 CURSOR Cert_ext IS select certificate_nbr ,
    29 part171,
    30 inactive_ind,
    31 hts_cde,
    32 originating_ind,
    33 iso_country_cde,
    34 basis_cde,
    35 producer_cde,
    36 cert_cde,
    37 regional_value_content_cde,
    38 part_nbr,
    39 effective_date,
    40 effective_from_date,
    41 effective_to_date,
    42 transaction_date
    43 from fta.SAP_CERTIFICATES_EXTRACT_VIEW
    44 where org_id= f_org_id AND
    45 customer_nbr_base= f_customer_nbr_base AND
    46 customer_nbr_sufx = f_customer_nbr_sufx AND
    47 to_char(effective_from_date, 'yy') = f_year AND
    48 to_char(effective_to_date, 'yy') = f_year;
    49 /*******************************************************************
    50 Cursor queries the view SAP_CERTIFICATES_EXTRACT_VIEW and gets the
    51 required data according to the conditions given and values inputed.
    52 ********************************************************************/
    53
    54 -- Processing begins
    55 begin
    56
    57 output_File := UTL_FILE.FOPEN (o_DataDir, o_FileName, 'w');
    58
    59 OPEN Cert_ext;
    60 loop -- This is the loop used to fetch data form cursor to output variables
    61
    62 fetch Cert_ext into v_CERTIFICATE_NBR ,
    63 v_PART171 ,
    64 v_INACTIVE_IND,
    65 v_HTS_CDE ,
    66 v_ORIGINATING_IND ,
    67 v_ISO_COUNTRY_CDE ,
    68 v_BASIS_CDE ,
    69 v_PRODUCER_CDE ,
    70 v_CERT_CDE ,
    71 v_REGIONAL_VALUE_CONTENT_CDE ,
    72 v_PART_NBR,
    73 v_effective_date,
    74 v_effective_from_date,
    75 v_effective_to_date,
    76 v_transaction_date ;
    77
    78 EXIT WHEN Cert_ext%NOTFOUND;-- exit at end of file/cursor
    79
    80 UTL_FILE.PUT_LINE (output_File, lpad('v_CERTIFICATE_NBR', 8, ' ') || ' '||
    81 lpad('v_PART171', 35 , ' ')|| ' '||
    82 lpad('v_INACTIVE_IND', 1 , ' ') || ' '||
    83 lpad('v_HTS_CDE' , 12 , ' ')|| ' '||
    84 lpad('v_ORIGINATING_IND', 1 , ' ') || ' '||
    85 lpad('v_ISO_COUNTRY_CDE', 2 , ' ') || ' '||
    86 lpad('v_BASIS_CDE' , 1 , ' ')|| ' '||
    87 lpad('v_PRODUCER_CDE', 1 , ' ') || ' '||
    88 lpad('v_CERT_CDE', 1 , ' ') || ' '||
    89 lpad('v_REGIONAL_VALUE_CONTENT_CDE', 1 , ' ') || ' '||
    90 lpad('v_PART_NBR', 35 , ' ') || ' '||
    91 lpad('v_EFFECTIVE_DATE', 6 ) || ' '||
    92 lpad('v_EFFECTIVE_FROM_DATE', 6 ) || ' '||
    93 lpad('v_EFFECTIVE_TO_DATE', 6 ) || ' '||
    94 lpad('v_TRANSACTION_DATE', 6 ));
    95
    96 end loop;--end of loop
    97 close Cert_ext;--cursor closed
    98 UTL_FILE.FCLOSE (output_File);--UTL_FILE closed
    99 DBMS_OUTPUT.PUT_LINE ('Data Extracted');
    100 END MEXICO_NAFTA_CERTIFICATE_EXT;
    101 /

    Procedure created.

    SQL>
    SQL> declare
    2 begin
    3 MEXICO_NAFTA_CERTIFICATE_EXT('0048', '00254101', '11', '03');
    4 end;
    5 /
    declare
    *
    ERROR at line 1:
    ORA-01481: invalid number format model
    ORA-06512: at "MPC_SOURCE.MEXICO_NAFTA_CERTIFICATE_EXT", line 61
    ORA-06512: at line 3

    Can anyone say me where the conversion went worng....

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Wow, but there's a bit I could comment on here, but I will try to focus on the issue at hand.

    Your view appears to define your dates as strings for whatever reason, but your SQL then attempts to manipulate them as dates.

    EFFECTIVE_FROM_DATE VARCHAR2(6)
    EFFECTIVE_TO_DATE VARCHAR2(6)
    ...
    47 to_char(effective_from_date, 'yy') = f_year AND
    48 to_char(effective_to_date, 'yy') = f_year;

    Are you sure the SQL itself runs correctly?
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #3
    Join Date
    May 2008
    Posts
    34
    Yes it does and i have removed the error..... thx for trying...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width