List exceptions from an SQL Script

Database
Highlighted
Enthusiast

List exceptions from an SQL Script

Hi Forum,

I found a very useful script for tracking errors from an SQL script answered by dnoeth.

I'm looking for some sort of explanantion of what the script is doing step by step so I can follow it.

I've marked what I understand and not at the end of the line.

 

--https://community.teradata.com/t5/General/Exception-handling-in-Stored-procedure/td-p/81115
-- LOGGING ERRORS IN SCRIPTS

REPLACE PROCEDURE dbc.sp_DYNAMIC_SQL_EXE   --ok
(
IN PAR_SQL_EXE VARCHAR(6000),       --ok
OUT PAR_EXE_STATUS CHAR(1),         --ok
OUT errmsg VARCHAR(200)             --ok
)
BEGIN                               --ok

	DECLARE VAR_STEP VARCHAR(6000);          --ok
	DECLARE EXIT HANDLER FOR SqlException    --ok
	
	BEGIN                                    --ok       
    
	  GET DIAGNOSTICS EXCEPTION 1 errmsg = Message_Text; -- I think this collects the exception as the error msg 
-- and passes its value to Message_Text SET PAR_EXE_STATUS = '0'; -- set the value of the VARCHAR(6000) as '0', not sure why though? END; --ok CALL DBC.SYSEXECSQL(PAR_SQL_EXE); -- Run the SQL query SET PAR_EXE_STATUS = '1'; -- set the value of the VARCHAR(6000) as '1', again not sure why?
END;

--------------------------------------------------------------------------------------------------------------
-- Now I want to run the SP, is this where I add the whole SQL script?
-- What are 'a' & 'b' supposed to represent here?
CALL dbc.DYNAMIC_SQL_EXE('SELECT YEAR_NUMBER, ID, Scanned_Sales_Value FROM dbc.ANNUAL_FIGURES', a, b); 
 
 

 I'm Calling the SP but getting no errors or results??

Can anyone help here?

Thanks


Accepted Solutions
Junior Contributor

Re: List exceptions from an SQL Script

Assuming the "script" is used to process data you can capture errors within an SP using Error Handlers, which insert the error information into a log table.

An EXIT handler will stop the execution of the SP while a CONTINUE handler simple skips the current statement and runs the next.

Handlers might be generic (FOR SQLEXCEPTION) or for a specific (usually an expected error which can be treated within the script) error (FOR SQLSTATE **bleep**).

And you can define different handlers for each BEGIN/END block.

 

There are lot of examples in the Stored Procedure manual.

You might also have a look at my CollectStatsQueue which shows a nested CONTINUE handler to catch individual errors during stats collection and a global EXIT handler for any other (unexpected) errors, both insert errors into a log table (this is the direct link to the script)

1 ACCEPTED SOLUTION
16 REPLIES 16
Junior Contributor

Re: List exceptions from an SQL Script

	  GET DIAGNOSTICS EXCEPTION 1 errmsg = Message_Text; -- I think this collects the exception as the error msg 
-- and passes its value to Message_Text

 Yes, this extracts one of the fields in the exception record, the exact error messsage. In your following call it will be "SQL statement is not supported within a stored procedure."

 

		SET PAR_EXE_STATUS = '0';                    -- set the value of the VARCHAR(6000) as '0', not sure why though? 
...
		SET PAR_EXE_STATUS = '1';                 -- set the value of the VARCHAR(6000) as '1', again not sure why?

 The PAR_EXE_STATUS is a CHAR(1), it's returned back to the caller after execution indicating the SP had no/an error (ccording to the original question '0' means failing).

 

-- Now I want to run the SP, is this where I add the whole SQL script?
-- What are 'a' & 'b' supposed to represent here?
CALL dbc.DYNAMIC_SQL_EXE('SELECT YEAR_NUMBER, ID, Scanned_Sales_Value FROM dbc.ANNUAL_FIGURES', a, b); 

a & b are the SP's 2nd and 3rd parameter: OUT PAR_EXE_STATUS CHAR(1) and OUT errmsg VARCHAR(200), as they're OUT-parameters you can use any name. In production you should use better names :-).

 

I'm Calling the SP but getting no errors or results??

This is probably a client problem, are you using ODBC & SQL Assistant?

Works fine for .NET or JDBC connections.

 

Btw, this SP is really dangerous, running unchecked/untested Dynamic SQL. For a minimum security it should be defined as SECURITY CALLER.

Enthusiast

Re: List exceptions from an SQL Script

Thanks dnoeth,

I want to avoid a dangerous script so if I have a very long script, what's the best way to capture & output exceptions?

I'm using SQL Assistant with an ODBC connection (as far as I recall - will check tomorrow).

I'm using Windows 7 o/s.

Some scripts are 1000's of rows long so can I pass the query name using a variable or something else rather than the below?

CALL dbc.DYNAMIC_SQL_EXE('SELECT YEAR_NUMBER, ID, Scanned_Sales_Value FROM dbc.ANNUAL_FIGURES', a, b); 

 Thanks

 

Tags (1)
Junior Contributor

Re: List exceptions from an SQL Script


I want to avoid a dangerous script so if I have a very long script, what's the best way to capture & output exceptions?

It's mainly dangerous because every user with the right to CALL this SP might be able to do things he wouldn't be allowed to run directly.

The way to catch exceptions in an SP is the way used here, using Exception Handlers.

 


