Jump to content

boobalganesan

Members
  • Content count

    92
  • Joined

  • Last visited

Community Reputation

0 Neutral

About boobalganesan

  • Rank
    Advanced Member
  • Birthday 09/30/1989

Contact Methods

  • Website URL
    http://
  • ICQ
    0

Profile Information

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

Recent Profile Visitors

80 profile views
  1. I've highlighted the counts in your text. It says 1,4,5 and not 1,3,5. Thanks, Boobal Ganesan
  2. 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
  3. @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
  4. Hello Ganesan

     

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

  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. Hello buddy, I guess the trigger which you've created is ROW LEVEL, but you need to change it to STATEMENT LEVEL for your requirement. ROW LEVEL trigger - If a single UPDATE statement updates 10 rows, the trigger is executed 10 times. STATEMENT LEVEL trigger - If a single UPDATE statement updates 10 rows, the trigger is executed only once. This is because this type of trigger doesnt mind the number of rows updated but only the number of statements executed. In this case only one statement, thus only fires once. Kindly make the necessary change and let us know for any more clarification. Thank you, Boobal Ganesan
×