Get Agile item data from oracle database 11g

I want to read item from database itself i am using oracle sql developer.
I want to read all attributes like page 2, page 3, history,change orders and bom of item from database.
I need your help to write sql query.
Thanx in advance.

Add Comment
4 Answer(s)

You aren’t asking for much…….  <grin>
 It is possible to get all this data from the database, but not all in 1 query (unless you want a HUGE query). Note that you could use the ITEM_P2P3 view to get most of the title block/P2/P3 stuff, but you would still have to convert all of the ID values into actual text (using NODETABLE and LISTENTRY). And that view will give you *ALL* of the attributes, no matter whether they are visible/used or not. All multitext P2/P3 attributes are held in the AGILE_FLEX table. Multilist attributes usually have  their data in the attribute itself unless you have enough ID values assigned that it exceeds the length of the attribute, in which case the values are held in the MSATT table. History, BOM, Revs/Change, AML, Relationships, Compliance, Attachments, etc. are all in separate tables, linked to the ITEM table via the ID attribute.   And of course, there is *no* public documentation on how the Agile schema works. And it is highly normalized, so IDs are much more prevalent than text values. I would suggest that you start simple (title block, P2 and P3) and work your way through how to make the data intelligible. Ask questions when you get stuck.

 Of course, you could also learn to use the Agile API/SDK, and write programs to get the data and never have to figure out the database schema.

Agile Angel Answered on April 21, 2016.
Add Comment

Hello Kevin – I am a noobie, similar to pranitchaudhari, though I have succeeded in writing a query to get most general item data. I unfortunately am struggling to find where lifecycle status’s are kept. I suspect it may be OBJVERSIONon the ITEM table, but cannot find where that refernece is stored. I tried ITEM, PAGE_TWO, PAGE_THREE, REV, BOM, CHANGE, NODETABLE and LISTENTRY and AGILE_FLEX.

Any help would be greatly appreciated.

Nick

Agile User Answered on April 21, 2016.
Add Comment

Lifecycle IDs are in the REV.RELEASE_TYPE, along with the revision and change that released it. The text values for the lifecycle are found in NODETABLE.
 OBJVERSION is simply a counter for how many times a particular ITEM record has been updated.

Agile Angel Answered on April 22, 2016.
Add Comment

Hi Kevin – This is exactly the info I needed. Thanks a bunch.

Nick

Agile User Answered on April 22, 2016.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.