这是一个纠结我一整天的问题了,在SQLDeveloper上写的我的存储过程就是很简单
create or replace procedure PROC_test
as
begin
select * from dba_tables where owner = 'ACM'
end PRO_test;执行
call PROC_test();出现下面的错误:SQL 错误: ORA-06575: 程序包或函数 PROC_TEST 处于无效状态
06575. 00000 - "Package or function %s is in an invalid state"
*Cause: A SQL statement references a PL/SQL function that is in an
invalid state. Oracle attempted to compile the function, but
detected errors.
*Action: Check the SQL statement and the PL/SQL function for syntax
errors or incorrectly assigned, or missing, privileges for a
referenced object.
请问有人能解决吗?万分感激!!!!
create or replace procedure PROC_test
as
begin
select * from dba_tables where owner = 'ACM'
end PRO_test;执行
call PROC_test();出现下面的错误:SQL 错误: ORA-06575: 程序包或函数 PROC_TEST 处于无效状态
06575. 00000 - "Package or function %s is in an invalid state"
*Cause: A SQL statement references a PL/SQL function that is in an
invalid state. Oracle attempted to compile the function, but
detected errors.
*Action: Check the SQL statement and the PL/SQL function for syntax
errors or incorrectly assigned, or missing, privileges for a
referenced object.
请问有人能解决吗?万分感激!!!!
2、问题
a.存储过程中隐式游标的select 语句必须要有into子句。
如:select col1 into v_col1 from dba_tables where owner = 'ACM'
col1为表中一字段,v_col1为一变量
b.在存储过程中访问视图dba_tables,没有权限,你需要显式授权。
如登录sys用户,
grant select on dba_tables to 你的用记
并且必须要有into,或定义成游标才行,其次end PRO_test这句应该是end PROC_test吧。
as
begin
select * from dba_tables where owner = 'ACM'
end PRO_test;
这是一个没有意义的语句;
select col into v_col from table_name where ......
Connected as SYS
SQL> create or replace procedure pro_test
2 as
3 begin
4 for i in (
5 select table_name from dba_tables
6 where owner='SCOTT') loop
7 dbms_output.put_line('tabels in scott schema:'||i.table_name);
8 end loop;
9 end pro_test;--注意end
10 /
Procedure created
SQL> set serveroutput on;
SQL> set pagesize 100;
SQL> exec pro_test;--调用无参过程只需写过程名
tabels in scott schema:DEPT
tabels in scott schema:EMP
tabels in scott schema:BONUS
tabels in scott schema:SALGRADE
tabels in scott schema:BIN$YCT5xmhFSU+EnAnp/mSHZw==$0
tabels in scott schema:BIN$MuwRbIWjRKiVUurtyIT03w==$0
tabels in scott schema:GOODS_TB1
tabels in scott schema:GOODS_TB2
tabels in scott schema:TEST
tabels in scott schema:BIN$vPkh4GFBSw21ItWKZT4KkA==$0
tabels in scott schema:BIN$BYrKqm3ZSxykDmtNfKVNfA==$0
tabels in scott schema:PROJECT_MANAGE
tabels in scott schema:BIN$Mw8EGfnRS72UzIG/j6X+Ew==$0
tabels in scott schema:SYS_TEMP_FBT
tabels in scott schema:BIN$hUpvDWyHTPKmNcrDdDy4IQ==$0
tabels in scott schema:BIN$DFFcU4qjShmXeco/LcjswQ==$0
tabels in scott schema:BIN$uoKglXK2RnKCr1qQXRoIIg==$0
tabels in scott schema:BIN$/aoGE/7uSauFL3HTtl6wUg==$0
tabels in scott schema:BIN$LRRbWxbsSMWAAbuPUHLjCQ==$0
tabels in scott schema:TEMP_TABLE_SESSION
PL/SQL procedure successfully completed
as
begin
select table_name from dba_tables where owner = 'ACM';
end PROC_test3;Warning: 执行完毕, 但带有警告
procedure PROC_test3 已编译。begin
set serveroutput on;
EXEC PROC_test3;
end错误报告:
ORA-06550: 第 2 行, 第 5 列:
PL/SQL: ORA-00922: 选项缺失或无效
ORA-06550: 第 2 行, 第 1 列:
PL/SQL: SQL Statement ignored
ORA-06550: 第 3 行, 第 6 列:
PLS-00103: 出现符号 "PROC_TEST3"在需要下列之一时:
:= . ( @ % ;
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
而且也通不过。
建议你看这个帖子
http://topic.csdn.net/t/20030707/16/1999981.html
存储过程里面进行赋值是不是要这样做的?
select table_name from dba_tables where owner := 'ACM';
我看报错的信息那里好像有这个意思