-
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....
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|