Stored Procedures (HELP NEEDED) [message #476173] |
Tue, 21 September 2010 21:41 |
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 #476183 is a reply to message #476173] |
Tue, 21 September 2010 23:49 |
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 #476292 is a reply to message #476227] |
Wed, 22 September 2010 09:20 |
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 #476296 is a reply to message #476292] |
Wed, 22 September 2010 09:33 |
CajunVarst
Messages: 55 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 |
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 |
|
Michel Cadot
Messages: 68684 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Michel Cadot wrote on Wed, 22 September 2010 16:27Use 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
|
|
|
|
|
|
|
|