Closed skamransajjad closed 3 years ago
Hi!
What exact problems do you have? Some errors or...?
Best regards, Zoran
On Wed, Nov 25, 2020 at 5:17 AM skamransajjad notifications@github.com wrote:
Anyone can help me to use this package in 11g database?
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/zorantica/plsql-qr-code/issues/1, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIAUJST6VMVHEQ4NA4KW53DSRSAODANCNFSM4UB2JH6Q .
Hello,
Many thanks for your email, the problem is with two procedures as below: 1- FUNCTION f_integer_2_binary(p_integer pls_integer) RETURN varchar2 IS lcBinary varchar2(100); BEGIN SELECT LISTAGG(SIGN(BITAND(p_integer, POWER(2, LEVEL-1))),'') WITHIN GROUP(ORDER BY LEVEL DESC) INTO lcBinary FROM dual CONNECT BY POWER(2, LEVEL-1) <= p_integer;
RETURN lcBinary;
END;
The error message is: ERROR line 107, col 12, ending_line 107, ending_col 18, Found 'LISTAGG', Only CUME_DIST, DENSE_RANK, PERCENT_RANK, RANK, PERCENTILE_CONT and PERCENTILE_DISC are valid with WITHIN GROUP
2- FUNCTION f_get_version( at the line SELECT to_number(column_value) a BULK COLLECT INTO lrData FROM XMLTABLE(lrValues(t));
The Error is: ERROR line 831, col 23, ending_line 831, ending_col 30, Found 'lrValues', Expecting: string -or- XMLNAMESPACES
The same package works fine in 12c database.
Please help as I need to use it in 11g
Best regards,
Kamran
On Wed, Nov 25, 2020 at 12:03 PM Zoran Tica notifications@github.com wrote:
Hi!
What exact problems do you have? Some errors or...?
Best regards, Zoran
On Wed, Nov 25, 2020 at 5:17 AM skamransajjad notifications@github.com wrote:
Anyone can help me to use this package in 11g database?
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/zorantica/plsql-qr-code/issues/1, or unsubscribe < https://github.com/notifications/unsubscribe-auth/AIAUJST6VMVHEQ4NA4KW53DSRSAODANCNFSM4UB2JH6Q
.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-733510636, or unsubscribe https://github.com/notifications/unsubscribe-auth/AEVSCKOLJK4KFZZQTM6MQQ3SRST5PANCNFSM4UB2JH6Q .
Hi!
Seems like You're not using the latest 11g database version (R2)... and those 2 statements are not supported. Which database version are you using?
Best regards, Zoran
On Wed, Nov 25, 2020 at 10:59 AM skamransajjad notifications@github.com wrote:
Hello,
Many thanks for your email, the problem is with two procedures as below: 1- FUNCTION f_integer_2_binary(p_integer pls_integer) RETURN varchar2 IS lcBinary varchar2(100); BEGIN SELECT LISTAGG(SIGN(BITAND(p_integer, POWER(2, LEVEL-1))),'') WITHIN GROUP(ORDER BY LEVEL DESC) INTO lcBinary FROM dual CONNECT BY POWER(2, LEVEL-1) <= p_integer;
RETURN lcBinary; END;
The error message is: ERROR line 107, col 12, ending_line 107, ending_col 18, Found 'LISTAGG', Only CUME_DIST, DENSE_RANK, PERCENT_RANK, RANK, PERCENTILE_CONT and PERCENTILE_DISC are valid with WITHIN GROUP
2- FUNCTION f_get_version( at the line SELECT to_number(column_value) a BULK COLLECT INTO lrData FROM XMLTABLE(lrValues(t));
The Error is: ERROR line 831, col 23, ending_line 831, ending_col 30, Found 'lrValues', Expecting: string -or- XMLNAMESPACES
The same package works fine in 12c database.
Please help as I need to use it in 11g
Best regards,
Kamran
On Wed, Nov 25, 2020 at 12:03 PM Zoran Tica notifications@github.com wrote:
Hi!
What exact problems do you have? Some errors or...?
Best regards, Zoran
On Wed, Nov 25, 2020 at 5:17 AM skamransajjad notifications@github.com wrote:
Anyone can help me to use this package in 11g database?
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/zorantica/plsql-qr-code/issues/1, or unsubscribe <
https://github.com/notifications/unsubscribe-auth/AIAUJST6VMVHEQ4NA4KW53DSRSAODANCNFSM4UB2JH6Q
.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub < https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-733510636 , or unsubscribe < https://github.com/notifications/unsubscribe-auth/AEVSCKOLJK4KFZZQTM6MQQ3SRST5PANCNFSM4UB2JH6Q
.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-733601986, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIAUJSQ3HV2BEK3N5NY4Z4DSRTIQDANCNFSM4UB2JH6Q .
Hello,
Sir, I am using 11.2.0.1.0 on Windows 2016.
Best regards
Kamran
On Wed, Nov 25, 2020 at 4:57 PM Zoran Tica notifications@github.com wrote:
Hi!
Seems like You're not using the latest 11g database version (R2)... and those 2 statements are not supported. Which database version are you using?
Best regards, Zoran
On Wed, Nov 25, 2020 at 10:59 AM skamransajjad notifications@github.com wrote:
Hello,
Many thanks for your email, the problem is with two procedures as below: 1- FUNCTION f_integer_2_binary(p_integer pls_integer) RETURN varchar2 IS lcBinary varchar2(100); BEGIN SELECT LISTAGG(SIGN(BITAND(p_integer, POWER(2, LEVEL-1))),'') WITHIN GROUP(ORDER BY LEVEL DESC) INTO lcBinary FROM dual CONNECT BY POWER(2, LEVEL-1) <= p_integer;
RETURN lcBinary; END;
The error message is: ERROR line 107, col 12, ending_line 107, ending_col 18, Found 'LISTAGG', Only CUME_DIST, DENSE_RANK, PERCENT_RANK, RANK, PERCENTILE_CONT and PERCENTILE_DISC are valid with WITHIN GROUP
2- FUNCTION f_get_version( at the line SELECT to_number(column_value) a BULK COLLECT INTO lrData FROM XMLTABLE(lrValues(t));
The Error is: ERROR line 831, col 23, ending_line 831, ending_col 30, Found 'lrValues', Expecting: string -or- XMLNAMESPACES
The same package works fine in 12c database.
Please help as I need to use it in 11g
Best regards,
Kamran
On Wed, Nov 25, 2020 at 12:03 PM Zoran Tica notifications@github.com wrote:
Hi!
What exact problems do you have? Some errors or...?
Best regards, Zoran
On Wed, Nov 25, 2020 at 5:17 AM skamransajjad < notifications@github.com> wrote:
Anyone can help me to use this package in 11g database?
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/zorantica/plsql-qr-code/issues/1, or unsubscribe <
https://github.com/notifications/unsubscribe-auth/AIAUJST6VMVHEQ4NA4KW53DSRSAODANCNFSM4UB2JH6Q
.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <
https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-733510636
, or unsubscribe <
https://github.com/notifications/unsubscribe-auth/AEVSCKOLJK4KFZZQTM6MQQ3SRST5PANCNFSM4UB2JH6Q
.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub < https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-733601986 , or unsubscribe < https://github.com/notifications/unsubscribe-auth/AIAUJSQ3HV2BEK3N5NY4Z4DSRTIQDANCNFSM4UB2JH6Q
.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-733663183, or unsubscribe https://github.com/notifications/unsubscribe-auth/AEVSCKK6CUAURGCPJIGW2BLSRTWKZANCNFSM4UB2JH6Q .
I checked some info regarding LISTAGG function... It was introduced in Oracle 11g R2... but I'm pretty sure that Your database version is not high enough and it is not supported.
There are other solutions You can try instead of LISTAGG function. Please check the following link. Tim Hall wrote an article on how to aggregate strings in various ways. You can try replacing LISTAGG function with WM_CONCAT (if it is still supported in Your database version) or use some other method.
Now the problem with XMLTABLE function... It should be available in Your database version (it was introduced in 10g) but I think that the problem is with input parameter type. I presume this type is not supported in Your database version. The meaning of the problematic SELECT statement is to take the comma separated string from collection ( lrValues(t) ), break it into elements and store those elements into another lrData collection. You can try to replace this SELECT statement with Your custom code, it should not be too complex to write.
Please let me know if You managed to solve those problems.
Best regards, Zoran
On Wed, Nov 25, 2020 at 4:20 PM skamransajjad notifications@github.com wrote:
Hello,
Sir, I am using 11.2.0.1.0 on Windows 2016.
Best regards
Kamran
On Wed, Nov 25, 2020 at 4:57 PM Zoran Tica notifications@github.com wrote:
Hi!
Seems like You're not using the latest 11g database version (R2)... and those 2 statements are not supported. Which database version are you using?
Best regards, Zoran
On Wed, Nov 25, 2020 at 10:59 AM skamransajjad <notifications@github.com
wrote:
Hello,
Many thanks for your email, the problem is with two procedures as below: 1- FUNCTION f_integer_2_binary(p_integer pls_integer) RETURN varchar2 IS lcBinary varchar2(100); BEGIN SELECT LISTAGG(SIGN(BITAND(p_integer, POWER(2, LEVEL-1))),'') WITHIN GROUP(ORDER BY LEVEL DESC) INTO lcBinary FROM dual CONNECT BY POWER(2, LEVEL-1) <= p_integer;
RETURN lcBinary; END;
The error message is: ERROR line 107, col 12, ending_line 107, ending_col 18, Found 'LISTAGG', Only CUME_DIST, DENSE_RANK, PERCENT_RANK, RANK, PERCENTILE_CONT and PERCENTILE_DISC are valid with WITHIN GROUP
2- FUNCTION f_get_version( at the line SELECT to_number(column_value) a BULK COLLECT INTO lrData FROM XMLTABLE(lrValues(t));
The Error is: ERROR line 831, col 23, ending_line 831, ending_col 30, Found 'lrValues', Expecting: string -or- XMLNAMESPACES
The same package works fine in 12c database.
Please help as I need to use it in 11g
Best regards,
Kamran
On Wed, Nov 25, 2020 at 12:03 PM Zoran Tica notifications@github.com wrote:
Hi!
What exact problems do you have? Some errors or...?
Best regards, Zoran
On Wed, Nov 25, 2020 at 5:17 AM skamransajjad < notifications@github.com> wrote:
Anyone can help me to use this package in 11g database?
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/zorantica/plsql-qr-code/issues/1, or unsubscribe <
https://github.com/notifications/unsubscribe-auth/AIAUJST6VMVHEQ4NA4KW53DSRSAODANCNFSM4UB2JH6Q
.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <
https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-733510636
, or unsubscribe <
https://github.com/notifications/unsubscribe-auth/AEVSCKOLJK4KFZZQTM6MQQ3SRST5PANCNFSM4UB2JH6Q
.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub <
https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-733601986
, or unsubscribe <
https://github.com/notifications/unsubscribe-auth/AIAUJSQ3HV2BEK3N5NY4Z4DSRTIQDANCNFSM4UB2JH6Q
.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub < https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-733663183 , or unsubscribe < https://github.com/notifications/unsubscribe-auth/AEVSCKK6CUAURGCPJIGW2BLSRTWKZANCNFSM4UB2JH6Q
.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-733771004, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIAUJSS2ACB3EUY7WK6MB4LSRUOD7ANCNFSM4UB2JH6Q .
Hello,
Many thanks for your help sir, It worked very well. the problem was with "XMLTABLE(lrValues(t))" this not compatible with my DB version. I replaced it with regular expression as suggested by you and it worked for me. I don't have words to thank you for your time and support.
THANKS, A LOT. 😃😃
Best regards, Kamran
On Thu, Nov 26, 2020 at 12:55 PM Zoran Tica notifications@github.com wrote:
I checked some info regarding LISTAGG function... It was introduced in Oracle 11g R2... but I'm pretty sure that Your database version is not high enough and it is not supported.
There are other solutions You can try instead of LISTAGG function. Please check the following link. Tim Hall wrote an article on how to aggregate strings in various ways. You can try replacing LISTAGG function with WM_CONCAT (if it is still supported in Your database version) or use some other method.
Now the problem with XMLTABLE function... It should be available in Your database version (it was introduced in 10g) but I think that the problem is with input parameter type. I presume this type is not supported in Your database version. The meaning of the problematic SELECT statement is to take the comma separated string from collection ( lrValues(t) ), break it into elements and store those elements into another lrData collection. You can try to replace this SELECT statement with Your custom code, it should not be too complex to write.
Please let me know if You managed to solve those problems.
Best regards, Zoran
On Wed, Nov 25, 2020 at 4:20 PM skamransajjad notifications@github.com wrote:
Hello,
Sir, I am using 11.2.0.1.0 on Windows 2016.
Best regards
Kamran
On Wed, Nov 25, 2020 at 4:57 PM Zoran Tica notifications@github.com wrote:
Hi!
Seems like You're not using the latest 11g database version (R2)... and those 2 statements are not supported. Which database version are you using?
Best regards, Zoran
On Wed, Nov 25, 2020 at 10:59 AM skamransajjad < notifications@github.com
wrote:
Hello,
Many thanks for your email, the problem is with two procedures as below: 1- FUNCTION f_integer_2_binary(p_integer pls_integer) RETURN varchar2 IS lcBinary varchar2(100); BEGIN SELECT LISTAGG(SIGN(BITAND(p_integer, POWER(2, LEVEL-1))),'') WITHIN GROUP(ORDER BY LEVEL DESC) INTO lcBinary FROM dual CONNECT BY POWER(2, LEVEL-1) <= p_integer;
RETURN lcBinary; END;
The error message is: ERROR line 107, col 12, ending_line 107, ending_col 18, Found 'LISTAGG', Only CUME_DIST, DENSE_RANK, PERCENT_RANK, RANK, PERCENTILE_CONT and PERCENTILE_DISC are valid with WITHIN GROUP
2- FUNCTION f_get_version( at the line SELECT to_number(column_value) a BULK COLLECT INTO lrData FROM XMLTABLE(lrValues(t));
The Error is: ERROR line 831, col 23, ending_line 831, ending_col 30, Found 'lrValues', Expecting: string -or- XMLNAMESPACES
The same package works fine in 12c database.
Please help as I need to use it in 11g
Best regards,
Kamran
On Wed, Nov 25, 2020 at 12:03 PM Zoran Tica < notifications@github.com> wrote:
Hi!
What exact problems do you have? Some errors or...?
Best regards, Zoran
On Wed, Nov 25, 2020 at 5:17 AM skamransajjad < notifications@github.com> wrote:
Anyone can help me to use this package in 11g database?
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/zorantica/plsql-qr-code/issues/1, or unsubscribe <
https://github.com/notifications/unsubscribe-auth/AIAUJST6VMVHEQ4NA4KW53DSRSAODANCNFSM4UB2JH6Q
.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <
https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-733510636
, or unsubscribe <
https://github.com/notifications/unsubscribe-auth/AEVSCKOLJK4KFZZQTM6MQQ3SRST5PANCNFSM4UB2JH6Q
.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub <
https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-733601986
, or unsubscribe <
https://github.com/notifications/unsubscribe-auth/AIAUJSQ3HV2BEK3N5NY4Z4DSRTIQDANCNFSM4UB2JH6Q
.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <
https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-733663183
, or unsubscribe <
https://github.com/notifications/unsubscribe-auth/AEVSCKK6CUAURGCPJIGW2BLSRTWKZANCNFSM4UB2JH6Q
.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub < https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-733771004 , or unsubscribe < https://github.com/notifications/unsubscribe-auth/AIAUJSS2ACB3EUY7WK6MB4LSRUOD7ANCNFSM4UB2JH6Q
.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-734136295, or unsubscribe https://github.com/notifications/unsubscribe-auth/AEVSCKOIJER5BP4CPAMDGZTSRYCVZANCNFSM4UB2JH6Q .
You're welcome Kamran. Glad that I could help!
If You encounter any problems don't hesitate to contact me.
Wish you all the best! Zoran
On Thu, Nov 26, 2020 at 8:48 PM skamransajjad notifications@github.com wrote:
Hello,
Many thanks for your help sir, It worked very well. the problem was with "XMLTABLE(lrValues(t))" this not compatible with my DB version. I replaced it with regular expression as suggested by you and it worked for me. I don't have words to thank you for your time and support.
THANKS, A LOT. 😃😃
Best regards, Kamran
On Thu, Nov 26, 2020 at 12:55 PM Zoran Tica notifications@github.com wrote:
I checked some info regarding LISTAGG function... It was introduced in Oracle 11g R2... but I'm pretty sure that Your database version is not high enough and it is not supported.
There are other solutions You can try instead of LISTAGG function. Please check the following link. Tim Hall wrote an article on how to aggregate strings in various ways. You can try replacing LISTAGG function with WM_CONCAT (if it is still supported in Your database version) or use some other method.
Now the problem with XMLTABLE function... It should be available in Your database version (it was introduced in 10g) but I think that the problem is with input parameter type. I presume this type is not supported in Your database version. The meaning of the problematic SELECT statement is to take the comma separated string from collection ( lrValues(t) ), break it into elements and store those elements into another lrData collection. You can try to replace this SELECT statement with Your custom code, it should not be too complex to write.
Please let me know if You managed to solve those problems.
Best regards, Zoran
On Wed, Nov 25, 2020 at 4:20 PM skamransajjad notifications@github.com wrote:
Hello,
Sir, I am using 11.2.0.1.0 on Windows 2016.
Best regards
Kamran
On Wed, Nov 25, 2020 at 4:57 PM Zoran Tica notifications@github.com wrote:
Hi!
Seems like You're not using the latest 11g database version (R2)... and those 2 statements are not supported. Which database version are you using?
Best regards, Zoran
On Wed, Nov 25, 2020 at 10:59 AM skamransajjad < notifications@github.com
wrote:
Hello,
Many thanks for your email, the problem is with two procedures as below: 1- FUNCTION f_integer_2_binary(p_integer pls_integer) RETURN varchar2 IS lcBinary varchar2(100); BEGIN SELECT LISTAGG(SIGN(BITAND(p_integer, POWER(2, LEVEL-1))),'') WITHIN GROUP(ORDER BY LEVEL DESC) INTO lcBinary FROM dual CONNECT BY POWER(2, LEVEL-1) <= p_integer;
RETURN lcBinary; END;
The error message is: ERROR line 107, col 12, ending_line 107, ending_col 18, Found 'LISTAGG', Only CUME_DIST, DENSE_RANK, PERCENT_RANK, RANK, PERCENTILE_CONT and PERCENTILE_DISC are valid with WITHIN GROUP
2- FUNCTION f_get_version( at the line SELECT to_number(column_value) a BULK COLLECT INTO lrData FROM XMLTABLE(lrValues(t));
The Error is: ERROR line 831, col 23, ending_line 831, ending_col 30, Found 'lrValues', Expecting: string -or- XMLNAMESPACES
The same package works fine in 12c database.
Please help as I need to use it in 11g
Best regards,
Kamran
On Wed, Nov 25, 2020 at 12:03 PM Zoran Tica < notifications@github.com> wrote:
Hi!
What exact problems do you have? Some errors or...?
Best regards, Zoran
On Wed, Nov 25, 2020 at 5:17 AM skamransajjad < notifications@github.com> wrote:
Anyone can help me to use this package in 11g database?
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/zorantica/plsql-qr-code/issues/1, or unsubscribe <
https://github.com/notifications/unsubscribe-auth/AIAUJST6VMVHEQ4NA4KW53DSRSAODANCNFSM4UB2JH6Q
.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <
https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-733510636
, or unsubscribe <
https://github.com/notifications/unsubscribe-auth/AEVSCKOLJK4KFZZQTM6MQQ3SRST5PANCNFSM4UB2JH6Q
.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub <
https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-733601986
, or unsubscribe <
https://github.com/notifications/unsubscribe-auth/AIAUJSQ3HV2BEK3N5NY4Z4DSRTIQDANCNFSM4UB2JH6Q
.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <
https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-733663183
, or unsubscribe <
https://github.com/notifications/unsubscribe-auth/AEVSCKK6CUAURGCPJIGW2BLSRTWKZANCNFSM4UB2JH6Q
.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub <
https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-733771004
, or unsubscribe <
https://github.com/notifications/unsubscribe-auth/AIAUJSS2ACB3EUY7WK6MB4LSRUOD7ANCNFSM4UB2JH6Q
.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub < https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-734136295 , or unsubscribe < https://github.com/notifications/unsubscribe-auth/AEVSCKOIJER5BP4CPAMDGZTSRYCVZANCNFSM4UB2JH6Q
.
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/zorantica/plsql-qr-code/issues/1#issuecomment-734457825, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIAUJSXUXYNBIQLKELKBBH3SR2WKDANCNFSM4UB2JH6Q .
Anyone can help me to use this package in 11g database?