Warning message when using Dynamic cursor -

Database
Enthusiast

Warning message when using Dynamic cursor -

Hi,

I am trying to executed a procedure having dynamic cursor which returning result set from a unix script.

Whenever i run the script, it give a warming message  as below

*** Procedure has been executed.

*** Warning: 3212 The stored procedure returned one or more result sets.

Can you please let me know how to suppress this warning, which is causing issue for the downstream...

Tags (3)
11 REPLIES
Junior Contributor

Re: Warning message when using Dynamic cursor -

Hi Ganesan,

just switch it off if BTEQ returns an errorlevel greater zero to the shell:

.SET ERRORLEVEL 3212 SEVERITY 0;

Dieter

Enthusiast

Re: Warning message when using Dynamic cursor -

Dieter, Thanks for your inputs,

Is there any way to suppress at the procedure level.

Thanks in advance,

Ganesan K

Junior Contributor

Re: Warning message when using Dynamic cursor -

Hi Ganesan,

in a SP simply add a CONTINUE HANDLER.

Dieter

Enthusiast

Re: Warning message when using Dynamic cursor -

Thanks dnoeth!!!

Enthusiast

Re: Warning message when using Dynamic cursor -

Dear Dnoeth, 

Still we get the same warning msg:

BTEQ -- Enter your SQL request or BTEQ command:

.SET ERRORLEVEL 3212 SEVERITY 0;

CALL file_count(81);

.SET ERRORLEVEL 3212 SEVERITY 0;

 BTEQ -- Enter your SQL request or BTEQ command:

CALL file_count(81);

 *** Procedure has been executed.

 *** Warning: 3212 The stored procedure returned one or more result sets.

 *** Total elapsed time was 19 seconds.

 *** ResultSet# 1 : 1 rows returned by "FILE_COUNT".

 count_samp

-----------

          0

I tried to use continue handler , even though i am getting the same warning msg.

Can u please guide.

Junior Contributor

Re: Warning message when using Dynamic cursor -

Hi Ganesan,

sorry, i was wrong, of course you can't handle that specific warning within the SP itself.

But when you set the ERRORLEVEL to zero the BTEQ job will also report a zero return code to the shell.

Dieter

Enthusiast

Re: Warning message when using Dynamic cursor -

Why do we get this error?

 

I craeted a procedure and added the DYNAMIC RESULT SETS 3. The no. of rows the cursor returns based on my SQL is 3. But it still gives me that error. Can you help me understand what are results sets ( i thought it is the no.of rows returned from the sql query in the SP)

Highlighted
Junior Contributor

Re: Warning message when using Dynamic cursor -

It's not an Error, it's a Warning, i.e. the SP run successfull.

 

A result set is the result of a SELECT statement, regardless how many rows are returned by it.

 

When DYNAMIC RESULT SETS 3 is defined the SP might open up to 3 cursors = return the result of up to 3 Selects.

Enthusiast

Re: Warning message when using Dynamic cursor -

Thank you so much @dnoeth I was confused about the results sets when i looked this msg. 

Executed as Single statement.
Elapsed time = 00:00:00.124

STATEMENT 1: Unknown completed. 3 rows returned.

[Teradata Database] [TeraJDBC xxxxxxxx] [Error 3212] [SQLState HY000] The stored procedure returned one or more result sets.

 

I stiil dont undertsnad Why would it say [Error 3213] in the warning msg? 

 

I have a simple program to practice cursors, can you help me with some questions i have, please?

 

replace procedure p_test_cursor
(
IN IN_STATE varchar(50)
--,OUT OUT_CITY_CD varchar(3)
)
DYNAMIC RESULT SETS 1
BEGIN
DECLARE var_city_cd varchar(3);
DECLARE cr_test SCROLL cursor with return only FOR
select city_cd from <database_name>.<table_name>
where STATE=IN_STATE
order by city_cd asc
;
OPEN cr_test;

--fetch NEXT from cr_test into var_plan_cd;

--CLOSE cr_test;
--set OUT_CITY_CD=var_city_cd;
END;

 

when i call

call p_test_cursor('NewYork')

It is giving me the result set of all the city_cd's in NewYork state in the Result set viewer.

 

 

1. When i use fetch can i make the results show in the viewer? So far I could only do an OUT parameter and assign the variable to outparam to give out the city code. But in that case i can only show1 city code in output, not all codes. How can i use fetch to show all the city codes in the result set?

 

2. is there anything besides cursor that i can use to display all the city codes in the result set viewer when procedure is called?