Desktop productivity for business analysts and programmers

while fetching data from SQl thru pass thrugh query column length is big

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

while fetching data from SQl thru pass thrugh query column length is big

Hello ,

i am geting below problem kindly check,

while fetching data from oracle using pass thru query ,the column length is more than 4000 in oracle .but it is defaultly taking 1000 length in sas data set .

for your reference appended code;

proc sql;

connect to oracle(user=developer password="&dbpass" path='cprdev');

execute (execute CPR147_NPA_LOSS_ASSETS(to_date(&DATE1,'yyyymmdd'),(to_date(&DATE6,'yyyymmdd')))) by oracle;

create table rbi_147 as select * from connection to oracle(select  * from NPA_LOSS_ASSETS_RPDG_REP order by SR_NO,NAME_OF_BORROWER );

disconnect from oracle;

quit;

Best Regards,

Ramesh


Accepted Solutions
Solution
‎12-26-2011 07:16 PM
Respected Advisor
Posts: 4,135

while fetching data from SQl thru pass thrugh query column length is big

Agree with DF. Using "dbmax_text" should allow you to retrieve strings up to the maximum a SAS character variable can store (32767 bytes).

If the Oracle variable contains an even longer string (eg. in a CLOB) then you need to split up this variable inside your pass-through SQL. You should find a lot of examples for this via Google as also PL/SQL can only hold variables up to 32K.

View solution in original post


All Replies
Frequent Contributor
Frequent Contributor
Posts: 94

while fetching data from SQl thru pass thrugh query column length is big

SAS has a default limit when converting certain datatypes from Oracle (e.g. RAW).  Have a look at the dbmax_text option at http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#connect.htm .  Hopefully that will help Smiley Happy.

PROC Star
Posts: 1,146

while fetching data from SQl thru pass thrugh query column length is big

I know that VARCHAR variables are very popular in Oracle; SAS doesn't have a variable length character datatype, so this can be a problem when accessing Oracle from SAS. If any of your Oracle variables are longer than 256 bytes, they are probably causing your problems. Could you perhaps define a view on the longer variables that could reduce the size?

Tom

Solution
‎12-26-2011 07:16 PM
Respected Advisor
Posts: 4,135

while fetching data from SQl thru pass thrugh query column length is big

Agree with DF. Using "dbmax_text" should allow you to retrieve strings up to the maximum a SAS character variable can store (32767 bytes).

If the Oracle variable contains an even longer string (eg. in a CLOB) then you need to split up this variable inside your pass-through SQL. You should find a lot of examples for this via Google as also PL/SQL can only hold variables up to 32K.

Contributor
Posts: 31

while fetching data from SQl thru pass thrugh query column length is big

yes DBMAX_TEXT=32767 option working.

New Contributor
Posts: 2

Re: while fetching data from SQl thru pass thrugh query column length is big

Apologies for bumping such an old thread. It comes up consistently with Google though so I hope this will help others.

 

The code below is an effective method for retrieving a single record with a large CLOB. Instead of calculating how many fields to split the clob into resulting in a very wide record, it instead splits it into multiple rows. See expected output at bottom.

 

Disclaimer: Although effective it may not be efficient ie may not scale well to multiple rows, the generally accepted approach then is row pipelining PLSQL. That being said, the below got me out of a pinch...

 

 

PROC SQL;
connect to oracle (authdomain=YOUR_Auth path=devdb DBMAX_TEXT=32767 );

create table clob_chunks (compress=yes) as select * from connection to Oracle ( SELECT id , key , level clob_order , regexp_substr(clob_value, '.{1,32767}', 1, level, 'n') clob_chunk FROM ( SELECT id, key, clob_value FROM schema.table WHERE id = 123 ) CONNECT BY LEVEL <= regexp_count(clob_value, '.{1,32767}',1,'n') ) order by id, key, clob_order; disconnect from oracle; QUIT;

Expected output:

 

ID	KEY	CHUNK	CLOB
1	1	1	short_clob
2	2	1	long clob chunk1of3
2	2	2	long clob chunk2of3
2	2	3	long clob chunk3of3
3	3	1	another_short_one

Explanation:

 

  1. DBMAX_TEXT tells SAS to adjust the default of 1024 for a clob field.
  2. The regex .{1,32767} tells Oracle to match at least once but no more than 32767 times. This splits the input and captures the last chunk which is likely to be under 32767 in length.
  3. The regexp_substr is pulling a chunk from the clob (param1) starting from the start of the clob (param2), skipping to the 'level'th occurance (param3) and treating the clob as one large string (param4 'n').
  4. The connect by re-runs the regex to count the chunks to stop the level incrementing beyond end of the clob.

 

 

References:

 

 

PROC Star
Posts: 1,146

Re: while fetching data from SQl thru pass thrugh query column length is big

Very interesting and useful. Thanks!

 

Tom

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 843 views
  • 0 likes
  • 5 in conversation