CODEWITHSUNDEEP
oracle-databasestored-procedures
Memillopeloz
Memillopeloz

Reputation: 58

Read SQL procedure from script file, Oracle

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?.

Upvotes: 0

Views: 139

Answers (1)

Ben
Ben

Reputation: 52913

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;
 /

Upvotes: 2

Related Questions