oraopensource/oos-utils

Common PL/SQL utility scripts

oos-utils's Issues

Bitor and Bitxor functions

https://connormcdonald.wordpress.com/2016/02/08/bitwise-operations/

Assert proc

Boolean to Varchar2

I find this simple function useful for debugging.

create or replace FUNCTION bto_char (b              BOOLEAN,
                                     true_value     VARCHAR2 DEFAULT 'TRUE',
                                     false_value    VARCHAR2 DEFAULT 'FALSE')
   RETURN VARCHAR2 IS
BEGIN
   IF b IS NULL
   THEN
      RETURN NULL;
   ELSE
      IF b
      THEN
         RETURN true_value;
      ELSE
         RETURN false_value;
      END IF;
   END IF;
END bto_char;

Dynamic IN clause for SELECT queries

-- Without actual dynamic SQL, effectively have an IN clause that is dynamically populated.
-- Very handy for ApEx Interactive Reports that need some pre-run limiting, without giving up too much flexibility. 
-- Technique learned from https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement
-- Assume P2_TEAM_UIDS is a hidden page item, a varchar2, comma-delmiited list of numerical primary keys, that was populated with a pick list or something like that. So, this one report will have the "UNION", so to speak, of whatever set of cooperating teams specified by being included in the P2_TEAM_UIDs page item.

select entity_UID, entity_name, description, etcetera
from entity_list
where expiration_date is null 
and entity_group_UID in (--P2_TEAM_UIDS  *must* be comma separate integer UIDs
          select regexp_substr(:P2_TEAM_UIDS,'[^,]+', 1, level) from dual
          connect by regexp_substr(:P2_TEAM_UIDS, '[^,]+', 1, level) is not null
        )
;

So when you need to specify a group of groups, but look at all the constituent members together, this can achieve it with pretty few special parts.

REST Utils

Create a new REST package.

Things to add:

• error: This will pass in a record table object which then loops over the errors, generates the proper JSON object (use APEX 4.2 apex_javascript for this) and then calls the owa_util:

    owa_util.status_line(nstatus => 400, creason => '',  bclose_header => false);
    owa_util.mime_header(ccontent_type => 'application/json');
    owa_util.http_header_close;

Error ideas: http://www.vinaysahni.com/best-practices-for-a-pragmatic-restful-api

Ideas:
http://raml.org/
http://swagger.io/

Codes:
For oos-utils: https://twiki.cern.ch/twiki/bin/view/DB/DevelopingOracleRestfulServices There’s a link there for REstful http statuses: http://www.restapitutorial.com/httpstatuscodes.html

Add Cache-control to oos_util_apex.download_file

See http://joelkallman.blogspot.ca/2014/03/yet-another-post-how-to-link-to.html for example.

https://developers.google.com/web/fundamentals/performance/optimizing-content-efficiency/http-caching?hl=en provides a list of Cache-control options.

Prevent from installing in oos_util schema

This will prevent anyone from creating a separate schema called oos_util. Since synonyms can't resolve this all references would need to be oos_util.oos_util.... which isn't good for developers.
#30

is_alpha/numeric/alphanumeric

e.g. http://www.techonthenet.com/oracle/questions/isnumeric.php

or..

function is_numeric(p_str in varchar2) return BOOLEAN
as
    l_num NUMBER;
begin
    l_num := to_number(p_str);

    return true;

exception 
    when value_error
    then 
        return false;
end is_numeric;

String to Table

Similar to apex_util.string_to_table but takes in a vc2 or a clob.

Can also extend to support returning a table of clobs.

Will be used for #4

clob2blob

Similar to this: http://www.sql.ru/forum/335822/clob2blob

Had to make slight adjustment since error occurred on larger files.

create or replace function clob2blob(p_clob clob) return blob as
  v_blob blob;
  v_str varchar2(32767);
  v_offset integer := 1;
  v_length integer;
  v_sum integer;
begin
  dbms_lob.createTemporary(v_blob, false, dbms_lob.SESSION);
  v_length := dbms_lob.getLength(p_clob);
  loop
    v_sum := 30000;
    dbms_lob.read(p_clob, v_sum, v_offset, v_str);
    dbms_lob.writeAppend(v_blob, v_sum, utl_raw.cast_to_raw(v_str));
    exit when v_offset + v_sum >= v_length;
    v_offset := v_offset + v_sum;
  end loop;
  return v_blob;
end;

clob_to_table