Some scripts are 1000's of rows long so can I pass the query name using a variable or something else rather than the below?


You must use Dynamic SQL only if you want to pass table or column names dynamically (but based on your previous posts this is probably what you still want to do).

 

Btw, do you really want to do a dynmic Select?

Then you need to use a Dynamic Cursor, see  https://docs.teradata.com/reader/I5Vi6UNnylkj3PsoHlLHVQ/iNPlK91YSucacNPS3aW92w

Enthusiast

Re: List exceptions from an SQL Script

Thanks again dnoeth,

So by a Dynamic Select, do you mean use a Cursor to go through the query results Row by Row?

Btw I am using ODBC, I checked to be sure from your prev question.

If you suggest Dynamic SQL, I have changed the SP to use a Cursor to go through the query Row by Row & look for exceptions.

I'm not sure how to get them to be inserted into a new table though?

Also, is this syntax correct for what I am trying?

The purpose of this script is to go through the below query;

SELECT DISTINCT YEAR_NUMBER, Scanned_Sales_Value
FROM dbc.ANNUAL_FIGURES
WHERE YEAR_NUMBER = vYearNumber

Row by Row, gather any exceptions and put them somewhere I can access them to create a report.

As a test, I've been adding a field that doesn't exist in the table but I haven't got it to collect anything yet.

Any suggestions help welcome.  

 

REPLACE PROCEDURE dbc.ERROR_LOGGING

(IN SQL_TEXT VARCHAR(6000), OUT QUERY_STATUS VARCHAR(200)))

BEGIN 

DECLARE vYearNumber INTEGER;
DECLARE vCountOfItems INTEGER;
DECLARE SQL_TEXT VARCHAR(6000)
DECLARE QUERY_STATUS VARCHAR(200)

	  BEGIN
				      
	  DECLARE EXIT HANDLER FOR SqlException					  
	  DECLARE Cursor1 CURSOR FOR SELECT Year_Number FROM dbc.ANNUAL_SALES FOR READ ONLY;
 
        SELECT Count(*) INTO vCountOfItems FROM dbc.ANNUAL_SALES; -- Count how many items we have to loop
 
		        IF vCountOfItems <> 0 THEN -- avoid a never ending loop
				
					            OPEN Cursor1;
											            Label1:
										                LOOP										 
													                FETCH Cursor1 INTO vYearNumber; 						  
																  				
															         			SET SQL_TEXT = 
																				SELECT DISTINCT YEAR_NUMBER, Scanned_Sales_Value
														                        FROM dbc.ANNUAL_FIGURES
														                        WHERE YEAR_NUMBER = vYearNumber
																				;																		       
															      				END;
				      
																			   EXECUTE IMMEDIATE DBC.SYSEXCSQL(SQL_TEXT)
																			  
																			   SELECT vCountOfItems -1 INTO vCountOfItems; -- decrement the loop
															 
																				                IF vCountOfItems = 0 THEN -- If 0, then you've cycled all the items in the table
																				                        LEAVE Label1;
																				                END IF;
		 
										                END LOOP Label1;
		 
								CLOSE Cursor1;				  
     END
	 
END;

-----------------------------------------------------------------------------------------------------------------------------------------------------------

CALL dbc.ERROR_LOGGING(SQL_TEXT,a,b);

 

  

 

Junior Contributor

Re: List exceptions from an SQL Script

The easiest way to process a cursor is a FOR, there's no need to manually control the FETCHes.

 

REPLACE PROCEDURE dbc.ERROR_LOGGING

(IN SQL_TEXT VARCHAR(6000), OUT QUERY_STATUS VARCHAR(200))

BEGIN 

   DECLARE vYearNumber INTEGER;
   DECLARE vCountOfItems INTEGER;
   DECLARE vSQL_TEXT VARCHAR(6000);
   DECLARE vQUERY_STATUS VARCHAR(200);


   DECLARE EXIT HANDLER FOR SqlException
   BEGIN

   END;

   FOR c AS -- process each row
   SELECT  Year_Number 
   FROM    dbc.ANNUAL_SALES
   DO 
      INSERT INTO my_new_table
      SELECT  DISTINCT YEAR_NUMBER, Scanned_Sales_Value
              FROM    dbc.ANNUAL_FIGURES
              WHERE   YEAR_NUMBER = c.YearNumber;
   END FOR;

END;

You should not create objects in dbc, it's holding only metadata.

Btw, you should not be able to create objects in dbc (not even when logged on as dbc), how did you get that access right?

 

 

Enthusiast

Re: List exceptions from an SQL Script

Thanks dnoeth,

The script only refers to dbc. because I changed it from the original DB.

So just to send a generic script for the Forum to check over & use to help me.

Cheers

Enthusiast

Re: List exceptions from an SQL Script

I'm getting an object 'c' does not exist.

I've tried declaring it but no good either.

Once this starts to work, where can I get the exceptions to output?

Thanks

Junior Contributor

Re: List exceptions from an SQL Script

The "c" is the name of the cursor :

FOR c AS

 no need to declare it.

 

What kind of exception do you expect?

You capture it in the handler.

Enthusiast

Re: List exceptions from an SQL Script

The Create Procedure is failing at the line "WHERE YEAR_NUMBER = c.YearNumber"

 

I want to test this by adding in a Filed that doesn't exist, but I need it to run first. 

 

When you say "You capture it in the handler", how from after I CALL the SP, can I get to the exception info?.