CODEWITHSUNDEEP
plsqloracle11g
Kenny
Kenny

Reputation: 59

calling data from procedure

Hi I'm not sure what I'm doing wrong. I'm trying to display/return all records from the procedure I created. Table structure is DD_PROJECT with the following IDPROJ, PROJNAME,PROJSTART,PROJEND,PROJGOAL,PROJ COORD. I need help pulling that information based off project ID specified. Anyways, here is my code so far:

     create or replace procedure DDPROJ_SP(p_proj_id DD_PROJECT.IDPROJ%type, p_proj_rec out DD_PROJECT%rowtype)
 is 
 begin 
 select *
 into p_proj_rec
 from DD_PROJECT
 where DD_PROJECT.IDPROJ=p_proj_id;

exception
 when others then
 p_proj_rec.idproj:=-1;
 end;

now I'm trying to invoke the procedure with this code:

set serveroutput on
DECLARE
 p_proj_id :=& proj_id;

BEGIN
  DDROJ_SP(p_proj_id);
DBMS_OUTPUT.PUT_LINE(
'Project ID: ' || p_proj_rec.projid 
||' Project Name: ' || p_proj_rec.projname 
||' Project Start Date: ' || p_proj_rec.projstartdate 
||' Project End Date: ' || p_proj_rec.projenddate
||' Project Fund Goal: ' ||  p_proj_rec.projfundgoal 
||' Project Coor: ' ||  p_proj_rec.projcoord 
);

end;

This is the error: Error report - ORA-06550: line 2, column 12: PLS-00103: Encountered the symbol "="

At this point I want the user to input a project Id and pull the information in my output code above.

Upvotes: 1

Views: 355

Answers (2)

pablomatico
pablomatico

Reputation: 2242

You missed the variable type:

SET SERVEROUTPUT ON
DECLARE
  p_proj_id dd_project.idproj%TYPE :=& proj_id;
  p_proj_rec dd_project%rowtype;
BEGIN
  DDROJ_SP(p_proj_id,p_proj_rec);
  DBMS_OUTPUT.PUT_LINE('Project ID: ' || p_proj_rec.projid 
                       ||' Project Name: ' || p_proj_rec.projname 
                       ||' Project Start Date: ' || p_proj_rec.projstartdate 
                       ||' Project End Date: ' || p_proj_rec.projenddate
                       ||' Project Fund Goal: ' ||  p_proj_rec.projfundgoal 
                       ||' Project Coor: ' ||  p_proj_rec.projcoord 
                       );

END;

Upvotes: 4

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

Error report - ORA-06550: line 2, column 12: PLS-00103: Encountered the symbol "="

Issue# 1

That is because in line 2 of the anonymous block you have declared the variable without mentioning it's DATA TYPE. You could do it as:

set serveroutput on
DECLARE
   p_proj_id DD_PROJECT.IDPROJ%TYPE; -- Data type
BEGIN
   p_proj_id :=& proj_id;

Issue# 2

DDROJ_SP(p_proj_id);

You also need to declare p_proj_rec which you are using as the OUT parameter in the procedure.

Your requirement is quite simple and you could do it using REFCURSOR. To print the row, you could use the print command in SQL*Plus.

For example,

SQL> CREATE OR REPLACE
  2  PROCEDURE p_rec(i_empno emp.empno%TYPE,
  3      emp_rec OUT sys_refcursor)
  4  IS
  5  BEGIN
  6    open emp_rec for select * FROM scott.emp where empno = i_empno;
  7  END p_rec;
  8  /

Procedure created.

SQL>
SQL> sho err
No errors.
SQL>
SQL> variable emp_rec refcursor
SQL>
SQL> BEGIN
  2    p_rec(7369, :emp_rec);
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> print emp_rec;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

SQL>

Ideally, you won't even need the procedure, it is just to take the empno as the INPUT.

You could just define the input, or prompt for the values, open the cursor and print the records:

SQL> variable r refcursor
SQL> define i_empno=7369
SQL>
SQL> BEGIN
  2    OPEN :r FOR SELECT * FROM emp WHERE empno = &i_empno;
  3  END;
  4  /
old   2:   OPEN :r FOR SELECT * FROM emp WHERE empno = &i_empno;
new   2:   OPEN :r FOR SELECT * FROM emp WHERE empno = 7369;

PL/SQL procedure successfully completed.

SQL>
SQL> print r;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

SQL>

Upvotes: 2

Related Questions