我是用的oracle数据库 在pl/sql中使用select text from user_source where name='pro_emp';语句查找存储过程的内容结果查出来是空的,换了DBA角色也不行,最后我把text换成*,却查出来了,郁闷。。。。。求解
还有一个问题:我创建的包和包体在右边的packages的文件树形结构下的图标上面都有一个红色的叉号,编译的时候没有错误调用包,提示创建的包无效。。。这是哪里错了?
--包
create or replace package my_pkg is
pragma serially_reusable;
v_sqlcode number;
v_sqlerrm varchar2(2048);
function add_dept(v_deptno number,v_dname varchar2,v_loc varchar2)return number;
function cntdept(v_deptno varchar2);
procedure read_dept;
end my_pkg;
--包体
create or replace package body my_pkg
is
pragma serially_reusable;
v_flag number;
cursor c_mvcursor is select * from dept;
mvcursor c_mvcursor%rowtype;
function cntdeptno(v_deptno dept.deptno%type)
is
begin
select count(*) into v_flag from dept where deptno=v_deptno;
if v_flag>0 then
v_flag:=1;
else
v_flag:=0;
end if;
return v_flag;
end cntdeptno;
function add_dept(v_deptno number,v_dname,varchar2(10),v_loc varchar2(20))
is
begin
if cntdeptno(v_deptno)=0 then
insert into dept values(v_deptno.v_dname,v_loc);
return 1;
else
return 0;
end if;
exception
when others then
v_sqlcode := sqlcode;
v_sqlerrm := sqlerrm;
return -1;
end add_dept;
procedure read_dept
is
begin
for c_mvcursor in mvcursor loop
v_deptno := c_mvcursor.deptno;
v_dname := c_mvcursor.dname;
dbms_output.put_line(v_deptno||' '||v_dname);
end loop;
end read_dept;
end my_pkg;
--调用包
declare
adddept number;
begin
adddept := my_pkg.add_dept(50,'dept1','loc1');
end;