Oracle CLOB column and LAG

I'm facing a problem when I try to use LAG function on CLOB column.

So let's assume we have a table

create table test (

    id number primary key, 

    not_clob varchar2(255),

    this_is_clob clob

);



insert into test values (1, 'test1', to_clob('clob1'));

insert into test values (2, 'test2', to_clob('clob2'));



DECLARE

x CLOB := 'C';

BEGIN



 FOR i in 1..32767

 LOOP

  x := x||'C';

 END LOOP;



 INSERT INTO test(id,not_clob,this_is_clob) values(3,'test3',x);



END;

/



commit;

Now let's do a select using non-clob columns

select id, lag(not_clob) over (order by id) from test;

It works fine as expected, but when I try the same with clob column

select id, lag(this_is_clob) over (order by id) from test;

I get

ORA-00932: inconsistent datatypes: expected - got CLOB

00932. 00000 -  "inconsistent datatypes: expected %s got %s"

*Cause:    

*Action:

Error at Line: 1 Column: 16

Can you tell me what's the solution of this problem as I couldn't find anything on that.

edited Nov 16 '18 at 13:07

asked Nov 16 '18 at 10:07

Alexey

1,54011022

add a comment |

I'm facing a problem when I try to use LAG function on CLOB column.

So let's assume we have a table

create table test (

    id number primary key, 

    not_clob varchar2(255),

    this_is_clob clob

);



insert into test values (1, 'test1', to_clob('clob1'));

insert into test values (2, 'test2', to_clob('clob2'));



DECLARE

x CLOB := 'C';

BEGIN



 FOR i in 1..32767

 LOOP

  x := x||'C';

 END LOOP;



 INSERT INTO test(id,not_clob,this_is_clob) values(3,'test3',x);



END;

/



commit;

Now let's do a select using non-clob columns

select id, lag(not_clob) over (order by id) from test;

It works fine as expected, but when I try the same with clob column

select id, lag(this_is_clob) over (order by id) from test;

I get

ORA-00932: inconsistent datatypes: expected - got CLOB

00932. 00000 -  "inconsistent datatypes: expected %s got %s"

*Cause:    

*Action:

Error at Line: 1 Column: 16

Can you tell me what's the solution of this problem as I couldn't find anything on that.

edited Nov 16 '18 at 13:07

asked Nov 16 '18 at 10:07

Alexey

1,54011022

add a comment |

I'm facing a problem when I try to use LAG function on CLOB column.

So let's assume we have a table

create table test (

    id number primary key, 

    not_clob varchar2(255),

    this_is_clob clob

);



insert into test values (1, 'test1', to_clob('clob1'));

insert into test values (2, 'test2', to_clob('clob2'));



DECLARE

x CLOB := 'C';

BEGIN



 FOR i in 1..32767

 LOOP

  x := x||'C';

 END LOOP;



 INSERT INTO test(id,not_clob,this_is_clob) values(3,'test3',x);



END;

/



commit;

Now let's do a select using non-clob columns

select id, lag(not_clob) over (order by id) from test;

It works fine as expected, but when I try the same with clob column

select id, lag(this_is_clob) over (order by id) from test;

I get

ORA-00932: inconsistent datatypes: expected - got CLOB

00932. 00000 -  "inconsistent datatypes: expected %s got %s"

*Cause:    

*Action:

Error at Line: 1 Column: 16

Can you tell me what's the solution of this problem as I couldn't find anything on that.

edited Nov 16 '18 at 13:07

asked Nov 16 '18 at 10:07

Alexey

1,54011022

I'm facing a problem when I try to use LAG function on CLOB column.

So let's assume we have a table

create table test (

    id number primary key, 

    not_clob varchar2(255),

    this_is_clob clob

);



insert into test values (1, 'test1', to_clob('clob1'));

insert into test values (2, 'test2', to_clob('clob2'));



DECLARE

x CLOB := 'C';

