Dynamic SQL in a function

From: Edwinah63 <edwinah_at_customercare.com.au>
Date: 18 Sep 2005 23:06:00 -0700
Message-ID: <1127109960.285040.49610@g47g2000cwa.googlegroups.com>

i am trying to determine no of chars in a long field.

googled around, adapted some code, put it in a function and it works beautifully:

function MYLEN (idx varchar2) return number as

    long_var LONG;

BEGIN     SELECT n.notes INTO long_var
    FROM myTable n
    WHERE n.rowid = idx;

    return length(long_var);

end;

select myLen(rowid) from MyTable;

---

want to change this select statement to be dynamic so i can change the field name and table name on the fly:

(idx varchar2, tbl varchar2, fld varchar2) return number as

    long_var LONG;
    str long;
    v varchar2(50);

 BEGIN
  /* dynamic sql */
  v := '''' || idx || '''';
  str := 'SELECT n.' || fld || ' INTO long_var FROM ttadm.' || tbl || ' n WHERE n.rowid =' || v;

   execute immediate str; --LINE 16
   return length(long_var);

end;

when used in my query:

select myLen(rowid, 'MyTable','MyLongFld') from MyTable;

all i get is:

ERROR at line 1:
ORA-00933: SQL command not properly ended ORA-06512: at "myLen", line 16

what am i doing wrong?

regards

Edwinah63 Received on Mon Sep 19 2005 - 01:06:00 CDT