Originally Posted by Saii
Just try using
to_date(to_char (<your date variable>,'DD-MON-YYYY'),'DD-MON-YYYY') while inserting to other table
I figured out the solution, this one was close, but it was actually this:
to_char(<your date variable>,'DD-MON-YYYY') but with quotes around it. Have to include the '' around it, too. Just the to_char by itself with formatting...
kalyson
Last Activity: Mar 28 '08, 09:26 PM
Joined: Mar 11 '08
Location:
-
Oracle gives an error for this, unfortunately....Leave a comment:
-
Thanks, but that did not work. I had already tried it a while back, but it was a good suggestion....Leave a comment:
-
I found in Toad where to make the NLS parameter setting. It still does the same thing...Geeezzz .Leave a comment:
-
Well, I ran the alter session outside the procedure, then ran the procedure and same thing happens. I print out the date variable just prior to insertion, and the dates are like, 1997, 1995. Then in the table I see they have been inserted as 2097, 2095. Very strange.Leave a comment:
-
I'll admit to being a newbie with PL/SQL, but I did already try that. I got a syntax error. Maybe I should put it outside the PL/SQL procedure...Leave a comment:
-
That happens to me sometimes (Toad version 9.1.0.62). It appears to be a bug -- I have to close out Toad and restart it and then it works fine. I don't know if that is the same thing you are experiencing, though. But once I restart it, it will work until the next time it gets that error. Then I restart again....Leave a comment:
-
Problem with inserting dates
I am using a variable of type: DATE
Our instance of Oracle has: NLS_DATE_FORMAT DD-MON-YY
I select a DATE from table A into this date variable. I then insert that variable into another table, B. Dates that were before 2000 change -- like 1995 becomes 2095. I do nothing to the date between select and insert.
My PL/SQL is being executed in Toad version 9.1. Oracle is release 1002000300... -
Well, I think I see the answer myself. You can use enclosing local blocks and have a no_data_found exception handler there that raises another exception that throws it to the main block's exception handler.Leave a comment:
-
Trapping system exceptions
Hello,
I see that Oracle raises its own exceptions for things like this:
EXECUTE IMMEDIATE l_sql_stmt INTO l_temp_string;
when there is no data found from the query. I don't want to handle all these exceptions in one WHEN DATA_NOT_FOUND exception handler. I think it is incredible that these exceptions can not be differentiated! In Java, such errors can be captured inside a try/catch block. But not here,... -
Newbie Question about dynamic table name references
Hello,
I realize that using the TYPE declaration for variables makes their definitions more dynamic, like this:
myField tablename.name% TYPE;
myRow tablename%ROWTY PE;
But I also want to be able to change the name of the table in the code.
So I want to have "tablename" above refer to a VARIABLE that contains the name of a table, so I don't have to go...
No activity results to display
Leave a comment: