Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Character string buffer too small

Posted on 2007-12-06
7
Medium Priority
?
1,046 Views
Last Modified: 2013-12-07
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
Comment
Question by:barnarp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20426025
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
 

Author Comment

by:barnarp
ID: 20426337
It is a DATE.
0
 

Author Comment

by:barnarp
ID: 20426353
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 20426446
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
 

Author Comment

by:barnarp
ID: 20426496
It works 100%
0
 
LVL 27

Expert Comment

by:sujith80
ID: 20426523
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
 

Author Comment

by:barnarp
ID: 20426547
I will try and let you know
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question