A reader, June 22, 2001 - 6:10 pm UTC
can a clob variable be used in 'EXECUTE IMMEDIATE statement?
mins, January 30, 2002 - 2:43 pm UTC
Can a clob variable be used in the dynamic sql(because the
sql statment is greater than 40000 characters)
January 30, 2002 - 4:25 pm UTC
No (a sql statement that is 40,000 characters -- I would be seriously rethinking that one).
You'll need to use dbms_sql. there is a parse routine that will do this for you:
procedure parse(c in integer, statement in varchar2s,
lb in integer, ub in integer,
lfflg in boolean, language_flag in integer);
-- Parse the given statement in the given cursor. The statement is not in
-- one piece but resides in little pieces in the PL/SQL table "statement".
-- Conceptually what happens is that the SQL string is put together as
-- follows:
-- String := statement(lb) || statement(lb + 1) || ... || statement(ub);
-- Then a regular parse follows.
-- If "lfflg" is TRUE then a newline is inserted after each piece.
-- For further information and for documentation on the rest of the
-- arguments see the regular parse procedure below.
the varchar2s ...
mins, January 31, 2002 - 4:45 pm UTC
where could I find an example of this ...
how to pass the the sql to the statement??
procedure parse(c in integer, statement in varchar2s,
lb in integer, ub in integer,
lfflg in boolean, language_flag in integer);
Could you point me to a site where I could look at an exmaple.
Thanks
January 31, 2002 - 6:24 pm UTC
declare
l_stmt dbms_sql.varchar2s;
l_cursor integer default dbms_sql.open_cursor;
l_rows number default 0;
begin
l_stmt(1) := 'insert';
l_stmt(2) := 'into foo';
l_stmt(3) := 'values';
l_stmt(4) := '( 1 )';
dbms_sql.parse( c => l_cursor,
statement => l_stmt,
lb => l_stmt.first,
ub => l_stmt.last,
lfflg => TRUE,
language_flag => dbms_sql.native );
l_rows := dbms_sql.execute(l_cursor);
dbms_sql.close_cursor( l_cursor );
end;
/
Using Native Dynamic SQL this can be accomplished
Andri Kurniawan, June 04, 2003 - 2:00 am UTC
helpful example on dbms_sql.parse
DBMS_SQL & DBMS_LOB ?
Vinnie, February 26, 2004 - 11:27 am UTC
Tom,
Can I use DBMS_SQL to get the length of a BLOB?
ex.
query = 'SELECT dbms_lob.getlength(event) FROM test'
dbms_sql.parse (l_cursor, query, dbms_sql.native);
dbms_sql.describe_columns(l_cusor, l_colCnt, l_descTbl);
I get the following after the dbms_sql.describe_columns:
dbms_sql.describe_columns overflow, col_Name_len=38. Use describe_columns2
?
February 26, 2004 - 2:18 pm UTC
SELECT dbms_lob.getlength(event) event_len FROM test
^^^^^^^^^
or, use describe_columns2 as it says :)
update CLOBs
A reader, March 17, 2004 - 7:08 am UTC
Hi
We have some plain text files stored as CLOB inside database. Until now whenever we modify these files using notepad what we do is truncate the table and reload the data to the table... I was wondering if we can do this using plain UPDATEs? The example you provided pass string to the procedure, how can I pass the text file to this procedure?
If I want to insert more than 4000 characters into clob ,what shall I do?
guolicai, June 17, 2004 - 4:42 am UTC
Tom,
I find that I have to do a loop to insert more than 4000 characters into clob, the codes are as follows:
for i in 2..(trunc((dbms_lob.getlength(lob_loc))/200)+1) loop
buffer:=dbms_lob.substr(lob_loc,200,j);
j:=i*200+1;
select statement into lob_loc1 from sta_dml where id=1 for update;
amount:=length(buffer);
dbms_lob.writeappend(lob_loc1,amount,buffer);
end loop;
commit;
The codes will most probably result in deadlock in parallel mode,please kindly suggest me what will you do or
what will be the exception like to catch deadlock.
thanks,
ally
June 17, 2004 - 10:06 am UTC
no idea what you mean, really? where do you see a deadlock happening. I see no opportunity for a deadlock.
and you do not need to do that for >4000.
simply:
select statement into lob_loc1 from sta_dml where id=1 for update;
dbms_lob.copy( lob_loc1, lob_loc,
dbms_lob.getlength(lob_loc),
dbms_lob.getlength(lob_loc1 );
Maximum limit for clob in pl/sql
A reader, August 23, 2004 - 6:39 am UTC
Tom,
What is the maximum number of bytes, I can store for clob variables in pl/sql?
Thanks
August 23, 2004 - 8:27 am UTC
4gig -- you use temporary clobs, they would be in temp space.
using clob,
sns, March 21, 2006 - 7:59 pm UTC
I am using dbms_metadata.get_ddl function to get the DDL of my composite partition.
Within my PL/SQL procedure, if I declare a "long" variable to store the DDL and later execute the variable using "execute immediate", it works. However, if I use CLOB variable, it fails.
I really need CLOB because I cannot guarantee my DDL for a composite partition is always less than 32K in size.
Example:
declare
v_sql long;
begin
select dbms_metadata.get_ddl( 'TABLE','SALES_COMPOSITE','SCHEMA1') into v_sql
FROM DUAL;
v_sql := replace(v_sql,'schema1','schema2');
execute immediate v_sql;
end;
If I declare v_sql as clob, I get the following error:
ERROR at line 10:
ORA-06550: line 8, column 21:
PLS-00382: expression is of wrong type
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored
Thanks,
March 22, 2006 - 3:45 pm UTC
if the ddl exceeds 32k and you want to do this in plsql, you'll have to use DBMS_SQL and parse the ddl you get line by line.
eg: you get a clob with ddl in it, the ddl is full of newlines (chr(10)'s). You will break that up using dbms_lob.substr line by line and put it into a plsql table type (doing your replace line by line). Then you can use dbms_sql.parse which accepts either a 32k or less string OR a plsql index by table.