Oracle UTL_PG
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Procedural Gateway specific routines to manipulate raws. This package provides SQL functions for raws which convert various non-Oracle number formats to/from raws. Included is conversion support for: IBM/370 vs COBOL II
AUTHID CURRENT_USER
Data Types  
Dependencies
STANDARD only    
Documented No
Exceptions
Error Code Reason
ORA-08401 Invalid compiler name
ORA-08414 Error encounterred
ORA-08429 Raw data has invalid digit in display type data
ORA-08430 Raw data missing leading sign
ORA-08431 Raw data missing zero as defined in picture
ORA-08433 Invalid picture type in convert raw to number
ORA-08434 Raw data has invalid trailing sign
ORA-08435 Picture mask missing leading sign
ORA-08436 Raw data has invalid sign digit
ORA-08437 Invalid picture type in picture mask
ORA-08441 Closed parenthesis is missing in picture mask
ORA-08443 Syntax error in BLANK WHEN ZERO clause in mask options
ORA-08444 Syntax error in JUSTIFIED clause in mask options
ORA-08445 Syntax error in SIGN clause in mask options
ORA-08446 Syntax error in SYNCHRONIZED clause in mask options
ORA-08447 Syntax error in USAGE clause in mask options
ORA-08448 Syntax error in DECIMAL-POINT environment clause
ORA-08449 Invalid numeric symbol found in picture mask
ORA-08450 Invalid specification of CR in picture mask
ORA-08451 Invalid specification of DB in picture mask
ORA-08452 Specification of E in picture mask unsupported
ORA-08453 More than one V symbol specified in picture mask
ORA-08454 More than one S symbol specified in picture mask
ORA-08455 Syntax error in CURRENCY SIGN environment clause
ORA-08456 No sign in picture mask but SIGN clause in mask options
ORA-08457 Syntax error in SEPARATE CHARACTER option of SIGN clause
ORA-08460 Invalid environment clause in environment parameter
ORA-08462 Raw buffer contains invalid decimal data
ORA-08463 Overflow converting decimal number to Oracle number
ORA-08464 Input raw decimal data contains more than 42 digits
ORA-08466 Raw buffer length <len> to short for <type>
ORA-08468 Mask option <option> is not supported
ORA-08469 Must use the SIGN IS SEPARATE clause in mask options
ORA-08498 Picture mask overrides mask option USAGE ... to DISPLAY
ORA-08499 Picture mask options ignored by UTL_PG
First Available 1993 but is not part of the default database installation
Security Model Owned by SYS with no granted privileges
Source {ORACLE_HOME}/rdbms/admin/utlpg.sql
{ORACLE_HOME}/rdbms/admin/prvtpgb.plb
Subprograms
 
MAKE_NUMBER_TO_RAW_FORMAT
Make a number_to_raw format conversion specification used to convert an Oracle number of declared precision and scale to a raw byte-string in the remote host internal format specified by mask, maskopts, envrnmnt, compname, and compopts utl_pg.make_number_to_raw_format(
mask     IN  VARCHAR2,
maskopts IN  VARCHAR2,
envrnmnt IN  VARCHAR2,
compname IN  VARCHAR2,
compopts IN  VARCHAR2,
nlslang  IN  VARCHAR2,
wind     IN  BOOLEAN,
wmsgbsiz IN  BINARY_INTEGER,
wmsgblk  OUT RAW) RETURN RAW;
set serveroutput on

DECLARE
 r1 RAW(32767);
 r2 RAW(32767);
BEGIN
  r1 := utl_pg.make_number_to_raw_format('999.00', 'COMP-4', 'DECIMAL-POINT IS COMMA', 'IBMVSCOBOLII', NULL, 'AMERICAN_AMERICA.WE8EBCDIC37C', TRUE, 1024, r2);

  dbms_output.put_line('R1: ' || r1);
  dbms_output.put_line(' ');
  dbms_output.put_line('R2: ' || r2);
END;
/
 
MAKE_RAW_TO_NUMBER_FORMAT
Make a raw_to_number format conversion specification used to convert a raw byte-string from the remote host internal format specified by mask, maskopts, envrnmnt, compname, and compopts into an Oracle number of comparable precision and scale utl_pg.make_raw_to_number_format(
mask     IN  VARCHAR2,
maskopts IN  VARCHAR2,
envrnmnt IN  VARCHAR2,
compname IN  VARCHAR2,
compopts IN  VARCHAR2,
nlslang  IN  VARCHAR2,
wind     IN  BOOLEAN,
wmsgbsiz IN  BINARY_INTEGER,
wmsgblk  OUT RAW)
RETURN RAW;
set serveroutput on

DECLARE
 r1 RAW(32767);
 r2 RAW(32767);
BEGIN
  r1 := utl_pg.make_raw_to_number_format('999.00', 'COMP-4', 'DECIMAL-POINT IS COMMA', 'IBMVSCOBOLII', NULL, 'AMERICAN_AMERICA.WE8EBCDIC37C', TRUE, 1024, r2);

  dbms_output.put_line('R1: ' || r1);
  dbms_output.put_line(' ');
  dbms_output.put_line('R2: ' || r2);
END;
/
 
NUMBER_TO_RAW
Convert an Oracle number to a raw byte-string in the remote host internal format specified by mask, maskopts, envrnmnt, compname, and compopts utl_pg.number_to_raw(
n        IN  NUMBER,
mask     IN  VARCHAR2,
maskopts IN  VARCHAR2,
envrnmnt IN  VARCHAR2,
compname IN  VARCHAR2,
compopts IN  VARCHAR2,
nlslang  IN  VARCHAR2,
wind     IN  BOOLEAN,
wmsgbsiz IN  BINARY_INTEGER,
wmsgblk  OUT RAW)
RETURN RAW;
TBD
 
NUMBER_TO_RAW_FORMAT
Convert, according to the number_to_raw conversion format n2rfmt, an Oracle number numval to a raw byte-string in the remote host internal format utl_pgnumber_to_raw_format(numval IN NUMBER, n2rfmt IN RAW) RETURN RAW;
TBD
 
RAW_TO_NUMBER
Convert a raw byte-string from the remote host internal format specified by mask, maskopts, envrnmnt, compname, and compopts into an Oracle number utl_pg.raw_to_number(
r        IN  RAW,
mask     IN  VARCHAR2,
maskopts IN  VARCHAR2,
envrnmnt IN  VARCHAR2,
compname IN  VARCHAR2,
compopts IN  VARCHAR2,
nlslang  IN  VARCHAR2,
wind     IN  BOOLEAN,
wmsgbsiz IN  BINARY_INTEGER,
wmsgblk  OUT RAW)
RETURN NUMBER;
TBD
 
RAW_TO_NUMBER_FORMAT
Convert, according to the raw_to_number conversion format r2nfmt, a raw byte-string rawval in the remote host internal format to an Oracle number utl_pg.raw_to_number_format(rawval IN RAW, r2nfmt IN RAW) RETURN NUMBER;
TBD
 
WMSG
Extract a warning message specified by wmsgitem from wmsgblk utl_pg.wmsg(
wmsgblk  IN  RAW,
wmsgitem IN  BINARY_INTEGER,
wmsgno   OUT BINARY_INTEGER,
wmsgtext OUT VARCHAR2,
wmsgfill OUT VARCHAR2)
RETURN BINARY_INTEGER;
TBD
 
WMSGCNT
Tests a wmsgblk to determine how many warnings (if any) may be present utl_pg.wmsgcnt(wmsgblk IN RAW) RETURN BINARY_INTEGER;
TBD

Related Topics
Conversion Functions
Packages
UTL_RAW

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved