Help to slove the Date Format Problem in a procedure

**NBSR** · 05-08-2008 04:04 PM

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** · 05-08-2008 04:31 PM

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.

**NBSR** · 05-09-2008 08:30 AM

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** · 05-09-2008 08:36 AM

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** · 05-09-2008 09:01 AM

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** · 05-09-2008 11:32 AM

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;

**NBSR** · 05-09-2008 11:39 AM

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

**LKBrwn_DBA** · 05-09-2008 01:23 PM

Post the description of fta.SAP_CERTIFICATES_EXTRACT_VIEW

**NBSR** · 05-09-2008 01:32 PM

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)

**LKBrwn_DBA** · 05-09-2008 02:31 PM

Originally Posted by 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!

Thread: Help to slove the Date Format Problem in a procedure

Thread Tools

Display

Help to slove the Date Format Problem in a procedure

Bookmarks

Bookmarks

Posting Permissions