Format dbms_output.put_line for text strings over 255 characters long

Every experienced PL/SQL developer knows the importance of dbms_output.put_line procedure which happened to have a limitation that makes it difficult to print large text strings (over 255 bytes). It only allows to print up to 255 characters on a single line.

In his blog post, Tom Kyte suggests using the following simple procedure:

create or replace procedure p( p_string in varchar2 )
is
   l_string long default p_string;
begin
   loop
     exit when l_string is null;
     dbms_output.put_line( substr( l_string, 1, 250 ) );
     l_string := substr( l_string, 251 );
   end loop;
end;
/

The obvious drawback of this method is the lines wrapped up (i.e. broken up) at every 250th random character.

Below is an improved version of this procedure which I use all the time in my work:

CREATE OR REPLACE PROCEDURE pl (p_text       VARCHAR2,
                                p_chunk_size NUMBER:=255)
AS
    v_len           NUMBER:=LENGTH(p_text);
    v_cnt           NUMBER:=0;
    v_chunk_size    NUMBER:=LEAST(p_chunk_size, 255);
    v_LF_postion    NUMBER:=INSTR(p_text, CHR(10));
BEGIN
    IF p_text IS NULL THEN
        RETURN;
    END IF;
    
    IF v_len<=v_chunk_size THEN
        DBMS_OUTPUT.PUT_LINE(p_text);
        RETURN;
    END IF;
    
    IF v_LF_postion<=v_chunk_size THEN
        v_chunk_size:=v_LF_postion-1;
    ELSE
        FOR i IN REVERSE 1..v_chunk_size LOOP
            IF SUBSTR(p_text, i, 1) IN (CHR(32), CHR(44)) THEN --space or comma
                v_chunk_size:=i;
                EXIT;
            END IF;
        END LOOP;    
    END IF;
    
    DBMS_OUTPUT.PUT_LINE(SUBSTR(p_text, 1, v_chunk_size));
    pl(p_text=> SUBSTR(p_text, v_chunk_size+2), p_chunk_size=>p_chunk_size);
END;
/

This procedure uses recursive calls to itself and splits the line at every line feed character (ASCII CODE 10) if the line is under 255 characters long, otherwise, it splits it at the most recent space or comma characters that precede 255th symbol.

For the demonstration purpose, let’s see how it will print out its own text:

SQL> DECLARE
  2    v_sql VARCHAR2(4000):=
  3    'CREATE OR REPLACE PROCEDURE pl (p_text       VARCHAR2,
  4                                  p_chunk_size NUMBER:=255)
  5      AS
  6          v_len           NUMBER:=LENGTH(p_text);
  7          v_cnt           NUMBER:=0;
  8          v_chunk_size    NUMBER:=LEAST(p_chunk_size, 255);
  9          v_LF_postion    NUMBER:=INSTR(p_text, CHR(10));
 10      BEGIN
 11          IF p_text IS NULL THEN
 12              RETURN;
 13          END IF;
 14
 15          IF v_len<=v_chunk_size THEN
 16              DBMS_OUTPUT.PUT_LINE(p_text);
 17              RETURN;
 18          END IF;
 19
 20          IF v_LF_postion<=v_chunk_size THEN  21              v_chunk_size:=v_LF_postion-1;  22          ELSE  23              FOR i IN REVERSE 1..v_chunk_size LOOP  24                  IF SUBSTR(p_text, i, 1) IN (CHR(32), CHR(44)) THEN --space or comma  25                      v_chunk_size:=i;  26                      EXIT;  27                  END IF;  28              END LOOP;  29  30          END IF;  31  32          DBMS_OUTPUT.PUT_LINE(SUBSTR(p_text, 1, v_chunk_size));  33          pl(p_text=> SUBSTR(p_text, v_chunk_size+2), p_chunk_size=>p_chunk_size);
 34      END;';
 35  BEGIN
 36      pl(v_sql);
 37  END;
 38  /

CREATE OR REPLACE PROCEDURE pl (p_text       VARCHAR2,
p_chunk_size NUMBER:=255)
AS
v_len           NUMBER:=LENGTH(p_text);
v_cnt           NUMBER:=0;
v_chunk_size    NUMBER:=LEAST(p_chunk_size, 255);
v_LF_postion    NUMBER:=INSTR(p_text, CHR(10));
BEGIN
IF p_text IS NULL THEN
RETURN;
END IF;
IF v_len<=v_chunk_size THEN
DBMS_OUTPUT.PUT_LINE(p_text);
RETURN;
END IF;
IF v_LF_postion<=v_chunk_size THEN
v_chunk_size:=v_LF_postion-1;
ELSE
FOR i IN REVERSE 1..v_chunk_size LOOP
IF SUBSTR(p_text, i, 1) IN (CHR(32), CHR(44)) THEN --space or comma
v_chunk_size:=i;
EXIT;
                END IF;
            END LOOP;

        END IF;