BEGIN



 FOR i in 1..32767

 LOOP

  x := x||'C';

 END LOOP;



 INSERT INTO test(id,not_clob,this_is_clob) values(3,'test3',x);



END;

/



commit;

Now let's do a select using non-clob columns

select id, lag(not_clob) over (order by id) from test;

It works fine as expected, but when I try the same with clob column

select id, lag(this_is_clob) over (order by id) from test;

I get

ORA-00932: inconsistent datatypes: expected - got CLOB

00932. 00000 -  "inconsistent datatypes: expected %s got %s"

*Cause:    

*Action:

Error at Line: 1 Column: 16

Can you tell me what's the solution of this problem as I couldn't find anything on that.

oracle oracle11g

edited Nov 16 '18 at 13:07

asked Nov 16 '18 at 10:07

Alexey

1,54011022

edited Nov 16 '18 at 13:07

asked Nov 16 '18 at 10:07

Alexey

1,54011022

edited Nov 16 '18 at 13:07

asked Nov 16 '18 at 10:07

Alexey

1,54011022

asked Nov 16 '18 at 10:07

Alexey

1,54011022

asked Nov 16 '18 at 10:07

Alexey

1,54011022

add a comment |

2 Answers
2

active

oldest

votes

The documentation says the argument for any analytic function can be any datatype but it seems unrestricted CLOB is not supported.

However, there is a workaround:

select id, lag(dbms_lob.substr(this_is_clob, 4000, 1)) over (order by id) 

from test;

This is not the whole CLOB but 4k should be good enough in many cases.

I'm still wondering what is the proper way to overcome the problem

Is upgrading to 12c an option? The problem is nothing to do with CLOB as such, it's the fact that Oracle has a hard limit for strings in SQL of 4000 characters. In 12c we have the option to use extended data types (providing we can persuade our DBAs to turn it on!). Find out more.

edited Nov 16 '18 at 12:23

answered Nov 16 '18 at 10:41

APC

120k15119230

Thanks, this works really good. I also couldn't find anything on limitations regarding LOB columns in documentation.

– Alexey
Nov 16 '18 at 10:49

@Alexey : This does not work when this_is_clob > 4000 characters. 32767 is the VARCHAR2 limit for PL/SQL in 11g and on certain settings in 12c and above for sql, but not in 11g. But the SQL limit is 4000 and dbms_lob.substr returns VARCHAR2 for CLOB input. See dbfiddle.uk/…

– Kaushik Nayak
Nov 16 '18 at 11:05

@KaushikNayak Hm, actually you're right. Though at least we can change dbms_lob.substr(this_is_clob, 32767, 1) to dbms_lob.substr(this_is_clob, 4000, 1) (with to_char we'll always get an exception when column length exceeds 4000)

– Alexey
Nov 16 '18 at 12:00

It's better than nothing, though I'm still wondering what is the proper way to overcome the problem

– Alexey
Nov 16 '18 at 12:01

@APC Unfortunately migrating to 12c isn't an option :( Thanks for the explanation anyway. At least now I can understand why it happens

– Alexey
Nov 16 '18 at 13:27

add a comment |

Some of the features may not work properly in SQL when using CLOBs(like DISTINCT , ORDER BY GROUP BY etc. Looks like LAG is also one of them but, I couldn't find anywhere in docs.

If your values in the CLOB columns are always less than 4000 characters, you may use TO_CHAR

select id, lag( TO_CHAR(this_is_clob)) over (order by id) from test;

convert it into an equivalent SELF JOIN ( may not be as efficient as LAG )

SELECT a.id,

       b.this_is_clob AS lagging

FROM test a

LEFT JOIN test b ON b.id < a.id;

Demo

answered Nov 16 '18 at 10:38

Kaushik Nayak

21.2k41332

Thanks for your reply, self join isn't a full replacement when you need more than one previous row (you have to do more than one self join in that case as in the original table order of ids isn't that obvious).

– Alexey
Nov 16 '18 at 10:50

