[Search for users] [Overall Top Noters] [List of all Conferences] [Download this site]

Conference orarep::nomahs::rdb_60

Title:Oracle Rdb - Still a strategic database for DEC on Alpha AXP!
Notice:RDB_60 is archived, please use RDB_70..
Moderator:NOVA::SMITHISON
Created:Fri Mar 18 1994
Last Modified:Fri May 30 1997
Last Successful Update:Fri Jun 06 1997
Number of topics:5118
Total number of notes:28246

4976.0. "Error Handling in Stored Procedures" by ORAREP::QCAV01::KRISH () Wed Jan 29 1997 06:10

    Hi,
    I need a help from you People.
    I using Rdb 6.0.
    I have written stored procedures and calling these stored procedures
    from the host language code (C-Program).
    I want to trap the errors encourntered while executing SQL statments 
    in the stored procedure.(Like constraint violation Eg. Duplicated
    Record, Not null column assigned a  null value).
    
    Earlier from you People i got the reply that the Get Diagonstic 
    Statement will do that i.e, for warning kind of scenarios,but i am not
    able to handle the above mentioned cases (constraint violation) using
    the get diagonstics statment. 
    So can you please help me  in this regard.
    
    I am facing one more problem also.
    When i try to create tables using scrips (i.e the script will contain
    the DDL statements to create one or more tables),and if the script
    contains statments for creating a quite a few tables the system hangs
    or throwing some error messages. This problem i can over come by
    giving the commit command in between . 
    In oracle for this kind of problme we need to increase or create
    rollback segments.
    What is the similar thing in Rdb and how to accomplish that.
    
    
    Please help me in this regard also.
    
    Thanks in advance.
    With regards
    
    S.Krishnakumar
T.RTitleUserPersonal
Name		DateLines
4976.1NOVA::SMITHIDon't understate or underestimate Rdb!Wed Jan 29 1997 10:0424
~    I want to trap the errors encourntered while executing SQL statments 
~    in the stored procedure.(Like constraint violation Eg. Duplicated
~    Record, Not null column assigned a  null value).

You can only trap these errors in a host application, not in stored
procedures.  This functionality is planned for a future release.
    
~    When i try to create tables using scrips (i.e the script will contain
~    the DDL statements to create one or more tables),and if the script
~    contains statments for creating a quite a few tables the system hangs
~    or throwing some error messages. This problem i can over come by
~    giving the commit command in between . 

SO what are the errors and why the hang?  (Use RMU/SHOW STAT to investigate
the hangs)

~    In oracle for this kind of problme we need to increase or create
~    rollback segments.
~    What is the similar thing in Rdb and how to accomplish that.

Rdb has no such thing - we use snapshot files and they created and managed for
you.  I doubt this has anything to do with the problem.

Ian
4976.2error handling in spORAREP::QCAV02::KRISHThu Jan 30 1997 06:5916
Hi,
Thanks for the immediate reply.
So the only way to handle errors encountered in the sql statements
of the stored procedure is call the call the stored procedure through a
sql module file and call the module file from  precompiler code.
That's right?

Regarding the problem i was facing while creating quite a few tables ,
i just increased the buffer sizes using the alter table command and
that solved the problem.

Thanks in advance.

With regards
S.Krishnakumar
4976.3NOVA::SMITHIDon't understate or underestimate Rdb!Thu Jan 30 1997 09:3212
~So the only way to handle errors encountered in the sql statements
~of the stored procedure is call the call the stored procedure through a
~sql module file and call the module file from  precompiler code.

Well you can call the stored procedure directly from the precompiler source:

	exec sql call yourproc (...parameters...);
	if sqlcode <> 0 then
	    ...handle the error...
	end if

Ian