Help to slove the Date Format Problem in a procedure

Hi Guys...

After i joined this fourm... i really learned a lot... actually i am working as much as i can...now i am thinking so logically... i am able to write a procedure in a day,... can u belive... my first one took 5days... second was 3... now 1day...i am happy for my progress... but still i am lost sometimes... Now i wrote a procedure

create or replace
procedure MEXICO_NAFTA_CERTIFICATE_EXT (
f_org_id NUMBER,
f_customer_nbr_base NUMBER,
f_customer_nbr_sufx number,
f_year date)
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,
to_char(effective_date, 'mon-dd-yy'),
to_char(effective_from_date, 'mon-dd-yy'),
to_char(effective_to_date, 'mon-dd-yy'),
to_char(transaction_date, 'mom-dd-yy')
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 || ' '||
v_EFFECTIVE_DATE || ' '||
v_EFFECTIVE_FROM_DATE || ' '||
v_EFFECTIVE_TO_DATE || ' '||
v_TRANSACTION_DATE );

UTL_FILE.FCLOSE (output_File);
DBMS_OUTPUT.PUT_LINE ('Data Extracted');

end loop;
close Cert_ext;

END MEXICO_NAFTA_CERTIFICATE_EXT;

Prefectly executed without any errors.... Really this is the first time i wrote something without errors....usually they will be 100's of them...
anyways now i am trying to pass some values like this

SQL> declare
2 begin
3 MEXICO_NAFTA_CERTIFICATE_EXT('0048', '00254101', '11', '03');
4 end;

and i am getting error as

end;
*
ERROR at line 4:
ORA-01840: input value not long enough for date format
ORA-06512: at line 3

I know what the error is... but i am not able to eleminate it....
i gave f_year in date formate so it takes by default dd-mom-yyyy but i am pass only yy in my values....thats the problem... i tried to put to_char(f_year ,'yy') date in the procedure but thats also giving me some errors... and i am lost....
Please give me some hints to work it out plzzzzz.... as tomm i need to submit the procedure to webserver...frontend is waiting for my procedure... plzzz some hint or i dont mind if u give me solution itself...:)

Thanks a lot

-NBSR

Code:

---

3 MEXICO_NAFTA_CERTIFICATE_EXT('0048', '00254101', '11', '03');
+ ---  This is not a date  ------------------------------^
...and does not fit here:
Procedure MEXICO...(...,  f_year date)

---

You must provide the parameter values exactly like the parameter type. :rolleyes:

Well thats the problem... the input form front end will be 03 and if i take f_year as number then when i equate it with the dates in where clause of cursor with dates.... its giving error.... whats should i do... who night i scrached my brain no idea.... please help me out.....

You are comparing the input as character:

to_char(effective_to_date, 'YY')=f_year;

So define paramater as character:

Procedure MEXICO...(..., f_year varchar2)

And do some validation if you want.
:rolleyes:

already tried and got error as
SQL> declare
2 begin
3 MEXICO_NAFTA_CERTIFICATE_EXT('0048', '00254101', '11', '03');
4 end;
5 /
declare
*
ERROR at line 1:
ORA-01821: date format not recognized
ORA-06512: at "FTA_SOURCE.MEXICO_NAFTA_CERTIFICATE_EXT", line 56
ORA-06512: at line 3

so i tried changing code in line 56 which is a fetch in the loop to

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,
to_char(v_EFFECTIVE_DATE , 'mon-dd-yy'),
to_char(v_EFFECTIVE_FROM_DATE ,'mon-dd-yy'),
to_char(v_EFFECTIVE_TO_DATE ,'mon-dd-yy'),
to_char(v_TRANSACTION_DATE, 'mon-dd-yy') ;

then i got errors as

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE MEXICO_NAFTA_CERTIFICATE_EXT:

LINE/COL ERROR
-------- -----------------------------------------------------------
56/1 PL/SQL: SQL Statement ignored
67/1 PLS-00306: wrong number or types of arguments in call to
'TO_CHAR'

I tried all possible ways that comes to my little brain no now ideas left :confused:

Try NOT to convert the dates to character:

CURSOR Cert_ext IS select certificate_nbr,
...
to_char(effective_date, 'mon-dd-yy'),
to_char(effective_from_date, 'mon-dd-yy'),
to_char(effective_to_date, 'mon-dd-yy'),
to_char(transaction_date, 'mom-dd-yy')

Only when you need to compare to other character:

where org_id= f_org_id AND
...etc ,,,
to_char(effective_from_date,'yy')=f_year AND
to_char(effective_to_date, 'YY')=f_year;
:confused:

Sorry same error...

create or replace
procedure MEXICO_NAFTA_CERTIFICATE_EXT (
f_org_id NUMBER,
f_customer_nbr_base NUMBER,
f_customer_nbr_sufx number,
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 ;

EXIT WHEN Cert_ext%NOTFOUND;

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 || ' '||
v_EFFECTIVE_DATE || ' '||
v_EFFECTIVE_FROM_DATE || ' '||
v_EFFECTIVE_TO_DATE || ' '||
v_TRANSACTION_DATE );

UTL_FILE.FCLOSE (output_File);
DBMS_OUTPUT.PUT_LINE ('Data Extracted');

end loop;
close Cert_ext;

END MEXICO_NAFTA_CERTIFICATE_EXT;

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-01722: invalid number
ORA-06512: at "FTA_SOURCE.MEXICO_NAFTA_CERTIFICATE_EXT", line 56
ORA-06512: at line 3

Post the description of fta.SAP_CERTIFICATES_EXTRACT_VIEW

it is a view


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 NOT NULL DATE
EFFECTIVE_FROM_DATE NOT NULL DATE
EFFECTIVE_TO_DATE NOT NULL DATE
TRANSACTION_DATE NOT NULL DATE
INACTIVE_IND CHAR(1)

Quote:

---

Originally Posted by LKBrwn_DBA

...You must provide the parameter values exactly like the parameter type. :rolleyes:

---

Make sure all parameters/variables are the SAME TYPE!

In the procedure you declare these parameters as NUMBER, but then you call the procedure with the same as CHARACTER and in the table, they are also defined as CHARACTER:

f_org_id NUMBER,
f_customer_nbr_base NUMBER,
f_customer_nbr_sufx number,
....
ORG_ID NOT NULL VARCHAR2(4)
CUSTOMER_NBR_SUFX NOT NULL VARCHAR2(2)
CUSTOMER_NBR_BASE NOT NULL VARCHAR2(8)

So, what's the deal?

I beleive that by now you get the picture, good luck! :rolleyes:

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

---

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