add a comment |

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53335563%2foracle-clob-column-and-lag%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

The documentation says the argument for any analytic function can be any datatype but it seems unrestricted CLOB is not supported.

However, there is a workaround:

select id, lag(dbms_lob.substr(this_is_clob, 4000, 1)) over (order by id) 

from test;

This is not the whole CLOB but 4k should be good enough in many cases.

I'm still wondering what is the proper way to overcome the problem

edited Nov 16 '18 at 12:23

answered Nov 16 '18 at 10:41

APC

120k15119230

Thanks, this works really good. I also couldn't find anything on limitations regarding LOB columns in documentation.

– Alexey
Nov 16 '18 at 10:49

@Alexey : This does not work when this_is_clob > 4000 characters. 32767 is the VARCHAR2 limit for PL/SQL in 11g and on certain settings in 12c and above for sql, but not in 11g. But the SQL limit is 4000 and dbms_lob.substr returns VARCHAR2 for CLOB input. See dbfiddle.uk/…

– Kaushik Nayak
Nov 16 '18 at 11:05

@KaushikNayak Hm, actually you're right. Though at least we can change dbms_lob.substr(this_is_clob, 32767, 1) to dbms_lob.substr(this_is_clob, 4000, 1) (with to_char we'll always get an exception when column length exceeds 4000)

– Alexey
Nov 16 '18 at 12:00

It's better than nothing, though I'm still wondering what is the proper way to overcome the problem

– Alexey
Nov 16 '18 at 12:01

@APC Unfortunately migrating to 12c isn't an option :( Thanks for the explanation anyway. At least now I can understand why it happens

– Alexey
Nov 16 '18 at 13:27

add a comment |

The documentation says the argument for any analytic function can be any datatype but it seems unrestricted CLOB is not supported.

However, there is a workaround:

select id, lag(dbms_lob.substr(this_is_clob, 4000, 1)) over (order by id) 

from test;

This is not the whole CLOB but 4k should be good enough in many cases.

I'm still wondering what is the proper way to overcome the problem

edited Nov 16 '18 at 12:23

answered Nov 16 '18 at 10:41

APC

120k15119230

Thanks, this works really good. I also couldn't find anything on limitations regarding LOB columns in documentation.

– Alexey
Nov 16 '18 at 10:49

@Alexey : This does not work when this_is_clob > 4000 characters. 32767 is the VARCHAR2 limit for PL/SQL in 11g and on certain settings in 12c and above for sql, but not in 11g. But the SQL limit is 4000 and dbms_lob.substr returns VARCHAR2 for CLOB input. See dbfiddle.uk/…

– Kaushik Nayak
Nov 16 '18 at 11:05

@KaushikNayak Hm, actually you're right. Though at least we can change dbms_lob.substr(this_is_clob, 32767, 1) to dbms_lob.substr(this_is_clob, 4000, 1) (with to_char we'll always get an exception when column length exceeds 4000)

– Alexey
Nov 16 '18 at 12:00

It's better than nothing, though I'm still wondering what is the proper way to overcome the problem

– Alexey
Nov 16 '18 at 12:01

@APC Unfortunately migrating to 12c isn't an option :( Thanks for the explanation anyway. At least now I can understand why it happens

– Alexey
Nov 16 '18 at 13:27

add a comment |

The documentation says the argument for any analytic function can be any datatype but it seems unrestricted CLOB is not supported.

However, there is a workaround:

select id, lag(dbms_lob.substr(this_is_clob, 4000, 1)) over (order by id) 

from test;

This is not the whole CLOB but 4k should be good enough in many cases.

I'm still wondering what is the proper way to overcome the problem

edited Nov 16 '18 at 12:23

answered Nov 16 '18 at 10:41

APC

120k15119230

The documentation says the argument for any analytic function can be any datatype but it seems unrestricted CLOB is not supported.

However, there is a workaround:

select id, lag(dbms_lob.substr(this_is_clob, 4000, 1)) over (order by id) 

