我编写了下面的PL/SQL代码来获取模式列表，并进一步从所有模式中的特定版本表中获取记录计数。我的想法是扩展这段代码，如果我成功地首先从一个表中获得输出，就包括更多的表。

-- select domain_name, project_name, db_name, pr_is_active from alm_15_sa_7.projects
-- select count(*) from support_support_sandbox_db.releases

SET serveroutput ON size unlimited;

DECLARE 
    sa_name      VARCHAR2(50 CHAR) :=  'alm_15_sa_7';
    table_exists        NUMBER;
    create_table        LONG;
TYPE cur_type IS REF CURSOR;
    schema_cur          cur_type;
    project_schemas_cur cur_type;
    projects_query      LONG;
    project_size_query  LONG;
    project_schema      VARCHAR2(100 CHAR);
    project_name        VARCHAR2(100 CHAR);
    domain_name         VARCHAR2(100 CHAR);
    is_active           VARCHAR2(1 CHAR);
    Releases_Count integer;
BEGIN
    projects_query := 'select domain_name, project_name, db_name, pr_is_active from ' || sa_name || '.projects';

-- projects_query = 'select domain_name, project_name, db_name, pr_is_active from alm_15_sa_7.projects'

    dbms_output.put_line(projects_query);
    
    create_table:=' 
CREATE  TABLE data_count_report  
(Domain VARCHAR2(50 CHAR),    
project VARCHAR2(50 CHAR),    
db_name  VARCHAR2(50 CHAR),     
is_active  VARCHAR2(1 CHAR),
Releases_Count integer
)';

    SELECT COUNT(*)
    INTO table_exists
    FROM dba_tables
    WHERE lower(table_name) = 'data_count_report';
  IF (table_exists         <> 0) THEN
    EXECUTE immediate 'drop TABLE data_count_report';
    COMMIT;
  END IF;
  EXECUTE immediate create_table;
  COMMIT;
  OPEN project_schemas_cur FOR projects_query;
  <<main_loop>>
  LOOP
    FETCH project_schemas_cur
    INTO domain_name,
        project_name,
        project_schema,
        is_active ;
    IF project_schemas_cur%found THEN
    
      dbms_output.put_line ( 'domain_name ' ||domain_name );
    project_size_query := 'insert into data_count_report (Domain, project, db_name , is_active, Releases_Count )   

   Select ' || '''' || domain_name || ''''|| ' as Domain, ' || '''' || project_name || ''''|| ' as project,  ' || ''''|| project_schema || '''' || ' as db_name,  
 ' || ''''|| is_active || '''' || ' as is_active,' || '''' || Releases_Count || '''' || ' as Releases_Count from  (select count(*) as Releases_Count from ' || project_schema||'.releases )';
-- '|| project_schema||'
      dbms_output.put_line(project_schemas_cur%ROWCOUNT);
      EXECUTE immediate project_size_query;
      COMMIT;
    ELSE
      EXIT;
    END IF;
  END LOOP main_loop;
  CLOSE project_schemas_cur;
END;
/

字符串
我收到以下错误消息

Error report -
ORA-00942: table or view does not exist
ORA-06512: at line 58
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

型
错误出现在下面

(select count(*) as Releases_Count from ' || project_schema||'.releases )';

型
如果我像下面这样硬编码，它工作正常

select count(*) from support_support_sandbox_db.releases
(select count(*) from support_support_sandbox_db.releases )';

型
如何在此行动态提供project_schema。请协助。
错误出现在下面

(select count(*) as Releases_Count from ' || project_schema||'.releases )';

型
如果我像下面这样硬编码，它工作正常

(select count(*) from support_support_sandbox_db.releases )';

型
如何在此行动态提供project_schema。请协助。