Today, i had a problem while trying to use a clob value at my dynamic sql. Let me explain the problem further;
Development IDE : Oracle Forms
Failed Function: forms_ddl
I was trying to give forms_ddl function a clob value which includes ‘insert into …_table (…) values(…)’ format insert sql. Everything was working fine. But then i had to insert a clob value that is more than 4000 character(As you know the biggest varchar2 value can be 4000 characters), then it failed to insert. I had a lot of research about this and i found out the problem lying behind it. It was the character limit. Forms_ddl function only takes 4000 character of sql and truncates if it comes more than 4000.
So, after researching i found out a usage which i will share with you. Please take a careful look at the code section below.
declare
c3 integer;
c3_tmp integer;
str varchar2(4000);
tmp clob;– the value to be inserted
begin
c3 := dbms_sql.open_cursor;– opens a cursor for us to execute our sql command
dbms_sql.parse(c3, str, 1);– this statement parses our sql. str is the parameter which our sql statements included.
–Other parameters can be used same.
dbms_sql.bind_variable(c3, ‘:values’, tmp);–binding variable :values into our dynamic sql and telling Oracle ———–that :values := tmp. By the help of this statement we do not have to write all the clob value into our dynamic sql. So —no character limit will effect this statement.
c3_tmp := dbms_sql.execute(c3);– This statement executes our sql. Everything works fine.
commit;
end;
–str is the string which is like ‘insert into … (column_names) values(values–but one of them is a clob value which is declared like a bind variable :values)’
example;
str := ‘insert into users_table (op_id,PlacesVisited) values (‘EOZER’,:values)’
this sql statement script can be used to insert user_table a clob value with bind variable.