from test;

This is not the whole CLOB but 4k should be good enough in many cases.

I'm still wondering what is the proper way to overcome the problem

edited Nov 16 '18 at 12:23

answered Nov 16 '18 at 10:41

APC

120k15119230

edited Nov 16 '18 at 12:23

answered Nov 16 '18 at 10:41

APC

120k15119230

answered Nov 16 '18 at 10:41

APC

120k15119230

answered Nov 16 '18 at 10:41

APC

120k15119230

Thanks, this works really good. I also couldn't find anything on limitations regarding LOB columns in documentation.

– Alexey
Nov 16 '18 at 10:49

@Alexey : This does not work when this_is_clob > 4000 characters. 32767 is the VARCHAR2 limit for PL/SQL in 11g and on certain settings in 12c and above for sql, but not in 11g. But the SQL limit is 4000 and dbms_lob.substr returns VARCHAR2 for CLOB input. See dbfiddle.uk/…

– Kaushik Nayak
Nov 16 '18 at 11:05

@KaushikNayak Hm, actually you're right. Though at least we can change dbms_lob.substr(this_is_clob, 32767, 1) to dbms_lob.substr(this_is_clob, 4000, 1) (with to_char we'll always get an exception when column length exceeds 4000)

– Alexey
Nov 16 '18 at 12:00

It's better than nothing, though I'm still wondering what is the proper way to overcome the problem

– Alexey
Nov 16 '18 at 12:01

@APC Unfortunately migrating to 12c isn't an option :( Thanks for the explanation anyway. At least now I can understand why it happens

– Alexey
Nov 16 '18 at 13:27

add a comment |

Thanks, this works really good. I also couldn't find anything on limitations regarding LOB columns in documentation.

– Alexey
Nov 16 '18 at 10:49

@Alexey : This does not work when this_is_clob > 4000 characters. 32767 is the VARCHAR2 limit for PL/SQL in 11g and on certain settings in 12c and above for sql, but not in 11g. But the SQL limit is 4000 and dbms_lob.substr returns VARCHAR2 for CLOB input. See dbfiddle.uk/…

– Kaushik Nayak
Nov 16 '18 at 11:05

@KaushikNayak Hm, actually you're right. Though at least we can change dbms_lob.substr(this_is_clob, 32767, 1) to dbms_lob.substr(this_is_clob, 4000, 1) (with to_char we'll always get an exception when column length exceeds 4000)

– Alexey
Nov 16 '18 at 12:00

It's better than nothing, though I'm still wondering what is the proper way to overcome the problem

– Alexey
Nov 16 '18 at 12:01

@APC Unfortunately migrating to 12c isn't an option :( Thanks for the explanation anyway. At least now I can understand why it happens

– Alexey
Nov 16 '18 at 13:27

Thanks, this works really good. I also couldn't find anything on limitations regarding LOB columns in documentation.

– Alexey
Nov 16 '18 at 10:49

@Alexey : This does not work when this_is_clob > 4000 characters. 32767 is the VARCHAR2 limit for PL/SQL in 11g and on certain settings in 12c and above for sql, but not in 11g. But the SQL limit is 4000 and dbms_lob.substr returns VARCHAR2 for CLOB input. See dbfiddle.uk/…

– Kaushik Nayak
Nov 16 '18 at 11:05

@KaushikNayak Hm, actually you're right. Though at least we can change dbms_lob.substr(this_is_clob, 32767, 1) to dbms_lob.substr(this_is_clob, 4000, 1) (with to_char we'll always get an exception when column length exceeds 4000)

– Alexey
Nov 16 '18 at 12:00

It's better than nothing, though I'm still wondering what is the proper way to overcome the problem

– Alexey
Nov 16 '18 at 12:01

@APC Unfortunately migrating to 12c isn't an option :( Thanks for the explanation anyway. At least now I can understand why it happens

– Alexey
Nov 16 '18 at 13:27

add a comment |

