SQL in PL/SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: SQL in PL/SQL

  1. #1
    Join Date
    Jan 2003
    Location
    Maryland, USA
    Posts
    12

    Question SQL in PL/SQL

    I create a simple PL/SQL procedure to update some rows in Oracle 9i. The procedure was working through either SQLPLUS or TOAD. However, I used Informatica to call this procedure. It return the error message like these: " Backtrace message as the stack is unwounded by unhandled exceptions", "ORA-06512 line 8 and ORA06512 column 2","non-numeric character was found where a numberic was expected". Please help me on the syntax of this procedure. Is it missing something or Does it not connect to right schema? Thanks in advance.

    Here is the coding:

    CREATE OR REPLACE PROCEDURE F_MONTH_UPD
    IS
    V_FISCAL_MONTH NUMBER (2);

    BEGIN

    V_FISCAL_MONTH :=3;

    UPDATE CAMSSTG.NDW_BOP_DETAIL_M_TEST
    SET FISCAL_MONTH = V_FISCAL_MONTH
    WHERE MODIFICATION_DATE BETWEEN '1-DEC-2002' AND '31-DEC-2002';

    COMMIT;

    END F_MONTH_UPD;
    /
    Frank

  2. #2
    Join Date
    May 2002
    Location
    California, USA
    Posts
    175

    Smile

    Check the Informatica date formating. It's the same as 'DD-MON-YYYY' or it's something else.

    Is it missing something or Does it not connect to right schema?
    You said it's OK in SQL and TOAD, which leaves one option - Informatica..


    Hope that helps,

    clio_usa - OCP 8/8i/9i DBA

    Visit our Web Site

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    you should use to_date when dealing with date because otherwise you will end up things working in one place but not working in others because of NLS settings

  4. #4
    Join Date
    Jan 2003
    Location
    Maryland, USA
    Posts
    12

    Thumbs up

    Clio_usa:

    Thanks for your help. You are right. Informatica default date format is 'MM/DD/YYYY'. It may cause this problem. However, it is hard to format Oracle Date into 'MM/DD/YYYY'. Thanks.
    Frank

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by polaris_ws
    However, it is hard to format Oracle Date into 'MM/DD/YYYY'.
    ??????
    What do you mean by that? All date formats were created equal when Larry struggled through 7 days of hard work creating Oracle.

    So why should be this particular date format anything special?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    my conclusion is either, polaris_ws you are new to databases (and I am not saying Oracle because I am sure other RDBMS has to_date functions or similar) or your main job is not related to databases

  7. #7
    Join Date
    Jan 2003
    Location
    Maryland, USA
    Posts
    12

    Question Response

    Pando:

    Please make judgement after you really understand what is the question. I didn't say Oracle did not have TO_DATE function. Since I use Informatica stored procedure transformation to call this procedure stored in Oracle database. So procedure must work for Oracle first. This has been verified by running procedure through SQLPLUS and TOAD. Then use informatica to call this procedure to run, come across the date format problem. I need either to modify date format in the procedure to make it work with Oracle DB and comply with Informatica transformation or to force the informatica to accept the date format in Oracle procedure to run. If you know how to format 'dd-Mon-yyyy' to 'mm/dd/yyyy' in Oracle. Show me your approach.
    Thanks.
    Frank

  8. #8
    Join Date
    May 2002
    Posts
    2,645

    Re: Response

    Originally posted by polaris_ws
    If you know how to format 'dd-Mon-yyyy' to 'mm/dd/yyyy' in Oracle. Show me your approach.
    Thanks.
    Code:
    SQL> select to_char(sysdate, 'fmDD-MON-RRRR') from dual;
    
    TO_CHAR(SYS
    -----------
    28-JAN-2003
    
    SQL> select to_char(sysdate, 'fmmm/dd/rrrr') from dual;
    
    TO_CHAR(
    --------
    1/28/2003
    
    SQL> select to_char(sysdate, 'mm/dd/rrrr') from dual;
    
    TO_CHAR(SY
    ----------
    01/28/2003

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Code:
    UPDATE CAMSSTG.NDW_BOP_DETAIL_M_TEST
    SET FISCAL_MONTH = V_FISCAL_MONTH
    WHERE MODIFICATION_DATE BETWEEN '1-DEC-2002' AND '31-DEC-2002';
    
    UPDATE CAMSSTG.NDW_BOP_DETAIL_M_TEST
    SET FISCAL_MONTH = V_FISCAL_MONTH
    WHERE MODIFICATION_DATE BETWEEN TO_DATE('1-DEC-2002', 'DD-MM-YYYY')
                                AND TO_DATE('31-DEC-2002', 'DD-MM-YYYY');
    now you tell me what's so difficult?

  10. #10
    Join Date
    Jan 2003
    Location
    Maryland, USA
    Posts
    12

    Thumbs up

    Pando:

    Thanks for your input. I also have used the same to_date function in procedure. Then Informatica can call that procedure. In the begining, I thought that the datatype of Modification_date column was date (19). So I do not need to format it. While the date format in Informatica is 'mm-dd-yyyy'. I tried to format Oracle date into 'mm-dd-yyyy' to match up with Informatica. It didn't work. This was why I posted on line for help. Anyway, thanks for your help. After I turn back to think it over, it is really not hard.
    Frank

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