Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL
PL/SQL [message #391291] Wed, 11 March 2009 11:51 Go to next message
jordz1986
Messages: 6
Registered: March 2009
Junior Member
Hello
Im new to PL/SQL and am having a few problems, writing a procedure for my library database. The procedure should take a book title parameter and then use that name in the query to bring back the publisher name and address of that book.

Here's the code.


DECLARE
name varchar2(20);
address varchar2(20);
book varchar2(15);
PROCEDURE get_publisher(
book IN varchar2)
is
BEGIN
select pubname into name
from publisher p, booktitle bt
where bt.pubid in
(select p1.pubid
from booktitle bt1, publisher p1
where bt1.pubid=p1.pubid
and bt1.btname=book);
select pubaddress into address
from publisher p, booktitle bt
where bt.pubid in
(select p1.pubid
from booktitle bt1, publisher p1
where bt1.pubid=p1.pubid
and bt1.btname=book);
END;
begin
dbms_output.put_line('Publishers Name: '|| name);
dbms_output.put_line('Publishers Address: '|| address);
end;
/

After compiling with no errors, and entering "exec get_publisher('Northern Lights') i get the following error:

BEGIN get_publisher; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object JBEARD.GET_PUBLISHER is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I would be very grateful for any ideas.. probably just being stupid.

Thanks

Jordz
Re: PL/SQL [message #391293 is a reply to message #391291] Wed, 11 March 2009 11:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

2/
always post your Oracle version (4 decimals).

3/
Use SQL*Plus and copy and paste your session.

4/
What you posted is NOT a procedure and so NOT the actual code

5/
"exec get_publisher('Northern Lights')"
"BEGIN get_publisher; END;"
You didn't execute what you say you did.

6/
Conclusion: We can't help

Regards
Michel
Re: PL/SQL [message #391297 is a reply to message #391291] Wed, 11 March 2009 12:12 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I believe that it is actually possible to be some help to the OP, even without that information.

BEGIN get_publisher; END;
 
 *
 ERROR at line 1:
 ORA-06550: line 1, column 7:
 PLS-00905: object JBEARD.GET_PUBLISHER is invalid
 ORA-06550: line 1, column 7:
 PL/SQL: Statement ignored


If you look at the view USER_ERRORS (assuming that you are the user JBEARD) that will show you the problems with the procedure GET_PUBLISHER.

Assuming that the code you posted is something to do with the GET_PUBLISHER procedure, the first thing that I notice is that you are not doing a SELECT INTO - in a pl/sql block, a SELECT statement needs to have an INTO clause immediately after the list of columns to be selected, listing the pl/sql variables to fetch the parameters into.

Don't worry about the posts telling you to post DDL/DML, or insisting you read the Forum Guide - there's a depressing trend round here these days towards criticising questions rather than answering them.
It'd be nice if you did read the Sticky post at the top of the forum, but rest assured that some of us will try to answer your questions anyway.
Previous Topic: How to get NLS_LANG from other session (merged)
Next Topic: Getting Data From Weak Ref Cursors
Goto Forum:
  


Current Time: Sat Dec 03 13:46:58 CST 2016

Total time taken to generate the page: 0.07945 seconds