Skip to Main Content
  • Questions
  • Stored Procedures for insert statements

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Geraldo.

Asked: January 22, 2019 - 9:35 pm UTC

Last updated: September 24, 2020 - 8:01 am UTC

Version: 18.3.0

Viewed 10K+ times! This question is

You Asked

Hello, Ask Tom Team.

I have like 20 tables using identity column for pk. Right now, I'm using returning clause to retrieve
the pk (identity column using Oracle 12c feature) to be used as fk in the child tables. I have been asked to use stored procedures to populate tables (insert statements). I have some questions about this:


1. Would a function work better?
2. Is it a good practice to use stored procedures to insert values in the tables? Any advantages? Performance? ...
3. If so, how can I return the value of the identity column to be used as fk in the child tables?
4. Do I have to create a stored procedure to populate each table?

Thanks in advanced.
Regards,

and Chris said...

1. Better than what?

2. Yes! The SmartDB architecture recommends this in fact. This has numerous performance and security advantages.

If you want to know more about this, Steven Feuerstein has put together a post collating all the key articles on this topic:

http://stevenfeuersteinonplsql.blogspot.com/2018/05/the-smartdb-resource-center.html

3. In the returning clause? Not sure what the issue is here.

4. No. I'd recommend you create a transaction API. This will have one procedure that does all the DML needed to complete the transaction.

For example, adding master-detail data such as an order and its lines might look something like:

create or replace procedure create_order ( 
  parameters ... 
) as
  order_id orders.order_id%type;
begin
  
  insert into orders ( order_id, ... ) 
  values ( default, ... )
  returning order_id into order_id;
  
  forall prod in 1 .. products.count
    insert into order_items ( order_id, product_id, ... )
    values ( order_id, products ( prod ), ... );
  
end create_order;
/


Now, you may put this procedure in a order_api package. And split the inserts into separate (private) procedures within the package to make your code more manageable.

But the point is to think top-down in terms of transactions. Which tables does this affect and in which way?

Not bottom up, how do we create code for each table.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Review

A reader, January 24, 2019 - 1:20 am UTC

So, basically:

1. create a package with many procedures as needed, where each procedure represents a insert for a specific table?

Thanks for the help!


Chris Saxon
January 24, 2019 - 10:20 am UTC

create a package with many procedures as needed

Yes

where each procedure represents a insert for a specific table?

Not necessarily. To make procedures easier to read, test, debug, etc. they should be "small". Ideally the logic should all fit on a single screen.

So when inserting into tables with many columns, the insert statement could fill a screen.

You can get around this by inserting a PL/SQL record. For example:

create table t (
  c1 int 
    generated by default on null 
    as identity, 
  c2 int
);

declare
  rec t%rowtype;
  id  t.c1%type;
begin
  rec.c2 := 2;
  insert into t values rec
  returning c1 into id;
end;
/

select * from t;

C1   C2   
   1    2 


This makes it more feasible to fit the logic in a few lines of code. And thus insert into more than one table in a procedure.

Or maybe you're receiving the data as a JSON document or object type. Which has values for parent and child tables in one parameter.

In which case you may be able to use a multitable insert and load everything in one statement. e.g.:

insert all
  when rn = 1 then 
    into orders ...
  when 1 = 1 then 
    into order_items ...
  select ... from json_table ( 
    jdoc, ...
  )


Note you'll need to get the PK sequence value before the insert for this to work.

Can the insert procedure be autogenerated with some tool like SQLDeveloper or code.

Amit, September 24, 2020 - 12:44 am UTC

Is it possible to generate the Procedure to insert data into a table automatically. Ofcourse I will add the body,but the signature, can it be autogenerated using some tool

So for ex, can this be autogenerated, given a table order.

create or replace procedure create_order (
parameters ...
) as
order_id orders.order_id%type;
begin
end create_order;
Chris Saxon
September 24, 2020 - 8:01 am UTC

In SQL Developer:

Open your connection
Go to tables -> table name -> Table -> Generate Table API

https://www.thatjeffsmith.com/archive/2013/05/an-oracle-designer-feature-generating-table-apis-with-oracle-sql-developer/

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library