Similar to apex_util.string_to_table (https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_util.htm#AEAPI185) create a clob_to_table and give option to define the max length for each entry.

oos_util_apex.join_session not working properly

To rejoin a session you need to call the function twice. It also doesn't appear that V('APP_USER)` is being set.

oos_util_apex Updates

• Prefix calls with sys (security)
• See http://joelkallman.blogspot.com/2014/03/yet-another-post-how-to-link-to.html for adding cache-control option

Add pre-requisite check

As part of the build process check that grants to create table, packages are allowed. References #30

Create demo on how to query from type in table.

i.e. use oos_util_string.tab_vc2_arr in a queryable table.

Mime Type Lookup

Have a list given a file exenstion it'll return the mime type.

Percentage usage of disk space of an Oracle XE 11g database

From @Dani3lSun (#38)

Percentage usage of disk space of an Oracle XE 11g database
https://gist.github.com/Dani3lSun/c2e0f8bb249ec2ae7bd2

Create a new packaged called oos_util_dba but this package won't be part of the default install as it requires DBA privileges. Will require modifications to the install scripts to exclude the DBA package.

to_char

Copy from logger.tochar.

Can't use to_char since conflicts with system to_char function.

Create APEX session

See http://www.talkapex.com/2012/08/how-to-create-apex-session-in-plsql.html for example

May need to modify for APEX 5.0 as it may not allow for multiple calls for a session to be created.

String to table

CREATE OR REPLACE PACKAGE apex_func
/**
  *  @PARAM   p_string colon delimited string to parse
  *  @RETURN  one coloum table
**/
AS
TYPE tbl_of_varchar
IS
  TABLE OF VARCHAR2 (32767);

  FUNCTION f_string_to_table(
      p_string IN VARCHAR2
      )
    RETURN tbl_of_varchar PIPELINED;
END apex_func ;

CREATE OR REPLACE PACKAGE BODY apex_func
AS
  FUNCTION f_string_to_table(
      p_string IN VARCHAR2
      )
    RETURN tbl_of_varchar PIPELINED
  IS
    v_temp apex_application_global.vc_arr2;
  BEGIN
    v_temp := apex_util.string_to_table(p_string);
    FOR I IN 1 .. v_temp.COUNT
    LOOP
      PIPE ROW (v_temp (I));
    END LOOP;
  END;
END apex_func ;

Excerpt by word count

-- SQL inline version:
-- The target word count is indicated in two places, and should agree: the second value in the curly-braces range specification in the call to regexp_substr, and then the first (and only) value in the call to regexp_instr 
select REGEXP_SUBSTR( QT.LONG_DESCRIPTION, '((\w*\W){1,8})') || DECODE(SIGN(REGEXP_INSTR(QT.LONG_DESCRIPTION, '((\w*\W){8,})')),1,'...') EXCERPT,
  qt.long_description 
from ( select 'Some really, really long piece of text with many words in it. Sometimes you do not want to just chop the text and you also want a nice and neat portion to display.' long_description from dual ) qt
;


-- as a nice pl/sql function:

/**
 * Return a conventional English ellipsis-indicated excerptation of a longer text.
 *
 * @author Justin Warwick
 * @created 2015-04-24
 * @param p_long_description raw longer text
 * @param p_max_count target word count, but it is flexible. 
 *        If the text is too short, no abbreviation is performed. 
 *        If a negative word count is supplied, treat it as the number of words to leave off the end.
 * @return varchar2 formatted excerpt
 */

create or replace function excerpt_by_wordcount(p_long_description in varchar2, p_max_count in number := 8) return varchar2 as
  l_word_count number;
  l_max_count number;
BEGIN
  if p_max_count < 0 then
    l_word_count := regexp_count(p_long_description,'(\w*\W*( |$))');
    l_max_count := l_word_count + p_max_count;
    if (l_max_count < 1) then
      l_max_count := l_word_count;
    end if;
  else
    l_max_count := p_max_count;
  end if;

  return REGEXP_SUBSTR( p_long_description, '((\w*\W){1,'||l_max_count||'})') || CASE SIGN(REGEXP_INSTR(p_long_description, '((\w*\W){'||l_max_count||',})')) WHEN 1 THEN '...' END;  
END
;
/

APEX: Is Developer

Supports both APEX 4 and 5:
coalesce(apex_application.g_edit_cookie_session_id, v('APP_BUILDER_SESSION'))

Look-up Tables

Hello Martin,

I would like to add Look-ups and Look-up values (Since I am from Oracle APPS background using the same terminology) to hold some common values being used in the application. Different set of values for each purpose at place and these can be used for validation or can be used for LOV etc.,

Please share your thoughts.

Thanks,
Pavan

Replace for Clob

See: http://dbaora.com/ora-22828-input-pattern-or-replacement-parameters-exceed-32k-size-limit/

Script outline to have re-usable create table scripts

See Logger for details on this for tables that can be re-runable (i.e. columns added, constraints, etc).

Date To Unix Timestamp

Honestly not sure how many would benefit from this, but here it is. Note this version includes processing the local timezone

CREATE OR REPLACE FUNCTION date_to_unix (in_date DATE)
   RETURN NUMBER
IS
   l_epoch   NUMBER;
BEGIN
   SELECT   (in_date - TO_DATE ('19700101', 'yyyymmdd')) * 86400
          - TO_NUMBER (SUBSTR (TZ_OFFSET (SESSIONTIMEZONE), 1, 3)) * 3600
     INTO l_epoch
     FROM DUAL;

   RETURN l_epoch;
END date_to_unix;

Check if an APEX item is rendered or not

From @Dani3lSun (#38)

Check if an APEX item is rendered or not (I think Patrick Wolf posted about it some time ago)
https://gist.github.com/Dani3lSun/f5e620e748fb09e1f352

Reason / Example usage: I use it if I have to do custom page level validations (and a item gets not rendered because of an condition or authorization). If using item level validation APEX itself does this check, but on page level a not null validation would fire, because APEX thinks the item is there...

Trim APEX page items

When an APEX page is submitted, leading and trailing white spaces aren't trimmed by default. Create a procedure that will automatically trim and update the page items. This process can then be called as a application level process.

blob2clob

Add blob2clob tool

  /**
   * Converts blob to clob
   *
   * Notes:
   *  - Copied from http://stackoverflow.com/questions/12849025/convert-blob-to-clob
   *
   * Related Tickets:
   *  -
   *
   * @author Martin D'Souza
   * @created 02-Mar-2014
   * @param p_blob blob to be converted to clob
   * @return clob
   */
  function blob2clob(
    p_blob in blob)
    return clob
  as
    l_scope logger_logs.scope%type := gc_scope_prefix || 'blob2clob';
    l_params logger.tab_param;

    l_clob         clob;
    l_dest_offsset integer := 1;
    l_src_offsset  integer := 1;
    l_lang_context integer := dbms_lob.default_lang_ctx;
    l_warning      integer;

  begin
    logger.log('START', l_scope, null, l_params);
    logger.log(logger.sprintf('p_blob size: %s', dbms_lob.getLength(p_blob)), l_scope);

    if p_blob is null then
      logger.log('p_blob is null', l_scope);
      return null;
    end if;

    logger.log('Before createTemporary', l_scope);
    dbms_lob.createTemporary(lob_loc => l_clob
                            ,cache   => false);

    logger.log('Before converttoclob', l_scope);
    dbms_lob.converttoclob(dest_lob     => l_clob
                          ,src_blob     => p_blob
                          ,amount       => dbms_lob.lobmaxsize
                          ,dest_offset  => l_dest_offsset
                          ,src_offset   => l_src_offsset
                          ,blob_csid    => dbms_lob.default_csid
                          ,lang_context => l_lang_context
                          ,warning      => l_warning);


    logger.log(logger.sprintf('l_clob_size: %s', dbms_lob.getLength(l_clob)), l_scope);
    logger.log('END', l_scope);

    return l_clob;
  exception
    when others then
      logger.log_error('Unhandled Exception', l_scope, null, l_params);
      raise;
  end blob2clob;

Is_Date

checks (based on date string and date format) if date can be converted to valid date

sleep wrapper

Images

http://sql-plsql-de.blogspot.ro/2013/06/image-processing-in-der-datenbank.html

Add sprintf function

Already in Logger: https://github.com/OraOpenSource/Logger/blob/master/docs/Logger%20API.md#sprintf

Should just copy it.

Sprintf order is invalid

For %s1% type replacement it will replace %s10 as %s1 instead because of the order of the replacement. Fix: change the loop order of sprintf

Check that all packages have "/" on last line

#30

Checking page_id and app_user for creating session

In the package oos_util_apex in the procedure create_session the parameter p_user_name and p_page_id are not checked.
If using collections or other features relying on a session created with that function there can be errors when the page or the user doesn't exist.

plz check these values like:
select page_id from apex_application_pages p where p.page_id = p_page_id and p.application_id = p_app_id;

and
select u.user_name from apex_workspace_apex_users u where u.workspace_id = l_workspace_id;

oos_util_apex.join_session doesn't map up to session changes

Suppose you do the following:

• Set P1_X = abc
• Join session (in plsql)
• Set P1_X = def
• Query P1_X in SQL, it'll still point to abc

APEX download

Sample code:

 -- set the size so the browser knows how much to download
  htp.p('Content-length: ' || DBMS_LOB.GETLENGTH(l_blob));
  -- the filename will be used by the browser if the users does a save as
  htp.p('Content-Disposition:  attachment; filename="test.png"');
  -- close the headers
  owa_util.http_header_close;
  -- download the BLOB
  wpg_docload.download_file( l_blob );

  apex_application.stop_apex_engine;

Recommended changes:

• add clob support
• content-disposition should be param
• include the mime-type

Create clob_to_table

Similar to string_to_table but only takes in p_clob and the piped row is of type clob. See #32 for example

Create grants and synonym script

Similar to Logger, add a grants and synonyms script.
#30

Is user logged in

A function that returns if user is logged into the APEX application for added security on third party report engines like:

• PLPDF
• Jasper
• BIRT

Parse an APEX URL

Reverse engineering an APEX URL. See apex_page.get_url for the parameters that this should return.

Update:

• Should parse all "normal" URLs and parse into logical structures
• Need to support new APEX 20.1 friendly URLs

Post documentation on readthedocs.org

Once the md files have been created see hoe we can implement such a site for nice documentation.

http://oracle.readthedocs.org/en/latest/plsql/cache/alternatives/deterministic.html

Could be useful for the Logger project.

http://oos-utils.readthedocs.org/en/latest/README/

Move all validation functions to a validation package?

Move all the is_xyz to a oos_util_validation package?

recommendations for oos_util_apex

Hi @martindsouza
I`d created some gists maybe some interesting stuff for the oos_util_apex package...

