snippetMinor
How to execute stored procedure with output parameter in Oracle PL/SQL?
Viewed 0 times
storedwithsqloutputprocedurehoworacleparameterexecute
Problem
This is the table I have created:
This is the sequence I have created:
Then I tried creating a simple stored procedure with output parameter:
But I'm getting a compilation error.
Where can be the mistake possibly be ?
CREATE TABLE Toy
(Toy_No NUMBER PRIMARY KEY,
Toy_Name VARCHAR(30) NOT NULL
);This is the sequence I have created:
CREATE SEQUENCE toy_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;Then I tried creating a simple stored procedure with output parameter:
CREATE OR REPLACE PROCEDURE insert_toys(toy_id OUT NUMBER,toy_name VARCHAR(30))
AS
BEGIN
toy_id := seq_toy.NEXTVAL;
INSERT INTO Toy (Toy_No,Toy_Name)
VALUES (toy_id,toy_name);
END;But I'm getting a compilation error.
Where can be the mistake possibly be ?
Solution
There are several mistakes.
The procedure takes 2 parameters, and should be called like that.
- When specifying parameters of procedure, you don't need to specify size. E.g. it should be
CREATE OR REPLACE PROCEDURE insert_toys(toy_id OUT NUMBER,toy_name VARCHAR ), not.... VARCHAR(30))
- You created sequence
CREATE SEQUENCE toy_seq, but trying to use sequence with different nametoy_id := seq_toy.NEXTVAL;(toy_seq vs seq_toy) - Parameter name is the same as field name (Toy_Name). Even though it's not a compilation error, qualifying variables is always much better compared to relying on resolution rules :
INSERT INTO Toy (Toy_No,Toy_Name)
VALUES (insert_toys.toy_id,insert_toys.toy_name);The procedure takes 2 parameters, and should be called like that.
set serveroutput on;
declare new_id NUMBER;
BEGIN
insert_toys(new_id,'name2');
dbms_output.put_line(new_id); --print value of new id
END;Code Snippets
INSERT INTO Toy (Toy_No,Toy_Name)
VALUES (insert_toys.toy_id,insert_toys.toy_name);set serveroutput on;
declare new_id NUMBER;
BEGIN
insert_toys(new_id,'name2');
dbms_output.put_line(new_id); --print value of new id
END;Context
StackExchange Database Administrators Q#183377, answer score: 4
Revisions (0)
No revisions yet.