1 declare 2 cursor a1 is select column_name, data_type from all_tab_columns where owner='SCOTT' and table_name='A'; 3 rst a1%rowtype; 4 begin 5 open a1; 6 if a1%isopen then 7 loop 8 fetch a1 into rst; 9 exit when a1%notfound; 10 dbms_output.put_line('table a has the column:'||rst.column_name); 11 dbms_output.put_line('the column type is:'||rst.data_type); 12 end loop; 13 end if; 14 close a1; 15* end; SQL> / table a has the column:COL the column type is:NUMBER table a has the column:TIME the column type is:DATE我用scott/tiger这个用户进入sqlplus执行的,好像没什么问题。 估计你是没有把owner和table_name大写。 写成where owner=upper('xxx') and table_name=upper('xx')
应该还是权限的问题,如果是当前用户可以使用下面语句SELECT column_name, data_type FROM USER_TAB_COLUMNS WHERE TABLE_NAME NOT IN ( SELECT VIEW_NAME FROM USER_VIEWS ) and TABLE_NAME='T' ORDER BY LENGTH( TABLE_NAME ) DESC;
我已经给SELECT_CATALOG_ROLE了,还要什么权限,其实我在这2种情况都是同一个用户登录的。
2 cursor a1 is select column_name, data_type from all_tab_columns where owner='SCOTT' and table_name='A';
3 rst a1%rowtype;
4 begin
5 open a1;
6 if a1%isopen then
7 loop
8 fetch a1 into rst;
9 exit when a1%notfound;
10 dbms_output.put_line('table a has the column:'||rst.column_name);
11 dbms_output.put_line('the column type is:'||rst.data_type);
12 end loop;
13 end if;
14 close a1;
15* end;
SQL> /
table a has the column:COL
the column type is:NUMBER
table a has the column:TIME
the column type is:DATE我用scott/tiger这个用户进入sqlplus执行的,好像没什么问题。
估计你是没有把owner和table_name大写。
写成where owner=upper('xxx') and table_name=upper('xx')