Read SQL procedure from script file, Oracle

Question

How can I read a stored procedure from a .sql file? Example of this file's content:

create or replace procedure insert(p_t varchar(20), p_e varchar(10), out p_id number)
as begin
insert into instrument(type, status) values (p_t, p_e);
commit;
select max(id) from instrument into p_id;
end /

...other procedures...

I want this procedure to be created when I use the command @"filepath", but instead of doing so, Oracle executes the content of the procedure.

I would like to create all my procedures reading the file I made, any ideas?.

Are you using SQL*Plus or some other tool?

Patrick Marchand
– Patrick Marchand

2013-08-21 18:05:24 +00:00
Commented Aug 21, 2013 at 18:05 — Patrick Marchand
– Patrick Marchand, Commented Aug 21, 2013 at 18:05

Ben · Accepted Answer · 2013-08-27 07:27:56Z

2

Firstly, please don't call your procedure INSERT. I'd be horrified if this actually compiled.

Secondly, you seem to be missing a semi-colon after end; and you need to put the / on a new line:

create or replace procedure insert_instrument (
   p_t varchar(20), p_e varchar(10), out p_id number ) as 

begin
  insert into instrument(type, status) values (p_t, p_e);
  commit;
  select max(id) from instrument into p_id;
end;
 /

You could simplify this using the RETURNING INTO clause; it saves an extra SELECT and so will operate quicker:

create or replace procedure insert_instrument (
   p_t varchar(20), p_e varchar(10), out p_id number ) as 

begin
  insert into instrument(type, status) 
  values (p_t, p_e)
  returning id into p_id;
  commit;
end;
 /

edited Aug 27, 2013 at 7:27

answered Aug 21, 2013 at 18:06

Ben

53.1k36 gold badges133 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Memillopeloz Over a year ago

Thank you very much, the procedure name was just a typo:D I had already fixed it but your "returning into" suggestion is very much appreciated. Would like to clarify if you meant to use 'returning id into p_id'

Collectives™ on Stack Overflow

Read SQL procedure from script file, Oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related