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: 3,836

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.

Trusted Advisor
Posts: 1,056

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: 3,836

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.

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 387 views
  • 0 likes
  • 4 in conversation