DBMS_OUTPUT.PUT_LINE(SUBSTR(p_text, 1, v_chunk_size));
        pl(p_text=>
SUBSTR(p_text, v_chunk_size+2), p_chunk_size=>p_chunk_size);
    END;

PL/SQL procedure successfully completed.

The output is not nicely formatted but it is compilable.
Let’s compare it to the output of the p procedure:

SQL> DECLARE
  2    v_sql VARCHAR2(4000):=
  3    'CREATE OR REPLACE PROCEDURE pl (p_text       VARCHAR2,
  4                                  p_chunk_size NUMBER:=255)
  5      AS
  6          v_len           NUMBER:=LENGTH(p_text);
  7          v_cnt           NUMBER:=0;
  8          v_chunk_size    NUMBER:=LEAST(p_chunk_size, 255);
  9          v_LF_postion    NUMBER:=INSTR(p_text, CHR(10));
 10      BEGIN
 11          IF p_text IS NULL THEN
 12              RETURN;
 13          END IF;
 14
 15          IF v_len<=v_chunk_size THEN
 16              DBMS_OUTPUT.PUT_LINE(p_text);
 17              RETURN;
 18          END IF;
 19
 20          IF v_LF_postion<=v_chunk_size THEN  21              v_chunk_size:=v_LF_postion-1;  22          ELSE  23              FOR i IN REVERSE 1..v_chunk_size LOOP  24                  IF SUBSTR(p_text, i, 1) IN (CHR(32), CHR(44)) THEN --space or comma  25                      v_chunk_size:=i;  26                      EXIT;  27                  END IF;  28              END LOOP;  29  30          END IF;  31  32          DBMS_OUTPUT.PUT_LINE(SUBSTR(p_text, 1, v_chunk_size));  33          pl(p_text=> SUBSTR(p_text, v_chunk_size+2), p_chunk_size=>p_chunk_size);
 34      END;';
 35  BEGIN
 36      p(v_sql);
 37  END;
 38  /

CREATE OR REPLACE PROCEDURE pl (p_text       VARCHAR2,

p_chunk_size NUMBER:=255)
    AS
        v_len
NUMBER:=LENGTH(p_text);
        v_cnt           NUMBER:=0;
        v_chunk_size
NUMBER:=LEAST(p_chunk_s
ize, 255);
        v_LF_postion    NUMBER:=INSTR(p_text, CHR(10));
    BEGIN

IF p_text IS NULL THEN
            RETURN;
        END IF;

        IF
v_len<=v_chunk_size THEN
            DBMS_OUTPUT.PUT_LINE(p_text);

RETURN;

END IF;

        IF v_LF_postion<=v_chunk_size THEN

v_chunk_size:=v_LF_postion-1;
        ELSE
            FOR i IN REVERSE
1..v_chunk_size LOOP
                IF SUBSTR(p_text, i, 1) IN (CHR(32),
CHR(44)) THEN --space or comma

v_chunk_size:=i;
                    EXIT;
                END IF;

END LOOP;

        END IF;

        DBMS_OUTPUT.PUT_LINE(SUBSTR(p_text, 1,
v_chunk_size));
        pl(p_text=> SUBSTR(p_text, v_chunk_size+2),
p_chunk_size=>p_
chunk_size);
    END;

PL/SQL procedure successfully completed.

It’s not so bad, but you cannot simply copy the output and compile it.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to bind IN and OUT parameters in PL/SQL

Suppose that you need to execute dynamic PL/SQL, a stored procedure, with one in and one out parameter:

CREATE OR REPLACE PROCEDURE scott.get_emp_count(p_deptno IN NUMBER, p_count OUT NUMBER)
AS
BEGIN
    SELECT COUNT(*) INTO p_count
    FROM emp
    WHERE deptno=p_deptno;
END get_emp_count;

Now we want to call it in SQL*Plus as follows:

DECLARE
   v_sql VARCHAR2(2000);
   v_count NUMBER;
   v_deptno NUMBER:=10;
BEGIN
   v_sql:='CALL get_emp_count(:0, :1)';
   EXECUTE IMMEDIATE v_sql USING v_deptno, v_count;

   DBMS_OUTPUT.PUT_LINE(v_count);
END;
/
*
ERROR at line 1:
ORA-06536: IN bind variable bound to an OUT position
ORA-06512: at line 7

To make it work we need to use OUT keyword before v_count variable:

DECLARE
   v_sql VARCHAR2(2000);
   v_count NUMBER;
   v_deptno NUMBER:=10;
BEGIN
   v_sql:='CALL get_emp_count(:0, :1)';
   EXECUTE IMMEDIATE v_sql USING v_deptno, OUT v_count;

   DBMS_OUTPUT.PUT_LINE(v_count);
END;
/
3

PL/SQL procedure successfully completed.

