Breaking News: Grepper is joining You.com. Read the official announcement!

Error when calling procedure from procedure

Brave Booby answered on January 18, 2022 Popularity 1/10 Helpfulness 1/10

answer Error when calling procedure from procedure

Error when calling procedure from procedure

Comment

/* GLOBAL TEMPORARY TABLE */

CREATE GLOBAL TEMPORARY TABLE TT_TB_TMP 
(
TABLE_NAME VARCHAR2(200) 
,COLUMN_NAME VARCHAR2(200) 
) 
ON COMMIT DELETE ROWS 
NOPARALLEL;

/* PROCEDURE B(SP_TEST_H2)*/

create or replace PROCEDURE SP_TEST_H2
(
  p_TBL_NAME IN VARCHAR
)
AS 
    v_cursor SYS_REFCURSOR;
BEGIN
    OPEN  v_cursor FOR
        SELECT TABLE_NAME, COLUMN_NAME 
          FROM ALL_TAB_COLUMNS
         WHERE TABLE_NAME = 'ALL_XML_SCHEMAS';  -- p_TBL_NAME
    DBMS_SQL.RETURN_RESULT(v_cursor);
    
END SP_TEST_H2;

/* PROCEDURE A(SP_TEST_H1)*/

create or replace PROCEDURE SP_TEST_H1
(
  p_TBL_NAME IN VARCHAR
)
AS 
     v_cursor SYS_REFCURSOR;
BEGIN

    
    DECLARE
      cv_ins SYS_REFCURSOR;
      v_temp TT_TB_TMP%ROWTYPE;
   
    BEGIN
      cv_ins := SP_TEST_H2('XXX');    
      LOOP
         FETCH cv_ins INTO v_temp;
         EXIT WHEN cv_ins%NOTFOUND;
         INSERT INTO TT_TB_TMP VALUES v_temp;
      END LOOP;
      CLOSE cv_ins;

       /*
      OPEN  v_cursor FOR
        SELECT * FROM TT_TB_TMP;
        DBMS_SQL.RETURN_RESULT(v_cursor);
      */


    END;

END SP_TEST_H1

Popularity 1/10 Helpfulness 1/10 Language sql

Source: stackoverflow.com

Tags: procedure sql when

Link to this answer
Share Copy Link