How to execute stored procedure with output parameter in Oracle PL/SQL?

Question

This is the table I have created:

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 ?

Answer 1

There are several mistakes.
1. 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))
2. You created sequence CREATE SEQUENCE toy_seq, but trying to use sequence with different name toy_id := seq_toy.NEXTVAL; (toy_seq vs seq_toy) 3. 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;