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.
formula for calculating tablespace growth
Error Building DB2 SQL Procedure
DB2 Command Centre
One or more values in the INSERT statement, UPDATE statement, or foreign key update c
The cursor specified in a FETCH or CLOSE statement is not open
online incremental backups in DB2
DB2 stored procedures filesystem full errors
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0125E Function sequence error. SQ
problems with DB2 Administration server
Adding space to SMS tablespaces - alternatives?
db2inidb through API
How to obtain a lock on the table
Activate not logged initially issue