I'm new to PL/SQL and I have recently installed Oracle 10g. When I try to execute the below PL/SQL statement, I'm getting the error message:
create or replace procedure raja AS V_name varchar2(30); V_age number(15); V_employee_id number(15); Begin select name, age, employee_id into V_name,V_age,V_employee_id from employee where employee_id= 1; dbms_output.put_line('V_name'|| V_age || V_employee_id); end; Error_message: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( ; is with authid as cluster compress order using compiled wrapped external deterministic parallel_enable pipelined The symbol ";" was substituted for "end-of-file" to continue.
Please assist in getting it resolved.
All PL/SQL blocks, including subprograms, must be terminated with a forward slash (/) on the next line after the end. The forward slash means "Execute" -- a throwback to the ancient dumb-terminal days of the 1970's when IBM invented SQL. I fixed a little SNAFU in your program (removed the single-quotes around the variable v_name) and prettied up the output slightly.
SQL> create table employee ( 2 name varchar2(30), age number(15), employee_id number(15) 3 ); Table created. SQL> CREATE OR REPLACE PROCEDURE raja 2 AS 3 v_name VARCHAR2(30); 4 v_age NUMBER(15); 5 v_employee_id NUMBER(15); 6 BEGIN 7 SELECT name, age, employee_id 8 INTO v_name, v_age, v_employee_id 9 FROM employee 10 WHERE employee_id = 1; 11 12 DBMS_OUTPUT.put_line(v_name || ': ' || v_age || ',' || v_employee_id); 13 END raja; 14 / Procedure created. -- insert a row SQL> INSERT INTO employee 2 VALUES ( 3 'Raja' 4 ,27 5 ,1 6 ); 1 row created. SQL> set serveroutput on size 1000000 format wrapped -- run the stored procedure SQL> BEGIN 2 raja; 3 END; 4 / Raja: 27,1 PL/SQL procedure successfully completed.
I have some other comments regarding your program:
You can anchor program variables on the actual table columns. This way, if the columns change, you get the changes for free -- no rework needed.
v_name VARCHAR2(30); v_age NUMBER(15); v_employee_id NUMBER(15); becomes v_name employee.name%type; v_age employee.age%type; v_employee_id employee.employee_id%type;
or even more concisely
v_rec employee%rowtype; -- reference elements as v_rec.name, v_rec.age, v_rec.employee_id
Some exception handling is in order. If no rows are found, you get an unhandled exception:
SQL> delete from employee; 1 row deleted. SQL> BEGIN 2 raja; 3 END; 4 / BEGIN * ERROR at line 1: ORA-01403: no data found ORA-06512: at "SCOTT.RAJA", line 7 ORA-06512: at line 2
You can add an exception handler to display a useful message:
EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line('Sorry, that employee does not exist!'); end raja;
Your procedure is more useful if you generalize things with an input parameter. If you provide an employee_id, for example, you can look it up:
CREATE OR REPLACE PROCEDURE raja(p_employee_id IN employee.employee_id%TYPE) AS v_rec employee%ROWTYPE; BEGIN SELECT * INTO v_rec FROM employee WHERE employee_id = p_employee_id; DBMS_OUTPUT.put_line( v_rec.name || ': ' || v_rec.age || ',' || v_rec.employee_id ); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line( 'Sorry, employee_id ' || p_employee_id || ' not found!' ); END raja; / SQL> BEGIN 2 raja(1); 3 END; 4 / Sorry, employee_id 1 not found! PL/SQL procedure successfully completed. SQL> INSERT INTO employee 2 VALUES ( 3 'Raja' 4 ,27 5 ,1 6 ); 1 row created. SQL> BEGIN 2 raja(1); 3 END; 4 / Raja: 27,1 PL/SQL procedure successfully completed. SQL> INSERT INTO employee VALUES ('Dan', 49, 2); 1 row created. SQL> BEGIN 2 raja(2); 3 END; 4 / Dan: 49,2 PL/SQL procedure successfully completed.
Dig Deeper on Using Oracle PL-SQL
Related Q&A from Dan Clamage
A reader asks Oracle expert Dan Clamage about a single date condition mucking up a dynamic query. Continue Reading
One reader asks expert Daniel Clamage about the PL/SQL to_date and to_char functions and how to properly convert date and string values. Continue Reading
Oracle PL-SQL expert Daniel Clamage answers a question about a problem with doing an “insert” in PL-SQL to create a table dynamically. Continue Reading