I've encountered an oddity using oracle's UTL_LMS.MESSAGE_FORMAT. When targeting %d replacements with variable substitutions, UTL_LMS is quietly failing to replace and disrupting exception-messaging in my call-stack.
UTL_LMS.FORMAT_MESSAGE calls for VARCHAR params, but type-conversion in the doc(11gR2) examples seems like this would be compatible with integer-variables. However, my %d are never being replaced.
I hoped for some ideas/advice. An example of the failure is below.
Running the following:
DECLARE
C_INTRANSITIVE_VERB CONSTANT VARCHAR2(50) := 'breakdance';
C_SUBJECT_NOUN CONSTANT VARCHAR2(50) := 'hobbit';
C_PREPOSITION_OBJECT CONSTANT VARCHAR2(50) := 'wookie';
C_MULTIPLIER CONSTANT INTEGER := 19;
C_TEMPLATE CONSTANT VARCHAR2(400) := 'The %s likes to %s with the %s %d times a day.';
BEGIN
DBMS_OUTPUT.PUT_LINE('My integer is: '|| C_MULTIPLIER);
DBMS_OUTPUT.PUT_LINE(
UTL_LMS.FORMAT_MESSAGE(C_TEMPLATE,C_SUBJECT_NOUN,C_INTRANSITIVE_VERB,C_PREPOSITION_OBJECT,C_MULTIPLIER)
);
END;
/
Yields:
My integer is: 19
The hobbit likes to breakdance with the wookie times a day.
However, the below converts the raw int fine:
DECLARE
C_INTRANSITIVE_VERB CONSTANT VARCHAR2(50) := 'breakdance';
C_SUBJECT_NOUN CONSTANT VARCHAR2(50) := 'hobbit';
C_PREPOSITION_OBJECT CONSTANT VARCHAR2(50) := 'wookie';
C_MULTIPLIER CONSTANT INTEGER := 19;
C_TEMPLATE CONSTANT VARCHAR2(400) := 'The %s likes to %s with the %s %d times a day.';
BEGIN
DBMS_OUTPUT.PUT_LINE('My integer is: '|| C_MULTIPLIER);
DBMS_OUTPUT.PUT_LINE(
UTL_LMS.FORMAT_MESSAGE(C_TEMPLATE,C_SUBJECT_NOUN,C_INTRANSITIVE_VERB,C_PREPOSITION_OBJECT,19)
);
END;
/
My integer is: 19
The hobbit likes to breakdance with the wookie 19 times a day.
The below workaround is undesirable.
DECLARE
C_INTRANSITIVE_VERB CONSTANT VARCHAR2(50) := 'breakdance';
C_SUBJECT_NOUN CONSTANT VARCHAR2(50) := 'hobbit';
C_PREPOSITION_OBJECT CONSTANT VARCHAR2(50) := 'wookie';
C_MULTIPLIER CONSTANT INTEGER := 19;
C_TEMPLATE CONSTANT VARCHAR2(400) := 'The %s likes to %s with the %s %d times a day.';
BEGIN
DBMS_OUTPUT.PUT_LINE('My integer is: '|| C_MULTIPLIER);
DBMS_OUTPUT.PUT_LINE(
UTL_LMS.FORMAT_MESSAGE(C_TEMPLATE,C_SUBJECT_NOUN,C_INTRANSITIVE_VERB,C_PREPOSITION_OBJECT,TO_CHAR(C_MULTIPLIER))
);
END;
/
My integer is: 19
The hobbit likes to breakdance with the wookie 19 times a day.
Thanks!
This post shows how substitution variables can replace hard-coded text in Oracle SQL and SQL*Plus statements. Jump straight to the Substitution Variable Examples if you don't have time to read this whole post, and are trying to solve a specific problem using variables in SQL statements in SQL*Plus.
These substitution variables are stored in Oracle's internal number representation as they are in the database. This allows display formats to be altered without any internal value loss. Substitution variables of BINARY_FLOAT and BINARY_DOUBLE types are similarly created for Oracle BINARY_FLOAT and BINARY_DOUBLE columns.
As far as the database server is concerned, literals and substitution variables are the same thing. Exactly the same behavior occurs when scripts contain placeholders to allow parameters to be sent to them from the command line. So for example, imagine a script called "dummy.sql" containing the following. This can be called from SQL*Plus like this.
A numeric substitution variable holds the full range of Oracle numbers. When a command line undergoes variable substitution, the resulting line length can be no more than: 3000 bytes if it is a line of SQL (like SELECT or INSERT) or PL/SQL text (like BEGIN or CREATE PROCEDURE)
Looking at the documentation at
https://docs.oracle.com/database/121/ARPLS/u_lms.htm#ARPLS71196
The example specifies a PLS_INTEGER rather than a Integer or Number. I have tested this out and using a PLS_INTEGER works where an Integer does not.
I am aware this is not an ideal solution and I am not sure if defining your variables as pls_integer would be an option for you. But may be of some help.
DECLARE
C_INTRANSITIVE_VERB CONSTANT VARCHAR2(50) := 'breakdance';
C_SUBJECT_NOUN CONSTANT VARCHAR2(50) := 'hobbit';
C_PREPOSITION_OBJECT CONSTANT VARCHAR2(50) := 'wookie';
C_MULTIPLIER CONSTANT PLS_INTEGER := 19;
C_TEMPLATE CONSTANT VARCHAR2(400) := 'The %s likes to %s with the %s %d times a day.';
BEGIN
DBMS_OUTPUT.PUT_LINE('My integer is: '|| C_MULTIPLIER);
DBMS_OUTPUT.PUT_LINE(
UTL_LMS.FORMAT_MESSAGE(C_TEMPLATE,C_SUBJECT_NOUN,C_INTRANSITIVE_VERB,C_PREPOSITION_OBJECT,C_MULTIPLIER)
);
END;
Gives the output
My integer is: 19
The hobbit likes to breakdance with the wookie 19 times a day.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With