• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1047
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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
 
sujith80Commented:
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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