Help to slove the Date Format Problem in a procedure

I got it and i corrected it..... thx a lot there is no error now,... but someing is wrong with the loop.... and i am not understaing it...

create or replace
procedure MEXICO_NAFTA_CERTIFICATE_EXT (
f_org_id varchar2,
f_customer_nbr_base varchar2,
f_customer_nbr_sufx varchar2,
f_year varchar2)
is
output_file utl_file.file_type;
o_filename VARCHAR2(50):= 'MEXICO_NAFTA_CERTIFICATE_EXT.txt';
o_DataDir CONSTANT VARCHAR2 (30) := '/d014/oradata/temp';
v_CERTIFICATE_NBR fta.SAP_CERTIFICATES_EXTRACT_VIEW.CERTIFICATE_NBR%type;
v_PART171 fta.SAP_CERTIFICATES_EXTRACT_VIEW.PART171%type;
v_HTS_CDE fta.SAP_CERTIFICATES_EXTRACT_VIEW.HTS_CDE%type;
v_ORIGINATING_IND fta.SAP_CERTIFICATES_EXTRACT_VIEW.ORIGINATING_IND%type;
v_ISO_COUNTRY_CDE fta.SAP_CERTIFICATES_EXTRACT_VIEW.ISO_COUNTRY_CDE%type;
v_BASIS_CDE fta.SAP_CERTIFICATES_EXTRACT_VIEW.BASIS_CDE%type;
v_PRODUCER_CDE fta.SAP_CERTIFICATES_EXTRACT_VIEW.PRODUCER_CDE%type;
v_CERT_CDE fta.SAP_CERTIFICATES_EXTRACT_VIEW.CERT_CDE%type;
v_REGIONAL_VALUE_CONTENT_CDE fta.SAP_CERTIFICATES_EXTRACT_VIEW.REGIONAL_VALUE_CONTENT_CDE%type;
v_PART_NBR fta.SAP_CERTIFICATES_EXTRACT_VIEW.PART_NBR%type;
v_EFFECTIVE_DATE fta.SAP_CERTIFICATES_EXTRACT_VIEW.EFFECTIVE_DATE%type;
v_EFFECTIVE_FROM_DATE fta.SAP_CERTIFICATES_EXTRACT_VIEW.EFFECTIVE_FROM_DATE%type;
v_EFFECTIVE_TO_DATE fta.SAP_CERTIFICATES_EXTRACT_VIEW.EFFECTIVE_TO_DATE%type;
v_TRANSACTION_DATE fta.SAP_CERTIFICATES_EXTRACT_VIEW.TRANSACTION_DATE%type;
v_INACTIVE_IND fta.SAP_CERTIFICATES_EXTRACT_VIEW.INACTIVE_IND%type;

CURSOR Cert_ext IS select certificate_nbr,
part171,
inactive_ind,
hts_cde,
originating_ind,
iso_country_cde,
basis_cde,
producer_cde,
cert_cde,
regional_value_content_cde,
part_nbr,
effective_date,
effective_from_date,
effective_to_date,
transaction_date
from fta.SAP_CERTIFICATES_EXTRACT_VIEW
where org_id= f_org_id AND
customer_nbr_base= f_customer_nbr_base AND
customer_nbr_sufx = f_customer_nbr_sufx AND
to_char(effective_from_date, 'yy') = f_year AND
to_char(effective_to_date, 'yy') =f_year;

begin

output_File := UTL_FILE.FOPEN (o_DataDir, o_FileName, 'w');

OPEN Cert_ext;
loop

fetch Cert_ext into v_CERTIFICATE_NBR,
v_PART171 ,
v_INACTIVE_IND,
v_HTS_CDE ,
v_ORIGINATING_IND ,
v_ISO_COUNTRY_CDE ,
v_BASIS_CDE ,
v_PRODUCER_CDE ,
v_CERT_CDE ,
v_REGIONAL_VALUE_CONTENT_CDE ,
v_PART_NBR,
v_effective_date,
v_effective_from_date,
v_effective_to_date,
v_transaction_date;

UTL_FILE.PUT_LINE (output_File, v_CERTIFICATE_NBR || ' '||
v_PART171 || ' '||
v_INACTIVE_IND || ' '||
v_HTS_CDE || ' '||
v_ORIGINATING_IND || ' '||
v_ISO_COUNTRY_CDE || ' '||
v_BASIS_CDE || ' '||
v_PRODUCER_CDE || ' '||
v_CERT_CDE || ' '||
v_REGIONAL_VALUE_CONTENT_CDE || ' '||
v_PART_NBR || ' '||
to_char(v_EFFECTIVE_DATE, 'mm-dd-yy') || ' '||
to_char(v_EFFECTIVE_FROM_DATE, 'mm-dd-yy') || ' '||
to_char(v_EFFECTIVE_TO_DATE, 'mm-dd-yy') || ' '||
to_char(v_TRANSACTION_DATE, 'mm-dd-yy'));

end loop;
UTL_FILE.FCLOSE (output_File);
close Cert_ext;
DBMS_OUTPUT.PUT_LINE ('Data Extracted');
END MEXICO_NAFTA_CERTIFICATE_EXT;

i tried in may ways but sill... there is some incorrect with the loops... please say me where i am wrong... i am out of time now...

Can't see the LOOP and EXIT statements in the above code. :rolleyes:

I got where its wrong... i mean the loop thing....
and i got a dam new error..... things are not going as i want them,.... my lead..donkey lead... only thinks about himself...he doesnt understand how hard it is for me,... and also take time for me to complete work...i dont like him... always email me is it done...?? is the code working,?? i want to see extracted file...??
Anyways,..... all leads are same... they only give work to other...never work themselves....

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 to_char(v_EFFECTIVE_DATE, 'mm-dd-yy') || ' '||
84 to_char(v_EFFECTIVE_FROM_DATE, 'mm-dd-yy') || ' '||
85 to_char(v_EFFECTIVE_TO_DATE, 'mm-dd-yy') || ' '||
86 to_char(v_TRANSACTION_DATE, 'mm-dd-yy'));
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> set serveroutput on
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

Now what is worong,.... invalid file id... where did that come from...??
please say me stright what is wrong....
please please....

Where are the LOOP and EXIT statements in your code?

Without the EXIT statement you have an infinite LOOP...

Quote:

---

Originally Posted by NBSR

And what is wrong with the loops... please say me...

---

On lesson #1 you learned you have to have a way to exist a loop, where is that piece of logic in your code?

No soup for you, one year! :cool:

I have delt with that error... there is another one i mean error... check it out

The directory name in UTL_FILE.OPEN() procedure has to be a directory object name (see manual entry for CREATE DIRECTORY).
:rolleyes:

I have decleared it as o_datadir and assingned the address to it...now its an object right,... i am wrong..by doing it like this,...

I have written 2 other procedure same like this and they have worked,,...

Quote:

---

Originally Posted by LKBrwn_DBA

The directory name in UTL_FILE.OPEN() procedure has to be a directory object name

---

Is this something new? I remember pointing UTL_FILE to a folder/directory providing the oracle account has the right privs on it.

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

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

Quote:

---

Directory location of file. This string is a directory object name and is case sensitive.

---

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

---