المشكله ان المتغير TRIGGER_BODY اذا كان في جدول كبير جدا بحييث الحجم اكبر من 32767 لا يقوم بتحميل الترجر وهذا شي طبيعي
لكن كيف يتم حل هذي المشكله اخواني مع العلم ان جربت جميع الداتتايب مثل row and long row stc لكن لم تنفع مع forms_ddl
فارجو المساعده
وهذا الكود

declare

cursor user_tables is SELECT object_name FROM  user_objects where  object_type='TABLE';
--cursor user_tables_FIELDS IS SELECT  column_name,table_name FROM user_tab_columns;
--	cursor get_pk1(OWNER_N VARCHAR2,TABLE_N varchar2) is select column_name from primary_constraint where OWNER=OWNER_N AND table_name=TABLE_N; 
user_table_name 	VARCHAR2(128);
FIELD_name 	VARCHAR2(128);
my_table_name 	VARCHAR2(128);
TRIGGER_BODY	VARCHAR2(32767);
ind number;
begin

LOGOUT;
logon(:t_user,:t_pass||'@'||:t_con);
open  user_tables;
	
ind:=1;
clear_list('control.l_tables');
	loop
fetch user_tables into user_table_name;
exit when user_tables%notfound;	
--------------------------------------------
DECLARE 
	PK1 VARCHAR2(500):=NULL;
PK VARCHAR2(500):=NULL;
	cursor user_tables_FIELDS IS SELECT  column_name,table_name FROM user_tab_columns;
	cursor get_pk1 is select column_name from SHR.primary_constraint where OWNER=:t_user AND table_name=user_table_name; 
BEGIN
	
--	OPEN get_pk1;
FOR R IN GET_PK1	LOOP
	--	FETCH get_pk1 INTO PK1 ;
	--	EXIT WHEN get_pk1%NOTFOUND;
		PK1:=PK1||':OLD.'||R.COLUMN_NAME||'||'||''' * '''||'||';
			PK:=PK||''''||R.COLUMN_NAME||''''||'||'||''' * '''||'||';
END LOOP;
---	IF PK1 IS NULL THEN 
--	PK :='NULL';
--	ELSE 
PK1:=PK1||'''#''';
PK:=PK||'''#''';
--END IF;
TRIGGER_BODY:='CREATE OR REPLACE TRIGGER SHR.'||:t_user||'_'||user_table_name;
 TRIGGER_BODY:=TRIGGER_BODY||' BEFORE UPDATE OR DELETE ON '||:t_user||'.'||user_table_name;
 TRIGGER_BODY:=TRIGGER_BODY||' REFERENCING OLD AS old NEW AS new FOR EACH ROW';
TRIGGER_BODY:=TRIGGER_BODY||' begin';
TRIGGER_BODY:=TRIGGER_BODY||'	declare ';
      			TRIGGER_BODY:=TRIGGER_BODY||'  ip_add varchar2(200);';
      			TRIGGER_BODY:=TRIGGER_BODY||'  SER_NUM varchar2(200);';
      			TRIGGER_BODY:=TRIGGER_BODY||'  user_t varchar2(200);';
               	TRIGGER_BODY:=TRIGGER_BODY||' begin'; 
	TRIGGER_BODY:=TRIGGER_BODY||' select SHR.get_ip_address into ip_add from dual;';
		TRIGGER_BODY:=TRIGGER_BODY||' select SHR.GET_MAX into SER_NUM from dual;';
			TRIGGER_BODY:=TRIGGER_BODY||' select nvl(user_id,000)  into user_t from shr.user_trans where ser_no=(select NVL(max(ser_no),0)from SHR.user_trans);';
open user_tables_FIELDS;

loop
 		fetch user_tables_FIELDS into FIELD_name,my_table_name;
		exit when user_tables_FIELDS%notfound;
		if my_table_name=user_table_name then
			-----------------------------
		
					--------------------------------------------
						TRIGGER_BODY:=TRIGGER_BODY||'IF UPDATING THEN';
				TRIGGER_BODY:=TRIGGER_BODY||' IF :OLD.'||FIELD_name ||' <> '||':NEW.'||FIELD_name||' THEN';
		TRIGGER_BODY:=TRIGGER_BODY||' insert into SHR.TRACK_USER_TABLES values';
		TRIGGER_BODY:=TRIGGER_BODY||'('''||user_table_name||''','''||FIELD_NAME||''',:old.'||FIELD_name||',:new.'||FIELD_name||','||'IP_ADD'||','||PK1||','||'user_t'||','''||FIELD_name||''','||'''UPDATED'''||','||'SYSDATE'||','||'SER_NUM'||','||'USER'||','||pk||');';
		TRIGGER_BODY:=TRIGGER_BODY||''||'END IF;';
		TRIGGER_BODY:=TRIGGER_BODY||'ELSE';
			TRIGGER_BODY:=TRIGGER_BODY||' insert into SHR.TRACK_USER_TABLES values';
		TRIGGER_BODY:=TRIGGER_BODY||'('''||user_table_name||''','''||FIELD_NAME||''',:old.'||FIELD_name||','||'NULL'||','||'IP_ADD'||','||PK1||','||'user_t'||','''||FIELD_name||''','||'''DELETED'''||','||'SYSDATE'||','||'SER_NUM'||','||'USER'||','||pk||');';
		TRIGGER_BODY:=TRIGGER_BODY||''||'END IF;';
	
		end if;
			
end loop;
close user_tables_FIELDS;
END;
	
TRIGGER_BODY:=TRIGGER_BODY||' end;';
	TRIGGER_BODY:=TRIGGER_BODY||' end;';
Forms_ddl(TRIGGER_BODY);
ind:=ind+1;
end loop;

end; 

جرب استخدام clob

تم تعديل 7 ديسمبر 201015 سنة بواسطة HELL-BOY

علي فكره منفعتش بردو مقبلتش اكتر من 32 الف حرف

declare
test clob :=lpad('n',33000,'n');
begin
null;
end;

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 2

تم تعديل 7 ديسمبر 201015 سنة بواسطة HELL-BOY