Questions tagged [exception]
The exception tag has no usage guidance.
49 questions
-1
votes
0
answers
74
views
Telerik OpenAccess: Change operation canceled by user [migrated]
I'm working on a C# application, using Telerik OpenAccess to perform modifications on an SQL-Server database.
While trying to set the status of an object ("Status" is one of the columns in ...
- 377
2
votes
1
answer
181
views
How to catch a particular exception?
It is possible to catch an exception in plpgsql:
EXCEPTION WHEN unique_violation THEN
But how to check for a violation of a particular constraint?
The following example creates two keys: one for a ...
- 359
0
votes
1
answer
52
views
sql bigger parameter then specyfied - no error - just taking substring? why?
I have some sql procedure (MSSQL 2019) if relevant
Create PROCEDURE [dbo].[test] @tmp varchar(32) AS
print @tmp
why when i do
EXEC @return_value = [dbo].[test]
@tmp = N'...
- 103
0
votes
1
answer
161
views
Why does inserting a value into a temp table's BINARY(8) column cause an error when casting the same value to BINARY(8) does not?
When I execute this:
DROP TABLE IF EXISTS #QueryHashes;
CREATE TABLE #QueryHashes (query_hash BINARY(8) NOT NULL PRIMARY KEY);
PRINT CAST(0x0169857CCD5BBBE4E AS BINARY(8)); -- just testing that the ...
- 2,191
1
vote
1
answer
183
views
How to get error code of an exception type without raising it?
Is there anyway in Oracle to get the error code associated with a user-defined exception type without that exception being actually raised?
I don't want to hard-code the code in the entire pl/sql ...
- 159
2
votes
1
answer
2k
views
Insert values to table only if first Insert was successful
Is there a way I could check the status of an insert query whether it was successful or not. I want to write to another table only if the first insertion was successful.
Below is the table I want to ...
- 145
1
vote
1
answer
6k
views
How to use user defined exception - PostgreSQL Function
Can you please provide syntax for to use user defined exception in PostgreSQL function?
Suppose I want to handle below user defined exception.
SQL Error [22023]: ERROR: password is too short.
There ...
- 143
0
votes
0
answers
374
views
How to transfer data from one table to another with exception handling in Postgresql?
So, I have two tables, one where I import data from an excel file all in string format without considering any sorts of constraints or data type validation. And then the data is transferred to the ...
- 111
3
votes
2
answers
1k
views
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 ...
- 73
1
vote
0
answers
84
views
Catch contraint error inside trigger on the table being inserted/updated
I would like to know if it is possible to automate some task when an insertion or update on a table generates an error (for example error codes 1062, 1451 or 1452)
Finding an answer to that is not ...
- 111
1
vote
1
answer
224
views
Do a lot of error checks affect query performance and is it bad practice?
If I have a stored procedure with a handful of error checks that come before the queries are executed and throw exceptions as necessary, could this affect performance? Is it bad practice to do this?
...
- 39.5k
5
votes
2
answers
10k
views
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 ...
- 153
0
votes
1
answer
568
views
how to convert from raiserror to throw in the current context?
I have a function that checks is the file is present on disk in an specific location.
Using that function I am checking for the latest backup, in the code below.
If we don't have a backup anymore, ...
- 16.6k
3
votes
2
answers
2k
views
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 ...
- 334
7
votes
2
answers
25k
views
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 ...
- 213
0
votes
1
answer
365
views
How to use COLUMN and TABLE options in a RAISE statement
According to the docs, there are options to RAISE called TABLE and COLUMN. However , if I explicitly set them, they do not end up in the resulting error message. It seems only DETAIL and HINT are ever ...
- 177
13
votes
1
answer
9k
views
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
...
- 1,612
0
votes
2
answers
1k
views
What is the F301: the CORRESPONDING clause in query expression?
The SQL 2011 Spec supports a CORRESPONDING clause,
Feature F301, “CORRESPONDING in query expressions”:
What is this feature? How is it used? And, is it supported by any industry RDBMs?
- 64.7k
0
votes
0
answers
2k
views
SSIS: Using Event Viewer to Trace Errors of A Package
Overview
A package was developed due to a planned database server migration, and other teams with a dependency on legacy cross-database queries. There is a policy of not having Linked Servers, so an ...
- 113
1
vote
0
answers
1k
views
Why NO_DATA_FOUND exception has a different behavior than others exceptions?
I have this PL/SQL function:
CREATE OR REPLACE FUNCTION Test_FunctionNoDataFound RETURN NUMERIC AS
var NUMERIC;
BEGIN
SELECT val INTO var FROM table1 WHERE id = -1; -- Throw NO_DATA_FOUND
...
- 177
0
votes
1
answer
563
views
How can I RAISE_APPLICATION_ERROR and do an INSERT in the same trigger?
From what I see, having the RAISE_APPLICATION_ERROR cancels the other operations that I would have in the trigger. I tried writing first the INSERT and then the RAISE_APPLICATION_ERROR hoping that ...
- 115
0
votes
2
answers
1k
views
How can I manage an exception generated by Postgis?
I am running the following query on Postgres 10.3/Postgis 2.4:
create table table_c as (
select A.*, B.area_name, B.id
from table_a A
left join table_b B
on ST_Contains(A.geom, B.geom)...
- 1,203
2
votes
0
answers
1k
views
In PostgreSQL, how to deal with error messages that show almost no context?
Today I realized a number of points where PostgreSQL (v10.3) is rather lackluster and sparse in its error messages.
The first point is illustrated by this code:
drop schema if exists X cascade;
...
- 317
0
votes
1
answer
1k
views
How to find out which records are causing an exception in a select statement?
I have a select query that I use as part of an insert statement,
but it raises the following exception:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
...
- 16.6k
2
votes
1
answer
155
views
MySQL error message
I have a small issue here. When I go to Startup/ShutDown screen in MySQL workbench, following error message is displayed.(Please refer the image)
I cannot stop the server when I press the Stop Server ...
1
vote
0
answers
49
views
Exceptions not working from outer procedure
I created a MySQL trigger:
CREATE TRIGGER `check_purchase_date` BEFORE INSERT ON `purchases`
FOR EACH ROW BEGIN
DECLARE purchase_date_in_future CONDITION FOR SQLSTATE '45000';
set time_zone='...
- 11
3
votes
1
answer
113
views
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 ...
2
votes
0
answers
3k
views
Mysql stored procedure SQLEXCEPTION
Below is my sample query, in which I first create new folder then update count then create a new tag and finally get new id.
insert into folder (name)values("new");
update folder set count = count + ...
- 121
-3
votes
1
answer
7k
views
Catch exception in DB2 function
I want to catch exception in DB2 function and return 0 if there was an exception.
I don't know how the right syntax
create function is_decimal(c_data varchar(100))
RETURNS INTEGER
begin
select ...
- 533
1
vote
0
answers
2k
views
Mongodb exception in initAndListen
My MongoDB server was full so Mongod service could not start. There was a specific db that I didn't need, so I have deleted its file from the dbpath.
Now when I try to start the service again I'm ...
- 235
1
vote
1
answer
4k
views
How to create index on long data type field
I have a MySQL table with a long data type field:
`create_date` long NOT NULL,
I want to index it:
CREATE INDEX ix_logs_create_date ON `logs` (create_date) USING BTREE;
But I encounter the ...
- 449
12
votes
2
answers
27k
views
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" ...
- 325
1
vote
0
answers
1k
views
ORA-01861 - Input seems ok and FX/FM modifiers not used
Using SQL Developer 4, I am getting an ORA-01861 exception when I do the following (and BTW, I ticked 'Ignore NLS Definitions'):
alter session set nls_date_format = 'DD-MON-YY';
alter session set ...
3
votes
3
answers
2k
views
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, ...
- 131
2
votes
1
answer
598
views
MySQL add few column exception for TRIGGER UPDATE FOR EACH ROW
How to effectively add column id, name exception for trigger:
CREATE TRIGGER updated BEFORE UPDATE ON table1 FOR EACH ROW SET NEW.date=NOW()
so that date=NOW() will be affected on updating any ...
- 121
3
votes
1
answer
3k
views
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 ...
- 35
0
votes
1
answer
1k
views
Is using dbms_utility.format_error_stack to filter errors in the exception block a bad idea?
I'm trying to handle two different exceptions could trigger in an external table that reads a CSV.
The code works great, but I'm concerned I'm doing it wrong.
SET SERVEROUTPUT ON;
DECLARE
records ...
- 229
1
vote
1
answer
4k
views
Cassandra: Request did not complete within rpc_timeout
i was working with Cassandra 1.2.4 probably, after restoring some key-space when i tried to query in a key-space it gave me Request did not complete within rpc_timeout
so i checked system.log & ...
- 129
2
votes
1
answer
3k
views
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.
...
- 2,130
1
vote
1
answer
756
views
exception: hosts cannot switch between localhost and hostname
I created a replication set.
I added localhost in the set in the beginning, but when I try to edit the member with the actual hostname. I get error "exception: hosts cannot switch between localhost ...
- 111
3
votes
1
answer
5k
views
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 ...
- 446
2
votes
0
answers
680
views
SSMS causing Event ID 4674: SeTakeOwnershipPrivilege
Somewhat of a strange one. Recently my user id's been showing up in a number of Audit Success records in the Security event log of one of our production database servers with the below content (or ...
- 449
4
votes
2
answers
2k
views
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
$...
- 833
2
votes
1
answer
482
views
INSERT trigger dying with with #target columns not equal to #expressions, but they are equal
I am trying to write my first trigger. It is an insert trigger as follows:
<b>Fatal error</b>: postgres7 error: [-1: ERROR: INSERT has more target columns than expressions at character ...
- 23
5
votes
2
answers
2k
views
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."
...
- 597
3
votes
1
answer
553
views
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-...
- 597
2
votes
1
answer
290
views
PLSQL Package - Catching Errors in procedure (Oracle)
I have a problem with my package (and procedure) not catching exceptions.
I have the below code:
CREATE OR REPLACE PACKAGE process_orders
IS
PROCEDURE add_order (
order_num NUMBER,
...
- 23
3
votes
2
answers
10k
views
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
...
Nandish A
25
votes
2
answers
79k
views
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 '...
- 12.3k