HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
storedwithsqloutputprocedurehoworacleparameterexecute

Problem

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 ?

Solution

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))



  1. 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)
  2. 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.