下面是我写的存储过程
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不知道哪里出了问题,求高人指点
谢谢
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不知道哪里出了问题,求高人指点
谢谢
--没什么问题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.
你最好用DBA身份去做