CODEWITHSUNDEEP
oracle-databaseprocedure
bicycle
bicycle

Reputation: 8430

Outputting a single row in stored procedure (oracle)

I'm trying to follow the example at http://dba-oracle.com/t_pl_sql_plsql_select_into_clause.htm

But when i however do 

create or replace PROCEDURE age
is
declare
info movie%rowtype;
BEGIN
dbms_output.enable();
select * into info from movie where mo_id=1;

dbms_output.put_line('The name of the product is ' || info.mo_id);

END age;

/

It gives a couple of errors:

Error(4,1): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior external language The symbol "begin" was substituted for "DECLARE" to continue.

and

Error(14,8): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

what's wrong with it?

Upvotes: 2

Views: 3369

Answers (2)

Nick Krasnov
Nick Krasnov

Reputation: 27251

There are a couple of things in your code to take a look at: First. As @Polppan has already mentioned, remove DECLARE keyword from your stored procedure. There is no need of it. You will need it however when you write anonymous PL/SQL block. Second. If you use dbms_output.enable() in your procedure then to display lines, I assume you are using sql*plus for this, you will need to invoke dbms_output.get_lines() otherwise it will not give you desired result. So to simplify that use set serveroutput on command of sql*plus to enable output. And do not mix dbms_output.enable() and setserveroutput on - use either of them. Not both. Here is an example:

SQL> CREATE OR REPLACE PROCEDURE Print_data
  2  is
  3    l_var_1 varchar2(101);
  4  BEGIN
  5    select 'Some data'
  6      into l_var_1
  7      from dual;
  8    dbms_output.put_line(l_var_1);
  9  END;
 10  /

Procedure created

SQL> set serveroutput on;
SQL> exec print_data;

Some data

PL/SQL procedure successfully completed

SQL>

Upvotes: 1

Jacob
Jacob

Reputation: 14731

Try with the following, you do not need to have declare inside a procedure.

create or replace PROCEDURE
age
is
info movie%rowtype;
BEGIN
--dbms_output.enable();
select * into info from movie where mo_id=1;

dbms_output.put_line('The name of the product is ' || info.mo_id);

END age;
/

and to execute the procedure you could do

exec age

Upvotes: 3

Related Questions