DB2 StoredProcedure not valid

jeppa 0
Viewed 71

what I missed here, why it is not valide in the context?

CREATE OR REPLACE PROCEDURE "TEST" ()
LANGUAGE SQL
SPECIFIC SQL220908110735859
BEGIN
 declare c1 cursor for select * from xyzschema.table;
  DECLARE GLOBAL TEMPORARY TABLE abc LIKE xyzschema.table;
  open c1;
  fetch c1 into abc; 
END

Error: {0:0} "abc" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.25.1301

The main task is to copy content via the stored procedure. Current Table

The red rows are already existing. The green rows should be created after the copy. This means the content of the row with id 1 should be copied. Input parameter is the org_id (here 1) and tocopy_id (here 2) from Table_A and the condition is that the type should be A.

My solution looks like this:

CREATE OR REPLACE PROCEDURE TEST
(IN org_id INT, IN tocopy_id INT)
LANGUAGE SQL
SPECIFIC SQL220909154402272
BEGIN
--copy contents of Table_B
   FOR v AS cur1 CURSOR FOR 
      select * from TABLE_B where Table_A_Ref = org_id JOIN TABLE_A table_a On Table_A_Ref = table_a.id Where table_a.type = 'A'
   DO 
   --Pk is auto generated
   INSERT INTO TABLE_B VALUES (tocopy_id, v.name_1, v.name_2);
   END FOR; 
   
   --copy contents of Table_C
END
db2 stored-procedures
1 Answers

You may try it as is.

Use of DGTT to store the result of SELECT.

--#SET TERMINATOR @

CREATE OR REPLACE PROCEDURE TEST
BEGIN
  DECLARE GLOBAL TEMPORARY TABLE SESSION.ABC AS 
  (
    SELECT * FROM SYSCAT.SCHEMATA
  ) WITH DATA WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
  -- Do something useful with SESSION.ABC here if needed
END@

CALL TEST@

SELECT * FROM SESSION.ABC@

Fetching into a row variable.

--#SET TERMINATOR @

SET SERVEROUTPUT ON@

BEGIN
  DECLARE TYPE T_SCHEMATA AS ROW ANCHOR ROW SYSCAT.SCHEMATA;
  DECLARE L_VAR T_SCHEMATA;
  DECLARE SQLSTATE CHAR (5);
  DECLARE C1 CURSOR FOR
    SELECT * FROM SYSCAT.SCHEMATA;
  OPEN C1;
  
  L1:
  LOOP
     FETCH C1 INTO L_VAR;
     IF SQLSTATE = '02000' THEN LEAVE L1; END IF;
     -- Do something useful with L_VAR here
     CALL DBMS_OUTPUT.PUT_LINE (L_VAR.SCHEMANAME);
  END LOOP L1;
  CLOSE C1;
END@

SET SERVEROUTPUT OFF@
Mark Barinstein0
Related