• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5884
  • Last Modified:

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

0
ashutosh_kumar
Asked:
ashutosh_kumar
  • 4
2 Solutions
 
Jinesh KamdarCommented:
The problem is because of the . command at the end of the SP, it never gets compiled. Try this.
SQL> create procedure test1 IS
  2  begin
  3  dbms_output.put_line('Hi Ashutosh');
  4  end;
  5  /
SQL> exec test1;

Open in new window

0
 
Jinesh KamdarCommented:
Of course, don't forget the SET SERVEROUT ON command.
SQL> SET SERVEROUT ON
SQL> create procedure test1 IS
  2  begin
  3  dbms_output.put_line('Hi Ashutosh');
  4  end;
  5  /
SQL> exec test1;

Open in new window

0
 
ashutosh_kumarAuthor Commented:
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

0
 
Jinesh KamdarCommented:
You missed a semi-colon after the EXIT statement - exit when m_c%notfound;
0
 
Jinesh KamdarCommented:
Glad to be of help :)
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now