看看我的pl/sql,错在那儿?
SQL> CREATE OR REPLACE PROCEDURE gen_tabs(p_owner VARCHAR2)
2 IS
3 v_bsize CONSTANT NUMBER := 4096;
4 v_buffer VARCHAR2(240);
5 CURSOR c1 is select table_name,tablespace_name,ini_trans FROM DBA_TABLES;
6 c2 c1%ROWTYPE;
7 BEGIN
8 OPEN c1;
9 LOOP
10 FETCH c1 into c2;
11 exit when c1%notfound;
12 end loop;
13 close c1;
14 end;
15 /
警告：已创建的过程出现编译错误。
SQL> show error
PROCEDURE GEN_TABS出现错误：
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/9 PLS-00341: ??'C1'???
5/15 PL/SQL: SQL Statement ignored
5/64 PLS-00201: ???'SYS.DBA_TABLES'
6/5 PL/SQL: Item ignored
10/2 PL/SQL: SQL Statement ignored
10/16 PLS-00320: ?????
SQL>
to bzszp(SongZip):错误依旧
SQL> show error
PROCEDURE GEN_TABS出现错误：
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/15 PL/SQL: SQL Statement ignored
5/29 PLS-00201: ???'SYS.DBA_TABLES'
6/5 PLS-00201: ???'SYS.DBA_TABLES'
6/5 PL/SQL: Item ignored
10/2 PL/SQL: SQL Statement ignored
10/16 PLS-00320: ?????
to jlandzpa(欧高黎嘉陈):
也不行
connect internal/oracle
grant select on sys.dba_tables to usname;//你目前使用的用户名
CREATE OR REPLACE PROCEDURE gen_tabs
IS
v_bsize CONSTANT NUMBER := 4096;
v_buffer VARCHAR2(240);
CURSOR c1 is select * FROM sys.DBA_TABLES;
c2 sys.DBA_TABLES%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 into c2;
exit when c1%notfound;
end loop;
close c1;
end;
/
16:22:57 jlanzpa817>CREATE OR REPLACE PROCEDURE gen_tabs(p_owner VARCHAR2)
16:25:08 2 IS
16:25:08 3 v_bsize CONSTANT NUMBER := 4096;
16:25:08 4 v_buffer VARCHAR2(240);
16:25:08 5 CURSOR c1 is select table_name,tablespace_name,ini_trans FROM sys.DBA_TABLES;
16:25:08 6 c2 c1%ROWTYPE;
16:25:08 7 BEGIN
16:25:08 8 OPEN c1;
16:25:08 9 LOOP
16:25:08 10 FETCH c1 into c2;
16:25:08 11 exit when c1%notfound;
16:25:08 12 end loop;
16:25:08 13 close c1;
16:25:08 14 end;
16:25:09 15 /
警告: 创建的过程带有编译错误。
已用时间: 00: 00: 00.91
16:25:10 jlanzpa817>sho err
PROCEDURE GEN_TABS 出现错误:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/9 PLS-00341: 游标 'C1' 的说明不完整或格式错误
5/15 PL/SQL: SQL Statement ignored
5/64 PLS-00201: 必须说明标识符 'SYS.DBA_TABLES'
6/5 PL/SQL: Item ignored
10/2 PL/SQL: SQL Statement ignored
10/16 PLS-00320: 此表达式的类型说明不完整或格式错误
16:25:15 jlanzpa817>conn sys/zpa
已连接。
16:25:34 jlanzpa817>grant select on DBA_TABLES to test;
授权成功。
已用时间: 00: 00: 00.70
16:25:44 jlanzpa817>CREATE OR REPLACE PROCEDURE gen_tabs(p_owner VARCHAR2)
16:25:50 2 IS
16:25:50 3 v_bsize CONSTANT NUMBER := 4096;
16:25:50 4 v_buffer VARCHAR2(240);
16:25:50 5 CURSOR c1 is select table_name,tablespace_name,ini_trans FROM sys.DBA_TABLES;
16:25:50 6 c2 c1%ROWTYPE;
16:25:50 7 BEGIN
16:25:50 8 OPEN c1;
16:25:50 9 LOOP
16:25:50 10 FETCH c1 into c2;
16:25:50 11 exit when c1%notfound;
16:25:50 12 end loop;
16:25:50 13 close c1;
16:25:50 14 end;
16:25:51 15 /
过程已创建。
已用时间: 00: 00: 00.11
16:25:52 jlanzpa817>
16:28:16 jlanzpa817>conn test/zpa
已连接。
16:28:21 jlanzpa817>CREATE OR REPLACE PROCEDURE gen_tabs(p_owner VARCHAR2)
16:28:27 2 IS
16:28:27 3 v_bsize CONSTANT NUMBER := 4096;
16:28:27 4 v_buffer VARCHAR2(240);
16:28:27 5 CURSOR c1 is select table_name,tablespace_name,ini_trans FROM sys.DBA_TABLES;
16:28:27 6 c2 c1%ROWTYPE;
16:28:27 7 BEGIN
16:28:27 8 OPEN c1;
16:28:27 9 LOOP
16:28:27 10 FETCH c1 into c2;
16:28:27 11 exit when c1%notfound;
16:28:27 12 end loop;
16:28:27 13 close c1;
16:28:27 14 end;
16:28:28 15 /
过程已创建。
已用时间: 00: 00: 00.20
