3

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 ?

2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.