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

637 profile views
  1. boobalganesan

    OCCI - Null pointer in Statement::execute()

    Hello Gisela, Can you try to use the below query in the "createStatement" function? select * from table(select my_col from MY_TABLE); Let us know if you get lucky with this. Thank you, Boobal Ganesan Author of Advanced PL/SQL Programming: DefinitiveReference
  2. boobalganesan

    pl/sql exception (error) raise

    Hey Hugo, We cannot find any difference between normal exception and the exception raised by the RAISE command as they produce the same out put. To learn more about the compilation errors, run-time errors, and their subcategories, please check out my Rampant book from my signature. Thank you, Boobal Ganesan. Author of Advanced PL/SQL Programming: The definitive reference
  3. boobalganesan

    Replacing strings

    Hello Unna, Thanks for your question. The below code satisfies your requirement. WITH t1 ( id, col1, col2, col3 ) AS ( SELECT 11, 1, '12154', 'Ran into $1 error, $2, because of $3' FROM dual UNION ALL SELECT 11, 2, 'TNS', 'Ran into $1 error, $2, because of $3' FROM dual UNION ALL SELECT 11, 3, 'NETWORK ERROR', 'Ran into $1 error, $2, because of $3' FROM dual ),t2 AS ( SELECT MAX(id) id, MAX(col3) col3, MAX( CASE WHEN col1 = 1 THEN col2 END ) attr1, MAX( CASE WHEN col1 = 2 THEN col2 END ) attr2, MAX( CASE WHEN col1 = 3 THEN col2 END ) attr3 FROM t1 ) SELECT id, replace(replace(replace(col3,'$1','ora-' || attr1),'$2',attr2),'$3',attr3) col3 FROM t2; Thank you, Boobal Ganesan. Author of Advanced PLSQL Programming: The Definitive Reference
  4. boobalganesan

    The function is executed but does not return the data.

    Hello Perica, Can you please post your table DDLs and DMLs for us to reproduce the issue? Thank you, Boobal Ganesan. Author of Advanced PLSQL Programming: The Definitive Reference
  5. boobalganesan

    Error : invalid identifier

    Hello Itzkashi, Rule #1: When its from cursor, you cannot bind variables to it. The below code is the simple form of your's and works fine. DECLARE v_col VARCHAR2(100); BEGIN SELECT val INTO v_col FROM test_ref; EXECUTE IMMEDIATE 'INSERT INTO TEST SELECT ' || v_col || ' FROM TEST_Q'; END; / Thank you, Boobal Ganesan. Author of Advanced PLSQL Programming: The Definitive Reference
  6. boobalganesan

    unexpected behaviour with DBMS_REDACT.PARTIAL

    Hello Prabhat, I'm not sure if you are having the right code as it works perfectly fine for me. Please find my notes on DBMS_REDACT here. Thank you, Boobal Ganesan. Author of Advanced PL/SQL Programming - The Definitive Reference
  7. Hello Suhaasv, Eventhough you don't have to use an EXECUTE IMMEDIATE in a nested way, your script will work just fine. It makes more sense to not include the syntax as it's already a string. Thank you, Boobal Ganesan Author of Advanced PL/SQL Programming: The definitive reference
  8. boobalganesan

    Storing the reference of TABLE function

    Hello Skywalker, You use TABLE function only when you want to join a TYPE to a TABLE for manipulation. I don't see any problem in using it twice if that's what your business demands. If you still want to use a common PL/SQL form of storage instead of using the TABLE function, you can very well assign it to a cursor like below and use it in your PL/SQL code. open cur for select * from table(your type); But, As your type BULK_COLL_ARRAY is in your memory already, you wont find any difference in TABLE(bulk_coll_arr) being called twice or more. I recommend you to use it the same way as it is unless you have any other performance issues. You cannot do the above! You cannot assign a table into a variable, but you can assign it to a TYPE, but still you cannot call a type in the SELECT statement without using the TABLE function. When you use the TABLE function, Oracle internally converts it into a GTT kind of table for you to use it only in your current session. And for your question regarding the size of the array, you are good if you have enough space in your PGA memory as COLLECTIONs use PGA memory and not SGA. But if its more than 100k, its sensible to put it in a table rather than in a TYPE just so to not to over-do your PGA. TYPES are most commonly used when you work with little to medium amount of data. For more information on Collections and other PL/SQL topics, please refer to my best selling PL/SQL book from my signature. Thank you, Boobal Ganesan Author of the Advanced PL/SQL Programming book
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. I've highlighted the counts in your text. It says 1,4,5 and not 1,3,5. Thanks, Boobal Ganesan
  15. 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