pkg_support.pkb
CREATE OR REPLACE PACKAGE BODY PKG_SUPPORT as /* || This package: || || * Generates create table scripts || * Generates create package scripts || * Generates create triggers scripts || * Creates DBMS_PIPE || * Runs dynamic SQL || * Recompiles all invalid objects || * Keeps selected objects in SQL_SHARED_POOL || * Removes selected objects from SQL_SHARED_POOL || */ /***************************************************************/ CURSOR c_body (lis_text IN VARCHAR2) IS SELECT DISTINCT name FROM USER_SOURCE WHERE UPPER(text) LIKE '%'|| UPPER(lis_text) || '%'; ls_ddl_dir VARCHAR2(30) :='/export/home/oracle'; PROCEDURE c_d_public_synonym (pis_action IN VARCHAR2, pis_object_type IN VARCHAR2) IS /* || Create or drop public synonym for specified object_type. */ ls_object_type VARCHAR2(50); sql_command varchar2(200) ; obj_name user_objects.object_name%type ; obj_type user_objects.object_type%type ; cursor c_all_objs is select object_name,object_type from user_objects where object_type in ('TABLE','VIEW','SEQUENCE','PACKAGE','PROCEDURE','FUNCTION'); cursor c_seq_objs is select object_name,object_type from user_objects where object_type = 'SEQUENCE'; cursor c_package_objs is select object_name, object_type from user_objects where object_type in ('PACKAGE','FUNCTION','PROCEDURE'); cursor c_table_objs is select object_name from user_objects where object_type = 'TABLE'; cursor c_view_objs is select object_name from user_objects where object_type = 'VIEW'; BEGIN IF pis_action = 'CREATE' THEN sql_command:='CREATE PUBLIC SYNONYM '; -- ELSE -- sql_command:='DROP PUBLIC SYNONYM '; END IF; IF pis_object_type IS NULL THEN FOR inv_obj in c_all_objs LOOP if substr(sql_command,1,1)='C' then sql_command := sql_command || inv_obj.object_name || ' FOR ' || inv_obj.object_name; else sql_command := sql_command || inv_obj.object_name ; end if; dynsql(sql_command, sql_command); END LOOP; END IF; IF pis_object_type = 'SEQUENCE' THEN FOR inv_obj in c_seq_objs LOOP declare begin if substr(sql_command,1,1)='C' then sql_command:='CREATE PUBLIC SYNONYM '; sql_command := sql_command || inv_obj.object_name || ' FOR ' || inv_obj.object_name; else sql_command := sql_command || inv_obj.object_name ; end if; dbms_output.put_line(sql_command); EXECUTE IMMEDIATE sql_command; exception when others then null; end; END LOOP; END IF; IF pis_object_type = 'TABLE' THEN FOR inv_obj in c_table_objs LOOP declare begin if substr(sql_command,1,1)='C' then sql_command:='CREATE PUBLIC SYNONYM '; sql_command := sql_command || inv_obj.object_name || ' FOR ' || inv_obj.object_name; else sql_command := sql_command || inv_obj.object_name ; end if; dynsql(sql_command, sql_command); exception when others then null; end; END LOOP; END IF; IF pis_object_type = 'VIEW' THEN FOR inv_obj in c_view_objs LOOP declare begin if substr(sql_command,1,1)='C' then sql_command:='CREATE PUBLIC SYNONYM '; sql_command := sql_command || inv_obj.object_name || ' FOR ' || inv_obj.object_name; else sql_command := sql_command || inv_obj.object_name ; end if; dynsql(sql_command, sql_command); exception when others then null; end; END LOOP; END IF; IF pis_object_type = 'PACKAGE' THEN FOR inv_obj in c_package_objs LOOP declare begin if substr(sql_command,1,1)='C' then sql_command:='CREATE PUBLIC SYNONYM '; sql_command := sql_command || inv_obj.object_name || ' FOR ' || inv_obj.object_name; else sql_command := sql_command || inv_obj.object_name ; end if; dynsql(sql_command, sql_command); exception when others then null; end; END LOOP; END IF; END c_d_public_synonym; /***************************************************************/ FUNCTION get_columns(t_name IN varchar2, tablist OUT tab_col_type ) RETURN NUMBER as n_item NUMBER ; cursor curs_id is select column_name from user_tab_columns where table_name=upper(t_name) order by column_name ; begin n_item := 0 ; FOR itm_column in curs_id LOOP n_item := n_item + 1 ; tablist(n_item) := itm_column.column_name ; END LOOP ; RETURN n_item ; END get_columns ; /***************************************************************/ PROCEDURE dynsql(command varchar2, outputerr varchar2) AS src_cursor integer ; return_code integer ; begin /* Open the cursor for processing. */ src_cursor := dbms_sql.open_cursor; /* Parse the SQL statement. */ dbms_sql.parse(src_cursor, command, dbms_sql.native) ; /* Execute the SQL statement. */ return_code := dbms_sql.execute(src_cursor); /* Close the cursor. */ dbms_sql.close_cursor(src_cursor); Exception when others then if dbms_sql.is_open(src_cursor) then dbms_sql.close_cursor(src_cursor); end if; raise_application_error(-20101, sqlerrm||'<<---->> For '|| outputerr) ; END dynsql ; /***************************************************************/ /* Recompile all INVALID objects for current schema.*/ /***************************************************************/ PROCEDURE recompile as sql_command varchar2(200) ; obj_name user_objects.object_name%type ; obj_type user_objects.object_type%type ; cursor c_inv_objs is select object_name,object_type from user_objects where status='INVALID' and object_type <> 'PACKAGE BODY' and object_type <> 'PACKAGE' and object_type <> 'TRIGGER'; cursor p_inv_objs is select object_name,object_type from user_objects where status='INVALID' and object_type = 'PACKAGE' and object_type <> 'PACKAGE' and object_type <> 'TRIGGER'; cursor b_inv_objs is select object_name, object_type from user_objects where status='INVALID' and object_type ='PACKAGE BODY' and object_type <> 'TRIGGER'; cursor t_inv_objs is select object_name from all_objects where object_type='TRIGGER' and status = 'INVALID' and owner=user ; BEGIN FOR inv_obj in c_inv_objs LOOP sql_command := 'ALTER ' || inv_obj.object_type || ' ' || inv_obj.object_name || ' COMPILE' ; dynsql(sql_command, sql_command); END LOOP; FOR inv_obj in p_inv_objs LOOP sql_command := 'ALTER PACKAGE ' || inv_obj.object_name || ' COMPILE package' ; dynsql(sql_command, sql_command); END LOOP; FOR inv_obj in b_inv_objs LOOP sql_command := 'ALTER package' || ' ' || inv_obj.object_name || ' COMPILE body' ; dynsql(sql_command, sql_command); END LOOP; FOR inv_obj in t_inv_objs LOOP sql_command := 'ALTER trigger' || ' ' || inv_obj.object_name || ' compile' ; dynsql(sql_command, sql_command); END LOOP; END recompile ; /***************************************************************/ /* PIN all packages in SGA. */ /***************************************************************/ PROCEDURE keep AS obj_name user_objects.object_name%type ; ls_user VARCHAR2(30); cursor c_pkg_name IS SELECT object_name FROM user_objects WHERE object_type = 'PACKAGE'; BEGIN /* Get schema */ SELECT USER INTO ls_user FROM dual; FOR c_inv_obj in c_pkg_name LOOP dbms_shared_pool.keep(ls_user||'.'||c_inv_obj.object_name); END LOOP; END keep; /***************************************************************/ /* Remove all packages from SGA. */ PROCEDURE unkeep AS obj_name user_objects.object_name%type ; ls_user VARCHAR2(30); cursor c_pkg_name IS SELECT object_name FROM user_objects WHERE object_type = 'PACKAGE'; BEGIN /* Get schema */ SELECT USER INTO ls_user FROM dual; FOR c_inv_obj in c_pkg_name LOOP dbms_shared_pool.unkeep(ls_user||'.'||c_inv_obj.object_name); END LOOP; END unkeep; /***************************************************************/ /* Run ANALYZE TABLE based on all TABLES for current schema.*/ /***************************************************************/ PROCEDURE table_analyze AS sql_command varchar2(200) ; obj_name user_objects.object_name%type ; obj_type user_objects.object_type%type ; cursor c_inv_objs is select object_name from ALL_objects where object_type = 'TABLE' AND OWNER=USER; begin FOR inv_obj in c_inv_objs LOOP sql_command := 'ANALYZE TABLE ' || inv_obj.object_name || ' ' || 'ESTIMATE STATISTICS' ; dynsql(sql_command, sql_command); END LOOP; END table_analyze ; /***************************************************************/ /* Run ANALYZE INDEX based on all INDEXES for current schema. */ /***************************************************************/ PROCEDURE index_analyze AS sql_command varchar2(200) ; obj_name user_objects.object_name%type ; obj_type user_objects.object_type%type ; cursor c_inv_objs is select object_name from ALL_objects where object_type = 'INDEX' AND OWNER=USER; BEGIN FOR inv_obj in c_inv_objs LOOP sql_command := 'ANALYZE INDEX ' || inv_obj.object_name || ' ' || 'ESTIMATE STATISTICS' ; dynsql(sql_command, sql_command); END LOOP; END index_analyze; /***************************************************************/ /* Run dynamic SQL against all TABLES for current schema. */ /***************************************************************/ PROCEDURE before_after(before IN varchar2, after IN varchar2) AS sql_command varchar2(200) ; obj_name user_objects.object_name%type ; obj_type user_objects.object_type%type ; cursor c_inv_objs is select object_name from user_objects where object_type = 'TABLE' ; BEGIN FOR inv_obj in c_inv_objs LOOP sql_command := before ||' '|| inv_obj.object_name || ' ' || after ; dynsql(sql_command, sql_command); END LOOP; END before_after ; /***************************************************************/ Function get_count (table_name in varchar2) return integer IS cursor_1 integer; do_it_1 integer; count_value integer; BEGIN cursor_1:= dbms_sql.open_cursor; dbms_sql.parse(cursor_1, ' select count(*) from '||table_name,dbms_sql.v7); dbms_sql.define_column(cursor_1,1,count_value); do_it_1:=dbms_sql.execute_and_fetch(cursor_1); dbms_sql.column_value(cursor_1,1,count_value); dbms_sql.close_cursor(cursor_1); return count_value; END get_count; Function get_bytes (where_string varchar2 ) return integer IS cursor_1 integer; do_it_1 integer; count_bytes integer; BEGIN cursor_1:= dbms_sql.open_cursor; dbms_output.put_line ('getbytes-'||where_string); dbms_output.put_line ('cursor_1= '||to_char(cursor_1)); dbms_sql.parse(cursor_1, 'select sum(bytes) from dba_segments ' ||where_string, dbms_sql.v7); -- dbms_sql.parse(cursor_1, 'select count(*) from abc_codes ' -- , dbms_sql.v7); dbms_output.put_line ('Out'); dbms_sql.define_column(cursor_1,1,count_bytes); do_it_1:=dbms_sql.execute_and_fetch(cursor_1); dbms_sql.column_value(cursor_1,1,count_bytes); dbms_sql.close_cursor(cursor_1); dbms_output.put_line('bytes='||to_char(count_bytes)); RETURN (count_bytes); END get_bytes; /***************************************************************/ Function get_index_bytes (p_where varchar2) return integer IS cursor_1 integer; do_it_1 integer; count_bytes integer:=0; BEGIN cursor_1:= dbms_sql.open_cursor; dbms_sql.parse(cursor_1, 'select sum(ds.bytes) from dba_segments ds, all_indexes ai '|| p_where,dbms_sql.v7); dbms_sql.define_column(cursor_1,1,count_bytes); do_it_1:=dbms_sql.execute_and_fetch(cursor_1); dbms_sql.column_value(cursor_1,1,count_bytes); dbms_sql.close_cursor(cursor_1); IF count_bytes is NULL THEN count_bytes:=0; END IF; return (count_bytes); EXCEPTION WHEN OTHERS THEN RETURN 0; END get_index_bytes; /***************************************************************/ /* Populate table t_count. */ /***************************************************************/ Procedure analysis_count IS p_count number:=0; p_table_sum number:=0; p_index_sum number:=0; p_total number:=0; p_avg number:=0; p_where varchar2(250); p_pass varchar2(100); p_initial NUMBER(10):=0; p_extents NUMBER(10):=0; p_sql varchar2(250); cursor aa_name is select object_name from all_objects where owner=user and object_type='TABLE' order by object_name; BEGIN -- delete tmp_count; for table_process in aa_name loop p_count :=0; p_table_sum :=0; p_index_sum :=0; p_total :=0; p_avg :=0; p_pass:=table_process.object_name; dbms_output.put_line(table_process.object_name); p_count:=get_count(p_pass); dbms_output.put_line('count= '||p_count); p_where:='where segment_name='||''''||p_pass||''''||' and owner=''ACME'' and segment_type=''TABLE'' group by segment_name, segment_type'; dbms_output.put_line(p_where); p_table_sum:=get_bytes(p_where); p_where:='where ai.index_name=ds.segment_name and ai.table_name='||''''||p_pass||''''||' and ai.table_owner=''ACME'' and ds.segment_name like ''PK_%'' group by ds.segment_name'; p_index_sum:=get_index_bytes (p_where); -- p_index_sum:=0; p_total:=p_table_sum+p_index_sum; if p_count >0 then p_avg:=p_total/p_count; end if; p_sql:='select initial_extent into p_initial from dba_segements where segment_name='||''''||p_pass||''''||' and owner=''ACME'' and segment_type=''TABLE'''; -- pkg_support.dynsql(p_sql,''); p_sql:='select extents into p_extents from dba_segements where segment_name='||''''||p_pass||''''||' and owner=''ACME'' and segment_type=''TABLE'''; -- pkg_support.dynsql(p_sql,''); --insert into tmp_count (table_name, row_count, table_bytes, average, index_bytes, total_bytes) --values (p_pass,p_count,p_table_sum, p_avg, p_index_sum, p_total); -- commit; end loop; commit; END analysis_count ; /***************************************************************/ Procedure dis_ena_trg (tname IN VARCHAR2, trg_nme IN VARCHAR2, action VARCHAR2) IS lv_trigger_nme user_triggers.trigger_name%TYPE; sql_txt varchar2(100); CURSOR c_trig_name IS SELECT trigger_name FROM user_triggers WHERE table_name = upper(tname); BEGIN FOR trig_name IN c_trig_name LOOP lv_trigger_nme:=trig_name.trigger_name; IF (trg_nme IS NOT NULL and upper(trg_nme )= upper(lv_trigger_nme)) OR trg_nme IS NULL THEN sql_txt := 'ALTER TRIGGER '|| lv_trigger_nme || ' '||action; pkg_support.dynsql(sql_txt,''); END IF; END LOOP; END dis_ena_trg; /***************************************************************/ /* LOS Application Related || || */ PROCEDURE sp_add_user (pis_user_id IN VARCHAR2, pis_instance_txt IN VARCHAR2) IS sql_text VARCHAR2(150); ln_count NUMBER(2):= 0; ls_temp VARCHAR2(30); BEGIN -- If there is no account in Oracle then create Oracle Account ls_temp := UPPER(pis_user_id); SELECT count(*) INTO ln_count FROM all_users WHERE username = UPPER(pis_user_id); IF ln_count = 0 -- New User in Oracle THEN sql_text:='create user '||ls_temp||' identified by '||pis_instance_txt|| ' default tablespace data'||' temporary tablespace temp'; execute IMMEDIATE SQL_TEXT; -- Grant sql_text:='grant create session to '||ls_temp; execute IMMEDIATE sql_text; sql_text:='grant resource to '||ls_temp; execute IMMEDIATE sql_text; sql_text:='grant create table to '||ls_temp; execute IMMEDIATE sql_text; sql_text:='grant create view to '||ls_temp; execute IMMEDIATE sql_text; sql_text:='grant create sequence to '||ls_temp; execute IMMEDIATE sql_text; sql_text:='grant create package to '||ls_temp; execute IMMEDIATE sql_text; sql_text:='grant create procedure to '||ls_temp; execute IMMEDIATE sql_text; END IF; --delete user_role where user_id = ls_temp; --delete regional_user where user_id = ls_temp; --delete los_user_lkup where user_id = ls_temp; END sp_add_user; /***************************************************************/ Procedure sp_del_user (pis_user_id IN VARCHAR2) IS sql_text VARCHAR2(150); ls_temp VARCHAR2(30); BEGIN IF upper(pis_user_id) NOT IN ('ACME','SYS','SYSTEM','JBOSS','IMPDATA1','IMPDATA2') THEN ls_temp:=UPPER(pis_user_id); dbms_output.put_line(ls_temp); sql_text:='drop user '||ls_temp||' cascade'; execute IMMEDIATE sql_text; END IF; END sp_del_user; /***************************************************************/ /* || DISABLE/ENABLE all triggers. || || If pis_trigger_name is NULL, then apply pis_action to all triggers based on USER_TRIGGERS, || otherwise only apply the pis_action to pis_trigger_name. || */ /***************************************************************/ PROCEDURE trigger_action (pis_trigger_name IN user_triggers.trigger_name%TYPE, pis_action IN CHAR) IS cursor c_trigger is select trigger_name from user_triggers ; sql_text VARCHAR2(100); BEGIN IF pis_trigger_name is NULL -- No trigger specified, apply action to all triggers. THEN FOR c_temp_trigger in c_trigger LOOP sql_text:='alter trigger '||c_temp_trigger.trigger_name||' '||pis_action; pkg_support.dynsql(sql_text,''); END LOOP; ELSE sql_text:='alter trigger '||pis_trigger_name||' '||pis_action; pkg_support.dynsql(sql_text,''); END IF; END trigger_action; /***************************************************************/ PROCEDURE sp_grant(pis_action IN VARCHAR2, pis_object_type IN user_objects.object_type%TYPE, pis_user VARCHAR2) IS sql_text VARCHAR2(150); cursor c_all_objs (pis_objec_type IN user_objects.object_type%TYPE) is select object_name,object_type from user_objects where object_type= UPPER(pis_object_type); BEGIN -- Check for REVOKE IF UPPER(SUBSTR(pis_action,1,1)) <> 'R' THEN FOR ls_object_name in c_all_objs (pis_object_type) LOOP sql_text:='grant '||UPPER(pis_action)||' on '||ls_object_name.object_name||' to '||UPPER(pis_user); pkg_support.dynsql(sql_text,''); END LOOP; ELSE -- REVOKE FOR ls_object_name in c_all_objs (pis_object_type) LOOP sql_text:=pis_action||' on '||ls_object_name.object_name||' from '||UPPER(pis_user); pkg_support.dynsql(sql_text,''); END LOOP; END IF; -- Check for REVOKE END sp_grant; /***************************************************************/ PROCEDURE OutputObj(p_FileHandle IN OUT UTL_FILE.FILE_TYPE, p_Schema IN VARCHAR2, p_ObjName IN VARCHAR2, p_ObjType IN VARCHAR2) IS /* These variables are used to retreive the trigger text. Since all_triggers * stores a trigger body as a LONG, we have to use DBMS_SQL to fetch the long * in chunks. */ v_SQLStmt VARCHAR2(200) := 'SELECT description, trigger_body FROM all_triggers WHERE owner = :v_owner AND trigger_name = :v_name'; v_SQLStmt2 VARCHAR2(200) := 'SELECT text FROM all_views WHERE owner = :v_owner AND view_name = :v_name'; v_Cursor INTEGER; v_NumRows INTEGER; v_Dummy INTEGER; v_Description all_triggers.description%TYPE; v_BodyChunk VARCHAR2(100); v_ChunkSize NUMBER := 100; v_CurPos NUMBER := 0; v_ReturnedLength NUMBER := 0; /* These variables are used to retrieve the source for other types of objects. * DBMS_SQL is not necessary here, since all_source stores each line of source * separately. */ v_TextLine all_source.text%TYPE; CURSOR c_ObjCur IS SELECT text FROM all_source WHERE owner = p_Schema AND name = p_ObjName AND type = p_ObjType ORDER BY line; BEGIN -- First, write the 'CREATE OR REPLACE ' to the file. -- UTL_FILE.PUT(p_FileHandle, 'CREATE OR REPLACE '); IF (p_ObjType = 'TRIGGER') THEN BEGIN UTL_FILE.PUT(p_FileHandle, 'CREATE OR REPLACE '); -- Output the object type (TRIGGER in this case) to the file. UTL_FILE.PUT(p_FileHandle, 'TRIGGER '); -- Open the cursor and parse the statement. v_Cursor := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(v_Cursor, v_SQLStmt, DBMS_SQL.V7); -- Bind the input variables to the placeholders. DBMS_SQL.BIND_VARIABLE(v_Cursor, ':v_owner', p_Schema); DBMS_SQL.BIND_VARIABLE(v_Cursor, ':v_name', p_Objname); -- Define the output variables. Note the use of DEFINE_COLUMN_LONG for -- the trigger body text. DBMS_SQL.DEFINE_COLUMN(v_Cursor, 1, v_Description, 2000); DBMS_SQL.DEFINE_COLUMN_LONG(v_Cursor, 2); -- Execute the statement, and fetch the row. We don't have to fetch in a -- loop since there is only one row per trigger. v_Dummy := DBMS_SQL.EXECUTE(v_Cursor); v_NumRows := DBMS_SQL.FETCH_ROWS(v_Cursor); -- Retreive the value for the trigger description, and output it to the file. -- Note that we can use UTL_FILE.PUT rather than UTL_FILE.PUT_LINE, since the -- table contains the trailing newline character. DBMS_SQL.COLUMN_VALUE(v_Cursor, 1, v_Description); UTL_FILE.PUT(p_FileHandle, v_Description); -- Loop until we've retreived the entire trigger body. We will retreive -- v_ChunkSize characters each loop iteration. LOOP DBMS_SQL.COLUMN_VALUE_LONG(v_Cursor, 2, v_ChunkSize, v_CurPos, v_BodyChunk, v_ReturnedLength); IF v_ReturnedLength < v_ChunkSize THEN -- We've just retreived the last chunk. For some reason, an extra NULL -- character is stored in the table after the newline. So we need to -- remove it first. v_BodyChunk := SUBSTR(v_BodyChunk, 1, LENGTH(v_BodyChunk) - 1); -- Output the trimmed chunk, and exit the loop. UTL_FILE.PUT(p_FileHandle, v_BodyChunk); EXIT; ELSE -- We've retreived a chunk from the middle of the long. Output it to the -- file, and update the current position for the next loop iteration. UTL_FILE.PUT(p_FileHandle, v_BodyChunk); v_CurPos := v_CurPos + v_ReturnedLength; END IF; END LOOP; -- Close the cursor, since we're finished the processing. DBMS_SQL.CLOSE_CURSOR(v_Cursor); EXCEPTION WHEN OTHERS THEN -- For all errors, first close the cursor, then re-raise the exception for -- handling in the calling environment. DBMS_SQL.CLOSE_CURSOR(v_Cursor); RAISE; END; ELSIF (p_ObjType = 'VIEW') THEN BEGIN UTL_FILE.PUT(p_FileHandle, 'CREATE OR REPLACE VIEW '||p_ObjName||' AS '); -- Output the object type (TRIGGER in this case) to the file. -- UTL_FILE.PUT(p_FileHandle, 'VIEW '); -- Open the cursor and parse the statement. v_Cursor := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(v_Cursor, v_SQLStmt2, DBMS_SQL.V7); -- Bind the input variables to the placeholders. DBMS_SQL.BIND_VARIABLE(v_Cursor, ':v_owner', p_Schema); DBMS_SQL.BIND_VARIABLE(v_Cursor, ':v_name', p_Objname); -- Define the output variables. Note the use of DEFINE_COLUMN_LONG for -- the trigger body text. -- DBMS_SQL.DEFINE_COLUMN(v_Cursor, 1, v_Description, 2000); DBMS_SQL.DEFINE_COLUMN_LONG(v_Cursor, 1); -- Execute the statement, and fetch the row. We don't have to fetch in a -- loop since there is only one row per trigger. v_Dummy := DBMS_SQL.EXECUTE(v_Cursor); v_NumRows := DBMS_SQL.FETCH_ROWS(v_Cursor); -- Retreive the value for the trigger description, and output it to the file. -- Note that we can use UTL_FILE.PUT rather than UTL_FILE.PUT_LINE, since the -- table contains the trailing newline character. -- DBMS_SQL.COLUMN_VALUE(v_Cursor, 1, v_Description); -- UTL_FILE.PUT(p_FileHandle, v_Description); -- Loop until we've retreived the entire trigger body. We will retreive -- v_ChunkSize characters each loop iteration. LOOP DBMS_SQL.COLUMN_VALUE_LONG(v_Cursor, 1, v_ChunkSize, v_CurPos, v_BodyChunk, v_ReturnedLength); IF v_ReturnedLength < v_ChunkSize THEN -- We've just retreived the last chunk. For some reason, an extra NULL -- character is stored in the table after the newline. So we need to -- remove it first. -- v_BodyChunk := SUBSTR(v_BodyChunk, 1, LENGTH(v_BodyChunk) - 1); /* 10-14-98 Changed by JWT due to missing the last character for some views. */ v_BodyChunk := SUBSTR(v_BodyChunk, 1, LENGTH(v_BodyChunk)); -- Output the trimmed chunk, and exit the loop. UTL_FILE.PUT(p_FileHandle, v_BodyChunk); EXIT; ELSE -- We've retreived a chunk from the middle of the long. Output it to the -- file, and update the current position for the next loop iteration. UTL_FILE.PUT(p_FileHandle, v_BodyChunk); v_CurPos := v_CurPos + v_ReturnedLength; END IF; END LOOP; UTL_FILE.PUT_LINE(p_FileHandle, ';'); -- Close the cursor, since we're finished the processing. DBMS_SQL.CLOSE_CURSOR(v_Cursor); EXCEPTION WHEN OTHERS THEN -- For all errors, first close the cursor, then re-raise the exception for -- handling in the calling environment. DBMS_SQL.CLOSE_CURSOR(v_Cursor); RAISE; END; ELSE UTL_FILE.PUT(p_FileHandle, 'CREATE OR REPLACE '); -- If we get here, we are not outputting a trigger. So we can just loop through -- the source lines in all_source, and output each one. -- Note that the first line will be the type and object name, plus 'IS' or 'AS'. OPEN c_ObjCur; LOOP FETCH c_ObjCur INTO v_TextLine; EXIT WHEN c_ObjCur%NOTFOUND; -- Each line already contains a trailing newline character, so we can just use -- UTL_FILE.PUT rather than UTL_FILE.PUT_LINE. UTL_FILE.PUT(p_FileHandle, v_TextLine); END LOOP; CLOSE c_ObjCur; END IF; -- Output the final ';'. UTL_FILE.PUT_line(p_FileHandle, ' '); UTL_FILE.PUT_line(p_FileHandle, '/'); END OutputObj; /***************************************************************/ PROCEDURE OneObj(p_Schema IN VARCHAR2, p_ObjName IN VARCHAR2, p_ObjType IN VARCHAR2 DEFAULT NULL, p_BothTypes IN BOOLEAN DEFAULT TRUE, p_FileDir IN VARCHAR2, p_FileName IN VARCHAR2, p_Mode IN VARCHAR2) IS v_FileHandle UTL_FILE.FILE_TYPE; v_ObjType all_objects.object_type%TYPE; BEGIN -- Validate the input parameters. IF p_BothTypes AND (p_ObjType != 'PACKAGE') THEN RAISE_APPLICATION_ERROR(-20000, 'Export.OneObj: BothTypes set but type != PACKAGE'); ELSIF p_ObjType IS NOT NULL AND p_ObjType NOT IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER','VIEW') THEN RAISE_APPLICATION_ERROR(-20001, 'Export.OneObj: Illegal value ' || p_ObjType || ' for object type'); ELSIF p_FileDir IS NULL OR p_FileName IS NULL or p_Mode IS NULL THEN RAISE_APPLICATION_ERROR(-20002, 'Export.OneObj: Directory, Filename and Mode must be non-NULL'); ELSIF p_Mode NOT IN ('A', 'a', 'W', 'w') THEN RAISE_APPLICATION_ERROR(-20003, 'Export.OneObj: Mode ' || p_Mode || ' not ''A'' or ''W'''); END IF; -- Determine the correct object type, and insure that the object -- exists. BEGIN IF p_ObjType IS NULL THEN -- No object type specified - check for existence whithout -- specifying the type. SELECT object_type INTO v_ObjType FROM all_objects WHERE owner = UPPER(p_Schema) AND object_name = UPPER(p_Objname) AND object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TRIGGER','VIEW'); ELSIF p_BothTypes THEN -- BothTypes specified - check for existence of package header -- first. SELECT object_type INTO v_ObjType FROM all_objects WHERE owner = UPPER(p_Schema) AND object_name = UPPER(p_Objname) AND object_type = 'PACKAGE'; -- Now check for the package body. BEGIN SELECT object_type INTO v_ObjType FROM all_objects WHERE owner = UPPER(p_Schema) AND object_name = UPPER(p_Objname) AND object_type = 'PACKAGE BODY'; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20006, 'Export.ObjObj: BothTypes set but package body ' || p_Schema || '.' || p_Objname || ' not found'); END; ELSE -- Object type specified, Bothtypes not specified - check for -- existence using object type. SELECT object_type INTO v_ObjType FROM all_objects WHERE owner = UPPER(p_Schema) AND object_name = UPPER(p_Objname) AND object_type = p_ObjType; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20004, 'Export.OneObj: Object ' || p_Schema || '.' || p_Objname || ' not found'); WHEN TOO_MANY_ROWS THEN RAISE_APPLICATION_ERROR(-20005, 'Export.OneObj: More than one match for ' || p_Schema || '.' || p_Objname); END; -- If we get to this point, we know that the object exists, so we can -- open the file and write to it. If the mode is 'A', then write 2 -- blank lines first. v_FileHandle := UTL_FILE.FOPEN(p_FileDir, p_FileName, p_Mode); IF p_Mode IN ('A', 'a') THEN UTL_FILE.NEW_LINE(v_FileHandle, 2); END IF; -- Output the object. IF p_ObjType = 'PACKAGE' AND p_BothTypes THEN OutputObj(v_FileHandle, p_Schema, p_ObjName, 'PACKAGE'); UTL_FILE.NEW_LINE(v_FileHandle, 2); OutputObj(v_FileHandle, p_Schema, p_ObjName, 'PACKAGE BODY'); ELSE OutputObj(v_FileHandle, p_Schema, p_ObjName, v_ObjType); END IF; -- Close the output file. UTL_FILE.PUT_LINE(v_FileHandle,'/'); UTL_FILE.FCLOSE(v_FileHandle); EXCEPTION -- Handle the UTL_FILE exceptions meaningfully, and make sure -- that the file is properly closed. WHEN UTL_FILE.INVALID_PATH THEN UTL_FILE.FCLOSE(v_FileHandle); RAISE_APPLICATION_ERROR(-20010, 'Export.OneObj: Invalid Path'); WHEN UTL_FILE.INVALID_OPERATION THEN UTL_FILE.FCLOSE(v_FileHandle); RAISE_APPLICATION_ERROR(-20010, 'Export.OneObj: Invalid Operation'); WHEN UTL_FILE.INVALID_FILEHANDLE THEN UTL_FILE.FCLOSE(v_FileHandle); RAISE_APPLICATION_ERROR(-20010, 'Export.OneObj: Invalid File Handle'); WHEN UTL_FILE.WRITE_ERROR THEN UTL_FILE.FCLOSE(v_FileHandle); RAISE_APPLICATION_ERROR(-20010, 'Export.OneObj: Write Error'); WHEN UTL_FILE.INTERNAL_ERROR THEN / /