CODEWITHSUNDEEP
oracle-databaseplsqloracle10g
Mohan
Mohan

Reputation: 248

Not able to execute a proc in oracle 10 g

I am trying to execute a simple proc Like this in oracle 10g but not able to do getting error PLS-00905: object dbnew.sp_TDCCountry is invalid any idea would be appreciated Table

CREATE TABLE TDCCountry
( CountryID number(10) NOT NULL,
  CountryName varchar2(50) NOT NULL  
);

Procedure

CREATE OR REPLACE PROCEDURE SP_TDCCountry 
IS
BEGIN  
select * from tdcCountry;
COMMIT;
 END SP_TDCCountry;

Execution 1.

begin
   SP_TDCCountry;
  end;

2.exec SP_TDCCountry;

Upvotes: 0

Views: 46

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65253

Because you do not have an into clause by which you return values to some variables. It may be proper to return your variable as a rowtype [ By the way a commit is not needed for a non-DDL( in this case, there's a SELECT) statement ].

So, You may use in the following way :

SQL> set serveroutput on;
SQL> CREATE OR REPLACE PROCEDURE SP_TDCCountry IS
      v_row tdcCountry%rowtype;
BEGIN
      select * into v_row from tdcCountry;
      dbms_output.put(v_row.countryid||' - ');
      dbms_output.put_line(v_row.countryname);
END;
/
SQL> exec SP_TDCCountry;

If your SELECT statement brings more than one row, then it's proper to return data by means of cursor :

SQL> CREATE OR REPLACE PROCEDURE SP_TDCCountry IS
      v_row tdcCountry%rowtype;
BEGIN
      for c in ( select * from tdcCountry )
      loop
       dbms_output.put(c.countryid||' - ');
       dbms_output.put_line(c.countryname);
      end loop; 
END;
/
SQL> exec SP_TDCCountry;

Upvotes: 2

Related Questions