asked on

Oracle: create procedure??

Hi,
I am new to Oracle...so facing some issue...any help would be appreciated.

whats wrong in the way I am creating the procedure below???

SQL> create procedure test1 IS
  2  begin
  3  dbms_output.put_line('Hi Ashutosh');
  4  end;
  5  .
SQL> exec test1;
BEGIN test1; END;
 
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'TEST1' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
 
 
SQL> create or replace procedure test1 IS
  2  begin
  3  dbms_output.put_line('Hi Ashutosh');
  4  end;
  5  .
SQL> exec test1;
BEGIN test1; END;
 
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'TEST1' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
 
 
SQL> call test1;
call test1
     *
ERROR at line 1:
ORA-06576: not a valid function or procedure name
 
 
SQL>

Open in new window

ASKER CERTIFIED SOLUTION

Jinesh Kamdar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jinesh Kamdar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ashutosh_kumar

ASKER

hi jinesh!!
thanks for your help...

ok now I can create the SPs...just one more small problem for you....

can you please tell me why this is not compiling???

SQL> create or replace procedure test1 IS
  2  
  3  str_ename emp.ename%type;
  4  str_dname dep.dname%type;
  5  
  6  cursor m_c is  select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;
  7  
  8  begin
  9   open m_c;
 10  
 11  
 12    if m_c%ISOPEN then
 13    loop 
 14       fetch m_c into str_ename, str_dname;
 15       exit when m_c%notfound
 16       
 17       dbms_output.put_line(str_ename || ' works in ' || str_dname || ' department');
 18     end loop;
 19     end if;
 20     end;
 21  /
 
Warning: Procedure created with compilation errors.

Open in new window

Jinesh Kamdar

You missed a semi-colon after the EXIT statement - exit when m_c%notfound;

Jinesh Kamdar

Glad to be of help :)