How to Compress Data in Pieces with UTL_COMPRESS.LZ_COMPRESS_ADD
Build a compressed BLOB row by row as data is read, without holding all of it uncompressed in one variable first.
PLSQL
PL/SQL (Procedural Language for SQL) is Oracle's proprietary extension to standard SQL. It combines the data manipulation power of SQL with the procedural capabilities of a programming language. PL/SQL allows developers to write complex database-centric applications by incorporating variables, conditions, loops, error handling, and modular programming constructs like procedures and functions. This language is tightly integrated with Oracle databases, enabling efficient data processing and transaction management.
How to Compress Data with UTL_COMPRESS
Shrink text-like BLOB and RAW data to a fraction of its size in gzip format, and see what is not worth compressing.
How to Format Oracle Error Messages with UTL_LMS
Read Oracle error messages in other languages and build your own messages from templates with %s and %d placeholders.
How to Compare Strings with UTL_MATCH (edit distance, Jaro-Winkler)
Find close matches for misspelled names and places with edit distance and Jaro-Winkler similarity scores.
How to Escape Text with UTL_I18N
Make text safe for HTML and XML, turn references back into characters, validate encodings, and look up locale data.
How to Encode Base64 with UTL_ENCODE
Turn text and binary data into Base64 for JSON, XML, and e-mail, including a BLOB encoded in pieces as a data URI.
How to Convert Character Sets with UTL_RAW.CONVERT
Turn text bytes from one character set into another, such as UTF-8 into Latin-1, and see what happens to characters that do not fit.
How to Cast Numbers to RAW with UTL_RAW
Turn PL/SQL numbers into the bytes binary formats use, in big-endian or little-endian order, and read them back.
How to Do Bit Operations with UTL_RAW
Handle RAW values byte by byte, and store and test yes-or-no flags in single bytes with AND, OR, XOR, and NOT.
How to Edit SecureFile LOBs with DBMS_LOB.FRAGMENT_ Procedures
Insert, delete, replace, and move text in the middle of a SecureFile LOB without rewriting everything after it.
How to Write a CLOB to a File with DBMS_LOB.CLOB2FILE
Save a large text value to a file on the database server in one call, such as a CSV export built with LISTAGG.
How to Convert CLOB to BLOB with DBMS_LOB.CONVERTTOBLOB
Encode CLOB text into BLOB bytes in the character set you choose, decode it back, and catch characters that do not convert.
How to Load Files into CLOBs with DBMS_LOB.LOADCLOBFROMFILE
Copy text and binary files from the database server into CLOB and BLOB values, with character set conversion for text.
How to Work with Temporary LOBs in PL/SQL (DBMS_LOB)
Build large text and binary values in PL/SQL with temporary LOBs: create, write, append, read, copy, trim, and free them.
How to Convert DBMS_SQL Cursors to Ref Cursors (TO_REFCURSOR)
Turn a DBMS_SQL cursor into a ref cursor to return it, or a ref cursor into a DBMS_SQL cursor to describe its columns.
How to Fetch Arrays with DBMS_SQL.DEFINE_ARRAY
Fetch the rows of a dynamic query in batches into collections, the DBMS_SQL way of doing BULK COLLECT with a LIMIT.
How to Bind Arrays with DBMS_SQL.BIND_ARRAY
Bind collections to placeholders in dynamic SQL so one execution inserts or updates a row for every element.
How to Describe a Query at Run Time with DBMS_SQL.DESCRIBE_COLUMNS
Learn the names, types, and nullability of a query's columns at run time, and write PL/SQL that prints any query.