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

[nitink26]

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!

[nitink26]

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

[Tom · Accepted solution]

Have your checked your DBMAX_TEXT setting?

Either on the LIBNAME statement or as a dataset option.

[nitink26]

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.

[LinusH]

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).