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

440 profile views
  1. boobalganesan

    Value in a range

    Hello SCB, Write a SELECT query with ID and ID_INDICATOR in the WHERE condition and put the below condition. "AND 57 between RANGO_MIN and RANGO_MAX" Adding the above condition will return you the desired row. Thank you, Boobal Ganesan
  2. boobalganesan

    SYS_CONTEXT('userenv','MODULE') for Database Vault

    Hi Sharath, If you wanna restriction an IP address, use can very well use SYS_CONTEXT('USERENV','IP_ADDRESS') function. Here are few examples, http://www.dba-oracle.com/t_adv_plsql_vpd_application_contexts.htm http://www.dba-oracle.com/t_adv_plsql_dbms_redact_partial.htm Thanks, Boobal Ganesan
  3. Hi, HTTPS works with certificates, either through Oracle or through Java, or even through our traditional browsers. Here is a cool link explaining how it works.. https://robertheaton.com/2014/03/27/how-does-https-actually-work/ Thank you, Boobal Ganesan
  4. boobalganesan

    select Long Raw??

    Hello emolloy17, Can you try increasing the size the VARCHAR2 variable "a"? Please make it the max (32767) and then check it. Mr. Burleson's script works totally fine for me. The error "Numeric or Value Error" shows that the space in the variable isnt enough to store the value. Thank you, Boobal Ganesan
  5. boobalganesan

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


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

  10. 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
  11. 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
  12. 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
  13. 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
  14. boobalganesan

    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
  15. boobalganesan

    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