Thursday, October 31, 2013

How to get list of tables used in a stored procedure or package

Try to run below query and privide the first value as the owner of this procedure and second value as the procedure name

select
   owner,
   type,
   name,
   referenced_owner,
   referenced_type,
   referenced_name
from
   dba_dependencies
where
   ((owner like upper('&1')
   and
      name like upper('&2') )
   or
   (referenced_owner like upper('&1')
   and
   referenced_name like upper('&2') ))
   and
   referenced_owner != 'SYS'
   and
   referenced_type != 'NON-EXISTENT'
order by
   owner, type, name;

This will give you a list of all tables , synonym , other packages used in your stored procedure.

For more information please see the below link
http://www.dba-oracle.com/t_tables_referenced_inside_pl_sql_procedures.htm 

Tuesday, October 29, 2013

oracle execute privilege on package

 In order to see which roles have execute priviliges on a table/package/any other database object I use below sql query 

SELECT grantee
  FROM all_tab_privs
 WHERE table_name = '<Database object name>'
  AND privilege = 'EXECUTE'

For more details have a look at below stackoverflow thread. http://stackoverflow.com/questions/1255310/how-do-i-check-which-schemata-have-been-granted-execute-permission-on-an-oracle


Monday, October 28, 2013

ORA-08103: object no longer exists

I got this exception while I was trying to run a procedure (written by somebody else in our company) which takes very long time to execute , I found that because of the below sequence of events I was getting this exception

Steps Which Produced This Problem
  1. Procedure was opening a cursor (selecting records) , keeping it open for sometime during which it had to do other operations
  2. After finihing those operations it was trying to execute a loop on the cursor (It created before) and was getting this problem.
Reason
     I found that during this time some of those records my cursor was refering to were deleted , and when the cursor tried to fetch those records in the loop there I was getting "ORA-08103: object no longer exists".

Solution
    The only solution I have in mind is to open the cursor and start  utilizing it immidiatly , i.e. don't keep it open waiting for someother operations to finish.This way I will be able to minimoze the probability of this problem to occur in future.

I was trying to fetch the records in a generic oracle table (which I inserted) , another possible reason could be that you have insert rights but don't have select rights on that table. This was not the case in my situation as I had all the rights on the table but I am sharing it here to give you another possible reason of this problem.

Please also have a look at below Stackover threads for detailed discussions on this problem

http://stackoverflow.com/questions/18747649/exception-ora-08103-object-no-longer-exists-on-using-setfetchsize-of-hirabenate

http://stackoverflow.com/questions/2696519/global-temporary-table-on-commit-delete-rows-functionality-discrepancy

Wednesday, October 23, 2013

error occurred during initialization of vm could not reserve enough space for object heap

I was facing this error today where everytime I was trying to run my tomcat server in eclipse I was getting

"error occurred during initialization of vm could not reserve enough space for object heap"

It looked like a problem with the heap size VM arguments I  checked my eclipse.ini it had below correct heap size settings, Still I was getting the problem

-Xms256m
-Xmx768m
Soultion

I found that there is another place in eclipse where I had to set these VM aruguments and that was the option under

Windows-->Prefrences-->Java --> Installed JRE
I had to edit the configured JDK and add "-Xms256m -Xmx768m" to Default VM Argument.
 

Spring forward to another controller

I use the following approach in my spring applicaitons whenever I need to forward the request from one controller to another controller


@RequestMappping({"/MyURL"})

public String execute(Model model){
   if(){
       //Do Something
      return "forward:/URLNO1"
   } else {
      //Do Something
      return "forward:/URLNO2"
   }

}


As recomended in the below stackoverflow thread

http://stackoverflow.com/questions/7366170/spring-3-0-forwarding-request-to-different-controller/7366199#7366199
 

Sunday, October 13, 2013

java.lang.IllegalArgumentException: Can't convert argument: null



Problem

java.lang.IllegalArgumentException: Can't convert argument: null

I got this exception when I was trying to run a JEE web application in tomcat 6 using ECLIPSE IDE.

Problem Cause
I noticed that my web.xml file contained many "javaee:" tags although the namespace for javaee was already defined at < web-app >  level as xmlns="http://java.sun.com/xml/ns/javaee"

Solution

This is the first time I noticed these javaee tags in a web.xml so I got rid of those , and tried to run the application again on tomcat this time it is working fine.

The below stackoverflow thread has some good details about it

http://stackoverflow.com/questions/2293797/tomcat-startup-web-xml-issue


 

Tuesday, October 8, 2013

A Better Prototyping Approach


I would like to share one experience I had changing the way of prototyping and making it more fun for myself and the user also saving time.

 
Prototyping is very important in a project , a finished prototype having  the user agreement may mean you have already developed 70-80 percent of your database and you have clear understanding of what you will be needing in terms of data , now you are in a better position to find out the availability of that data and less risk of finding surprises later on.

Traditional Way Of Prototyping

                The common practice is to gather the user requirements and use HTML , FLEX,MS WORD or any other technology to build screenshots and show it to user get his feedback change the screens again and repeat the same process few number of times until you have an agreement with the user. This process takes time to finalize the prototype. Sometime we also go ahead and introduce some functionality into the system where a prototype is actually saving and getting the data from database , and this adds some more time to the whole effort.

Remember the goal of this effort is to get user’s agreement on what he needs in the application.

Prototyping approach recommended by David A.Patterson
I took a course at Coursera instructed by David A.Patterson where he shared his experience with the traditional prototyping and the approach he came up with (in fact he said the approach is widely being used these days ).

 Basically he recommended to forget about all the prototyping tools at this stage of your project and take a paper a pencil and sit with user let him draw whatever he has in his mind for what he really wants in the application , of course it will span on multiple screens and discuss all one by one repeat the process until you both agree on a single screen and then do it for other screens. He said this is the fastest and fun way of working on a prototype. Once you agree on a screen think about it in different prospective , like who should be able to see this screen , what should he see and what should be hidden based on role , here you are preparing your test/acceptance cases.

I tried this approach in one recent system I developed for my company and it really helped and saved some time and involve the user at very early stage of the project. Since he drew his requirements for me he had the right expectations from the first iteration of the project J.