Home » SQL & PL/SQL » SQL & PL/SQL » please help with my code PL/SQL Block (Oracle SQL developer)
please help with my code PL/SQL Block [message #570483] Sat, 10 November 2012 23:32 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
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 #570486 is a reply to message #570485] Sun, 11 November 2012 00:43 Go to previous messageGo to next message
etoilethay
Messages: 9
Registered: October 2012
Junior Member
Thanks for the reply. Sorry about the posting not being well formatted
I changed the c_dphone to c_d_phone and yet I still have errors. Any suggestions?
.
Re: please help with my code PL/SQL Block [message #570488 is a reply to message #570486] Sun, 11 November 2012 01:33 Go to previous messageGo to next message
Littlefoot
Messages: 19542
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Plenty of them.
  • Post description of a table involved in your code.
  • Saying that you "still have errors", how are we supposed to know which ones if you don't specify them?
  • Why do you FETCH twice?
  • Why didn't you use a cursor FOR loop? It is much simpler. Your way: you need to declare a cursor and a cursor variable. You need to open a cursor. You need to enter a loop, fetch into a cursor and worry about exiting the loop. Finally, you need to close a cursor. Please, compare your code with mine - I used it correctly, you didn't (that's why you have 2 FETCHes.
    Another way: in a cursor FOR loop, you just need one step - select.

    See the difference:
    SQL> declare
      2    cursor c1 is select dname from dept;
      3    c1r c1%rowtype;
      4  begin
      5    open c1;
      6    loop
      7      fetch c1 into c1r;
      8      exit when c1%notfound;
      9      dbms_output.put_line(c1r.dname);
     10    end loop;
     11    close c1;
     12  end;
     13  /
    ACCOUNTING
    RESEARCH
    SALES
    OPERATIONS
    
    PL/SQL procedure successfully completed.
    
    SQL> begin
      2    for c1r in (select dname from dept) loop
      3      dbms_output.put_line(c1r.dname);
      4    end loop;
      5  end;
      6  /
    ACCOUNTING
    RESEARCH
    SALES
    OPERATIONS
    
    PL/SQL procedure successfully completed.
    
    SQL>
Re: please help with my code PL/SQL Block [message #570500 is a reply to message #570488] Sun, 11 November 2012 07:32 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 10930
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 #570504 is a reply to message #570500] Sun, 11 November 2012 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am using oracle sql developer 3.2.09


And if you want to correctly post (and so get answer), stop using SQL Developer and use SQL*Plus. You will use SQL Developer when you no more need help from forums.

Regards
Michel

[Updated on: Sun, 11 November 2012 08:05]

Report message to a moderator

Re: please help with my code PL/SQL Block [message #570505 is a reply to message #570504] Sun, 11 November 2012 08:15 Go to previous messageGo to next message
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 #570506 is a reply to message #570505] Sun, 11 November 2012 08:30 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2011
Registered: January 2010
Senior Member
You can use same DESC CUSTOMER in SQL*Developer to get list of table CUSTOMER columns.

SY.
Re: please help with my code PL/SQL Block [message #570510 is a reply to message #570488] Sun, 11 November 2012 11:18 Go to previous messageGo to next message
etoilethay
Messages: 9
Registered: October 2012
Junior Member
Thanks littlefoot and others. I got my code right this time
Smile
Re: please help with my code PL/SQL Block [message #570511 is a reply to message #570510] Sun, 11 November 2012 11:22 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about posting it to help futur readers (with your table description)?

Regards
Michel
Re: please help with my code PL/SQL Block [message #570512 is a reply to message #570511] Sun, 11 November 2012 12:36 Go to previous messageGo to next message
etoilethay
Messages: 9
Registered: October 2012
Junior Member
Here we go:
Set serveroutput on;
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;
/

[Updated on: Sun, 11 November 2012 12:37]

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 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
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 #570514 is a reply to message #570513] Sun, 11 November 2012 13:21 Go to previous messageGo to next message
etoilethay
Messages: 9
Registered: October 2012
Junior Member
Yes the link youprocvided is giving error, but on the SQL developer it was fine. It did compile and I got a result! here is a screenshot
here/

[Updated on: Sun, 11 November 2012 13:35]

Report message to a moderator

Re: please help with my code PL/SQL Block [message #570515 is a reply to message #570514] Sun, 11 November 2012 13:43 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't see your image (this is why we ask you to use SQL*Plus) but I bet this is not the same code.
And the link I posted works.

Regards
Michel
Re: please help with my code PL/SQL Block [message #570516 is a reply to message #570515] Sun, 11 November 2012 13:55 Go to previous messageGo to next message
etoilethay
Messages: 9
Registered: October 2012
Junior Member
It is the same code I posted!
The link you posted works but It did give me an error yet I had a result and the screen shot shows it

I uploaded file Just for you :d
./fa/10503/0/


[mod-edit: imaged inserted into message body by bb]
  • Attachment: screen.jpg
    (Size: 80.53KB, Downloaded 705 times)

[Updated on: Sun, 11 November 2012 15:28] by Moderator

Report message to a moderator

Re: please help with my code PL/SQL Block [message #570517 is a reply to message #570516] Sun, 11 November 2012 14:33 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It is the same code I posted!


No it is not.
Your code does not compile.

Regards
Michel
Re: please help with my code PL/SQL Block [message #570537 is a reply to message #570516] Sun, 11 November 2012 23:48 Go to previous message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
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

Previous Topic: pl/sql with amount due
Next Topic: replace with the first occurence in the string
Goto Forum:
  


Current Time: Wed Sep 03 02:36:32 CDT 2014

Total time taken to generate the page: 0.10397 seconds