1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

Solve the limit(255) for DBMS_OUTPUT and size(1000000) for serverout

CREATE TABLE pl_tab
(
job_id NUMBER(15),
pl_seq NUMBER(15),
pl_line VARCHAR2(255)
);

-- create dbms_output 'replacement' package

CREATE OR REPLACE PACKAGE p$
IS
g_c_newline CONSTANT VARCHAR2 (1) := CHR (10); -- newline character
g_c_space CONSTANT VARCHAR2 (1) := CHR (32); -- spaceace character
g_c_tab CONSTANT VARCHAR2 (1) := CHR (9); -- tab character
g_c_comma CONSTANT VARCHAR2 (1) := ','; -- comma

FUNCTION get_p_textlen
RETURN INTEGER;

PROCEDURE set_p_textlen (p_textlen IN PLS_INTEGER DEFAULT 80);

PROCEDURE put_line (
text IN LONG,
textlen IN NUMBER DEFAULT get_p_textlen,
whspace IN VARCHAR2 DEFAULT g_c_newline
|| g_c_space
|| g_c_tab
|| g_c_comma
);

FUNCTION get_pl_method
RETURN VARCHAR2;

PROCEDURE set_pl_method (p_method IN VARCHAR2);

FUNCTION get_job_id
RETURN NUMBER;

PROCEDURE set_job_id;
END p$;
/

CREATE OR REPLACE PACKAGE BODY p$
IS
g_c_screen VARCHAR2 (6) := 'SCREEN';
g_c_table VARCHAR2 (5) := 'TABLE';
g_pl_method VARCHAR2 (6) := g_c_screen;
g_seq NUMBER := 0;
g_job_id NUMBER;
g_textlen PLS_INTEGER DEFAULT 80;

--
FUNCTION get_p_textlen
RETURN INTEGER
IS
BEGIN
RETURN g_textlen;
END get_p_textlen;

--
PROCEDURE set_p_textlen (p_textlen IN PLS_INTEGER DEFAULT 80)
IS
BEGIN
IF p_textlen BETWEEN 1 AND 255
THEN
g_textlen := p_textlen;
END IF;
END;

--
FUNCTION get_pl_method
RETURN VARCHAR2
IS
BEGIN
RETURN g_pl_method;
END get_pl_method;

--
PROCEDURE set_pl_method (p_method IN VARCHAR2)
IS
BEGIN
IF UPPER (p_method) = g_c_table
THEN
set_job_id;
g_pl_method := g_c_table;
ELSIF UPPER (p_method) = g_c_screen
THEN
g_pl_method := g_c_screen;
ELSE
DBMS_OUTPUT.put_line ('Invalid put_method detected, set to SCREEN');
g_pl_method := g_c_screen;
END IF;
END set_pl_method;

--
FUNCTION get_next_job_id
RETURN NUMBER
IS
l_job_id NUMBER;
BEGIN
SELECT NVL (MAX (job_id), 0) + 1
INTO l_job_id
FROM pl_tab;

RETURN l_job_id;
END get_next_job_id;

--
FUNCTION get_job_id
RETURN NUMBER
IS
BEGIN
RETURN g_job_id;
END get_job_id;

--
PROCEDURE set_job_id
IS
BEGIN
g_job_id := get_next_job_id;
END;

--
FUNCTION get_next_seq
RETURN NUMBER
IS
BEGIN
g_seq := g_seq + 1;
RETURN g_seq;
END get_next_seq;

--
PROCEDURE put_line
-- ---------------------------------------------------------------------------
-- This procedure breaks the 255 character barrier imposed by
-- dbms_output.put_line. It breaks a long data text field into
-- line lengths defined by the user. An optional length sets the
-- limit on each line (must be between 10 and 255)
-- ---------------------------------------------------------------------------
(
text IN LONG,
textlen IN NUMBER DEFAULT get_p_textlen,
whspace IN VARCHAR2 DEFAULT g_c_newline
|| g_c_space
|| g_c_tab
|| g_c_comma
)
IS
startpos INTEGER := 1; -- start of string to print
stoppos INTEGER; -- end of substring to print
eospaceos INTEGER := LENGTH (text); -- end of string to print
newlinepos INTEGER; -- point where newline found
len INTEGER := GREATEST (LEAST (textlen, 255), 10);
-- 10 <= len <=255

--
PROCEDURE pl (p_line IN VARCHAR2)
IS
BEGIN
IF g_pl_method = g_c_table
THEN
g_seq := get_next_seq;

