下面是我写的存储过程
CREATE OR REPLACE PROCEDURE schema_tables 
IS
    schema_count        NUMBER:=1;
    type ref_cursor is REF CURSOR;
    v_cursor            ref_cursor;
    v_schema            VARCHAR2(1024);
BEGIN
    OPEN v_cursor FOR select username from SYS.DBA_USERS;
    LOOP
        fetch v_cursor into v_schema;
        exit when v_cursor%notfound;
        DBMS_OUTPUT.PUT_LINE(v_schema);
        
        schema_count := schema_count+1;
      --  END LOOP;
        DBMS_OUTPUT.PUT_LINE('schema_count is');
        DBMS_OUTPUT.PUT_LINE(schema_count);    END LOOP;
END;
/但是调试不通
show error后的结果如下
Errors for PROCEDURE SCHEMA_TABLES:LINE/COL ERROR
-------- -----------------------------------------------------------------
8/23     PL/SQL: SQL Statement ignored
8/48     PL/SQL: ORA-00942: table or view does not exist不知道哪里出了问题,求高人指点
谢谢

解决方案 »

  1.   


    --没什么问题CREATE OR REPLACE PROCEDURE schema_tables  
    IS
      schema_count NUMBER:=1;
      type ref_cursor is REF CURSOR;
      v_cursor ref_cursor;
      v_schema VARCHAR2(1024);
    BEGIN
      OPEN v_cursor FOR select username from SYS.DBA_USERS;
      LOOP
      fetch v_cursor into v_schema;
      exit when v_cursor%notfound;
      DBMS_OUTPUT.PUT_LINE(v_schema);
        
      schema_count := schema_count+1;
      -- END LOOP;
      DBMS_OUTPUT.PUT_LINE('schema_count is');
      DBMS_OUTPUT.PUT_LINE(schema_count);  END LOOP;
    END;
    /EXEC   schema_tables;
    24  PL/SQL block, executed in 0.078 sec.
        ERPCN                               
        schema_count is                     
        2                                   
        ERP                                 
        schema_count is                     
        3                                   
        U1US                                
        schema_count is                     
        4                                   
        U1UP                                
        schema_count is                     
        5                                   
        U0NA01P                             
        schema_count is                     
        6                                   
        U1SS                                
        schema_count is                     
        7                                   
        U1SP                                
        schema_count is                     
        8                                   
        U1PP                                
        schema_count is                     
        9                                   
        U1PS                                
        schema_count is                     
        10                                  
        U1HP                                
        schema_count is                     
        11                                  
        U1HS                                
        schema_count is                     
        12                                  
        U1GS                                
        schema_count is                     
        13                                  
        U1GP                                
        schema_count is                     
        14                                  
        U1FS                                
        schema_count is                     
        15                                  
        U1FP                                
        schema_count is                     
        16                                  
        U1CP                                
        schema_count is                     
        17                                  
        U1CS                                
        schema_count is                     
        18                                  
        U1BP                                
        schema_count is                     
        19                                  
        U1BS                                
        schema_count is                     
        20                                  
        SCOTT                               
        schema_count is                     
        21                                  
        TSMSYS                              
        schema_count is                     
        22                                  
        MDDATA                              
        schema_count is                     
        23                                  
        DIP                                 
        schema_count is                     
        24                                  
        DBSNMP                              
        schema_count is                     
        25                                  
        SYSMAN                              
        schema_count is                     
        26                                  
        MDSYS                               
        schema_count is                     
        27                                  
        ORDSYS                              
        schema_count is                     
        28                                  
        EXFSYS                              
        schema_count is                     
        29                                  
        DMSYS                               
        schema_count is                     
        30                                  
        WMSYS                               
        schema_count is                     
        31                                  
        CTXSYS                              
        schema_count is                     
        32                                  
        ANONYMOUS                           
        schema_count is                     
        33                                  
        XDB                                 
        schema_count is                     
        34                                  
        ORDPLUGINS                          
        schema_count is                     
        35                                  
        SI_INFORMTN_SCHEMA                  
        schema_count is                     
        36                                  
        OLAPSYS                             
        schema_count is                     
        37                                  
        MGMT_VIEW                           
        schema_count is                     
        38                                  
        SYS                                 
        schema_count is                     
        39                                  
        SYSTEM                              
        schema_count is                     
        40                                  
        OUTLN                               
        schema_count is                     
        41                                  
        Total execution time 0.094 sec.     
      

  2.   

    是不是你创建此过程的用户没有SYS.DBA_USERS的权限???
    你最好用DBA身份去做
      

  3.   

    -- 你当前用户没有访问 sys.dba_users数据字典的权限!