Skip to Main Content
Oracle PL/SQL Programming: A Developer's Workbook
book

Oracle PL/SQL Programming: A Developer's Workbook

by Steven Feuerstein, Andrew Odewahn
May 2000
Intermediate to advanced content levelIntermediate to advanced
594 pages
11h 32m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL Programming: A Developer's Workbook

Intermediate

Q:

23-11.

By default (i.e., the basic SET SERVEROUTPUT ON command), SQL*Plus trims leading blanks.

Q:

23-12.

By default, SQL*Plus pretends you didn’t ask it to display a blank line. You can override this annoying behavior with the FORMAT WRAPPED option:

SET SERVEROUTPUT ON SIZE 500000 FORMAT WRAPPED

Q:

23-13.

The WORD_WRAPPED option comes in handy for this purpose:

SET SERVEROUTPUT ON SIZE 500000 FORMAT WORD_WRAPPED

Q:

23-14.

The TRUNCATE option comes in handy for this purpose:

SET SERVEROUTPUT ON SIZE 500000 FORMAT TRUNCATE

Q:

23-15.

You get an unhandled exception:

PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'

DBMS_OUTPUT.PUT_LINE is only overloaded for VARCHAR2, DATE, and NUMBER. You cannot pass it a Boolean directly.

Q:

23-16.

You get an unhandled exception:

ORA-06502: PL/SQL: numeric or value error

DBMS_OUTPUT.PUT_LINE cannot handle strings with more than 255 bytes.

Q:

23-17.

You can use either DBMS_OUTPUT.GET_LINE to extract a single line or DBMS_OUTPUT.GET_LINES to dump all of the contents into an index-by table. The following procedure dumps the buffer and returns it through the parameter list:

PROCEDURE dump_do_buffer (buffer IN OUT DBMS_OUTPUT.CHARARR)
IS
   linenum PLS_INTEGER := 1000000;
BEGIN
   DBMS_OUTPUT.GET_LINES (buffer, linenum);
END;

Q:

23-18.

Here is one possible implementation:

 /* Filename on web page: putboolean.sp */ CREATE OR REPLACE PROCEDURE put_boolean (bool ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 9781449324070Supplemental ContentErrata Page