procedure question

i am a student at the university of toronto. I have to make a database in oracle for a coursework. I have never used oracle before and am a little unfamilliar with sql. I have to make a procedure in pl/sql to display details of a video supplier from a given address in my video supplier database. I am totally stuck with this.

Here I assume that you have installed Oracle database in your system and know how to invoke the SQL*Plus, now see the following example:

/* This procedure will take department number as input, manipulate data and display the output on SQL prompt */

CRAETE OR REPLACE PROCEDURE get_emp_info (p_deptno IN NUMBER) IS

CURSOR c_emp IS
SELECT empno,ename,job,sal
FROM emp
WHERE deptno=p_deptno;

BEGIN
/* dbms_output.put_line prints the user output on SQL prompt */
dbms_output.put_line('Employees information for department '||LTRIM(p_deptno));
dbms_output.put_line('---------------------------------------');
dbms_output.put_line('Employee Employee Name Job Salary');
dbms_output.put_line(' No.');
dbms_output.put_line(-------- --------------- --------- ------');

/* loop creates an implicit cursor "emp_rec" that has same structure defined in CRAETE CURSOR command */
FOR emp_rec IN c_emp
LOOP
dbms_output.put_line(LPAD(emp_rec.empno,9,' ')||' '||RPAD(emp_rec.ename,15,' ')||' '||RPAD(emprec.job,10,' ')||emp_rec.sal);
END LOOP;

END;
/

When your procedure is created successfully, run it as follows:

SQL>execute get_emp_info(10);

before run your procedure to get output, make sure that your serveroutput is on, to make your serveroutput on, use the following command:

SQL>set serveroutput on size 1000000

Let's see how you done your assignment.

Regards

Hi there, thanks for helping me out with that procedure. I have changed it to my values but i still get compilation errors.

CREATE OR REPLACE PROCEDURE get_supplier_info (p_supp_code IN NUMBER) IS

CURSOR c_su IS
SELECT supp_code,name,address,tel_num
FROM supplier
WHERE supp_code=p_supp_code;

BEGIN
/* dbms_output.put_line prints the user output on SQL prompt */
dbms_output.put_line('Supplier information for given address '||LTRIM(p_supp_code));
dbms_output.put_line('---------------------------------------');
dbms_output.put_line('Supp_code Name Address Tel_Num');
dbms_output.put_line(' No.');
dbms_output.put_line(-------- --------------- --------- ------');

/* loop creates an implicit cursor "emp_rec" that has same structure defined in CRAETE CURSOR command */
FOR supp_rec IN c_sup
LOOP
dbms_output.put_line(LPAD(sup_rec.supp_code,9,' ')||' '||RPAD(sup_rec.name,15,' ')||' '||RPAD(supp_rec.address,10,' ')||sup_rec.tel_num);
END LOOP;

here is your error free code:

CREATE OR REPLACE PROCEDURE get_supplier_info (p_supp_code IN NUMBER)
IS
CURSOR c_sup IS
SELECT supp_code,name,address,tel_num
FROM supplier
WHERE supp_code=p_supp_code;

BEGIN
/* dbms_output.put_line prints the user output on SQL prompt */
dbms_output.put_line('Supplier information for given address '||LTRIM(p_supp_code));
dbms_output.put_line('---------------------------------------');
dbms_output.put_line('Supp_code Name Address Tel_Num');
dbms_output.put_line(' No.');
dbms_output.put_line('-------- --------------- --------- ------');
/* loop creates an implicit cursor "emp_rec" that has same structure defined in CRAETE CURSOR command */
FOR sup_rec IN c_sup
LOOP
dbms_output.put_line(LPAD(sup_rec.supp_code,9,' ')||' '||
RPAD(sup_rec.name,15,' ')||' '||
RPAD(sup_rec.address,10,' ')||' '||sup_rec.tel_num);
END LOOP;
END;
/

To remove errors from your code, use 'show errors' command, this command displays the line number that has error and possible solution.

SQL>show errors

Regards,