Tag Archives: Oracle

How to see Stored Procedure Code in Oracle & Sql Server

Suppose we are writing some new query/script/SP and we want to see the code of preexisting SP in query window itself without going Schema Browser or Directly going to that object

We can able to do this in both Oracle and Sql Server.

— Oracle

select * from USER_SOURCE where Name =’PRC_SEND_EMAIL’ — Provided you are given permission to access USER_SOURCE
there is also one more way

— DBA specific Schema specific need to have permission to see this.

select * from DBA_SOURCE where Name =’PRC_SEND_EMAIL’

— You can see signature of the procedure in Current Schema

Desc ‘PRC_SEND_EMAIL’

In Sql server we can do the same with the help of

sp_helptext ‘dbo.sp_helptext’

Advertisements

Resolve ORA-03113: end-of-file on communication channel = Check following settings !!!

At the time of resolving ORA-03113 before considering that threre is an error in our code check for following , you can save your time for debugging code for any leak.

1) Newtork issue – something might be wrong in connection faulty connection , check LAN settings.

2) Sever machine where application hosted is getting restarted or under maintenance or some patch running , this is largly occured reason for this exception . !!!!

3)Problem with Connection Pooling ,in correct handling of more than one connection at time.

4)Check that machines firewall that is ending the connection after certain period of time also check idle time out session out setttings.

5) Go to your sqlnet.ora file and check for the SQLNET.EXPIRE_TIME settings Set Value greater than 0,use parameter SQLNET.EXPIRE_TIME to specify a the time interval which specified in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.

you can find details at Oracle

6) Check for your Select queries ,if they are produsing output more than your Default Buffer Size then also we can find this error coming increase Buffer Size

Last but not least … if u think u tried all possibilities  Contact Oracle Metalink !!!

There can be many more reasons or checklist available for this error I was just writing which I experienced while using Oracle with  .Net .. I update post gradually after finding new !!!!