Re: Dynamic SQL in a function

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 19 Sep 2005 14:43:09 -0700
Message-ID: <1127166189.693705.219880@g47g2000cwa.googlegroups.com>

I attempted to post a response to this earlier, but it has been quite a while and it is not being displayed, so I am trying again. If this ends up being a duplicate, then I will delete it, if possible.

Although it is possile to do what you are trying to do, a better solution is to change your long columns to clob columns, so that you can just use the built-in length function. I have demonstrated both below.

scott_at_ORA92> CREATE TABLE mytable (notes LONG)   2 /

Table created.

scott_at_ORA92> INSERT INTO mytable (notes) VALUES ('This is a test.')   2 /

1 row created.

scott_at_ORA92> INSERT INTO mytable (notes) VALUES ('This is another test.')
  2 /

1 row created.

scott_at_ORA92> SELECT LENGTH (notes)
  2 FROM mytable
  3 /
SELECT LENGTH (notes)

               *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

scott_at_ORA92> CREATE OR REPLACE FUNCTION mylen   2 (p_rowid IN ROWID,

  3  	p_tab	IN VARCHAR2,
  4  	p_col	IN VARCHAR2)
  5    RETURN	   NUMBER
  6  AS
  7    v_long	   LONG;

  8 BEGIN
  9 EXECUTE IMMEDIATE
 10 'SELECT ' || p_col
 11 || ' FROM ' || p_tab
 12 || ' WHERE ROWID=:b_rowid'
 13 INTO v_long
 14 USING p_rowid;
 15 RETURN LENGTH (v_long);
 16 END mylen;
 17 /

Function created.

scott_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> SELECT mylen (ROWID, 'mytable', 'notes')   2 FROM mytable
  3 /

MYLEN(ROWID,'MYTABLE','NOTES')

---

                            15
                            21

scott_at_ORA92> ALTER TABLE mytable MODIFY (notes CLOB)   2 /

Table altered.

scott_at_ORA92> DESCRIBE mytable

 Name                                      Null?    Type
 ----------------------------------------- --------


----------------------------

 NOTES                                              CLOB

scott_at_ORA92> SELECT LENGTH (notes)
  2 FROM mytable
  3 /

LENGTH(NOTES)

---

           15
           21

scott_at_ORA92> Received on Mon Sep 19 2005 - 16:43:09 CDT