473,324 Members | 2,356 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

fetched results not separating

I am working on an anonymous pl/sql block that is supposed to retrieve & output customer#, date & total price of each order, listed by order date in chronological order. My pl/sql statement is producing the right answer, but as one long number followed by the date. ex 1005100026.5731-MAR-05 is supposed to be customer# 1005 order# 1000 total price 26.57 order date 31-Mar-05

when I try to separate the answer in the DBMS_output.put_line as 'customer#:' v_customer#, I get an error msg. Can anyone help with this please?

my code is:
Declare

v_customer# varchar2(30);
v_order# number(4);
v_sum number(6,2);
v_orderdate date;

cursor total_price_cursor is

select customer#, order#, (sum(quantity*retail)) "total order", orderdate
from customers join orders using (customer#)
join orderitems using (order#)
join books using (isbn)
group by customer#, order#, orderdate
order by orderdate;

begin

open total_price_cursor;
loop
fetch total_price_cursor into v_customer#, v_order#, v_sum, v_orderdate;
exit when total_price_cursor %notfound;

DBMS_output.put_line ( V_customer# || v_order# || v_sum || v_orderdate);
end loop;

close total_price_cursor;

end;
/
Dec 4 '08 #1
5 1886
amitpatel66
2,367 Expert 2GB
Is the error something like this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> set serveroutput on;
  3. SQL> /
  4. begin
  5. *
  6. ERROR at line 1:
  7. ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
  8. ORA-06512: at "SYS.DBMS_OUTPUT", line 35
  9. ORA-06512: at "SYS.DBMS_OUTPUT", line 133
  10. ORA-06512: at line 2
  11.  
  12.  
If yes, then the data that you are trying to print in a single line is more than 255 characters as explained in the above error.
Dec 4 '08 #2
Pilgrim333
127 100+
Hi,

Stating the error would be nice. What Oracle version are you using?

Pilgrim.
Dec 4 '08 #3
I am using Oracle 10g SQL,

This is my first try at PL/SQL so I have no idea what I should be looking for, or why the output works (sort of) when its just listed as v_customer# || v_order# || v_sum || v_orderdate, but the moment I add 'customer#:' before the v_customer# I get this error message.

All your help is greatly appreciated!

my error msg is this:

Error starting at line 7 in command:
Declare

v_customer# number(4);
v_order# number(4);
v_sum number(8,2);
v_orderdate date;

cursor total_price_cursor is

select customer#, order#, (sum(quantity*retail)) "total order", orderdate
from customers join orders using (customer#)
join orderitems using (order#)
join books using (isbn)
group by customer#, order#, orderdate
order by orderdate;

begin

open total_price_cursor;
loop
fetch total_price_cursor into v_customer#, v_order#, v_sum, v_orderdate;
exit when total_price_cursor %notfound;

DBMS_output.put_line('customer#' v_customer# ||'order#' v_order# ||'sum' v_sum ||'order date' v_orderdate);
end loop;

close total_price_cursor;

end;

Error report:
ORA-06550: line 24, column 35:
PLS-00103: Encountered the symbol "V_CUSTOMER#" when expecting one of the following:

) , * & | = - + < / > at in is mod remainder not rem => ..
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ as between from using || multiset member
SUBMULTISET_
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
Dec 4 '08 #4
amitpatel66
2,367 Expert 2GB
Ah here is the error. My friend you will need to use concatenation symbol between 'Customer#' and V_Customer#. Try this statement:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DBMS_output.put_line('customer#:'||v_customer#||' order#:'||v_order# ||' sum:'||v_sum ||' order date:'|| v_orderdate);
  3.  
  4.  
Dec 5 '08 #5
Pilgrim333
127 100+
That is an easily made mistake, especially when you are just starting with Oracle. You can find more info on the concat operater at:

Concatenation Operator

Pilgrim.
Dec 5 '08 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: MRK | last post by:
I want to add a <p> after 10 results are given and do this in a loop after every 10 results. Here is my query and my current results code How would I modify this to add a <P> after every 10...
1
by: Vdasu | last post by:
What is the max number of records can be fetched in oracel in one call ? I have read it as 64k. Is this correct ?
2
by: none | last post by:
I run a web site that searches for data on other web sites and displays the results for the user onscreen. Currently, it uses perl to fetch the data, sort it and display it. It can take quite a...
8
by: Jeff S | last post by:
Please note that this question is NOT about any particular pattern - but about the general objective of separating out presentation logic from everything else. I'm trying to "get a grip" on some...
4
by: douglaswade | last post by:
I am new to Access, but I find this answer a little difficult to understand. I have an access query that list late work, I want to run the query and let say the results is ten records. I want one...
3
by: ViperBlade | last post by:
Hi, i'm unsure of the versions of MySQL and PHP I'm using (although my webhost 'guarantees' the latest), but I don't think the answer would vary that much between versions. Now to the point: I'm...
3
by: Ifoel | last post by:
Hi all pls help me How to know "HOW MANY SECOND" we fetched the rows of the record MySQL via Visual Basic 6...? Sample: 321 rows Fetched in 0,234s -> i need this field to load on the FORM...
1
by: preejith | last post by:
Error Code : 1329, No data - zero rows fetched, selected, or processed. MYSQL I am getting the following error while running a stored procedure in mysql5.0 Error Code : 1329 No data - zero rows...
1
by: gubbachchi | last post by:
Hi, For my project I have a "add" button, upon clicking it, it will take the user to next page where there will be a text box and list of data displayed below it which is fetched from mysql...
2
by: jaguarpk786 | last post by:
ive this code which fetches track title and artist name from the database ,,,, i want to insert radio button in front of each fetched row through which user wud b able to select a song which he wants...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.