×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

yes, sql file and error message...

raise_salary.sql

create procedure raise_salary (dno number, percentage number DEFAULT 0.5) is
cursor emp_cur(dept_no number) is
select SAL from EMP where DEPTNO=dept_no
for update of SAL;
empsal number(8);
begin
open emp_cur(dno)
loop
fetch emp_cur into empsal;
exit when emp_cur%NOTFOUND;
update EMP st SAL = empsal *((100+percentage)/100)
where current of emp_cur;
end loop;
close emp_cur;
commit;
end raise_salary;
/

SQL>exec raise_salary(7782)

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'RAISE_SALARY' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Report

Replies, comments and Discussions:

  • 工作学习 / IT技术讨论 / 问一个oracle procedure的问题
    一个procedure写好了之后,如何执行呢?
    我在sqlplus下用exec执行,总是指出identifier 'procedure_name' must be declare?

    以前没有用过oracle, oracle procedure运行之前是否要compile以下?或者说是把它存到数据库里面?
    • do you use following statements to execute it: exec procedure_name? what is your procedure name?
      • yes, sql file and error message...
        raise_salary.sql

        create procedure raise_salary (dno number, percentage number DEFAULT 0.5) is
        cursor emp_cur(dept_no number) is
        select SAL from EMP where DEPTNO=dept_no
        for update of SAL;
        empsal number(8);
        begin
        open emp_cur(dno)
        loop
        fetch emp_cur into empsal;
        exit when emp_cur%NOTFOUND;
        update EMP st SAL = empsal *((100+percentage)/100)
        where current of emp_cur;
        end loop;
        close emp_cur;
        commit;
        end raise_salary;
        /

        SQL>exec raise_salary(7782)

        ERROR at line 1:
        ORA-06550: line 1, column 7:
        PLS-00201: identifier 'RAISE_SALARY' must be declared
        ORA-06550: line 1, column 7:
        PL/SQL: Statement ignored
        • 自己up以下
        • I am thinking that might be caused by different username.
          Look like you create a procedure and then try to execute it from SQL*Plus.
          The schema name of the procedure should be your login name when you create the procedure.
          If you still use the same login name to execute this procedure, there should be NO problem.
          But, there will be a problem if you use a different user to execute this procedure under the condition that you didn't grant execute priviledge to the new user.
          Just some thoughts, may need more detail to troubleshoot your problem.
        • Hi, you should use "@raise_salary.sql to run the sql file to create the procedure , after that you can use "select * from dba_source where name='procedure_name';" to check whether your procedure has
          been generated. If it's there,then you can "exec procedure_name' to exec it
          • thank you, this what I want.
            • before the "end raise_salary; ", I think you also need add a "end;"