+create or replace package sample_pkg is
+    --
+    -- Error Handling function
+    --
+    function demo_error_handling (
+        p_error in apex_error.t_error )
+        return apex_error.t_error_result;
+    
+    --
+    -- Tag Cleaner function
+    --
+    function demo_tags_cleaner (
+        p_tags  in varchar2,
+        p_case  in varchar2 default 'U') 
+        return varchar2;
+    
+    --
+    -- Tag Synchronisation Procedure
+    --
+    procedure demo_tag_sync (
+        p_new_tags          in varchar2,
+        p_old_tags          in varchar2,
+        p_content_type      in varchar2,
+        p_content_id        in number );
+
+end sample_pkg;
+/
+
 create sequence DEMO_CUST_SEQ start with 100;
 create sequence DEMO_ORD_SEQ start with 100;
 create sequence DEMO_PROD_SEQ start with 100;

end;
/


  MESSAGE         VARCHAR2(4000) not null
);

create or replace package body sample_pkg as
    --
    -- Error Handling function
    --
    function demo_error_handling (
        p_error in apex_error.t_error )
        return apex_error.t_error_result
    is
        l_result          apex_error.t_error_result;
        l_reference_id    number;
        l_constraint_name varchar2(255);
    begin
        l_result := apex_error.init_error_result (
                        p_error => p_error );

        -- If it's an internal error raised by APEX, like an invalid statement or
        -- code which can't be executed, the error text might contain security sensitive
        -- information. To avoid this security problem we can rewrite the error to
        -- a generic error message and log the original error message for further
        -- investigation by the help desk.
        if p_error.is_internal_error then
            -- mask all errors that are not common runtime errors (Access Denied
            -- errors raised by application / page authorization and all errors
            -- regarding session and session state)
            if not p_error.is_common_runtime_error then
                -- log error for example with an autonomous transaction and return
                -- l_reference_id as reference#
                -- l_reference_id := log_error (
                --                       p_error => p_error );
                --
    
                -- Change the message to the generic error message which doesn't expose
                -- any sensitive information.
                l_result.message         := 'An unexpected internal application error has occurred. '||
                                            'Please get in contact with your system administrator and provide '||
                                            'reference# '||to_char(l_reference_id, '999G999G999G990')||
                                            ' for further investigation.';
                l_result.additional_info := null;
            end if;
        else
            -- Always show the error as inline error
            -- Note: If you have created manual tabular forms (using the package
            --       apex_item/htmldb_item in the SQL statement) you should still
            --       use "On error page" on that pages to avoid loosing entered data
            l_result.display_location := case
                                           when l_result.display_location = apex_error.c_on_error_page then apex_error.c_inline_in_notification
                                           else l_result.display_location
                                         end;
    
            -- If it's a constraint violation like
            --
            --   -) ORA-00001: unique constraint violated
            --   -) ORA-02091: transaction rolled back (-> can hide a deferred constraint)
            --   -) ORA-02290: check constraint violated
            --   -) ORA-02291: integrity constraint violated - parent key not found
            --   -) ORA-02292: integrity constraint violated - child record found
            --
            -- we try to get a friendly error message from our constraint lookup configuration.
            -- If we don't find the constraint in our lookup table we fallback to
            -- the original ORA error message.
            if p_error.ora_sqlcode in (-1, -2091, -2290, -2291, -2292) then
                l_constraint_name := apex_error.extract_constraint_name (
                                         p_error => p_error );

                begin
                    select message
                      into l_result.message
                      from demo_constraint_lookup
                     where constraint_name = l_constraint_name;
                exception when no_data_found then null; -- not every constraint has to be in our lookup table
                end;
            end if;

            -- If an ORA error has been raised, for example a raise_application_error(-20xxx, '...')
                -- in a table trigger or in a PL/SQL package called by a process and we
            -- haven't found the error in our lookup table, then we just want to see
            -- the actual error text and not the full error stack with all the ORA error numbers.
            if p_error.ora_sqlcode is not null and l_result.message = p_error.message then
                l_result.message := apex_error.get_first_ora_error_text (
                                        p_error => p_error );
            end if;

            -- If no associated page item/tabular form column has been set, we can use
            -- apex_error.auto_set_associated_item to automatically guess the affected
            -- error field by examine the ORA error for constraint names or column names.
            if l_result.page_item_name is null and l_result.column_alias is null then
                apex_error.auto_set_associated_item (
                    p_error        => p_error,
                    p_error_result => l_result );
            end if;
        end if;

        return l_result;
    end demo_error_handling;
        
    
    ---
    --- Tag Cleaner function
    ---
    function demo_tags_cleaner (
        p_tags  in varchar2,
        p_case  in varchar2 default 'U' ) return varchar2
    is
        type tags is table of varchar2(255) index by varchar2(255);
        l_tags_a        tags;
        l_tag           varchar2(255);
        l_tags          apex_application_global.vc_arr2;
        l_tags_string   varchar2(32767);
        i               integer;
    begin

        l_tags := apex_util.string_to_table(p_tags,',');

        for i in 1..l_tags.count loop
            --remove all whitespace, including tabs, spaces, line feeds and carraige returns with a single space
            l_tag := substr(trim(regexp_replace(l_tags(i),'[[:space:]]{1,}',' ')),1,255);
  
            if l_tag is not null and l_tag != ' ' then
                if p_case = 'U' then
                    l_tag := upper(l_tag);
                elsif p_case = 'L' then
                    l_tag := lower(l_tag);
                end if;
                --add it to the associative array, if it is a duplicate, it will just be replaced
                l_tags_a(l_tag) := l_tag;
            end if;
        end loop;

        l_tag := null;

        l_tag := l_tags_a.first;

        while l_tag is not null loop
            l_tags_string := l_tags_string||l_tag;
            if l_tag != l_tags_a.last then
                l_tags_string := l_tags_string||', ';
            end if;
            l_tag := l_tags_a.next(l_tag);
        end loop;

        return substr(l_tags_string,1,4000);

    end demo_tags_cleaner;

    ---
    --- Tag Synchronisation Procedure
    ---
    procedure demo_tag_sync (
        p_new_tags          in varchar2,
        p_old_tags          in varchar2,
        p_content_type      in varchar2,
        p_content_id        in number )
    as
        type tags is table of varchar2(255) index by varchar2(255);
        l_new_tags_a    tags;
        l_old_tags_a    tags;
        l_new_tags      apex_application_global.vc_arr2;
        l_old_tags      apex_application_global.vc_arr2;
        l_merge_tags    apex_application_global.vc_arr2;
        l_dummy_tag     varchar2(255);
        i               integer;
    begin

        l_old_tags := apex_util.string_to_table(p_old_tags,', ');
        l_new_tags := apex_util.string_to_table(p_new_tags,', ');

        if l_old_tags.count > 0 then --do inserts and deletes

            --build the associative arrays
            for i in 1..l_old_tags.count loop
                l_old_tags_a(l_old_tags(i)) := l_old_tags(i);
            end loop;

            for i in 1..l_new_tags.count loop
                l_new_tags_a(l_new_tags(i)) := l_new_tags(i);
            end loop;

            --do the inserts
            for i in 1..l_new_tags.count loop
                begin
                    l_dummy_tag := l_old_tags_a(l_new_tags(i));
                exception when no_data_found then
                    insert into demo_tags (tag, content_id, content_type )
                        values (l_new_tags(i), p_content_id, p_content_type );
                    l_merge_tags(l_merge_tags.count + 1) := l_new_tags(i);
                end;
            end loop;

            --do the deletes
            for i in 1..l_old_tags.count loop
                begin
                    l_dummy_tag := l_new_tags_a(l_old_tags(i));
                exception when no_data_found then
                    delete from demo_tags where content_id = p_content_id and tag = l_old_tags(i);
                    l_merge_tags(l_merge_tags.count + 1) := l_old_tags(i);
                end;
            end loop;
        else --just do inserts
            for i in 1..l_new_tags.count loop
                insert into demo_tags (tag, content_id, content_type )
                    values (l_new_tags(i), p_content_id, p_content_type );
                l_merge_tags(l_merge_tags.count + 1) := l_new_tags(i);
            end loop;
        end if;

        for i in 1..l_merge_tags.count loop
            merge into demo_tags_type_sum s
            using (select count(*) tag_count
                     from demo_tags
                    where tag = l_merge_tags(i) and content_type = p_content_type ) t
               on (s.tag = l_merge_tags(i) and s.content_type = p_content_type )
             when not matched then insert (tag, content_type, tag_count)
                                   values (l_merge_tags(i), p_content_type, t.tag_count)
             when matched then update set s.tag_count = t.tag_count;

            merge into demo_tags_sum s
            using (select sum(tag_count) tag_count
                     from demo_tags_type_sum
                    where tag = l_merge_tags(i) ) t
               on (s.tag = l_merge_tags(i) )
             when not matched then insert (tag, tag_count)
                                   values (l_merge_tags(i), t.tag_count)
             when matched then update set s.tag_count = t.tag_count;
        end loop;

    end demo_tag_sync;


end sample_pkg;
/

create or replace package sample_data_pkg as

