Saturday, October 22, 2005

simple PL/SQL printf()

One can consider using utl_lms.format_message for printf()-like formatting. For example:
SQL> SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
PL/SQL Release 10.1.0.4.0 - Production
CORE 10.1.0.4.0 Production
TNS for 32-bit Windows: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production

SQL> DESC utl_lms
FUNCTION FORMAT_MESSAGE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FORMAT VARCHAR2 IN
ARGS UNDEFINED IN
...

SQL> VAR v VARCHAR2(4000);
SQL> SET AUTOPRINT ON
SQL> EXEC :v := utl_lms.format_message('%s %d %d %d', 'vbegun', 1976, 11, 27);

PL/SQL procedure successfully completed.

V
-----------------
vbegun 1976 11 27

SQL> EXEC :v := utl_lms.format_message('%s %% %d %d %d %d %d', 'test', 1, 2, 3, 4, 5);

PL/SQL procedure successfully completed.

V
-----------------
test % 1 2 3 4 5
Starting from 10g Oracle PL/SQL functions can support variable numbers of arguments like C does. For now it's undocumented and not exposed to be used by developers. Anyway, it's a big step forward. Let's see what "11g" will have.

2 comments:

Anonymous said...

а как это можно использовать?

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create or replace procedure un(v_xObjs ...) is
2 begin
3 null;
4 end;
5 /

Warning: Procedure created with compilation errors.

SQL> show errors;
Errors for PROCEDURE UN:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/14 PLS-00999: implementation restriction (may be temporary) ellipsis
not allowed in this context

Vladimir Begun said...

> а как это можно использовать?

For now it's undocumented and not exposed to be used by developers. Anyway, it's a big step forward. Let's see what "11g" will have.