1. Get BLOB / Filename / Mime_Type from apex_application_temp_files
   https://gist.github.com/Dani3lSun/461a6d6f6eb991107802

2. Wrapper for APEX_ZIP.ADD_FILE for CLOB content
   https://gist.github.com/Dani3lSun/46e2e75768cfae7175d6

3. check if an APEX item is rendered or not (I think Patrick Wolf posted about it some time ago)
   https://gist.github.com/Dani3lSun/f5e620e748fb09e1f352

4. percentage usage of disk space of an Oracle XE 11g database
   https://gist.github.com/Dani3lSun/c2e0f8bb249ec2ae7bd2

Human Readable file size

Similar to http://stackoverflow.com/questions/840966/what-is-an-elegant-way-to-return-a-readable-file-size-of-a-file-stored-in-an-o

Given a byte length, return B, KB, MB, GB, TB value. Could also have a param to display commas for each thousand.

Create full build script

Getting error converting blob to clob

On first call to oos_util_lob.blob2clob get the following error:

Error report -
ORA-01426: numeric overflow
ORA-06512: at "GIFFY.OOS_UTIL_LOB", line 17
ORA-06512: at line 11
01426. 00000 -  "numeric overflow"
*Cause:    Evaluation of an value expression causes an overflow/underflow.
*Action:   Reduce the operands.

