APEXtras

create or replace package APEXTRAS_DEPLOYMENT is

  -- Author  : Roger Cohen 
  -- Created : 10/02/2009 
  -- Purpose : APEXtras blog code deployment example
  PACKAGE_LIST          CONSTANT VARCHAR2(100)   := 'MYPACKAGE1,MYPACKAGE2';
  FUNCTION_LIST         CONSTANT VARCHAR2(100)   := 'MYFUNCTION1,MYFUNCTION2';
  PROCEDURE_LIST        CONSTANT VARCHAR2(100)   := 'MYPROC1,MYPROC2';
  CODE_OUTPUT_DIRECTORY CONSTANT VARCHAR2(40)    := 'CODE_SAVE';

  PROCEDURE AllCode(p_filename     IN VARCHAR2,
                       p_target       IN VARCHAR2 := 'LIVE');

end APEXTRAS_DEPLOYMENT;
/
create or replace package body APEXTRAS_DEPLOYMENT is

  --Utility function to get the nth delimited item from a list
  FUNCTION get_delimited_token(p_the_list  CLOB,
                               p_the_index NUMBER,
                               p_delim1    VARCHAR2 := '[',
                               p_delim2    VARCHAR2 := ']') RETURN VARCHAR2 IS
    l_start_pos NUMBER;
    l_end_pos   NUMBER;
    l_index     NUMBER;
    l_output    VARCHAR2(32767);
  BEGIN
    IF (p_delim1 = p_delim2) THEN
      l_index := p_the_index - 1;
    ELSE
      l_index := p_the_index;
    END  IF;    
    IF (p_the_index = 1) THEN
      IF (p_delim1 = p_delim2) THEN
        l_start_pos := 1;
      ELSE
        l_start_pos := instr(p_the_list,p_delim1,1,1)  + length(p_delim1);
      END IF;
    ELSE
        l_start_pos := instr(p_the_list,p_delim1,1,l_index);
        IF l_start_pos = 0 THEN
            l_output := NULL;
        ELSE
            l_start_pos := l_start_pos + length(p_delim1);
        END IF;
    END IF;
    IF (l_start_pos != 0) THEN
      l_end_pos := instr(p_the_list,p_delim2,l_start_pos,1);
      IF l_end_pos = 0 THEN
          l_output := substr(p_the_list,l_start_pos);
      ELSE
          l_output := substr(p_the_list,l_start_pos,l_end_pos - l_start_pos);
      END IF;
    END IF;
    RETURN l_output;
  END get_delimited_token;

  --Wrapper for get_delimited_token setting start and end delimiters to be identical and , by default
  FUNCTION get_token(p_the_list  CLOB,
                     p_the_index NUMBER,
                     p_delim     VARCHAR2 := ',') RETURN VARCHAR2 IS
  BEGIN
    RETURN get_delimited_token(p_the_list,p_the_index,p_delim,p_delim);
  END get_token;
  
  --Return the parts of a string before and after a given delimiter
  PROCEDURE Content_Attribute(p_txt               IN VARCHAR2,
                              p_content           OUT VARCHAR2, 
                              p_content_attribute OUT VARCHAR2,
                              p_delimiter         IN VARCHAR2) IS
    l_pos INTEGER;
  BEGIN
    l_pos := instr(p_txt,p_delimiter);
    IF (l_pos > 1) THEN
      p_content := SUBSTR(p_txt,1,l_pos - 1);
      p_content_attribute := SUBSTR(p_txt,l_pos + 1);
    ELSIF (l_pos = 1) THEN
      p_content := 'NONE';
      p_content_attribute := SUBSTR(p_txt,l_pos + 1);
    ELSE
      p_content := p_txt;
      p_content_attribute := 'NONE';
    END IF;
  END;

  --Write code for one package, function, or procedure
  PROCEDURE WriteCode(p_object_name IN VARCHAR2,
                      p_object_type IN VARCHAR2,
                      p_file_handle IN UTL_FILE.file_type,
                      p_target      IN VARCHAR2 := 'LIVE') IS
    l_control_section     VARCHAR2(32767);
    l_control             VARCHAR2(50);
    l_start_end           VARCHAR2(50);
    l_comment_marker      VARCHAR2(2);
    l_start_comment       VARCHAR2(2) := '/*';
    l_end_comment         VARCHAR2(2) := '*/';
    l_line                VARCHAR2(32767);
    l_control_comment_off BOOLEAN;
    l_start_control       VARCHAR2(2) := '!~';
    l_end_control         VARCHAR2(2) := '~!';
  BEGIN
    FOR I IN (SELECT us.text AS TEXT
              FROM USER_SOURCE us
              WHERE ((UPPER(us.name) = UPPER(p_object_name))
              AND (UPPER(us.type) = UPPER(p_object_type))))
    LOOP
      l_comment_marker := '';
      l_control_section := NULL;
      IF ((INSTR(I.TEXT,l_start_control) != 0) AND (INSTR(I.TEXT,l_end_control) != 0)) THEN
        l_control_section := UTILITY.get_delimited_token(I.TEXT,1,l_start_control,l_end_control);
      END IF;
      
      IF (l_control_section IS NOT NULL) THEN
        Content_Attribute(l_control_section,l_control,l_start_end,'_');
        IF (UPPER(l_control) != UPPER(p_target)) THEN
          IF (UPPER(l_start_end) = 'BEGIN') THEN
            l_control_comment_off := TRUE;
            UTL_FILE.PUT(p_file_handle,I.TEXT);
            UTL_FILE.FFLUSH(p_file_handle);
            UTL_FILE.PUT_LINE(p_file_handle,l_start_comment);
          ELSIF (UPPER(l_start_end) = 'END') THEN
            l_control_comment_off := FALSE;
            UTL_FILE.PUT_LINE(p_file_handle,l_end_comment);
            UTL_FILE.PUT(p_file_handle,I.TEXT);
            UTL_FILE.FFLUSH(p_file_handle);
          END IF;
        ELSE
          IF (UPPER(l_start_end) = 'BEGIN') THEN
            l_control_comment_off := TRUE;
          ELSIF (UPPER(l_start_end) = 'END') THEN
            l_control_comment_off := FALSE;
          END IF;
          UTL_FILE.PUT(p_file_handle,I.TEXT);
        END IF;
      ELSE
        IF (l_control_comment_off AND ((INSTR(TRIM(I.TEXT),l_start_comment) = 1) OR (INSTR(TRIM(I.TEXT),l_end_comment) = 1))) THEN
          l_line := '';
        ELSE
          l_line := I.TEXT;
        END IF;
        UTL_FILE.PUT(p_file_handle,l_line);
      END IF;
      UTL_FILE.FFLUSH(p_file_handle);
    END LOOP;
  END;
  --Write the SQL script to create a single package, function, or procedure. Create the output file if it does not already exist,
  --or if requested
  PROCEDURE WriteOneObject(p_object_name IN VARCHAR2,
                           p_object_type IN VARCHAR2,
                           p_filename    IN VARCHAR2,
                           p_newfile     IN BOOLEAN := FALSE,
                           p_target      IN VARCHAR2 := 'LIVE',
                           p_file_header IN VARCHAR2 := '') IS
    l_file_handle         UTL_FILE.file_type;
    l_mode                VARCHAR2(1);
    l_fexists             BOOLEAN;
    l_file_length         PLS_INTEGER;
    l_block_size          PLS_INTEGER;
    l_set_commands        VARCHAR2(100) := 'SET DEFINE OFF;';
  BEGIN
    UTL_FILE.fgetattr(CODE_OUTPUT_DIRECTORY,p_filename,l_fexists,l_file_length,l_block_size);
    IF l_fexists AND (NOT p_newfile) THEN
      l_mode := 'A';
    ELSE
      l_mode := 'W';
    END IF;
    l_file_handle := UTL_FILE.fopen(CODE_OUTPUT_DIRECTORY,p_filename,l_mode);
    IF (p_file_header IS NOT NULL) THEN
      UTL_FILE.PUT_LINE(l_file_handle,p_file_header);
    END IF;
    UTL_FILE.PUT_LINE(l_file_handle,l_set_commands);
    UTL_FILE.PUT_LINE(l_file_handle,'prompt ' || p_object_type || ' ' || p_object_name || UTL_TCP.CRLF);
    UTL_FILE.PUT(l_file_handle,'CREATE OR REPLACE ');

    IF (UPPER(p_object_type) = 'PACKAGE') THEN
      WriteCode(p_object_name => p_object_name,
                p_object_type => 'PACKAGE',
                p_file_handle => l_file_handle,
                p_target      => p_target);
      UTL_FILE.PUT_LINE(l_file_handle,'');
      UTL_FILE.PUT_LINE(l_file_handle,'/');
      UTL_FILE.PUT(l_file_handle,'CREATE OR REPLACE ');
      WriteCode(p_object_name => p_object_name,
                p_object_type => 'PACKAGE BODY',
                p_file_handle => l_file_handle,
                p_target      => p_target);
    ELSE
      WriteCode(p_object_name => p_object_name,
                p_object_type => p_object_type,
                p_file_handle => l_file_handle,
                p_target      => p_target);
    END IF;
    UTL_FILE.PUT_LINE(l_file_handle,'');
    UTL_FILE.PUT_LINE(l_file_handle,'/');
    UTL_FILE.fclose(l_file_handle);
  END;
  --Write the SQL script to create all the items in the given list
  PROCEDURE CodeOutput(p_list        IN VARCHAR2,
                       p_object_type IN VARCHAR2,
                       p_filename    IN VARCHAR2,
                       p_target      IN VARCHAR2 := 'LIVE',
                       p_newfile     IN BOOLEAN := FALSE) IS
    l_object_name  VARCHAR2(30);
    l_index        INTEGER;
    l_file_header  VARCHAR2(100);

  BEGIN
    l_file_header := '--' || TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') || ' Created by CodeOutput';
    l_index       := 1;
    l_object_name := get_token(p_list,l_index);
    WHILE (l_object_name IS NOT NULL)
    LOOP
      WriteOneObject(p_object_name => l_object_name,
                     p_object_type => p_object_type,
                     p_filename    => p_filename,
                     p_newfile     => (l_index = 1) AND p_newfile,
                     p_target      => p_target,
                     p_file_header => l_file_header);
      l_index        := l_index + 1;
      l_object_name  := get_token(p_list,l_index);
    END LOOP;
  END;

  --Write the SQL script to create all the items in the package, function, and procedure lists
  PROCEDURE ALLCode(p_filename     IN VARCHAR2,
                    p_target       IN VARCHAR2 := 'LIVE') IS
  BEGIN
    CodeOutput(p_list        => PACKAGE_LIST,
               p_object_type => 'PACKAGE',
               p_filename    => p_filename,
               p_target      => p_target,
               p_newfile     => TRUE);
    CodeOutput(p_list        => FUNCTION_LIST,
               p_object_type => 'FUNCTION',
               p_filename    => p_filename,
               p_target      => p_target,
               p_newfile     => FALSE);
    CodeOutput(p_list        => PROCEDURE_LIST,
               p_object_type => 'PROCEDURE',
               p_filename    => p_filename,
               p_target      => p_target,
               p_newfile     => FALSE);
  END;

end APEXTRAS_DEPLOYMENT;
/

Back to Apex deployment script