Skip to Main Content

SQL & PL/SQL

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!

sys_connect_by_path & to_CLOB

719825Sep 25 2009 — edited Sep 27 2009
Hi,

I tried this query to use on my database (took it from [http://tkyte.blogspot.com/2007_03_01_archive.html|http://tkyte.blogspot.com/2007_03_01_archive.html] )
SQL> select deptno,  
2         max(sys_connect_by_path(ename, ' ' )) scbp  
3    from (select deptno, ename,                     row_number() over                    (partition by deptno order by ename) rn  
4            from emp  
5             )  
6  start with rn = 1  
7  connect by prior rn = rn-1 and prior deptno = deptno  
8  group by deptno  
9  order by deptno 
10  /    

DEPTNO SCBP---------- ----------------------------------------        
10          CLARK KING MILLER        
20          ADAMS FORD JONES SCOTT SMITH        
30          ALLEN BLAKE JAMES MARTIN TURNER WARD
When I use the same query on my table in my database (not this one) I have a lot of results and I get this error:

ORA-01489: result of string concatenation is too long
(I can have between 1 et 500 entries, different lenghts)

I made some search in the forum and Google and I think I can use the function TO_CLOB but it doesen't work. (Line 3) select deptno, (to_clob(ename)),
SQL> select deptno,  
2         max(sys_connect_by_path(ename, ' ' )) scbp  
3    from (select deptno, (to_clob(ename)),                     row_number() over                    (partition by deptno order by ename) rn  
4            from emp  
5             )  
6  start with rn = 1  
7  connect by prior rn = rn-1 and prior deptno = deptno  
8  group by deptno  
9  order by deptno 
10  /    

ORA-00904: string: invalid identifier
My Question: can I use to_clob in this query?
thanks, Roseline

Edited by: Roseline on 2009-09-25 12:06
This post has been answered by Solomon Yakobson on Sep 26 2009
Jump to Answer

Comments

Kishore Guggilla

what is the exact issue you're facing...

Russ Proudman

The issues never seem to end.

First of all there are Java classes that are not installed in the correct directory with the BI client tool that are in the Linux directories.

Then in the data-model-cmd.cmd script, you have to have extra parameters that are not required in the .sh version (because it's a client installation,

but, of course, if you're using the BI Admin Tool from Windows (ala v11.x) then that would always be a client installation).

Then it's not a trusted connection as it's a client installation so now you have to be running SSL in OBIEE as well (trusted store, key, etc.), etc.

Now I'm getting a useless message such as "connection error: -1", except the server, and the port are correct.

It's just an uphill battle.

Compare it to the data-model-cmd.sh script where it just works. Same parameters as I use in the .cmd script (well less required) and it worked first time.

Unfortunately I'm at a location where jumping on to the Linux box and running it from there are not possible.

So my original request is finding ANYONE who has successfully uploaded an RPD from the Windows client .cmd script and what they went through

'cause it's not obvious and has a number of 'unwritten' challenges.

Thanks.

Andrew Fomin.

Hi.

My client system is Windows 7 (tested on Win 10 also) and a server is on Oracle Linux 6.

The only issue I've faced was copying two jars from Linux to Windows. It looks like you've already done it. After that, everything works for me.

Here is my post about it: https://bisoftdiary.com/obiee12c_repository_upload

Russ Proudman

First of all, thanks for the reply.

For your server, is it on the Windows machine as well (ie: Linux running on the same laptop)?

Reason I'm asking is there seems to be extra fields needed when you're running the command from a client machine (Windows) to a server (Linux) and one of them I believe was a mandatory -SSL field.

So I'm just wondering if it's different if on the same box versus from a box to the same box (whether or not running VirtualBox, etc.)?

Thanks.

Andrew Fomin.

Pictures from my blog post were taken on the following configuration: VM #1: OBIEE 12c working on Oracle Linux 6. VM #2: OBIEE client on MS Windows 10. Both VMs work on the same host (Windows 10). But I tried the same procedure with a remote Linux server (Windows 7 - client, OEL 6 - remote server). Everything worked fine without -SSL key.

By the way, have you checked things like a firewall and Java version/bitness? Sometimes the easiest answer is the best one.

Frog Toad

Andrewif написал(а):

Here is my post about it: https://bisoftdiary.com/obiee12c_repository_upload

Andrewif, thousands ray goodness to you for this post

Andrew Fomin.
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 25 2009
Added on Sep 25 2009
12 comments
8,901 views