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....
