Jump to content


  • Content count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About boobalganesan

  • Rank
    Advanced Member
  • Birthday 09/30/1989

Contact Methods

  • Website URL
  • ICQ

Profile Information

  • Gender
  • Location
  • Interests
    SQL, PL/SQL and stuff ;)

Recent Profile Visitors

232 profile views
  1. Send messages to the user while running plsql blocks

    Hello Jeff, Im afraid Oracle can't do that. When a block is running, we must wait for it to get completed until our next process. Why don't you create an email facility using UTL_MAIL or UTL_SMTP to check the count of the table for ever few minutes (using DBMS_SCHEDULERS) and send it to the concerned team/people? Also if its a single DELETE, this isn't possible. You can have AFTER DELETE STATEMENT level trigger to send an email stating that the process is over. Thanks, Boobal Ganesan
  2. I've highlighted the counts in your text. It says 1,4,5 and not 1,3,5. Thanks, Boobal Ganesan
  3. Hi Sam, If input is "+A+++500+read++", the output will be 1 for "A", 3 for "500" and 1 for "read" isnt it? So the result will be 1,3,1. How are you expecting a result of 1, 3, 5 ? Thank you, Boobal Ganesan
  4. @Sam@11 Sorry for the delayed response Please find the query below. SELECT LISTAGG( regexp_instr( 'A+++500+read++', regexp_substr( 'A+++500+read++', '[^+]+', 1, level ) ), ',' ) WITHIN GROUP(ORDER BY ROWNUM) col1 FROM dual CONNECT BY level <= regexp_count( 'A+++500+read++', '[^+]+' ); The result is, 1,5,9 Oracle counts from 1, not 0. Thank you, Boobal Ganesan
  5. Hello Ganesan


    Thanks for your response..One more requirement is there n i need you are help..

  6. My bad! First Answer, with t1 as (select 'A+++500+read++' col1 from dual) select regexp_count(substr(col1,1,regexp_instr(col1,'[^\+]+',1,regexp_count(col1,'[^\+]+'))-1),'\+') from t1; Second Answer, with t1 as (select 'A+++500+read++' col1 from dual) select regexp_count(col1,'[^\+]+') from t1; Let me know how this goes for you. Thanks Boobal Ganesan
  7. Hi Sam, To your second question, we can quickly achieve by using REGEXP_COUNT as below, with t1 as (select 'A+++500+read++' col1 from dual) select regexp_count(col1,'[[:alnum:]]+') from t1; Here we are just checking for alpha numeric words. Thanks, Boobal Ganesan
  8. Hello Sam, Adding to Mr. Burleson, You can do it with the help of REGEXP_SUBSTR and REGEXP_COUNT as shown in the below query, with t1 as (select 'A+++500+read++' col1 from dual) select regexp_count(regexp_substr(col1,'[^read]+'),'\+') from t1; Thanks, Boobal Ganesan
  9. Hello Mate, This is because the trigger is activated for all the rows and not for the particular rows which are updated. In your trigger, kindly change the IF UPDATING clause to , if updating and nvl(:new.ID,1) <> nvl(:old.ID,0) This will log only if there is a difference in your ID from your previous one, Please let us know how it goes after this change. Cheers, Boobal Ganesan
  10. Prevent Oracle users to drop their own objects

    Hello Rich, There are different ways I could think of for your questions, 1) You can very well use ALTER TABLE TABLE_NAME DISABLE TABLE LOCK; to disable all the DDL operations on that table, yet the user can issue a similar ENABLE command for him. 2) A user will always have access to drop tables from his schema. If you don't want him to do that, why don't you just create those tables in a different schema and grant only appropriate privileges to him? 3) Create a DDL trigger as mentioned but in a different schemas? Thank you, Boobal Ganesan
  11. returning collection results from loop and bulk collect

    Hello Roger, I understood your requirement, but why are you desperately trying to use the collections when you can use REF CURSORs? Let me tell you about the pros and cons of the both, Collections: Pros: 1) Effective when you want a small PL/SQL table for a temporary purpose like a temporary look up table. 2) Any row can be operated at any time. Cons: 1) If the data is more, your PGA fills up fast, so not recommended for large data processing. 2) When you want to return it as OUT parameter to either JAVA or any other PL/SQL, if your row set is more, its a pain in the PGA. REF CURSOR: Pros: 1) Unlike collection, the REF CURSOR doesnt send the complete rows but it just sends the pointer to the private area where are rows are stored initially when you create the cursor. 2) The main reason for the creation of the REF CURSOR is to send record sets between PL/SQL and PL/SQL or PL/SQL and Java, effectively. Cons: 1) You cannot process any row you like. You just have to flow through all the rows in their order. So, learning all these, please understand that a BULK COLLECT collection is not faster than a REF CURSOR when you are trying to send record sets to Java from PL/SQL. Oracle has created the REF CURSOR just for that purpose! Its super fast and efficient. Note: OPEN your ref cursor in your PL/SQL but dont close it inside the PL/SQL itself! From your Java, read the REF CURSOR by looping it, and once you are done reading all the data, CLOSE the cursor. If you dont close your cursor and if the number of open cursors per session increases the limit set in the OPEN_CURSORS parameter, your code may fail. Thank you, Boobal Ganesan
  12. column with quoted value

    Hello jmurphy, The multiple ways to get this done is shown below. The third pointer is what you need if you are into SQL. 1) You can go change all your single quotes to double single quotes, which is a tedious process as below, select 'vick''s' result from dual; 2) Use PL/SQL in your code to achieve it as Mr. Burleson said. 3) Use the QUOTE literal. select q'[vick's]' result from dual; Thank you, Boobal Ganesan
  13. what happened for view if i delete table related it

    Hi Reem, Adding to Mr. Burleson's answer, as he said if the underlying table is dropped, the view goes invalid. But if you drop your view, you cannot find it in the recycle bin as the recycle bin is meant for storing objects which has a size. A view doesnt have a size on its own and it just wraps up a SQL, which is ran when you run a view. You just dont lose any data or setting if you drop your view! In other words, there will be no effect on the database if your existing view is dropped. The below objects are taken to the recyclebin (if they are dropped + if the recycle bin is turned ON) TABLE NORMAL INDEX BITMAP INDEX NESTED TABLE LOB LOB INDEX DOMAIN INDEX IOT TOP INDEX IOT OVERFLOW SEGMENT IOT MAPPING TABLE TRIGGER CONSTRAINT Table Partition Table Composite Partition Index Partition Index Composite Partition LOB Partition LOB Composite Partition Thank you, Boobal Ganesan
  14. returning collection results from loop and bulk collect

    Hello Roger, There shouldn't be a problem when you are assigning a strong REF CURSOR into a collection. Do you mean a WEAK REF CURSOR? Post us your complete code for analyzing. Thank you, Boobal Ganesan
  15. returning collection results from loop and bulk collect

    Hello Roger, In your case, you can directly return your REF CURSOR instead of copying its data into a collection and then to return the collection. On either case, you have to create your code as a part of a procedure or a function and have its OUT parameter assigned to the REF CURSOR or the COLLECTION, so that you can collect them at your output parameter and use it for your further processing. Thank you, Boobal Ganesan