oraopensource / oos-utils Goto Github PK
View Code? Open in Web Editor NEWCommon PL/SQL utility scripts
License: MIT License
Common PL/SQL utility scripts
License: MIT License
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;
-- 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.
Create a new REST package.
Things to add:
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
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.
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
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;
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
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;
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.
To rejoin a session you need to call the function twice. It also doesn't appear that V('APP_USER
)` is being set.
sys
(security)cache-control
optionAs part of the build process check that grants to create table, packages are allowed. References #30
i.e. use oos_util_string.tab_vc2_arr
in a queryable table.
Have a list given a file exenstion it'll return the mime type.
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.
Copy from logger.tochar
.
Can't use to_char
since conflicts with system to_char
function.
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.
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 ;
-- 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
;
/
Supports both APEX 4 and 5:
coalesce(apex_application.g_edit_cookie_session_id, v('APP_BUILDER_SESSION'))
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
See Logger for details on this for tables that can be re-runable (i.e. columns added, constraints, etc).
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;
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...
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.
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;
checks (based on date string and date format) if date can be converted to valid date
Already in Logger: https://github.com/OraOpenSource/Logger/blob/master/docs/Logger%20API.md#sprintf
Should just copy it.
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
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;
Suppose you do the following:
abc
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:
Similar to string_to_table
but only takes in p_clob and the piped row is of type clob. See #32 for example
Similar to Logger, add a grants and synonyms script.
#30
A function that returns if user is logged into the APEX application for added security on third party report engines like:
Reverse engineering an APEX URL. See apex_page.get_url for the parameters that this should return.
Update:
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.
Move all the is_xyz
to a oos_util_validation
package?
Hi @martindsouza
I`d created some gists maybe some interesting stuff for the oos_util_apex package...
Get BLOB / Filename / Mime_Type from apex_application_temp_files
https://gist.github.com/Dani3lSun/461a6d6f6eb991107802
Wrapper for APEX_ZIP.ADD_FILE for CLOB content
https://gist.github.com/Dani3lSun/46e2e75768cfae7175d6
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
percentage usage of disk space of an Oracle XE 11g database
https://gist.github.com/Dani3lSun/c2e0f8bb249ec2ae7bd2
Given a byte length, return B, KB, MB, GB, TB value. Could also have a param to display commas for each thousand.
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.
/**
* 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;
Similar to https://api.jquery.com/jquery.extend/
Use APEX_JSON
. May need to make this package conditionally compile.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.