Help to slove the Date Format Problem in a procedure

[NBSR]

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

[LKBrwn_DBA]

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.

[NBSR]

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

[LKBrwn_DBA]

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.

[NBSR]

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

[LKBrwn_DBA]

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;

[LKBrwn_DBA]

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

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!

[NBSR]

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

[LKBrwn_DBA]

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

[NBSR]

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