Home » SQL & PL/SQL » Client Tools » Stored Procedures (HELP NEEDED) (Oracle database 10g Express)
Stored Procedures (HELP NEEDED) [message #476173] Tue, 21 September 2010 21:41 Go to next message
jubbub
Messages: 4
Registered: September 2010
Location: Alabama
Junior Member
Hello! I'm trying hard to learn SQL and especially stored procedures! What my problem is that I can't get the sequence correct. My textbook has been very vaque on the use of stored procedures to DELETEan item.

Here's my example : Write a stored procedure to delete and invoice given the invoice number as a parameter. Name the procedure prc_inv_delete.

The Invoice TABLE RESEMBLES this:

INV_NUM | CUST_NUM | INV_DATE | INV_AMOUNT
8000 1000 23-MAR-08 235.89
8001 . . .

This is the sequence I tried:
CREATE OR REPLACE PROCEDURE PRC_INV_DELETE
(INV_NUM IN NUMBER)
AS BEGIN
DELETE FROM INVOICE
VALUES (INV_NUM);
END;
/

EXEC PRC_INV_DELETE (8002);

I kept receiving an error message that read
incorrect/MISSING syntax on line 4 DELETE FROM INVOICE

Am I even in the right ballpark here?

Thanks.
Re: Stored Procedures (HELP NEEDED) [message #476179 is a reply to message #476173] Tue, 21 September 2010 21:59 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
>I kept receiving an error message that read
>incorrect/MISSING syntax on line 4 DELETE FROM INVOICE

You have tables & we don't
You have data & we don't.
You have code & we don't
If you can not debug situation, why do you expect us to be able to do so while lacking in *EVERY* detail you have?
Re: Stored Procedures (HELP NEEDED) [message #476183 is a reply to message #476173] Tue, 21 September 2010 23:49 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
jubbub wrote on Tue, 21 September 2010 21:43

DELETE FROM INVOICE
VALUES (INV_NUM);

/


This is wrong!
Should be
DELETE FROM INVOICE 
       WHERE INV_NUM=P_INV_NUM;


also you need to use exception handler in your pl/sql code.

Do NOT give the same name to parameter as that of that column.
If you do so Oracle will consider it as a column name first and
you are going to loose all the data.

Regards
Ved

[Updated on: Tue, 21 September 2010 23:54]

Report message to a moderator

Re: Stored Procedures (HELP NEEDED) [message #476189 is a reply to message #476183] Wed, 22 September 2010 00:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is very strange you post a question because you need help, usually people post here because they need nothin and surly not help.

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, 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.

Quote:
Write a stored procedure to delete and invoice given the invoice number as a parameter.

Database SQL Reference

PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Re: Stored Procedures (HELP NEEDED) [message #476223 is a reply to message #476183] Wed, 22 September 2010 03:10 Go to previous messageGo to next message
cookiemonster
Messages: 11070
Registered: September 2008
Location: Rainy Manchester
Senior Member
Its_me_ved wrote on Wed, 22 September 2010 05:49

also you need to use exception handler in your pl/sql code.

Why?
Re: Stored Procedures (HELP NEEDED) [message #476225 is a reply to message #476183] Wed, 22 September 2010 03:18 Go to previous messageGo to next message
Maaher
Messages: 7054
Registered: December 2001
Senior Member
I'm with cookiemonster: an exception handler is not always needed.

MHE
Re: Stored Procedures (HELP NEEDED) [message #476227 is a reply to message #476225] Wed, 22 September 2010 03:25 Go to previous messageGo to next message
cookiemonster
Messages: 11070
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd go further and say an exception handler isn't usually needed.
Re: Stored Procedures (HELP NEEDED) [message #476292 is a reply to message #476227] Wed, 22 September 2010 09:20 Go to previous messageGo to next message
jubbub
Messages: 4
Registered: September 2010
Location: Alabama
Junior Member
Thanks for the help. I used Ved's suggestion, and it removed my error message and allowed for the procedure to create:

CREATE OR REPLACE PROCEDURE PRC_INV_DELETE (L_INV_NUM IN NUMBER)
AS
BEGIN
DELETE FROM INVOICE
WHERE INV_NUM = L_INV_NUM;

END;
/

Now when I perform the Execute:

EXEC PRC_INV_DELETE(xx#xx);

I get the following error message:

ORA-00900: invalid SQL statement

Where did I take a wrong turn?

Dennis
Re: Stored Procedures (HELP NEEDED) [message #476293 is a reply to message #476292] Wed, 22 September 2010 09:25 Go to previous messageGo to next message
cookiemonster
Messages: 11070
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) Can you read and follow the orafaq forum guide as Michel already asked you.
2) What's this supposed to be:
(xx#xx);

3) In what tool are you running the exec command?
Re: Stored Procedures (HELP NEEDED) [message #476294 is a reply to message #476293] Wed, 22 September 2010 09:27 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session, the WHOLE session.
And post it FORMATTED.

Regards
Michel
Re: Stored Procedures (HELP NEEDED) [message #476296 is a reply to message #476292] Wed, 22 September 2010 09:33 Go to previous messageGo to next message
CajunVarst
Messages: 54
Registered: April 2010
Location: Washington, D.C.
Member
Please provide the following from you sql plus session:
-create table statement
-insert statements
-create procedure statement
-select * from table
-exec procedure statement
-select * from table again

PASTE this from your sql session so that we can see what you are doing.
Re: Stored Procedures (HELP NEEDED) [message #476310 is a reply to message #476296] Wed, 22 September 2010 10:01 Go to previous messageGo to next message
jubbub
Messages: 4
Registered: September 2010
Location: Alabama
Junior Member
Here it is with the table

CREATE TABLE "INVOICE"
( "INV_NUM" NUMBER NOT NULL ENABLE,
"CUST_NUM" NUMBER NOT NULL ENABLE,
"INV_DATE" DATE NOT NULL ENABLE,
"INV_AMOUNT" NUMBER NOT NULL ENABLE,
CONSTRAINT "INVOICE_PK" PRIMARY KEY ("INV_NUM") ENABLE,
CONSTRAINT "INVOICE_FK" FOREIGN KEY ("CUST_NUM")
REFERENCES "CUSTOMER" ("CUST_NUM") ENABLE
)
/

SELECT * FROM INVOICE;


INV_NUM CUST_NUM INV_DATE INV_AMOUNT
6 1 23-APR-08 619.44
3 2 23-MAR-08 312.82
2 1 23-MAR-08 235.89
4 2 30-MAR-08 528.1
5 1 12-APR-08 197.78


CREATE OR REPLACE PROCEDURE PRC_INV_DELETE (L_INV_NUM IN NUMBER)
AS
BEGIN
DELETE FROM INVOICE
WHERE INV_NUM = L_INV_NUM;

END;
/

Procedure created.

EXEC PRC_INV_DELETE(4);


ORA-00900: invalid SQL statement

That's all I know! I'm a novice at this and this is really stumping me. I'm sorry If I'm not following the exact forum protocol (Michel and cookiemonster) I don't know how to paste this from my SQL Expression, as when I do, you see the above.

Dennis

Dennis
Re: Stored Procedures (HELP NEEDED) [message #476313 is a reply to message #476310] Wed, 22 September 2010 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 22 September 2010 16:27
Use SQL*Plus and copy and paste your session, the WHOLE session.
And post it FORMATTED.


Regards
Michel

SQL> CREATE TABLE "INVOICE" 
  2  ( "INV_NUM" NUMBER NOT NULL ENABLE,
  3  "CUST_NUM" NUMBER NOT NULL ENABLE, 
  4  "INV_DATE" DATE NOT NULL ENABLE, 
  5  "INV_AMOUNT" NUMBER NOT NULL ENABLE
  6  )
  7  /

Table created.

SQL> CREATE OR REPLACE PROCEDURE PRC_INV_DELETE (L_INV_NUM IN NUMBER)
  2  AS
  3  BEGIN
  4  DELETE FROM INVOICE
  5  WHERE INV_NUM = L_INV_NUM;
  6  
  7  END;
  8  /

Procedure created.

SQL> EXEC PRC_INV_DELETE(4);

PL/SQL procedure successfully completed.

It works!

Quote:
3) In what tool are you running the exec command?


Regards
Michel

[Updated on: Wed, 22 September 2010 10:10]

Report message to a moderator

Re: Stored Procedures (HELP NEEDED) [message #476350 is a reply to message #476313] Wed, 22 September 2010 13:28 Go to previous messageGo to next message
Littlefoot
Messages: 19687
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
[I'm 100% sure that Michel would simply LOVE IT if it was TOAD]
Re: Stored Procedures (HELP NEEDED) [message #476352 is a reply to message #476350] Wed, 22 September 2010 13:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Laughing
Re: Stored Procedures (HELP NEEDED) [message #476380 is a reply to message #476310] Thu, 23 September 2010 00:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7995
Registered: November 2002
Location: California, USA
Senior Member
You are using the Express Edition and running it from the SQL Expression box, which only accepts SQL and PL/SQL, not SQL*Plus. EXEC is SQL*Plus, so using:

EXEC PRC_INV_DELETE(4);

produces an error that it is not a SQL expression. So, you need to use:

BEGIN PRC_INV_DELETE(4); END;

instead.
Re: Stored Procedures (HELP NEEDED) [message #476384 is a reply to message #476380] Thu, 23 September 2010 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or maybe
CALL PRC_INV_DELETE(4);

Regards
Michel
Re: Stored Procedures (HELP NEEDED) [message #476764 is a reply to message #476380] Sat, 25 September 2010 15:30 Go to previous message
jubbub
Messages: 4
Registered: September 2010
Location: Alabama
Junior Member
Thanks so much for the help.

BEGIN PRC_INV_DELETE(4); END;

The above syntax worked well on 10g express!

All of your help was greatly appreciated.

Dennis
Previous Topic: how can isqlplus available for windows client?
Next Topic: Serveroutput
Goto Forum:
  


Current Time: Tue Oct 21 20:03:36 CDT 2014

Total time taken to generate the page: 0.11583 seconds