topic Re: How to read 10000 Char text in SAS and insert into Oracle in SAS Programming

How to read 10000 Char text in SAS and insert into Oracle

nitink26 — Fri, 27 Oct 2023 00:16:33 GMT

Hi,

I need help as I want to read 10000 Char text in SAS and insert into Oracle table -

How to SAS can read 10000 character field from flat file?
and how can I insert this field to Oracle table (Oracle VarChar2 is limited to 4000 char long string) once read the variable?

Can someone help me with the code?

Thanks!

Re: How to read 10000 Char text in SAS and insert into Oracle

nitink26 — Fri, 27 Oct 2023 01:24:20 GMT

I can read the 10000 character variable from sas but unable to put into the oracle table.

Re: How to read 10000 Char text in SAS and insert into Oracle

Tom — Fri, 27 Oct 2023 01:37:12 GMT

Have your checked your DBMAX_TEXT setting?

Either on the LIBNAME statement or as a dataset option.

Re: How to read 10000 Char text in SAS and insert into Oracle

nitink26 — Fri, 27 Oct 2023 01:42:31 GMT

This option doesn't apply for oracle varchar2 ---
Restrictions: This option applies when you retrieve, append, and update rows in an existing table. It does not apply when you create a table.
This option is ignored for CHAR, VARCHAR, and VARCHAR2 (Oracle) data types.

Re: How to read 10000 Char text in SAS and insert into Oracle

LinusH — Fri, 27 Oct 2023 07:49:07 GMT

Do I understand it correctly that the varcghar2 column has a defined max length of 4000?

And that can't be extended?

"Variable-length character string having maximum length size bytes or characters. You must specify size for VARCHAR2. Minimum size is 1 byte or 1 character. Maximum size is:

32767 bytes or characters if MAX_STRING_SIZE = EXTENDED
4000 bytes or characters if MAX_STRING_SIZE = STANDARD"

So question is are you willing to truncate the variable to 4000 chars? If so, there should not be a problem inserting it into Oracle (substr).