CREATE OR REPLACE PROCEDURE gen_tabs
IS
v_bsize CONSTANT NUMBER := 4096;
v_buffer VARCHAR2(240);
CURSOR c1 is select * FROM DBA_TABLES;
c2 DBA_TABLES%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 into c2;
exit when c1%notfound;
end loop;
close c1;
end;
/
IS
v_bsize CONSTANT NUMBER := 4096;
v_buffer VARCHAR2(240);
CURSOR c1 is select * FROM DBA_TABLES;
c2 DBA_TABLES%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 into c2;
exit when c1%notfound;
end loop;
close c1;
end;
/
先授权
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(欧高黎嘉陈):
也不行
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: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: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