How to use fetch in a dynamic cursor

Database
Enthusiast

How to use fetch in a dynamic cursor

Hi ,

I have a dynamic cursor like this

below one is not that exact query:

Declare merge_cursor_statement VARCHAR(3000);
Declare RefCursor2 CURSOR WITH RETURN ONLY FOR merge_cursor_statement;

SET merge_cursor_statement='LOCKING ROW FOR ACCESS SELECT columname deom dbc.columns';
now i want to use the columname from this query and assin to another variable
but when i m using the

OPEN RefCursor2;
L5:
LOOP
FETCH RefCursor2 INTO var1;

I am getting the error SPL5005:E(L497), FETCH not allowed for a WITH RETURN ONLY cursor 'RefCursor2'.

when i m removing the WITH RETURN ONLY i m still getting the same error.

so just wnat to know how to use the columname to assign to another variable.
Tags (1)
15 REPLIES
Enthusiast

Re: How to use fetch in a dynamic cursor

like this procedure
CREATE PROCEDURE abc (IN data1v VARCHAR(10), IN data2v VARCHAR(10) ) DYNAMIC RESULT SETS 1
BEGIN DECLARE sql_stmt1 VARCHAR(100); DECLARE sales DECIMAL(8,2); DECLARE item INTEGER; DECLARE cstmt CURSOR WITH RETURN ONLY FOR stmt1;
SET sql_stmt1 = 'SELECT T1.item, T1.sales FROM T1 WHERE' data1v | '= store_name AND ' | data2v | '= region;';
PREPARE stmt1 FROM sql_stmt1;
OPEN cstmt; END;

how to use the value of the T1.item, T1.sales
because when i do the fetch it will throws an error
FETCH not allowed for a WITH RETURN ONLY cursor 'RefCursor2
Senior Apprentice

Re: How to use fetch in a dynamic cursor

WITH RETURN ONLY is for returning answer sets to the client, not for processing them within the SP, i think you just need to remove it.

But do you actually need a cursor for your processing, you should always try to rewrite it with set-based SQL.
This is an SP in Teradata and not a PL/SQL script.

Dieter
Enthusiast

Re: How to use fetch in a dynamic cursor

replace PROCEDURE RRS_STG_USER. MyFirstProc(IN in_Text VARCHAR(500))
begin

Declare statement1_str VARCHAR(250);
DECLARE Ex CURSOR FOR statement1_str;

SET statement1_str = 'SELECT city_id FROM rrs_stg.rrs_city_arc';

END;

I try compiling this one but this is also not compiling and getting the error

Warnings reported during compilation
SPL5012:W(L7), WITH RETURN clause is used without specifying the DYNAMIC RESULT SETS value.

I want to create the above dynamic cursor and use the value of the city_id in the procedure
Senior Apprentice

Re: How to use fetch in a dynamic cursor

This is copied straight from the manuals:

CREATE PROCEDURE GetEmployeeSalary
(IN EmpName VARCHAR(100), OUT Salary DEC(10,2))
BEGIN
DECLARE SqlStr VARCHAR(1000);
DECLARE C1 CURSOR FOR S1;
SET SqlStr = 'SELECT Salary FROM EmployeeTable WHERE EmpName = ?';
PREPARE S1 FROM SqlStr;
OPEN C1 USING EmpName;
FETCH C1 INTO Salary;
CLOSE C1;
END;

What is your exact TD release and again, why do you think you need to process that data in a cursor?

Dieter
Enthusiast

Re: How to use fetch in a dynamic cursor

Dieter,

We have a situation where the customer wants to output the results of the Teradata 14.0 Stored Procedure through a front end Java Application. Most of the Stored Procedures they have do not contain an OUT parameter and they want to capture a whole bunch of col1, col2 output to the client application through a SELECT operation. Couple of questions:

1) My understanding is that to output the result set, we should use DYNAMIC RESULT SET option. If DYNAMIC RESULT SET is used, should that be used with a combination of WITH RETURN TO CLIENT and by declaring CURSORs?