Some of the features may not work properly in SQL when using CLOBs(like DISTINCT , ORDER BY GROUP BY etc. Looks like LAG is also one of them but, I couldn't find anywhere in docs.

If your values in the CLOB columns are always less than 4000 characters, you may use TO_CHAR

select id, lag( TO_CHAR(this_is_clob)) over (order by id) from test;

convert it into an equivalent SELF JOIN ( may not be as efficient as LAG )

SELECT a.id,

       b.this_is_clob AS lagging

FROM test a

LEFT JOIN test b ON b.id < a.id;

Demo

answered Nov 16 '18 at 10:38

Kaushik Nayak

21.2k41332

Thanks for your reply, self join isn't a full replacement when you need more than one previous row (you have to do more than one self join in that case as in the original table order of ids isn't that obvious).

– Alexey
Nov 16 '18 at 10:50

add a comment |

Some of the features may not work properly in SQL when using CLOBs(like DISTINCT , ORDER BY GROUP BY etc. Looks like LAG is also one of them but, I couldn't find anywhere in docs.

If your values in the CLOB columns are always less than 4000 characters, you may use TO_CHAR

select id, lag( TO_CHAR(this_is_clob)) over (order by id) from test;

convert it into an equivalent SELF JOIN ( may not be as efficient as LAG )

SELECT a.id,

       b.this_is_clob AS lagging

FROM test a

LEFT JOIN test b ON b.id < a.id;

Demo

answered Nov 16 '18 at 10:38

Kaushik Nayak

21.2k41332

Thanks for your reply, self join isn't a full replacement when you need more than one previous row (you have to do more than one self join in that case as in the original table order of ids isn't that obvious).

– Alexey
Nov 16 '18 at 10:50

add a comment |

Some of the features may not work properly in SQL when using CLOBs(like DISTINCT , ORDER BY GROUP BY etc. Looks like LAG is also one of them but, I couldn't find anywhere in docs.

If your values in the CLOB columns are always less than 4000 characters, you may use TO_CHAR

select id, lag( TO_CHAR(this_is_clob)) over (order by id) from test;

convert it into an equivalent SELF JOIN ( may not be as efficient as LAG )

SELECT a.id,

       b.this_is_clob AS lagging

FROM test a

LEFT JOIN test b ON b.id < a.id;

Demo

answered Nov 16 '18 at 10:38

Kaushik Nayak

21.2k41332

Some of the features may not work properly in SQL when using CLOBs(like DISTINCT , ORDER BY GROUP BY etc. Looks like LAG is also one of them but, I couldn't find anywhere in docs.

If your values in the CLOB columns are always less than 4000 characters, you may use TO_CHAR

select id, lag( TO_CHAR(this_is_clob)) over (order by id) from test;

convert it into an equivalent SELF JOIN ( may not be as efficient as LAG )

SELECT a.id,

       b.this_is_clob AS lagging

FROM test a

LEFT JOIN test b ON b.id < a.id;

Demo

answered Nov 16 '18 at 10:38

Kaushik Nayak

21.2k41332

answered Nov 16 '18 at 10:38

Kaushik Nayak

21.2k41332

answered Nov 16 '18 at 10:38

Kaushik Nayak

21.2k41332

answered Nov 16 '18 at 10:38

Kaushik Nayak

21.2k41332

Thanks for your reply, self join isn't a full replacement when you need more than one previous row (you have to do more than one self join in that case as in the original table order of ids isn't that obvious).

– Alexey
Nov 16 '18 at 10:50

add a comment |

Thanks for your reply, self join isn't a full replacement when you need more than one previous row (you have to do more than one self join in that case as in the original table order of ids isn't that obvious).

– Alexey
Nov 16 '18 at 10:50

Thanks for your reply, self join isn't a full replacement when you need more than one previous row (you have to do more than one self join in that case as in the original table order of ids isn't that obvious).

– Alexey
Nov 16 '18 at 10:50

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Vfrdtyky