Home » SQL & PL/SQL » SQL & PL/SQL » srw.do_sql ora-01830 date picture ends ...
icon5.gif  srw.do_sql ora-01830 date picture ends ... [message #230020] Tue, 10 April 2007 10:38 Go to next message
mbl73
Messages: 7
Registered: April 2007
Junior Member
Hi
I am new to Oracle and I am getting the following error:
"REP-1425:'afterpform': Error running DO_SQL package - "Error opening SQL cursor. ORA-01830 date picture ends before converting the entire input string"

v_ehy_dt := to_char(v_ehy.perf,'DD-MON-YY HH24:MI');

srw.do_sql('update temp_DI
set y_dt = '''||TO_CHAR(TO_DATE(v_ehy_dt,'DD-MON-YY HH24:MI'),'DD-MON-YY HH24:MI')||
''' where ctr_key = '||v_ehy.eq_key);


v_ehy_dt is declared as varchar2(50).
The problem arises in the do_sql statement, if the the do_sql statement is deleted I do not get the error. Is anyone familiar with this problem?
Re: srw.do_sql ora-01830 date picture ends ... [message #230025 is a reply to message #230020] Tue, 10 April 2007 11:24 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Oracle Error :: ORA-01830

date format picture ends before converting entire input string
Cause

A valid date format picture included extra data. The first part of the format picture was converted into a valid date, but the remaining data was not required.
Action

Check the specifications for date format pictures and correct the statement.
Re: srw.do_sql ora-01830 date picture ends ... [message #230048 is a reply to message #230025] Tue, 10 April 2007 13:46 Go to previous messageGo to next message
mbl73
Messages: 7
Registered: April 2007
Junior Member
Thank you, I modified it as below (v_ehy_dt now declared as a date)

srw.do_sql('update temp_DI
set y_dt = '''|v_ehy_dt||
''' where ctr_key = '||v_ehy.eq_key);

I am now getting the date part to be displayed in the report but the time is not being updated.
Re: srw.do_sql ora-01830 date picture ends ... [message #230147 is a reply to message #230048] Wed, 11 April 2007 01:19 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
mbl73 wrote on Tue, 10 April 2007 20:46
I am now getting the date part to be displayed in the report but the time is not being updated.

You do commit, right?
And what data type is Y_DT in your table temp_DI?

MHE
Re: srw.do_sql ora-01830 date picture ends ... [message #230289 is a reply to message #230147] Wed, 11 April 2007 08:03 Go to previous messageGo to next message
mbl73
Messages: 7
Registered: April 2007
Junior Member
I did not commit but I tried it with the same results.
y_dt is declared as a date.
Is there a problem with srw.do_sql and timestamps?
Re: srw.do_sql ora-01830 date picture ends ... [message #230298 is a reply to message #230289] Wed, 11 April 2007 08:22 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
You need to give a format mask if you want to see the time portion of a DATE field.
Previous Topic: deleting duplicate rows
Next Topic: Error message ORA-01487
Goto Forum:
  


Current Time: Thu Dec 08 20:09:35 CST 2016

Total time taken to generate the page: 0.13638 seconds