Re: Dynamic SQL in a function

From: Billy <vslabs_at_onwe.co.za>
Date: 19 Sep 2005 00:17:37 -0700
Message-ID: <1127114257.157239.103080@g47g2000cwa.googlegroups.com>

Edwinah63 wrote:

> what am i doing wrong?

Besides the syntax error, not using bind variables. ALWAYS USE BIND VARIABLES. Even when dealing with dynamic SQL. (There are a very few exceptions where "always use bind variables" is not true - but very few developers ever run into these.)

If you do not use bind variables, then your function will generate a unique SQL for every single long row processed (as the rowid will be unique literal in the SQL statement). A 1000 rows? A 1000 unique SQL statements in the shared pool. Very Bad (tm).

Here's sample code to show how you should be dealing with this: ==
SQL> create or replace function LengthLong( cTableName varchar2, cColumnName varchar2, cRowID varchar2 ) return number is

  2          sql$    varchar2(4000);
  3          l       long;
  4  begin
  5          sql$ := 'select '||cColumnName||' from '||cTableName||'

where rowid = :0';
  6
  7 execute immediate sql$ into l using cRowID;   8

  9          return(
 10                  length(l)
 11          );

 12 end;
 13 /

Function created.

SQL> select

  2          obj#,
  3          LengthLong( 'sys.tabpart$', 'HIBOUNDVAL', rowid ) as
"LENGHT",
  4          HIBOUNDVAL
  5  from       sys.tabpart$
  6  where      rownum < 11

  7 /

      OBJ# LENGHT HIBOUNDVAL
---------- ---------- ----------

      2945          1 0
      4313          3 100
      4315          3 100
      4317          3 100
      4319          3 100
      4321          3 100
      4325          3 100
      4329          3 100
      4333          3 100
      4335          3 100

10 rows selected.

SQL>
==

I've used a sys table to demonstrate the function - we do not use longs (LOBs are far more superior). I reach for my lead pipe when developers try to use longs in their tables on my databases. :-)

--
Billy

Received on Mon Sep 19 2005 - 02:17:37 CDT