The PRINT_SQL stored procedure is used to format SQL for being displayed. The procedure allows you to customize the string length and formatting options to help produce a more readable output for SQL statements compared to simply using DBMS_OUTPUT.PUT_LINE.
| Parameter | Description | |
|---|---|---|
| V_STRING | Type: | INPUT |
| Datatype: | CLOB | |
| Default Value: | None | |
| Description | The SQL to be formatted and displayed. | |
| V_LENGHT | Type: | INPUT |
| Datatype: | NUMBER | |
| Default Value: | 100 | |
| Description | The maximum length to allow for each string during formatting. | |
| V_FORMAT | Type: | INPUT |
| Datatype: | VARCHAR2(7) | |
| Default Value: | SIMPLE | |
| Description | The formatting option to use. | |
| Options |
SIMPLE EXTRA MESSAGE |
|
Example
EXEC IN_DB_UTIL.PRINT_SQL(V_EXEC_SQL); EXEC IN_DB_UTIL.PRINT_SQL(V_EXEC_SQL, 150); EXEC IN_DB_UTIL.PRINT_SQL(V_EXEC_SQL, 75, 'EXTRA');
Example output
DECLARE V_EXEC_SQL CLOB; BEGIN V_EXEC_SQL := 'SELECT PRODUCT_MOD_ID, PRODUCT_VERSION, MOD_TIME, PRODUCT_MOD_NOTES FROM INUSER.IN_PRODUCT_MOD_HIST ORDER BY MOD_TIME DESC'; IN_DB_UTIL.PRINT_SQL(V_EXEC_SQL,75,'EXTRA'); END; / SELECT PRODUCT_MOD_ID, PRODUCT_VERSION, MOD_TIME, PRODUCT_MOD_NOTES FROM INUSER.IN_PRODUCT_MOD_HIST ORDER BY MOD_TIME DESC; PL/SQL procedure successfully completed.