Create PROCEDURE using output "TABLE LIKE"
I'm attempting to return a result set as an out parameter in a stored procedure using: CREATE PROCEDURE GetReconnectJobs ( OUT o_data TABLE LIKE GETRECONNECTJOBS_VIEW AS LOCATOR ) BEGIN SELECT fields1..n INTO o_data FROM cibjjo.GetReconnectJobs_VIEW; END; But am getting error: SQL0408N A value is not compatible with the data type of its assignment target. Target name is "O_DATA". Any ideas? Oracle has a ref_cursor, and I'm assuming this is the DB2 equivalent of that???
Originally Posted by Jim313 I'm assuming this is the DB2 equivalent of that??? Your assumption is incorrect. Example: Returning a REF CURSOR from a procedure (PL/SQL) - IBM DB2 9.7 for Linux, UNIX, and Windows
Is there a way to return a results set in DB2 v9.1? The link you provided is for pl/sql using a ref_cursor.
Returning result sets from SQL procedures
I have it compiling ok and will be testing it, but this seemed to work. Thank you!
I can open a cursor and return the results from the SELECT, but I need to update a status flag before I retrieve the records and updated it again after the SELECT. Adding either update statement to the stored procedure causes it to not compile. I tried adding begin-end blocks around each of the Updates, but that didn't seem to matter. Any suggestions on how I can resolve this? CREATE PROCEDURE GetReconnectJobs ( ) LANGUAGE SQL CALLED ON NULL INPUT MODIFIES SQL DATA OPTHINT ' ' REOPT ONCE DYNAMIC RESULT SETS 1 begin-- ADDING THIS UPDATE STATEMENT BREAKS THE PROCEDURE AND CAUSES IT TO NOT COMPILE -- reserve records for select below... UPDATE T14TOPS.CI006v SET RP_SURV_FLPAL_FLG = 'X' -- in transit where RP_SURV_FLPAL_FLG = 'N'; end; Re: BEGIN DECLARE c_ReonnRecs CURSOR WITH RETURN FOR SELECT CUST_NBR, PREM_NBR, FO_NBR, MTR_NES_ID, RECN_RCPT_CK_AMT, FO_INIT_CMT_TXT, ORDPEN_EFF_DAT, FO_TYPE_CDE, AS_CALL_NBR, FO_RSN_ISS_CDE, FO_ORG_DAT, FO_ORG_TIM, ORD_STAT_ID, CUST_NAM, CUST_PHN_NBR, HSE_NBR, ST_NAM, APT_ID, ZIP_CDE, MTR_CONSTA_EXIS_ID, XFMR_POLEPAD_NBR, MTR_LOC_CDE, AMI_RD_FLG -- AMI = 'Y' FROM GetReconnectJobs_VIEW where RP_SURV_FLPAL_FLG = 'X'; end Re; begin -- ADDING THIS UPDATE STATEMENT BREAKS THE PROCEDURE AND CAUSES IT TO NOT COMPILE -- un-reserve records, and set status to In-process UPDATE T14TOPS.CI006v SET RP_SURV_FLPAL_FLG = 'I' -- In-process where RP_SURV_FLPAL_FLG = 'X'; end; OPEN c_ReonnRecs; END ;
Originally Posted by Jim313 Adding this breaks the proceedure. This isn't very informative. I guess we'll just wait for mindreaders to help diagnose the problem.
DB2 Backup with TSM failing with SQL2062N and Reason Code 610
Task Center - User Interface Scheduling
ON DELETE RESTRICT vs ON DELETE NO ACTION
DB2 scheduled backup
DB2 Performance problem
Fetch in DB2 stored procedure
how can I start "db2cc"!
query optimizer runs astray....
Migration AS/400 DB2 to Windows DB2 8.1
Creating a new database with the existing database with same structure
load error SQL0302N
load error SQL0302N
Db2 Udb Unix Aix
Dropping and recreating a primary key which has already unique index