Bind parameter mode is similar to a parameter mode in a stored procedure – IN is the default and we don’t have to use it, though we can:

  1  DECLARE
  2     v_sql VARCHAR2(2000);
  3     v_count NUMBER;
  4     v_deptno NUMBER:=10;
  5  BEGIN
  6     v_sql:='CALL get_emp_count(:0, :1)';
  7     EXECUTE IMMEDIATE v_sql USING IN v_deptno, OUT v_count;
  8     DBMS_OUTPUT.PUT_LINE(v_count);
  9* END;
SQL> /
3

PL/SQL procedure successfully completed.

This technique is useful when you don’t know the name of the stored procedure to be executed with EXECUTE IMMEDIATE statement in advance, though you know the parameters.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Privileges needed to create spatial index in dynamic SQL

Executing DDL commands explicitly or with EXECUTE IMMEDIATE statement usually require special privileges granted directly (not as a part of a ROLE). With creating tables and indexes, things are really simple, you just need to grant CREATE TABLE privilege and that’s it.

When you need to create a special index, such as SPATIAL index, it is not obvious which privileges need to be granted.

Symptoms of the problem:

SQL> BEGIN
  2      EXECUTE IMMEDIATE 'CREATE INDEX idx_poi_geometry ON poi_geometry(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX';
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13249: internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index: index build failed
ORA-13249: Error in R-tree: [mdrcrtscrt]
ORA-13231: failed to create index table [MDRT_2942E$] during R-tree creation
ORA-13249: Stmt-Execute Failure: CREATE TABLE "SCOTT".MDRT_2942E$
(NODE_ID NUMBER, NODE_LEVEL NUMBER, INFO BLOB)  LOB (INFO) STORE AS (NOCACHE)
PCTFREE 2
ORA-29400: data cartridge error
ORA-01950: no privileges on tablespace 'USERS'
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10
ORA-06512: at line 2


Elapsed: 00:00:00.07

Note, that even though the statement failed, Oracle created an object, so before retrying you need to drop it:

SQL> DROP INDEX idx_poi_geometry;

Index dropped.

From the error message, we can conclude that there is something wrong with the tablespace privileges.
And this is not something dynamic SQL specific as the plain DDL command fails with the same error:

SQL> CREATE INDEX idx_poi_geometry ON poi_geometry(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
CREATE INDEX idx_poi_geometry ON poi_geometry(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13249: internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index: index build failed
ORA-13249: Error in R-tree: [mdrcrtscrt]
ORA-13231: failed to create index table [MDRT_29430$] during R-tree creation
ORA-13249: Stmt-Execute Failure: CREATE TABLE "SCOTT".MDRT_29430$
(NODE_ID NUMBER, NODE_LEVEL NUMBER, INFO BLOB)  LOB (INFO) STORE AS (NOCACHE)
PCTFREE 2
ORA-29400: data cartridge error
ORA-01950: no privileges on tablespace 'USERS'
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10

After granting UNLIMITED TABLESPACE privilege:

SQL> DROP INDEX idx_poi_geometry;

Index dropped.

Elapsed: 00:00:00.04
SQL> CREATE INDEX idx_poi_geometry ON poi_geometry(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Index created.

Elapsed: 00:00:05.98

Dynamic SQL also works:

SQL> DROP INDEX idx_poi_geometry;

Index dropped.

Elapsed: 00:00:00.36
SQL> BEGIN
  2      EXECUTE IMMEDIATE 'CREATE INDEX idx_poi_geometry ON poi_geometry(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX';
  3  END;
  4  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.08

A tricky part comes when you try to execute this dynamic SQL wrapped into a packaged procedure call made by a different user:

SQL> BEGIN
  2      EXECUTE IMMEDIATE 'BEGIN scott.pkg_indexes.create_spatial_index; END;';
  3  END;
  4  /
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13249:
internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index:
index build failed
ORA-13249: Error in R-tree: [mdrcrtscrt]
ORA-13231: failed to
create index table [MDRT_29480$] during R-tree creation
ORA-13249: Stmt-Execute
Failure: CREATE TABLE "SCOTT".MDRT_29480$ (NODE_ID NUMBER, NODE_LEVEL
NUMBER, INFO BLOB)  LOB (INFO) STORE AS (NOCACHE)  PCTFREE 2
ORA-29400:
data cartridge error
ORA-01031: in

PL/SQL procedure successfully completed.

Now it’s time to grant more privileges (as a SYSTEM USER):

SQL> GRANT CREATE TABLE, CREATE SEQUENCE TO scott
  2  /

Grant succeeded.

Now let’s execute last dynamic PL/SQL again (the packaged procedure create_spatial_index includes drop index command, so we should not worry about it):

SQL> BEGIN
  2      EXECUTE IMMEDIATE 'BEGIN scott.pkg_indexes.create_spatial_index; END;';
  3  END;
  4  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:39.67

Summary:

To successfully create a spatial index in dynamic SQL (or dynamic PL/SQL), the owner of the index may need the following privileges granted explicitly:

• UNLIMITED TABLESPACE
• CREATE TABLE
• CREATE SEQUENCE