Oracle UTL_PG
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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 DEFINER
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 Warning if enabled: Picture mask overrides mask option USAGE ... to DISPLAY
ORA-08499 Warning if enabled: 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 EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/utlpg.sql
{ORACLE_HOME}/rdbms/admin/prvtpgb.plb
-- if created in 12.2.0.1 as SYS produces the following output

SQL> @?/rdbms/admin/utlpg.sql

SP2-0808: Package created with compilation warnings

Errors for PACKAGE UTL_PG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1 PLW-05018: unit UTL_PG omitted optional AUTHID clause; default value
DEFINER used

41/26 PLW-07203: parameter 'WMSGBLK' may benefit from use of the NOCOPY
compiler hint

42/10 PLW-05019: the language element near keyword RESTRICT_REFERENCES
is deprecated beginning with version 11.2

173/26 PLW-07203: parameter 'WMSGBLK' may benefit from use of the NOCOPY
compiler hint

174/10 PLW-05019: the language element near keyword RESTRICT_REFERENCES
is deprecated beginning with version 11.2

293/38 PLW-07203: parameter 'WMSGBLK' may benefit from use of the NOCOPY
compiler hint

294/10 PLW-05019: the language element near keyword RESTRICT_REFERENCES
is deprecated beginning with version 11.2

417/38 PLW-07203: parameter 'WMSGBLK' may benefit from use of the NOCOPY
compiler hint

418/10 PLW-05019: the language element near keyword RESTRICT_REFERENCES
is deprecated beginning with version 11.2

535/10 PLW-05019: the language element near keyword RESTRICT_REFERENCES
is deprecated beginning with version 11.2

571/10 PLW-05019: the language element near keyword RESTRICT_REFERENCES
is deprecated beginning with version 11.2

597/10 PLW-05019: the language element near keyword RESTRICT_REFERENCES
is deprecated beginning with version 11.2

629/17 PLW-07203: parameter 'WMSGTEXT' may benefit from use of the NOCOPY
compiler hint

630/17 PLW-07203: parameter 'WMSGFILL' may benefit from use of the NOCOPY
compiler hint

631/10 PLW-05019: the language element near keyword RESTRICT_REFERENCES
is deprecated beginning with version 11.2

SQL> @?/rdbms/admin/prvtpgb.plb

SP2-0810: Package Body created with compilation warnings

Errors for PACKAGE BODY UTL_PG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
18/20 PLW-07203: parameter 'WMSGBLK' may benefit from use of the NOCOPY compiler hint
32/20 PLW-07203: parameter 'WMSGBLK' may benefit from use of the NOCOPY compiler hint
47/20 PLW-07203: parameter 'WMSGBLK' may benefit from use of the NOCOPY compiler hint
63/20 PLW-07203: parameter 'WMSGBLK' may benefit from use of the NOCOPY compiler hint
99/21 PLW-07203: parameter 'WMSGTEXT' may benefit from use of the NOCOPY compiler hint
100/21 PLW-07203: parameter 'WMSGFILL' may benefit from use of the NOCOPY compiler hint
121/26 PLW-07203: parameter 'WMSGBLK' may benefit from use of the NOCOPY compiler hint
147/26 PLW-07203: parameter 'WMSGBLK' may benefit from use of the NOCOPY compiler hint
172/20 PLW-07203: parameter 'WMSGBLK' may benefit from use of the NOCOPY compiler hint
196/20 PLW-07203: parameter 'WMSGBLK' may benefit from use of the NOCOPY compiler hint
243/17 PLW-07203: parameter 'WMSGTEXT' may benefit from use of the NOCOPY compiler hint
244/17 PLW-07203: parameter 'WMSGFILL' may benefit from use of the NOCOPY compiler hint

Synonym created.

Grant succeeded
Subprograms
MAKE_NUMBER_TO_RAW_FORMAT NUMBER_TO_RAW_FORMAT WMSG
MAKE_RAW_TO_NUMBER_FORMAT RAW_TO_NUMBER WMSGCNT
NUMBER_TO_RAW RAW_TO_NUMBER_FORMAT  
 
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
Built-in Functions
Built-in Packages
Conversion Functions
UTL_RAW
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx