• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1056
  • Last Modified:

Character string buffer too small

Hi,

I have the following code:

g_user_node xmldom.DOMNode;
g_root_element xmldom.DOMElement;
g_item_text xmldom.DOMText;
g_xmlnode xmldom.DOMNode;

cursor c_get_requests is
      select rownum,
      REQUEST_NO,
      REQUEST_DATE date_created1
from IT02M_VIS_REQUEST

I write my xml file using:

for r_get_requests in c_get_requests loop

g_item_element := xmldom.createElement(g_document, 'DATE_CREATED');
g_xmlnode := xmldom.appendChild(g_item_node, xmldom.makeNode(g_item_element));
g_item_text := xmldom.createTextNode(g_document, r_get_requests.date_created1);
g_user_node := xmldom.appendChild(g_xmlnode, xmldom.makeNode(g_item_text));

The above works fine.

NOW I have to output my date_created element in a specific format, but if I change the cursor to:

to_char(REQUEST_DATE,'dd/mm/yyyy hh24:mi:ss') as date_created1,  

I get a ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "TIA.IB02M_VIS", line 284

I dont know why, please help.

Regards    


0
barnarp
Asked:
barnarp
  • 4
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what data type and/or format is the field REQUEST_DATE actually?

if it is varchar, you have to put a to_date "first":

to_char(to_date(REQUEST_DATE, 'your_current_format_goes_here'),'dd/mm/yyyy hh24:mi:ss') as date_created1,
0
 
barnarpAuthor Commented:
It is a DATE.
0
 
barnarpAuthor Commented:
I need the XML element in a certain date format: dd/mm/yyyy hh24:mi:ss.

If I dont convert to_char I get 07-DEC-07 which I dont want.

I dont know how else to get this done as The moment I do a to_char, I get the error.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what happens if you run this query directly:

select rownum,
      REQUEST_NO,
      REQUEST_DATE,
      to_char(REQUEST_DATE,'dd/mm/yyyy hh24:mi:ss') as date_created1
from IT02M_VIS_REQUEST

does that work or does it give the same error?
0
 
barnarpAuthor Commented:
It works 100%
0
 
SujithData ArchitectCommented:
Try to change the cursor declaration like this:

cast( to_char(REQUEST_DATE, 'dd/mm/yyyy hh24:mi:ss') as varchar2(19) ) as date_created1
0
 
barnarpAuthor Commented:
I will try and let you know
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now