Q
Problem solve Get help with specific problems with your technologies, process and projects.

Using the PL/SQL input parameter and forward slash in a PL/SQL program

Learn how to use the PL/SQL input parameter and forward slash in a PL/SQL program to avoid an error message in this tip from PL/SQL expert Dan Clamage.

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.

So

 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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close