Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [exception]

48 questions
25
votes
2 answers

How to catch and handle only specific Oracle exceptions?

From this and this i guess, that there is no predefined Named System Exceptions for ORA-00955. How can I rewrite the following to catch only the error ORA-00955? begin EXECUTE IMMEDIATE 'CREATE SEQUENCE S_TEST START WITH 1 INCREMENT BY…
bernd_k
  • 12,369
  • 24
  • 79
  • 111
14
votes
1 answer

How do I reraise an exception in a PL/pgSQL EXCEPTION block?

Consider the following (incomplete) block of PL/pgSQL inside a function: CREATE OR REPLACE FUNCTION my_calc(myvar1 NUMERIC, myvar2 NUMERIC) RETURNS NUMERIC RETURNS NULL ON NULL INPUT IMMUTABLE LANGUAGE plpgsql AS $$ BEGIN …
jpmc26
  • 1,652
  • 3
  • 20
  • 38
12
votes
2 answers

How to get exception context for a manually raised exception in PL/pgSQL?

In Postgres, we get the "stack trace" of exceptions using this code: EXCEPTION WHEN others THEN GET STACKED DIAGNOSTICS v_error_stack = PG_EXCEPTION_CONTEXT; This works fine for "natural" exceptions, but if we raise an exception using RAISE…
Taytay
  • 325
  • 2
  • 3
  • 11
7
votes
2 answers

Best practices for committing a transaction in SQL Server where TRY CATCH is used

In a SQL Server code block, what is the best place to place the commit transaction? Inside the try catch block or outside it?. For example, is option A or option B the correct approach or are they subjective choices? Option A CREATE PROCEDURE…
user20358
  • 213
  • 2
  • 4
  • 6
5
votes
2 answers

One Complex Stored Procedure Causes Estimated Cost Exception

In SQL Server, I'm getting the following error "The query has been canceled because the estimated cost of this query (5822) exceeds the configured threshold of 300. Contact the system administrator." This is the result of an execution of a stored…
Brian Mains
  • 597
  • 2
  • 10
  • 17
5
votes
2 answers

How to make ON CONFLICT work for compound foreign key columns?

I'm having trouble with ON CONFLICT not working for foreign key columns where the foreign key is compound. Here's an example. create table foreign_table ( id_a text not null, id_b text not null, id integer primary key, …
Paul C
  • 153
  • 1
  • 1
  • 4
4
votes
2 answers

Can stable (or immutable) functions call volatile functions?

The PostgreSQL documentation states: Any function with side-effects must be labeled VOLATILE... Consider the following function: CREATE OR REPLACE FUNCTION count_items() RETURNS integer AS $BODY$ DECLARE v_result INTEGER DEFAULT 0; BEGIN …
Dave Jarvis
  • 833
  • 1
  • 10
  • 25
3
votes
3 answers

How to raise_application_error beyond a when others clause

Background: I've used a few Oracle articles to develop an error package, with some procedures. One of these is Log_And_Return which is called throughout the database. I've simplified it for brevity, and it looks like this: PROCEDURE Log_And_Return…
Andrew Martin
  • 131
  • 4
3
votes
1 answer

MongoDB Shard - Disable Cursor timeout

I'm using an sharded-cluster(Mongo version 2.4.1) with a java application. After a specific time (10 minutes or more) I got the following exception: com.mongodb.MongoException$Network: Read operation to server cs-mongo-mongo-west-t:3306 failed on…
DanielH
  • 35
  • 1
  • 4
3
votes
1 answer

Does MySQL close cursor if stored procedure encounters an exception?

I've seen two different approaches of cursor handling is MySQL stored procedures. Now I'm curious about their stability, due to potential MYSQLEXCEPTION risks in stored routines. Assuming, that CURSOR is opened when DBMS raises MYSQLEXCEPTION and…
BlitZ
  • 446
  • 6
  • 16
3
votes
1 answer

An inconsistency in SQL Server 2008 R2

(could be) possible duplicate with An inconsistency was detected I have small table that returns from query UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is inactive.UDA-SQL-0564 [Microsoft SQL Server Native Client 10.0]An …
mKorbel
  • 597
  • 2
  • 12
  • 25
3
votes
2 answers

Display line number where error occurred in DB2 stored procedure

I have added an EXIT handler in my procedure that captures the SQLSTATE and SQLCODE, and even found a way to get the procedure name, but I also need to know where the error occurred. Suggestions greatly appreciated. declare EXIT handler for…
Jake v1
  • 73
  • 1
  • 10
3
votes
2 answers

Nested stored procedures and catch blocks - ERROR_PROCEDURE() issue

I have noticed something odd, and I am hoping someone in the community has an answer for me. I have noticed that when a stored procedure is nested it seems that all the blocks prior to last catch block have a weird bug with the ERROR_PROCEDURE()…
SpaceGhost440
  • 334
  • 3
  • 14
3
votes
1 answer

When can a runtime exception occur during commit?

I've been asked this question but neither I seem to be able to answer it on my own, nor can I find anything related on the web. So what are the cases that might cause a runtime exception when committing a transaction in Oracle?The only thing that I…
Mikayil Abdullayev
  • 261
  • 6
  • 18
3
votes
2 answers

Why Sqlcode 100 does not come with exception

I have following query in a PL\SQL procedure on Oracle 10.2: This is the code LOOP BEGIN SELECT a.poid_id0 into v_acc_account_poidid0 FROM account_t a WHERE a.poid_id0=i_acct_id0 FOR UPDATE OF a.poid_id0 NOWAIT; EXIT WHEN…
Nandish A
1
2 3 4