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