Tuesday, July 25, 2017

Wrod Wrapin in PL Sql

----Wrod Wrap-- package--
CREATE OR REPLACE PACKAGE WROD_WRAP
AS
FUNCTION bi_word_wrap (p_str varchar2, p_linesize pls_integer, p_sep varchar2 := ' ') RETURN VARCHAR2;
END WROD_WRAP;

---BOdy---
CREATE OR REPLACE PACKAGE BODY WROD_WRAP
AS
FUNCTION bi_word_wrap (p_str varchar2, p_linesize pls_integer, p_sep varchar2 := ' ') RETURN VARCHAR2
as
l_str long := p_str || p_sep;
l_line long;
l_pos pls_integer;
R_VAL VARCHAR2(3000):=NULL;
begin
while l_str is not null loop
l_line := substr(l_str, 1, p_linesize);
l_pos := instr(l_line, p_sep, -1);
l_line := substr(l_line, 1, l_pos);
dbms_output.put_line(l_line);
R_VAL:=R_VAL||CHR(10)||l_line;
l_str := substr(l_str, l_pos + 1);
end loop;
RETURN R_VAL;
end bi_word_wrap;
END WROD_WRAP;

 ----execution 
  DECLARE
  RET VARCHAR2(1000);
  begin
  RET:=WROD_WRAP.bi_word_wrap(:S,20);
  END;

select WROD_WRAP.bi_word_wrap(:S,20) a from dual;

No comments:

Post a Comment