Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Group by comma separated string

584873Nov 16 2010 — edited Nov 17 2010
i all,

Oracle 11 g
I have the following table and data


CREATE TABLE dummy_data (
ID number NOT NULL
,test_Timestamp timestamp NOT NULL,
groups varchar2(500)
);
insert into dummy_data (id,test_TimeStamp,groups) values (1,'08-MAR-10 09.43.30.922000000','group1,group2,group3');
insert into dummy_data (id,test_TimeStamp,groups) values (2,'08-MAR-10 09.46.30.922000000','group1,group2');
insert into dummy_data (id,test_TimeStamp,groups) values (3,'08-MAR-10 09.23.30.922000000','group2,group4');
insert into dummy_data (id,test_TimeStamp,groups) values (4,'08-MAR-10 09.26.30.922000000','group4');
insert into dummy_data (id,test_TimeStamp,groups) values (5,'08-MAR-10 09.13.30.922000000','group3,group4');
insert into dummy_data (id,test_TimeStamp,groups) values (6,'08-MAR-10 09.12.30.922000000','group3');
commit;

I need to group the group names based on 10 minute interval of time stamp . In other words, this is the result I am looking for
08-MAR-10 09.40.00 - group1 - 2
08-MAR-10 09.40.00 - group2 - 2
08-MAR-10 09.40.00 - group3 - 1
08-MAR-10 09.20.00 - group2 - 1
08-MAR-10 09.20.00 - group4 - 2
08-MAR-10 09.10.00 - group4 - 1
08-MAR-10 09.10.00 - group3 - 2

Anyway to parse using regular expression ?

Thanks
_Pete

Comments

William Robertson

A lot of lines will be part comment, so I'm not sure this will be very useful. If you are searching source code for references to chk_this excluding comment lines, you will still find it in

new_thing();  -- Similar to chk_this but for bananas

and so on.

Manik

A lot of lines will be part comment, so I'm not sure this will be very useful. If you are searching source code for references to chk_this excluding comment lines, you will still find it in

new_thing(); _-- Similar to chk_this but for bananas_

and so on.

Hi., firstly thanks for your comment. I agree that there are lot of possibilities to present the comment situations.

One of the other possibilities can be as depicted below as well.

pastedImage_0.png

My intention is just to start something in the lines of classifying commented text. where its a "complete" comment. like the one stated in the original question.

May be oracle can convert your example as : (observe line number does not add up because of the comment.)

Line number text Comment_flag

10 new_thing(); 'N'

_-10 -- Similar to chk_this but for bananas 'Y'_

------------------------------------------

And second example may be transformed as:

-------------------------------

Line Number text comment_flag

10 begin 'N'

11 new_thing(a,b 'N'

-11 /* this is optional parameter */ 'Y'

11 ,c); 'N'

12 end; 'N'

Again this is just a thought.. Comments welcome !

Cheers,

Manik.

Mike Kutz

Hi., firstly thanks for your comment. I agree that there are lot of possibilities to present the comment situations.

One of the other possibilities can be as depicted below as well.

pastedImage_0.png

My intention is just to start something in the lines of classifying commented text. where its a "complete" comment. like the one stated in the original question.

May be oracle can convert your example as : (observe line number does not add up because of the comment.)

Line number text Comment_flag

10 new_thing(); 'N'

_-10 -- Similar to chk_this but for bananas 'Y'_

------------------------------------------

And second example may be transformed as:

-------------------------------

Line Number text comment_flag

10 begin 'N'

11 new_thing(a,b 'N'

-11 /* this is optional parameter */ 'Y'

11 ,c); 'N'

12 end; 'N'

Again this is just a thought.. Comments welcome !

Cheers,

Manik.

I'm still not understanding why you want to know if LINE 5 is a comment line or not.

Personally, I would prefer some way (dbms package? XML_COMMENTS field? ) to extract the PL/Doc style comments so I can create something with that information.

I feel that knowing "Line 5 is a comment" is useless.

Knowing the PL/Doc comment for "function LETS_KILL_THE_DBA return int" states that the function "orders a pizza using the DBA's CC" would be more useful.

MK

BEDE

I think that if you select * from all_source where lower(text) like '%x_type%' you would like to know if that x_type does not happen to be found in a comment and is actually used by the procedure. Isn't that it? Kind-of eliminating dead code and finding where some object is referenced in code.

Manik

I think that if you select * from all_source where lower(text) like '%x_type%' you would like to know if that x_type does not happen to be found in a comment and is actually used by the procedure. Isn't that it? Kind-of eliminating dead code and finding where some object is referenced in code.

Unfortunately the whole thing is made into lower or upper like this :

pastedImage_0.png

Cheers,

Manik.

BEDE

Pondering deeper on the subject, I think this is the most difficult way to put things. So, to see if that is part of a comment or not, that would mean loading the whole stored program unit in memory and having it compiled and also having somehow indications that a referred object is found on certain lines.

A better way to put it would be: if there could be some say pipelined function that would return all the program units that refer some database object or stored program (like refer a package or type or package.procedure or type.method or type.attribute).

Wouldn't this look like being more feasible?

And things get really tricky if some program units contain dynamic SQL. Then, it would be even more difficult to determine where some object is referred. I don't think there would be a complete solution to this kind of problem and I guess using dba_dependencies will have to suffice for quite a long time.

1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 15 2010
Added on Nov 16 2010
20 comments
2,196 views