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
    
/
/