2) The Stored Procedures are not using CURSORS and don't see a point in rewriting them to use CURSORS (prefer SET based approach)

3) If we can't use a combination of DYNAMIC RESULT SET and WITH RETURN TO CLIENT, is dumping the result to a VOLATILE table the only option?

Below is the sample stored procedure that is used:

REPLACE proc b2b_se_sign_s ( IN se10 BIGINT, IN max_age_days INT DEFAULT 21 ) 
BEGIN
-- se_sign can have multiple rows per se10.  We only want the most recent one.
-- seq_nbr is the primary key on se_sign, and is a bigint comprised of the julian date
-- when loaded into the table concatenated with the record number from that load.
-- max(seq_nbr) will therefore return the most recently loaded row for an se10.
-- :max_age_days default of 21 is a completely arbitrary limit,
-- based on the assumption that no legitimate SE data in se_sign meant to move
-- quickly into se_char should remain exclusively in se_sign for more than 21 days.
-- This assumption is based on some data analysis and se_char's weekly cycle time.
DECLARE target_seq_nbr BIGINT; DECLARE max_age_dt DATE;
-- Get the start of the desired range
SET max_age_dt = dateadd(DAY, -1 * :max_age_days, convert(DATE, getdate())); -- Need to replace this with TD functions
-- Get the PK for the most recent row with insert_dt in the desired range
SET target_seq_nbr =
( SELECT  MAX(ss1.seq_nbr)
FROM     se_sign ss1
WHERE    ss1.se10      = :se10
AND      ss1.insert_dt > max_age_dt
GROUP BY ss1.se10
)
;
-- Select that row
-- Note that insert_dt is selected as open_dt because se_sign contains only SEIMS signings,
-- and se_sign.insert_dt generally corresponds to se_char_seims.open_dt.
-- There are some se10s for which insert_dt != open_dt,
-- but I am going to assume these are unusual circumstances like reinstates or SE10 changes.
SELECT ss1.se10                ,
       ss1.dba_nm              ,
       ss1.insert_dt           ,
       ss1.insert_dt AS open_dt,
       'Y'           AS recent_sign_ind
FROM   se_sign SS
WHERE  ss1.seq_nbr = :target_seq_nbr ;

END;

Thank You

Senior Apprentice

Re: How to use fetch in a dynamic cursor

A cursor used for "dynamic result set" is just named cursor, but it's not fetched sequentially. It's like any result set from a SELECT, just using a more complicated syntax :-)

Thus a Volatile Table is only better when you have to access the result multiple times or use it in another SQL statement.

This seems to be a port from MS SQL Server, where SPs are used a lot to utilise parameters. As long as they use only simple logic (without loops, etc.) you might replace them with macros.

dateadd(DAY, -1 * :max_age_days, convert(DATE, getdate()));

-> CURRENT_DATE - :max_age_days;

Btw, your example is easy to rewrite as a single select in a view.

Dieter

Enthusiast

Re: How to use fetch in a dynamic cursor

Dear Dieter,

can you compile your procedure:

CREATE PROCEDURE GetEmployeeSalary

(IN EmpName VARCHAR(100), OUT Salary DEC(10,2))

BEGIN

DECLARE SqlStr VARCHAR(1000);

DECLARE C1 CURSOR FOR S1;

SET SqlStr = 'SELECT Salary FROM EmployeeTable WHERE EmpName = ?';

PREPARE S1 FROM SqlStr;

OPEN C1 USING EmpName;

FETCH C1 INTO Salary;

CLOSE C1;

END;

I am getting: SPL5009:E(L5), Dynamic cursor 'C1' is not allowed with WITHOUT RETURN, WITH RETURN TO CALLER/CLIENT.

Thanks

Senior Apprentice

Re: How to use fetch in a dynamic cursor

Of course, what's your exact release? You should check if dynamic cursors are supported in yoiur release.

I just compiled it on a TD13.

Dieter

Enthusiast

Re: How to use fetch in a dynamic cursor

RELEASE    13.00.00.12

VERSION    13.00.00.12

LANGUAGE SUPPORT MODE    Standard

TD express