Follow by Email

Wednesday, October 8, 2014

oracle How can I tell where my database function was used
As mentioned in the above stackoverflow thread , below query can be used to find where your database function/procedure has been used.
The oracle documentation for all_sources can be found at below link 

Below post is also very informative

Thursday, September 25, 2014

Crystal report passing string date parameter to a report from my jsp page

Problem Statement :

Just found out that passing a string attribute from my jsp page to a crystal report is not working and the report was asking for the date parameter again.

Solution :

Thanks to a thread on stackoverflow , the solution was to create date string as

#9/25/2014# and then pass it to crystal report from my page and now it's working fine.


Monday, September 8, 2014

Eclipse saving workbench state hangs

I followed the below instructions in the blog

  • Make a backup of your “.metadata“-folder (very important!!) (this folder inside teh eclipse workbench/workspace folder)
  • navigate into the “.plugins“-folder located inside “.metadata
  • delete the folder “org.eclipse.ui.workbench
  • startup eclipse again and check if there is still everything you need


    and the problem was resolved , now when I close my eclipse it doesn't hang at saving workbench state.

    infact I have noticed that the eclipse is starting a closing faster than before.

    Usefull Oracle Database Queries

    Get the details of important oracle parameters such as "PASSWORD_LOCK_TIME" and "SESSIONS_PER_USER" etc

    select * from dba_profiles order by profiles

    Get the Name of the oracle instance

    SELECT sys_context('USERENV','DB_NAME') AS Instance FROM dual;
    Get the name of the current oracle instance and user id

     select name,user from v$database

    I will keep adding other usefull queries here.... Connection reset

    One reason of this exception I have noticed is when user clicked on a link in your website and before it was loaded completly user clicked anotehr link on the same page.

    This causes Connection reset

    Sunday, August 10, 2014

    Oracle Splitting String into multiple rows

    I used below query to split a comma separated string to rows in oracle

    select regexp_substr(a.col1,'[^,]+', 1, level) RT_PARAM_NAME from

            (select 'a,b,c,d' col1 from dual) a

            connect by regexp_substr(a.col1,'[^,]+', 1, level) is not null  

    Note the regular expression '[^,]+' which basically tells the query to use , as a delimeter , and note the use of level and connect by.

    Wednesday, August 6, 2014

    ORA-01422: exact fetch returns more than requested number of rows

    This error normaly means that the query fetched more than one records , while it was expected to return only one record (in cases where you are using in clause in a query to assign the result to a variable , you are assuming it will always retun only one row but at the time of this exception it is returning more rows).

    If this is the case the solution is very simple , introduce more strict criteria in your query to make sure that it alwasy returns only one row.

    How ever I also faced this exception in another interesting case.


    In my oracle function I was passing a parameter and the name of the parameter was "alert_id" , then I was comparing the value of this parameter with a field in the query below

    select a.message into html from  My_Table a where a.alert_id=alert_id;

    I double checked there was only one record in the databas with that alert_id , but inside trhe function when i was running this query it was giving me ORA-01422 again which did not make sense.


    I found that the problem was that the name of the field in the database and the name of the parameter was same (alert_id in my case) , when you do that you will face this problem because oracle will pace the current value in the database field after the = sign , so my query was getting interprated as (suppose alertid in the data base is 5380)

    select a.message  from  rtoc.rtoc_alert a where 5380=5380;

    when I ran the above query in PL/SQL it showed me more than one records.

    Moral of the story :)

    Naver use the parameter names ina  function which have any possibility of exsitance in tables you are using in your function.

    Suggessted Naming Convention

    function parameters should start with "p_" ==> "p_alert_id"
     variables declared whould start with  "v_"