Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Dynamic SQL in a function
Although it is possible to do what you are trying to do, a better
solution is to convert 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> 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;
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 - 15:11:24 CDT