Oracle: How to convert LONG to CLOB

It is very simple to convert a LONG column to CLOB.

Create table test( col1 long);

Alter table test modify col1 clob;

That's it.

ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have seperator as part of column value

SYS_CONNECT_BY_PATH is used in hierarchial queries. It returns the path of column value from a root node

example: SYS_CONNECT_BY_PATH(column_name, '/') where '/' is the seperator.

If the column_name contains the separator character, then you get the above error
"ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have seperator as part of column value"

In my example below, foldername had values like "Tree for Case/Document". Since the foldername has "/" which matches your seperator, you get the error.

select f.foldername, sys_connect_by_path(f.foldername,'/') dir
from folder f
connect by parentfolderkey = prior folderkey
start with casekey = 7457950


To remove the error, make sure that your seperator is unique, The error disappeared when I replaced the seperator "/" with "//"

select f.foldername, sys_connect_by_path(f.foldername,'//') dir
from folder f
connect by parentfolderkey = prior folderkey
start with casekey = 7457950