please help with my code PL/SQL Block [message #570483] |
Sat, 10 November 2012 23:32  |
 |
etoilethay
Messages: 9 Registered: October 2012
|
Junior Member |
|
|
I am trying to create a PL/SQL program block that retrieves each customer from the CUSTOMER table and creates a list that displays each customer's name, address and phone number on a single line and then print a heading at the beginning of the list. But I am getting many errors : here is the code
SET serveroutput ON;
DECLARE
CURSOR cursMailingList IS
SELECT INITCAP(c_first || ' ' || c_last || ' ' || c_address || ' ' || c_dphone) AS STR
FROM customer
GROUP BY(c_first || ' ' || c_last || ' ' || c_address || ' ' || c_dphone);
cursRow cursMailingList%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Clearwater Traders Mailing List');
OPEN cursMailingList;
FETCH cursMailingList INTO cursRow;
LOOP
EXIT WHEN cursMailingList%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(cursRow.STR);
FETCH cursMailingList INTO cursRow;
END LOOP;
CLOSE cursMailingList;
END;
and here are the errors
Error report:
ORA-06550: line 5, column 69:
PL/SQL: ORA-00904: "C_DPHONE": invalid identifier
ORA-06550: line 3, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 2, column 10:
PLS-00341: declaration of cursor 'CURSMAILINGLIST' is incomplete or malformed
ORA-06550: line 6, column 11:
PL/SQL: Item ignored
ORA-06550: line 10, column 30:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 10, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 13, column 26:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 13, column 5:
PL/SQL: Statement ignored
ORA-06550: line 14, column 32:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 14, column 5:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
|
|
|
Re: please help with my code PL/SQL Block [message #570485 is a reply to message #570483] |
Sun, 11 November 2012 00:15   |
 |
Michel Cadot
Messages: 68765 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
Use SQL*Plus and copy and paste your session, the WHOLE session like that:
SQL> DECLARE
2 CURSOR cursMailingList IS
3 SELECT INITCAP(c_first || ' ' || c_last || ' ' || c_address || ' ' || c_dphone) AS STR
4 FROM customer
5 GROUP BY(c_first || ' ' || c_last || ' ' || c_address || ' ' || c_dphone);
6 cursRow cursMailingList%ROWTYPE;
7 BEGIN
8 DBMS_OUTPUT.PUT_LINE('Clearwater Traders Mailing List');
9 OPEN cursMailingList;
10 FETCH cursMailingList INTO cursRow;
11 LOOP
12 EXIT WHEN cursMailingList%NOTFOUND;
13 DBMS_OUTPUT.PUT_LINE(cursRow.STR);
14 FETCH cursMailingList INTO cursRow;
15 END LOOP;
16 CLOSE cursMailingList;
17 END;
18
19 /
GROUP BY(c_first || ' ' || c_last || ' ' || c_address || ' ' || c_dphone);
*
ERROR at line 5:
ORA-06550: line 5, column 69:
PL/SQL: ORA-00904: "C_DPHONE": invalid identifier
ORA-06550: line 3, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 2, column 10:
PLS-00341: declaration of cursor 'CURSMAILINGLIST' is incomplete or malformed
ORA-06550: line 6, column 11:
PL/SQL: Item ignored
ORA-06550: line 10, column 30:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 10, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 13, column 26:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 13, column 5:
PL/SQL: Statement ignored
ORA-06550: line 14, column 32:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 14, column 5:
PL/SQL: SQL Statement ignored
SQL> desc customer
Name Null? Type
-------------------------------- -------- ----------------------
CUST_NAME VARCHAR2(10 CHAR)
DISTANCE NUMBER
PAYMENT NUMBER
REGULAR NUMBER
PROFILE_ID NUMBER
There is no C_DPHONE column in CUSTOMER table.
Regards
Michel
|
|
|
|
|
Re: please help with my code PL/SQL Block [message #570500 is a reply to message #570488] |
Sun, 11 November 2012 07:32   |
 |
etoilethay
Messages: 9 Registered: October 2012
|
Junior Member |
|
|
Sorry It was so late and I missed posting the report. I will try the your suggestion but here is the list of errors for my original code. I am using oracle sql developer 3.2.09
Error report:
ORA-06550: line 5, column 65:
PL/SQL: ORA-00904: "C_D_PHONE": invalid identifier
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 2, column 8:
PLS-00341: declaration of cursor 'CURSMAILINGLIST' is incomplete or malformed
ORA-06550: line 6, column 9:
PL/SQL: Item ignored
ORA-06550: line 10, column 28:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 10, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 13, column 22:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 13, column 1:
PL/SQL: Statement ignored
ORA-06550: line 14, column 28:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 14, column 1:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
[Updated on: Sun, 11 November 2012 07:38] Report message to a moderator
|
|
|
Re: please help with my code PL/SQL Block [message #570502 is a reply to message #570500] |
Sun, 11 November 2012 07:39   |
cookiemonster
Messages: 13967 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
etoilethay wrote on Sun, 11 November 2012 13:32
Error report:
ORA-06550: line 5, column 65:
PL/SQL: ORA-00904: "C_D_PHONE": invalid identifier
So there's no column called c_d_phone in customer table either.
Stop guessing what your columns are called and start looking what actual columns are in the table.
|
|
|
|
Re: please help with my code PL/SQL Block [message #570505 is a reply to message #570504] |
Sun, 11 November 2012 08:15   |
 |
etoilethay
Messages: 9 Registered: October 2012
|
Junior Member |
|
|
Umm I just learning how to use sql developer.this is what I am getting through school. I am still trying to figure out my way. I looked at the table and the name of the column is D_phone.
I will look up SQL*Plus to see how it works. I am going to review my notes again to see if I can up with something closer to a correct answer.
Thanks
[Updated on: Sun, 11 November 2012 09:21] Report message to a moderator
|
|
|
|
|
|
|
Re: please help with my code PL/SQL Block [message #570513 is a reply to message #570512] |
Sun, 11 November 2012 12:38   |
 |
Michel Cadot
Messages: 68765 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
This code (which is not formatted) does not even compile.
If you don't know how to format some code, goto SQL Formatter, choose database: Oracle/PLSQL, then output: SQL(Text), copy and paste your code in the text field and click on "Format SQL", you will get the result (when the code will be correct).
Regards
Michel
[Updated on: Sun, 11 November 2012 12:42] Report message to a moderator
|
|
|
|
|
|
|
Re: please help with my code PL/SQL Block [message #570537 is a reply to message #570516] |
Sun, 11 November 2012 23:48  |
 |
Michel Cadot
Messages: 68765 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
And so I was right:
cus_row.d-phone -> cus_row.d_phone
and ";" at the end of dbms_output line.
The codes were different abd the first one you posted does not compile.
With these fixes, the Instant SQL Formatter gives you:
DECLARE
CURSOR cml IS
SELECT first,
last,
address,
d_phone
FROM customers;
cus_row customers%ROWTYPE;
BEGIN
FOR cus_row IN cml LOOP
dbms_output.Put_line(cus_row.first
||' '
||cus_row.last
||' '
||cus_row.address
||' '
||cus_row.d_phone);
END LOOP;
END;
Which is much better than what you posted, isn't it?
Regards
Michel
[Updated on: Sun, 11 November 2012 23:52] Report message to a moderator
|
|
|