Error when calling procedure from procedure

The following procedure compilation error occurred:

Procedure A receives the result from table B, inputs it to the GLOBAL TEMPORARY TABLE, and retrieves the final result after the operation.

Procedure B is a function that manipulates and retrieves the source data.

When I run the A procedure, I get the following compilation error:

/* 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

PLS-00222: Function with name 'SP_TEST_H2' does not exist in scope

What did I do wrong?

If you're returning something, then use a function - they are designed for such a purpose.

That's what Oracle told you:

Function with name 'SP_TEST_H2' does not exist in scope

which is related to this line in your code:

cv_ins := SP_TEST_H2('XXX');

Function:

SQL> CREATE OR REPLACE FUNCTION sf_test_h2 (p_tbl_name IN VARCHAR)
  2     RETURN SYS_REFCURSOR
  3  AS
  4     v_cursor  SYS_REFCURSOR;
  5  BEGIN
  6     OPEN v_cursor FOR SELECT table_name, column_name
  7                         FROM all_tab_columns
  8                        WHERE table_name = p_tbl_name;
  9
 10     RETURN v_cursor;
 11  END sf_test_h2;
 12  /

Function created.

Procedure:

SQL> CREATE OR REPLACE PROCEDURE sp_test_h1 (p_tbl_name IN VARCHAR)
  2  AS
  3     cv_ins  SYS_REFCURSOR;
  4     v_temp  tt_tb_tmp%ROWTYPE;
  5  BEGIN
  6     cv_ins := sf_test_h2 (p_tbl_name);
  7
  8     LOOP
  9        FETCH cv_ins INTO v_temp;
 10
 11        EXIT WHEN cv_ins%NOTFOUND;
 12
 13        INSERT INTO tt_tb_tmp (table_name, column_name)
 14             VALUES (v_temp.table_name, v_temp.column_name);
 15     END LOOP;
 16
 17     CLOSE cv_ins;
 18  END sp_test_h1;
 19  /

Procedure created.

Testing:

SQL> EXEC sp_test_h1('DEPT');

PL/SQL procedure successfully completed.

SQL> SELECT * FROM tt_tb_tmp;

TABLE_NAME           COLUMN_NAME
-------------------- --------------------
DEPT                 LOC
DEPT                 DNAME
DEPT                 DEPTNO

SQL>

Error when calling procedure from procedure

Related

Recent Posts