Skip to Main Content
  • Questions

  • Different between dml statement execution through script and packages

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, mgrkaruna.

Asked: March 11, 2016 - 9:15 am UTC

Last updated: March 18, 2016 - 2:02 am UTC

Version: 11g

Viewed 1000+ times

You Asked

when we need to execute some dml statements, we can execute them in two different ways

1. By placing dml statements into one .sql file and we can call the script.
2. And other way we can place all the statements into one package procedure and then we can call that package procedure.

Now my question is what is difference between these two approaches and also tell me which approach is good with respect to performance

Thanks in advance





and Connor said...

If it is a one-off task (eg installation of some reference data) then either is perfectly fine.

If it is for task that will be repeated, eg, a sequence of steps to (say) create new employee details, then I'd advise using a packaged procedure.

Check out this link

https://blogs.oracle.com/plsql-and-ebr/entry/why_use_pl_sql

Rating

  (3 ratings)

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

Comments

A reader, March 16, 2016 - 11:39 am UTC

Hi ,

Thanks for your reply . I hope my question was not answered fully . Here is complete details of question .

Actually i am working in a company as a oracle developer. we have a product where we use bunch of sql scripts to install the product. I am planning to convert them into database packages (for Modularity) . But i heard that sql scripts are more faster in terms of execution compare with package execution . Can you please suggest me which one is good approach whe we compare performance ?



Connor McDonald
March 16, 2016 - 11:46 am UTC

"we have a product where we use bunch of sql scripts to install the product"

That sounds like a one-off process to me ?

If you've got hundreds or thousands of scripts, then perhaps some re-organisation into packages etc might be beneficial from an organisation point of view.

But performance wise you'll see little difference.

Bootstrapping?

Duke Ganote, March 17, 2016 - 4:36 pm UTC

Is this some sort of 'bootstrap' installation? Somehow the packages have to be created, probably from a SQL script initially.

I'm not seeing a lot of difference, other than ease-of-reinstallation if it's all "in the database" anyways.

With the proper caveats, of course...
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9526903800346258884#9527143800346661228


Also an opportunity to use the spiffy SQL*PLUS error logging procure

Duke Ganote, March 17, 2016 - 8:07 pm UTC

If you're using scripts to bootstrap the packages, or load the database.

Spiffy stuff.
https://blogs.oracle.com/oracle-database-app-admin/entry/sql_plus_error_logging_new

I was just using it to load in comments for tables & columns.
Connor McDonald
March 18, 2016 - 2:02 am UTC

Good input.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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