Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [exception]

49 questions
22
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
  • 11,741
  • 23
  • 73
  • 108
11
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
  • 305
  • 2
  • 3
  • 10
9
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,493
  • 2
  • 16
  • 33
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
  • 577
  • 2
  • 8
  • 17
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
  • 795
  • 1
  • 8
  • 23
4
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
  • 191
  • 1
  • 2
  • 6
3
votes
1 answer

Using EXCEPTION to ignore duplicates during bulk inserts

In pq 9.3.5, I'm importing records from an external source where duplicates are VERY rare, but they do happen. Given a readings table with a unique compound key on (real_time_device_id, recorded_at), the following will fail once in a blue…
fearless_fool
  • 147
  • 1
  • 1
  • 6
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

Is it possible to raiserror within a For Delete Trigger that rolls back the deletes

There is a claim at the end of this accepted answer that if an error occurs within a "For Delete" trigger, the delete action would be rolled back because it is part of an implicit transaction. However here is an example showing that the deleted rows…
crokusek
  • 1,956
  • 3
  • 21
  • 32
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
  • 436
  • 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
  • 8
3
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, …
marathon
  • 133
  • 1
  • 4
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
  • 322
  • 2
  • 10
1
2 3 4