INSERT INTO pl_tab
(job_id, pl_seq, pl_line
)
VALUES (g_job_id, g_seq, p_line
);

COMMIT;
ELSE
DBMS_OUTPUT.put_line (p_line);
END IF;
END pl;
BEGIN
-- Determine if the line is short enough to write out in one chunk
IF (eospaceos <= len)
THEN
pl (text);
ELSE
-- Line must be broken up to print
WHILE (startpos <= eospaceos)
LOOP
newlinepos :=
INSTR (SUBSTR (text, startpos, len), g_c_newline) + startpos
- 1;

--- Determining if a newline break exists
IF (newlinepos >= startpos)
THEN
pl (SUBSTR (text, startpos, newlinepos - startpos));
-- Moving one position past the newline character
startpos := newlinepos + 1;
ELSE -- no newline exists in chunk; look for whitespaceace
stoppos := LEAST (startpos + len - 1, eospaceos);
-- next chunk not EOS

IF (stoppos < eospaceos)
THEN
FOR i IN REVERSE startpos .. stoppos
LOOP
IF (INSTR (whspace, SUBSTR (text, i, 1)) != 0)
THEN
stoppos := i; -- found suitable break pt
EXIT; -- break out of loop
END IF;
END LOOP; -- find break pt
ELSE -- this is the last chunk
stoppos := stoppos + 1; -- point just past EOS
END IF;

pl (SUBSTR (text, startpos, stoppos - startpos + 1));
startpos := stoppos + 1; -- get next chunk
END IF; -- find newline to break on
END LOOP; -- writing chunks
END IF; -- short enough or break up string?
END put_line;
--
END p$;
/
-- create dbms_output 'replacement' procedure

CREATE OR REPLACE PROCEDURE pl
-- ---------------------------------------------------------------------------
-- This stored procedure breaks the 255 character barrier imposed by
-- dbms_output.put_line. It breaks a long data text field into
-- line lengths defined by the user. An optional length sets the
-- limit on each line (must be between 10 and 255)
-- ---------------------------------------------------------------------------
(
text IN LONG,
textlen IN NUMBER DEFAULT p$.get_p_textlen,
whspace IN VARCHAR2 DEFAULT p$.g_c_newline
|| p$.g_c_space
|| p$.g_c_tab
|| p$.g_c_comma
)
IS
BEGIN
p$.put_line (text, textlen, whspace);
END pl;
/

GRANT EXECUTE ON pl TO PUBLIC;
CREATE PUBLIC SYNONYM pl FOR pl;


---------------- test script cut from here -----------------
SET serveroutput on;
SPOOL emp.log

SET FEEDBACK OFF
SET HEADING OFF
SET lines 255
SET TRIMSPOOL ON
-- *********************************************************
-- Example without TABLE usage
--**********************************************************
PROMPT example with defaults

DECLARE
v_line VARCHAR2 (255);
v_long_line VARCHAR2 (1000) := RPAD ('A', 1000, 'A');
BEGIN
p$.set_p_textlen (255); -- set linesize to max

--
SELECT empno || ' ' || ename || ' ' || job
INTO v_line
FROM emp
WHERE ROWNUM = 1;

pl (v_line);
pl (v_long_line);
END;
/

SET HEADING ON
SELECT COUNT (*)
FROM pl_tab;
SET HEADING OFF

-- *********************************************************
-- Example with TABLE usage
--**********************************************************
PROMPT example with TABLE usage
VARIABLE job_id NUMBER
VARIABLE pl_method VARCHAR2(6)

DECLARE
v_line VARCHAR2 (255);
v_long_line VARCHAR2 (1000) := RPAD ('A', 1000, 'A');
BEGIN
:pl_method := 'TABLE'; -- set loging mode to TABLE PL_TAB
p$.set_pl_method ('TABLE');
p$.set_p_textlen (255); -- set linesize to max

--
SELECT empno || ' ' || ename || ' ' || job
INTO v_line
FROM emp
WHERE ROWNUM = 1;

pl (v_line);
:job_id := p$.get_job_id;
pl (v_long_line);
END;
/

SELECT pl_line
FROM pl_tab
WHERE :pl_method = 'TABLE' AND job_id = :job_id
/
SET HEADING ON
SELECT COUNT (*)
FROM pl_tab;
SPOOL OFF
----------------end test script output from here on ------------
SCOTT@>@pl_usg
example with defaults
7369 SMITH CLERK
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

COUNT(*)
---------
0
example with TABLE usage

7369 SMITH CLERK
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

COUNT(*)
---------
5
SCOTT@>

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy