From Eigenpedia

Contents 1 Syntax 2 Purpose 3 Parameters 3.1 Input 3.2 Output 4 Exceptions 5 Example

Syntax

SELECT CHUNK_OFFSET,CHUNK_TEXT
FROM TABLE(SYS_ROOT.RETRIEVE_REPOSITORY_LOB_TEXT(MOF_ID, ATTRIBUTE_NAME))

RETRIEVE_REPOSITORY_LOB_TEXT is a UDX, which must be invoked in the FROM clause of a SQL query.

Purpose

Retrieves the text of a string-valued catalog attribute in chunks. LucidDB does not currently support the LOB datatype, but some string-valued catalog attributes can be very long since the underlying repository has no limit on their precision. This UDX makes it possible to retrieve the full value without truncation.

Parameters

Input

• MOF_ID VARCHAR(128): MOFID of catalog object to be accessed (see LucidDbSystemViews)
• ATTRIBUTE_NAME VARCHAR(128): name of attribute to be retrieved from catalog object (this requires knowledge of FEM, since underlying attribute names are typically different from the column names exposed in the system views)

Output

• CHUNK_OFFSET: 0-based offset of returned chunk within the full string value, or -1 if the attribute value was null
• CHUNK_TEXT: chunk of full string starting at CHUNK_OFFSET, either 1024 characters long, or less if last chunk in value (but if attribute value was null, then this is too)

If the string value is the empty string, then a single row is returned, with the empty string for CHUNK_TEXT.

Exceptions

Exceptions are thrown in the following situations:

• The MOFID does not correspond to any repository object.
• The referenced repository object does not have an attribute with the specified name.

If the attribute exists but is not string-valued, then its Java toString representation is used.

Example

0: jdbc:luciddb:> create schema orders description 'Long description ...';
0: jdbc:luciddb:> select mof_id from sys_root.dba_schemas where schema_name='ORDERS';
+---------------------+
|       MOF_ID        |
+---------------------+
| j:0000000000004AE1  |
+---------------------+
0: jdbc:luciddb:> select * from table(sys_root.retrieve_repository_lob_text('j:0000000000004AE1', 'description')) order by chunk_offset;
+---------------+-----------------------+
| CHUNK_OFFSET  |      CHUNK_TEXT       |
+---------------+-----------------------+
| 0             | Long description ...  |
| 1024          | ... continuation ...  |
| ...           | ... continuation ...  |
+---------------+-----------------------+