Create CLOBs by concatenating VARCHAR2s

/**
* Procedure concatenates a VARCHAR2 to an CLOB.
* It uses another VARCHAR2 as a buffer until it reaches 32767 characters.
* Then it flushes the current buffer to the CLOB and resets the buffer using
* the actual VARCHAR2 to add.
* Your final call needs to be done setting p_eof to TRUE in order to
* flush everything to the CLOB.
*
* @param p_clob        The CLOB buffer.
* @param p_vc_buffer   The intermediate VARCHAR2 buffer. (must be VARCHAR2(32767))
* @param p_vc_addition The VARCHAR2 value you want to append.
* @param p_eof         Indicates if complete buffer should be flushed to CLOB.
*/
PROCEDURE clob_vc_concat( p_clob IN OUT NOCOPY CLOB
                        , p_vc_buffer IN OUT NOCOPY VARCHAR2
                        , p_vc_addition IN VARCHAR2
                        , p_eof IN BOOLEAN DEFAULT FALSE
                        )
AS
BEGIN 
    -- Standard Flow
    IF NVL(LENGTHB(p_vc_buffer), 0) + NVL(LENGTHB(p_vc_addition), 0) < 32767 THEN
        p_vc_buffer := p_vc_buffer || p_vc_addition;
    ELSE
        IF p_clob IS NULL THEN
            dbms_lob.createtemporary(p_clob, TRUE);
        END IF;
        dbms_lob.writeappend(p_clob, LENGTH(p_vc_buffer), p_vc_buffer);
        p_vc_buffer := p_vc_addition;
    END IF;

 -- Full Flush requested
    IF p_eof THEN
        IF p_clob IS NULL THEN
            p_clob := p_vc_buffer;
        ELSE
            dbms_lob.writeappend(p_clob, LENGTH(p_vc_buffer), p_vc_buffer);
        END IF;
    p_vc_buffer := NULL;
    END IF;
END clob_vc_concat;

JSON Support to extend a JSON string

Similar to https://api.jquery.com/jquery.extend/

Use APEX_JSON. May need to make this package conditionally compile.