Help to slove the Date Format Problem in a procedure
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

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

  1. #1
    Join Date
    May 2008
    Posts
    34

    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

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483

    Talking

    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.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    May 2008
    Posts
    34
    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.....

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483

    Cool

    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.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    May 2008
    Posts
    34
    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
    Last edited by NBSR; 05-09-2008 at 10:30 AM.

  6. #6
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483

    Cool

    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;
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  7. #7
    Join Date
    May 2008
    Posts
    34
    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

  8. #8
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483
    Post the description of fta.SAP_CERTIFICATES_EXTRACT_VIEW
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  9. #9
    Join Date
    May 2008
    Posts
    34
    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)

  10. #10
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483

    Cool

    Quote 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!
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width