SQL in PL/SQL

[polaris_ws]

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;
/

[clio_usa]

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

[pando]

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

[polaris_ws]

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.

[jmodic]

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?

[pando]

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

[polaris_ws]

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.

[stecal]

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.

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

[pando]

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?

[polaris_ws]

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.