SQLcl: MCP Server & SQL Prompt

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Missing linebreak after deployment

Thomas SJan 21 2022

Hi there,
I have the following problem since version 21.X. I have a procedure / function which I export with XY.
Procedure:
If I export and import this with a SQLCL version 21.x, the last line is always shifted directly behind the previous one.
-> Version sqlcl_21.4.1
lb genschema -grants
File:
lb update -changelog controller.xml
Result on the Database:

In this case it is ugly but valid.
But what about types?
The object becomes invalid.
The problem did not exist in older versions. So I'm assuming it's a bug and I hope it gets fixed.

best regards
Thomas

Solomon Yakobson

Roseline wrote:

I made some search in the forum and Google and I think I can use the function TO_CLOB but it doesen't work.

Well, sorry to disappoint you, but SYS_CONNECT_BY_PATH does not support CLOB, just VARCHAR2/CHAR. You could write own aggregation function to concatenate CLOBs (check this forum for STRAGG).

SY.

Hoek

Depending on your DB-version, using to_clob works on 10GXE, but you'll need to use a different separator:

(Since I'm on XE at the moment, I used the HR scheme, instead of EMP/DEPT etc.)

HR%xe> select department_id
  2  ,      ltrim(max(sys_connect_by_path(last_name, ' ' )), ' ') scbp  
  3  from ( select department_id
  4         ,      to_clob(last_name) last_name
  5         ,      row_number() over (partition by department_id order by last_name) rn  
  6         from   employees  
  7        )  
  8  start with rn = 1  
  9  connect by prior rn=rn-1
 10         and prior department_id = department_id  
 11  group by department_id  
 12  order by department_id;
       from   employees
              *
FOUT in regel 6:
.ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have seperator as
part of column value


HR%xe> select department_id
  2  ,      ltrim(max(sys_connect_by_path(last_name, ',' )), ',') scbp  
  3  from ( select department_id
  4         ,      to_clob(last_name) last_name
  5         ,      row_number() over (partition by department_id order by last_name) rn  
  6         from   employees  
  7        )  
  8  start with rn = 1  
  9  connect by prior rn=rn-1
 10         and prior department_id = department_id  
 11  group by department_id  
 12  order by department_id;

DEPARTMENT_ID SCBP
------------- --------------------------------------------------
           10 Whalen
           20 Fay,Hartstein
           30 Baida,Colmenares,Himuro,Khoo,Raphaely,Tobias
           40 Mavris
           50 Atkinson,Bell,Bissot,Bull,Cabrio,Chung,Davies,Dell
              inger,Dilly,Everett,Feeney,Fleaur,Fripp,Gates,Gee,
              Geoni,Grant,Jones,Kaufling,Ladwig,Landry,Mallin,Ma
              rkle,Marlow,Matos,McCain,Mikkilineni,Mourgos,Nayer
              ,OConnell,Olson,Patel,Perkins,Philtanker,Rajs,Roge
              rs,Sarchand,Seo,Stiles,Sullivan,Taylor,Vargas,Voll
              man,Walsh,Weiss

           60 Austin,Ernst,Hunold,Lorentz,Pataballa
           70 Baer
           80 Abel,Ande,Banda,Bates,Bernstein,Bloom,Cambrault,Ca
              mbrault,Doran,Errazuriz,Fox,Greene,Hall,Hutton,Joh
              nson,King,Kumar,Lee,Livingston,Marvins,McEwen,Olse
              n,Ozer,Partners,Russell,Sewall,Smith,Smith,Sully,T
              aylor,Tucker,Tuvault,Vishney,Zlotkey

           90 De Haan,King,Kochhar
          100 Chen,Faviet,Greenberg,Popp,Sciarra,Urman
          110 Gietz,Higgins
              Grant

12 rijen zijn geselecteerd.

HR%xe>

Solomon Yakobson

hoek wrote:
Depending on your DB-version, using to_clob works on 10GXE, but you'll need to use a different separator:

And what does it have to do with SYS_CONNECT_BY_PATH result exceeding 4000 bytes?

SY.

Hoek

And what does it have to do with SYS_CONNECT_BY_PATH result exceeding 4000 bytes?

:|

oops
Well, errrmm...actually...ummm, well: nothing at all.
Thanks for setting things straight, Solomon.
turns more red/redder than any tomato
You're 100% right, I completely missed this part:

When I use the same query on my table in my database (not this one)

and started playing to eager with the EMP(loyees) example right away.
A simple rpad is enough to fully blow my silly example:

HR%xe> select department_id
  2  ,      ltrim(max(sys_connect_by_path(last_name, ',' )), ',') scbp  
  3  from ( select department_id
  4         ,      to_clob(rpad(last_name, 5000, '*')) last_name
  5         ,      row_number() over (partition by department_id order by last_name) rn  
  6         from   employees  
  7        )  
  8  start with rn = 1  
  9  connect by prior rn=rn-1
 10         and prior department_id = department_id  
 11  group by department_id  
 12  order by department_id;
       from   employees
              *
FOUT in regel 6:
.ORA-01489: result of string concatenation is too long

Therefore:
walks over to a certain 'Hall' (again)

Frank Kulash

Hi,

You can use SYS_CONNECT_BY_PATH to form parts of the string in a sub-query, and then concatenate those parts into a CLOB in the main query.

The example below assumes we can only put 2 itmes in a VARCHAR2. I set the number that small just for testing; you should set it as high as possible. For example, if your items can be up to 20 bytes long, and you need 1 byte for the delimiter, then you can safely cram 190 items into a 4000-byte VARCHAR2 column., and you should set <tt>:n := 190</tt>.

VARIABLE	n	NUMBER

EXEC	:n := 2;

WITH	got_rnum	AS
(
	SELECT	deptno
	,	ename
	,	ROW_NUMBER () OVER ( PARTITION BY  deptno
			      	     ORDER BY  	   ename
				   )  AS rnum
	FROM    scott.emp
)
,	got_short_path	AS
(
	SELECT	deptno
	,	CEIL (rnum / :n)			AS n_num
	,	SYS_CONNECT_BY_PATH (ename, ',')	AS short_path
	FROM	got_rnum
	WHERE	CONNECT_BY_ISLEAF	= 1
	START WITH	MOD (rnum, :n)	= 1
	CONNECT BY	rnum	    	= PRIOR rnum + 1
	AND		deptno	    	= PRIOR deptno
	AND		MOD (rnum, :n)	!= 1
)
SELECT	  deptno
,	  TO_CLOB (MAX (CASE WHEN n_num = 1 THEN short_path END))	||
	  TO_CLOB (MAX (CASE WHEN n_num = 2 THEN short_path END))	||
	  TO_CLOB (MAX (CASE WHEN n_num = 3 THEN short_path END))	||
	  TO_CLOB (MAX (CASE WHEN n_num = 4 THEN short_path END))	||
	  TO_CLOB (MAX (CASE WHEN n_num = 5 THEN short_path END))	AS path
FROM	  got_short_path
GROUP BY  deptno;

The query above will concatenate 5 VARCHAR2 columns together. You can make this any number, but it has to be hard-coded. You can use dynamic SQL to figure out how many you actually need and write that part of the main query for you.

Instead of packing the same number of enames into every short_path, it would be possible to figure out exactly how many would fit, using <tt>SUM (LENGTH (ename) + 1) OVER (PARTITION BY deptno ORDER BY rnum)</tt>, but that would be much more complex, and you could get the same effect by just concatenating more short_paths in the main query.

Edited by: Frank Kulash on Sep 26, 2009 5:50 AM

1 person found this helpful

Solomon Yakobson

Answer

Frank Kulash wrote:

You can use SYS_CONNECT_BY_PATH to form parts of the string in a sub-query, and then concatenate those parts into a CLOB in the main query.

It is much simpler and completely transparent (you do not need to precalculate into how many parts to split the hierarchy) to use HIERARCHY package I wrote so many moons ago that completely forgot about it and add CLOB support:

CREATE OR REPLACE
  PACKAGE Hierarchy
    IS
        TYPE BranchTableVarchar2Type IS TABLE OF VARCHAR2(4000)
          INDEX BY BINARY_INTEGER;
        BranchTableVarchar2 BranchTableVarchar2Type;
        TYPE BranchTableClobType IS TABLE OF CLOB
          INDEX BY BINARY_INTEGER;
        BranchTableClob BranchTableClobType;
        FUNCTION Branch(
                        p_Level          IN NUMBER,
                        p_Value          IN VARCHAR2,
                        p_Delimiter      IN VARCHAR2 DEFAULT CHR(0)
                       )
          RETURN VARCHAR2;
        PRAGMA RESTRICT_REFERENCES(Branch,WNDS);
        FUNCTION Branch(
                        p_Level          IN NUMBER,
                        p_Value          IN CLOB,
                        p_Delimiter      IN VARCHAR2 DEFAULT CHR(0)
                       )
          RETURN CLOB;
        PRAGMA RESTRICT_REFERENCES(Branch,WNDS);
END Hierarchy;
/
CREATE OR REPLACE
  PACKAGE BODY Hierarchy
    IS
        ReturnValueVarchar2 VARCHAR2(4000);
        ReturnValueClob     CLOB;
    FUNCTION Branch(
                    p_Level        IN NUMBER,
                    p_Value        IN VARCHAR2,
                    p_Delimiter    IN VARCHAR2 DEFAULT CHR(0)
                   )
      RETURN VARCHAR2
      IS
      BEGIN
          BranchTableVarchar2(p_Level) := p_Value;
          ReturnValueVarchar2          := p_Value;
          FOR I IN REVERSE 1..p_Level - 1 LOOP
            ReturnValueVarchar2 := BranchTableVarchar2(I)|| p_Delimiter || ReturnValueVarchar2;
          END LOOP;
          RETURN ReturnValueVarchar2;
    END Branch;
    FUNCTION Branch(
                    p_Level        IN NUMBER,
                    p_Value        IN CLOB,
                    p_Delimiter    IN VARCHAR2 DEFAULT CHR(0)
                   )
      RETURN CLOB
      IS
      BEGIN
          BranchTableClob(p_Level) := p_Value;
          ReturnValueClob          := p_Value;
          FOR I IN REVERSE 1..p_Level - 1 LOOP
            ReturnValueClob := BranchTableClob(I)|| p_Delimiter || ReturnValueClob;
          END LOOP;
          RETURN ReturnValueClob;
    END Branch;
END Hierarchy;
/

Now:

SQL> select  sys_connect_by_path(ename,',') ename
  2    from  (
  3           select  to_clob(rpad(ename,1000,'*')) ename,
  4                   empno,
  5                   mgr
  6             from  emp
  7          )
  8    where connect_by_isleaf = 1
  9      and rownum = 1
 10    start with mgr is null
 11    connect by mgr = prior empno
 12  /
           from  emp
                 *
ERROR at line 6:
ORA-01489: result of string concatenation is too long


SQL> set long 10000
SQL> select  hierarchy.branch(level,ename,',') ename
  2    from  (
  3           select  to_clob(rpad(ename,1000,'*')) ename,
  4                   empno,
  5                   mgr
  6             from  emp
  7          )
  8    where connect_by_isleaf = 1
  9      and rownum = 1
 10    start with mgr is null
 11    connect by mgr = prior empno
 12  /

ENAME
--------------------------------------------------------------------------------
KING****************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************

ENAME
--------------------------------------------------------------------------------
********************************************************************************
****************************************,CLARK**********************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************

ENAME
--------------------------------------------------------------------------------
********************************************************************************
********************************************************************************
********************************************************************************
*,FORD**************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************

ENAME
--------------------------------------------------------------------------------
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
******************************************,ADAMS********************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************

ENAME
--------------------------------------------------------------------------------
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************
***


SQL>

SY.

Marked as Answer by 719825 · Sep 27 2020

719825

Hi,

I tried the example and I have no results:

ENAME 
-  

1 rows returned in 0,00 seconds

I did everything in SQL Workshop in Oracle Express except for the line:

set long 10000

I didn't work so I did some research and saw that I had to do it in SQLPLUS . So I connected as SYSDBA and put the line

set long 10000

Do you thing it'S why I don't have any results in return?

thanks,

Roseline

Frank Kulash

Hi,

If you had a problem with SET LONG, then you wouldn't see all of the output, but you would see the first part of it (maybe 80 characters).

If you're not seeing anything, but not getting an error, then it may be because you're not using the standard scott.emp table.
Change the table and column names to something that you know you can read.

If you really want to find why Solomon's idea wasn't working for you, try this:

SELECT  deptno
,       ename
FROM    scott.emp
WHERE   ROWNUM <= 5;

What results do you get? Make sure you say scott.emp (not just emp) to make sure you're not using your own table (or synonym).

1 person found this helpful

719825

You are right. I tried the query and it dosen't work. It's not scott.emp
Where can I find scott.emp exact structure and data?

thanks again,

Roseline

Edited by: Roseline on 2009-09-26 21:08

719825

Frank Kulash

Hi,

Roseline wrote:
You are right. I tried the query and it dosen't work. It's not scott.emp
Where can I find scott.emp exact structure and data?

Yoiu may already have the scott schema; if so, you just have to get privileges on it.

Log in as SYSTEM as say:

GRANT SELECT ON scott.emp TO PUBLIC;

If that works, do the same for the other tables owned by scott, including dept and salgrade.

If you really don't have the scott schema, you can get it from [http://wiki.oracle.com/page/scott%2Ftiger].
(Thanks to Hoek.)

1 person found this helpful

Solomon Yakobson

Roseline wrote:
You are right. I tried the query and it dosen't work. It's not scott.emp

Why didn't you try it on your query? Make sure user you ran your query as has CREATE PROCEDURE privilege and create hierarchy package I posted (in general you should create it under a separate user, e.g. COMMON, that owns user defined shared objects, grant execute on the package to PUBLIC and create a public synonym for it). However, even if you modify your query to use the package, quey will not run. Oracle does not support aggregate functions against CLOB:

SQL> create table emp1 as select to_clob(ename) ename_clob,deptno from emp
  2  /

Table created.

SQL> select deptno,max(ename_clob) from emp1 group by deptno
  2  /
select deptno,max(ename_clob) from emp1 group by deptno
                  *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB


SQL>

You need to replace aggregation, which by the way is not good solution performance-wise. You are using analytic function anyway, so all you need in add analytic COUNT and select row with rn = COUNT:

select  deptno,
        scbp
  from  (
         select  deptno,
                 hierarchy.branch(level,ename_clob,' ' ) scbp,
                 rn,
                 cnt
           from  (
                  select  deptno,
                          to_clob(ename) ename_clob,
                          row_number() over(partition by deptno order by ename) rn,
                          count(*) over(partition by deptno) cnt
                    from  emp
                 )  
           start with rn = 1
           connect by prior rn = rn - 1
                  and prior deptno = deptno
        )
  where rn = cnt
  order by deptno
/

    DEPTNO SCBP
---------- --------------------------------------------------------------------------------
        10 CLARK KING MILLER
        20 ADAMS FORD JONES SCOTT SMITH
        30 ALLEN BLAKE JAMES MARTIN TURNER WARD

SQL>

SY.

1 person found this helpful

1 - 12

Added on Jan 21 2022

3 comments

141 views

SQLcl: MCP Server & SQL Prompt

Missing linebreak after deployment

Comments

Post Details