how to get folder path

Hi,

How can I retrive the folder path for a file using Query or LAPI call? I used below QUery to retrive the folder path:

select dataid, name, sys_connect_by_path(name,':') as "Path" , subtype from DTree where dataid in(select dataid from DTree connect by prior dataid=parentid start with dataid=232782) connect by prior dataid=parentid start with dataid=2000

This works fine even when I have more than 15 sub folders and I give leaf node ID. But for the same 15 level hierarchy, If I give 1st level folder and try to retrive the folder path of that It is not working, oracle go on processing but doesnt retrun any result I don't understand that for 15 level it gaave me entire path, but for 1st leavel folder which is very short one it iw not working.

Any idea why that happens?

Find more posts tagged with

Comments

Lindsay_Davies

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

Hi Sunitha,

So presumably the folder hierarchy is quite deep.

Do you know how deep? If you output the pseudo column Level instead of using sys_connect_by_path you can determine that.

When a hierarchy is very deep it becomes unmanageable.

There is not much you can do to affect this limit.

Syntax

Description of the illustration sys_connect_by_path.gif

PurposeSYS_CONNECT_BY_PATH is valid only in hierarchical queries. It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition.Both column and char can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 datatype and is in the same character set as column.

You cannot CAST from varchar2 to CLOB so you are stuck with this limit.

I expect folders would normally have fairly short names.

Maybe you only exceed the limit because of long document names.

Filter your query by subtype to avoid objects that are not containers.

e.g. where subtype=0

There are a few tricks you could employ to avoid the issue.

You could stop at a certain depth

e.g. where level<6

If you output the level and subtype, you could then start again with the lowest level folders.

I hope these suggestions help.

Regards

Lindsay

OpenText UK

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2012 February 28, Tue 08:31
To: eLink Recipient
Subject: how to get folder path

how to get folder path

Posted by sunitha_iyer@infosys.com (Iyer, Sunitha Ramesh) On 02-28-2012 03:26

Hi, How can I retrive the folder path for a file using Query or LAPI call? I used below QUery but it gives error for concatination beyond 4000 characters, and my folde rpath excedes that limit. Any other way I can fetch folder path of the document without any limitation? select dataid, name, sys_connect_by_path(name,':') as "Path" , subtype from DTree connect by prior dataid=parentid start with dataid=%1 Anyother way I can retrive folder path or overcome the limitation of above query?

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Sunitha_Iyer

Suppose my hierarchy is

FOlder1\Folder2\Folder3\Folder4\Folder5\Folder6\Folder7....\Folder10\abc.txt

Now when i Try to get the path of abc.txt i am able to get it using the query. But when I use same query to retvie path of Folder2 then oracle just keep on processing, and it took some time mroe than 20 mins to retrive the path value which is jsut "Folder1\Folder2".

Issue should be retriving path for abc.txtx but it is working fine, don't know why it is not working for Folder2.

Appu_Nair

If memory serves me right somebody in this forum has shown how you would write a Oracle function getllpath.It works not by the connect by which is giving you trouble but something like this in psuedo code.I hope you follow my representation

Enterprise(2000):Myfolder(10001):.............:Mydeepdocument(1845678)

select dataid,getllpath(dataid) from dtree where dataid=1845678

This would print the tree all the way from Ent:--- all the way to the document for each node encountered it will concatenate the name.this is what a lot of people use to create the Object Importer command files so it must be available with OT support as well.

In lapi I have done this as my learning stuff

https://communities.opentext.com/communities/llisapi.dll/open/7762536

I think the call is directly available in enterprise web services as well.

What good is this anawy.If you have such a long named straucture how would one use the report other than for academic purposes.

If you can't find getllpath anywhere or write that yourself let me know I am willing to give a look in my old files collection.

Appu_Nair

Look for the last post by Scott Wallace(incidentally a great friend) He has the getllpath that I mention

https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=4461418&objAction=viewincontainer&ShowReplyEntry=8848087#forum_topic_8848087

Sunitha_Iyer

Hey, thank you very much for that link and help you provided.

Thanks to your friend as well. Now it is really very fast.