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

LVL 6
ashutosh_kumarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.