Re: PL/SQL:Efficiency of inline SQL vs use of Dynamic SQL Package

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 14 Sep 1998 15:37:57 GMT
Message-ID: <360037f2.11940960@192.86.155.100>

A copy of this was sent to ross_mcadam_at_my-dejanews.com (if that email address didn't require changing) On Mon, 14 Sep 1998 09:11:43 GMT, you wrote:

>I am writing an application in PL/SQL which involves many SQL insert
>statements where I require a count of the number of records for each insert.
>

have a look at sql%rowcount. It'll tell you as well. Consider:

SQL> begin

  2     insert into foo select * from all_users where username like '%AA%';
  3     dbms_output.put_line( sql%rowcount || ' rows inserted...' );

  4 end;
  5 /
20 rows inserted...

PL/SQL procedure successfully completed.

Use sql%rowcount after any insert/update/delete to find the rows modified (and use it before the next sql statement or you'll lose that value)

>My query is whether 1. It is more efficient to perform SQL inline and use
>select count(*) to return the number of rows;, or, 2. Use the dynamic SQL
>package which also returns the number of rows inserted.
>
>Thanks in advance, RM.
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Sep 14 1998 - 10:37:57 CDT