ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

UDTF receiving CLOB LOCATOR

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • UDTF receiving CLOB LOCATOR

    Hello,

    I'm trying to create a User-Defined Table Function (UDTF) which receives 2 parameters: a CLOB_LOCATOR which is, for example, the full content of a UTF-8 text file and a VARCHAR(2) delimiter value which specifies what character(s) (e.g. x'0D0A') indicate a line break within the first value. The current, roughly working, version of my function is defined as follows:

    Code:
    create or replace function
      LIB1/SPLIT_CLOB(
        CLOBDATA CLOB(2M) AS LOCATOR,
        DELIM VARCHAR(2)
      )
    returns table (
      TEXT_LINE VARCHAR(8192) CCSID 1208
    )
    The assumption here is that the incoming CLOB_LOCATOR contains data encoded as UTF-8 (CCSID 1208), but I'm not able to specify CCSID 1208 on the first parameter - this results in error SQL0441 ("Clause or keyword AS LOCATOR not valid where specified") when creating the function.

    So questions:

    1) is there a way to specify a parameter as being both 'CLOB_LOCATOR' and UTF-8 (CCSID 1208)?
    2) if a CLOB which is encoded other than as UTF-8 is passed to my function can this be detected and then either converted to UTF-8 or result in an error being thrown?
    3) would it perhaps make more sense to define a 3rd parameter for the CCSID to allow the caller of my function to specify the encoding of the data passed in the first parameter?

    Thanks in advance for any assistance.

  • #2
    A CLOB Locator is a kind of pointer that points to the data. You do not pass any data ... but you can handle a CLOB_Locator with SQL as if it is a Character Field.

    Comment

    Working...
    X