topic Re: SAS Intelligent Decisioning Code File writing to Oracle Database with LONG or CLOB data type in SAS Viya

SAS Intelligent Decisioning Code File writing to Oracle Database with LONG or CLOB data type

JAE2019 — Fri, 17 Feb 2023 06:31:13 GMT

Hi Experts!

We have a Data Query code file in SAS Intelligent Decisioning that writes to an Oracle Database. One string variable is being written to a variable with CLOB data type in Oracle, since its values will go beyond the 4000 limit of VARCHAR.

It works when we do the insert statement via SAS Studio, but testing the code file from the SCORING tab of a Decision Flow in SAS ID deems that it's unable to write to the table. Same thing happens when we publish to MAS - it's also unable to write to the table.

Is there any CAS / MAS related limitation to writing to a CLOB variable in Oracle? We encounter the same problem for writing to variables in Oracle with LONG data type. Attached is a portion of the logs for reference.

Hoping to get some guidance regarding this error

Thank you!!!

Re: SAS Intelligent Decisioning Code File writing to Oracle Database with LONG or CLOB data type

fja — Fri, 17 Feb 2023 07:26:04 GMT

Hello!
I guess you have posted this twice ... could you remove one of these postings?

Just for the sake of focus ... 🙂
Cheers
FJa

Re: SAS Intelligent Decisioning Code File writing to Oracle Database with LONG or CLOB data type

JAE2019 — Fri, 17 Feb 2023 09:51:58 GMT

I can only see edit post, but cannot find the option to delete ☹️

Re: SAS Intelligent Decisioning Code File writing to Oracle Database with LONG or CLOB data type

Patrick — Fri, 17 Feb 2023 10:50:40 GMT

Actually.... Oracle Varchar can store around 32KB similar to a SAS character variable.

You can set option DBMAX_TEXT to a value up-to 32KB and then you don't need a CLOB or BLOB which is always a bit hard to work with.

I don't know if the SAS solution allows for this or if this CLOB field in Oracle is part of a solution table that you're not supposed to change. If so then raise a SAS TechSupport track if you don't find a solution.

Below link to the SAS documentation Data Types for Oracle that provides the details how Oracle maps to SAS data types (and vice versa).

And if you really need to deal with an Oracle data type that doesn't map "nicely" to a SAS data type then you can always write an explicit pass-through query and cast the Oracle variable to a type that maps to a SAS data type.
For example with a CLOB which can contain much more than 32KB you would need to split the Clob into multiple Varchar type variables with 32KB on the Oracle side to map such data to a SAS table.

Re: SAS Intelligent Decisioning Code File writing to Oracle Database with LONG or CLOB data type

alexal — Fri, 17 Feb 2023 12:42:00 GMT

You should be able to insert 32k long string if you define a variable like shown below:

dcl varchar(32000) character set utf8 long_string;

I see there were some bugs related to DS2 SQLSTMT statement that were fixed in Viya 4 - 2020.0.5. What version are you using?

Re: SAS Intelligent Decisioning Code File writing to Oracle Database with LONG or CLOB data type

fja — Fri, 17 Feb 2023 14:30:51 GMT

@JAE2019 wrote:

I can only see edit post, but cannot find the option to delete ☹️

Hello!
You could post a hint to the duplicate thread and mark that as solved ...
as long as there is at least one thread without any answer.
That would also have the charme that you earned yourself an accepted answer ... 😉 (Eventhough people frown on that.)
Cheers
Fja

Re: SAS Intelligent Decisioning Code File writing to Oracle Database with LONG or CLOB data type

JAE2019 — Mon, 20 Feb 2023 09:38:58 GMT

Thank you @alexal for the reply!

We are using 2022.1 LTS at the moment.

Another option we have tried following another thread was to position the oracle CLOB/LONG fields mapped to SAS Intellligent Decision Datagrids, towards the end of the table - so far it seems this option works.

Does anyone have any callout/experience though if writing to CLOB/LONG variables from SAS ID Data Query cause any performance problems?