Help to slove the Date Format Problem in a procedure

Quote:

---

Originally Posted by LKBrwn_DBA

Ooops, my bad (Monday -- need coffe). :o

---

And i need break,.. form this error...and my lead:o

Quote:

---

Originally Posted by NBSR

And i need break,.. form this error...and my lead:o

---

Check my corrected post, I was right the first time...;)

Quote:

---

Originally Posted by LKBrwn_DBA

Ooops, my bad (Monday -- need coffe) :o

Or maybe not...now you confused me, the manual says:


And the example is:

Code:

---

SQL> CREATE DIRECTORY out_dir AS '/appl/gl/user';
SQL> GRANT READ ON DIRECTORY user_dir TO PUBLIC;
-- etc --
DECLARE
  V1 VARCHAR2(32767);
  F1 UTL_FILE.FILE_TYPE;
BEGIN
  -- In this example MAX_LINESIZE is less than GET_LINE's length request
  -- so the number of bytes returned will be 256 or less if a line terminator is seen.
  F1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R',256);
  UTL_FILE.GET_LINE(F1,V1,32767);
  UTL_FILE.FCLOSE(F1);
-- etc --

---

---

I dont think its problem with the dir,.... i think its still the same problem with the date format,... as i tried to remove the utl error and got this


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_HTS_CDE fta.SAP_CERTIFICATES_EXTRACT_VIEW.HTS_CDE%type;
14 v_ORIGINATING_IND fta.SAP_CERTIFICATES_EXTRACT_VIEW.ORIGINATING_IND%type;
15 v_ISO_COUNTRY_CDE fta.SAP_CERTIFICATES_EXTRACT_VIEW.ISO_COUNTRY_CDE%type;
16 v_BASIS_CDE fta.SAP_CERTIFICATES_EXTRACT_VIEW.BASIS_CDE%type;
17 v_PRODUCER_CDE fta.SAP_CERTIFICATES_EXTRACT_VIEW.PRODUCER_CDE%type;
18 v_CERT_CDE fta.SAP_CERTIFICATES_EXTRACT_VIEW.CERT_CDE%type;
19 v_REGIONAL_VALUE_CONTENT_CDE fta.SAP_CERTIFICATES_EXTRACT_VIEW.REGIONAL_VALUE_CONTENT_CDE%type;
20 v_PART_NBR fta.SAP_CERTIFICATES_EXTRACT_VIEW.PART_NBR%type;
21 v_EFFECTIVE_DATE fta.SAP_CERTIFICATES_EXTRACT_VIEW.EFFECTIVE_DATE%type;
22 v_EFFECTIVE_FROM_DATE fta.SAP_CERTIFICATES_EXTRACT_VIEW.EFFECTIVE_FROM_DATE%type;
23 v_EFFECTIVE_TO_DATE fta.SAP_CERTIFICATES_EXTRACT_VIEW.EFFECTIVE_TO_DATE%type;
24 v_TRANSACTION_DATE fta.SAP_CERTIFICATES_EXTRACT_VIEW.TRANSACTION_DATE%type;
25 v_INACTIVE_IND fta.SAP_CERTIFICATES_EXTRACT_VIEW.INACTIVE_IND%type;
26
27 CURSOR Cert_ext IS select certificate_nbr,
28 part171,
29 inactive_ind,
30 hts_cde,
31 originating_ind,
32 iso_country_cde,
33 basis_cde,
34 producer_cde,
35 cert_cde,
36 regional_value_content_cde,
37 part_nbr,
38 effective_date,
39 effective_from_date,
40 effective_to_date,
41 transaction_date
42 from fta.SAP_CERTIFICATES_EXTRACT_VIEW
43 where org_id= f_org_id AND
44 customer_nbr_base= f_customer_nbr_base AND
45 customer_nbr_sufx = f_customer_nbr_sufx AND
46 to_char(effective_from_date, 'yy') = f_year AND
47 to_char(effective_to_date, 'yy') =f_year;
48
49 begin
50
51 output_File := UTL_FILE.FOPEN (o_DataDir, o_FileName, 'w');
52
53 OPEN Cert_ext;
54 loop
55
56 fetch Cert_ext into v_CERTIFICATE_NBR,
57 v_PART171 ,
58 v_INACTIVE_IND,
59 v_HTS_CDE ,
60 v_ORIGINATING_IND ,
61 v_ISO_COUNTRY_CDE ,
62 v_BASIS_CDE ,
63 v_PRODUCER_CDE ,
64 v_CERT_CDE ,
65 v_REGIONAL_VALUE_CONTENT_CDE ,
66 v_PART_NBR,
67 v_effective_date,
68 v_effective_from_date,
69 v_effective_to_date,
70 v_transaction_date;
71
72 UTL_FILE.PUT_LINE (output_File, v_CERTIFICATE_NBR || ' '||
73 v_PART171 || ' '||
74 v_INACTIVE_IND || ' '||
75 v_HTS_CDE || ' '||
76 v_ORIGINATING_IND || ' '||
77 v_ISO_COUNTRY_CDE || ' '||
78 v_BASIS_CDE || ' '||
79 v_PRODUCER_CDE || ' '||
80 v_CERT_CDE || ' '||
81 v_REGIONAL_VALUE_CONTENT_CDE || ' '||
82 v_PART_NBR || ' '||
83 v_EFFECTIVE_DATE|| ' '||
84 v_EFFECTIVE_FROM_DATE || ' '||
85 v_EFFECTIVE_TO_DATE || ' '||
86 v_TRANSACTION_DATE);
87
88 UTL_FILE.FCLOSE (output_File);
89 DBMS_OUTPUT.PUT_LINE ('Data Extracted');
90 end loop;
91 close Cert_ext;
92 END MEXICO_NAFTA_CERTIFICATE_EXT;
93 /

Procedure created.

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

SQL> desc fta.SAP_CERTIFICATES_EXTRACT_VIEW;
Name Null? Type
----------------------------------------- -------- ---------------
CERTIFICATE_NBR NOT NULL NUMBER
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 DATE
EFFECTIVE_FROM_DATE DATE
EFFECTIVE_TO_DATE DATE
TRANSACTION_DATE DATE
INACTIVE_IND CHAR(1)

i removed all the to_char in the code... in view and procedure.... and i still have that dam error.... what ever it is... i am going crazy...


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

Procedure created.

SQL> declare
2 begin
3 MEXICO_NAFTA_CERTIFICATE_EXT('0048', '00254101', '11', '03');
4 end;
5 /
Data Extracted
declare
*
ERROR at line 1:
ORA-29282: invalid file ID
ORA-06512: at "SYS.UTL_FILE", line 774
ORA-06512: at "FTA_SOURCE.MEXICO_NAFTA_CERTIFICATE_EXT", line 71
ORA-06512: at line 3