Oracle UTL_PG
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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 19c
What's New In 20c-21c

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