Procedures in PLSQL using ORACLE DB

Question

CREATE OR REPLACE PROCEDURE ABC ( REG_NO   IN  CO_ENROLMENT.S_REGNO%TYPE,
                                  TERM     IN  COURSEOFFERING.CO_TERMNUMBER%TYPE,
                                  YEAR     IN  COURSEOFFERING.CO_YEAR%TYPE,
                                  CO_TITLE IN  COURSE.C_TITLE%TYPE,
                                  EN_DATE  OUT CO_ENROLMENT.COE_ENROLDATE%TYPE,
                                  COM_ST   OUT CO_ENROLMENT.COE_COMPLETIONSTATUS%TYPE)

AS 
BEGIN
   SELECT M.COE_COMPLETIONSTATUS, M.COE_ENROLDATE 
     INTO COM_ST, EN_DATE
     FROM COURSEOFFERING O
    INNER JOIN COURSE C
            ON C.C_ID = O.C_ID
    INNER JOIN CO_ENROLMENT M
            ON M.CO_ID = O.CO_ID
    WHERE M.S_REGNO LIKE REG_NO AND
          O.CO_TERMNUMBER LIKE TERM AND
          O.CO_YEAR LIKE YEAR AND
          C.C_TITLE LIKE CO_TITLE;
END ABC;

I have written down above procedure. Below is PL/SQL block to call above procedure,

DECLARE
  COMPL_STATUS   CO_ENROLMENT.COE_COMPLETIONSTATUS%TYPE;
  ENROL_DATE     CO_ENROLMENT.COE_ENROLDATE%TYPE;
BEGIN
  ABC (44444444, 2009, 2, 'PLSQL Programming', 
       EN_DATE => enrol_date, COM_ST =>compl_status);
  DBMS_OUTPUT.PUT_LINE ('STUDENT COMPLETION STATUS AND ENROLMENT DATE IS ' 
                         || ENROL_DATE
                         || '    ' 
                         || compl_status );
END;

It returns me error as no data found, but when I run query separately I get the output. I couldn't figure out what is wrong. Did I wrote procedure block correctly and Parameters passed in PL/SQL block are correct?

if your procedure is not doing any insert/update/delete then why not use a function instead? — Jacob
– Jacob, Commented Oct 31, 2012 at 7:55
Everything is working fine except that output values are not printed......output line is printed i.e.STUDENT COMPLETION STATUS AND ENROLMENT DATE IS but no values for ENROL_DATE and COMPL_STATUS. I also cross checked with just query and it returned me output values with the same input parameters. I think the problem is somewhere in passing or retrieving output parameters though I'm not sure. — Pravin
– Pravin, Commented Oct 31, 2012 at 8:04
Why are you passing the value 2009 to term and 2 to year? Surely these parameters should be reversed. It seems like you would benefit from always using named parameter notation (like you use for the last two parameters). — Colin 't Hart
– Colin 't Hart, Commented Oct 31, 2012 at 8:06

elrado · Accepted Answer · 2012-10-31 06:56:51Z

1

In proc you have year on third place buth you call it on second place. And how are types (COURSEOFFERING.CO_TERMNUMBER%TYPE, COURSEOFFERING.CO_TERMNUMBER%TYPE) declared?

answered Oct 31, 2012 at 6:56

elrado

5,3321 gold badge19 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Pravin Over a year ago

I declared all the field with the same type as that of a specified table's column.

elrado Over a year ago

Ind what about parameter switching

elrado Over a year ago

try declaring reg_no, term and year AS number type!

Pravin Over a year ago

got the output as but date and completion status is not printed OUTPUT - STUDENT COMPLETION STATUS AND ENROLMENT DATE IS

elrado Over a year ago

then also change other parameter types in simple types (DATE, NUMBER, VARCHAR2...)

|

Nick Krasnov · Accepted Answer · 2012-10-31 06:59:39Z

When select statement in your ABC procedure returns no rows the NO_DATA_FOUND exception will be raised and execution of your stored-procedure will be halted. To avoid such behavior you need to add EXCEPTION section in the stored procedure to catch the exception and react appropriately. To that end the execution section of your stored procedure might look:

BEGIN
   SELECT M.COE_COMPLETIONSTATUS, M.COE_ENROLDATE 
     INTO COM_ST, EN_DATE
     FROM COURSEOFFERING O
    INNER JOIN COURSE C
            ON C.C_ID = O.C_ID
    INNER JOIN CO_ENROLMENT M
            ON M.CO_ID = O.CO_ID
    WHERE M.S_REGNO LIKE REG_NO AND
          O.CO_TERMNUMBER LIKE TERM AND
          O.CO_YEAR LIKE YEAR AND
          C.C_TITLE LIKE CO_TITLE;
EXCEPTION
  WHEN NO_DATA_FOUND
  THEN DBMS_OUTPUT.PUT_LINE('No data found') -- for example
END ABC

Or if you want to proceed even if select statement raises exception you might enclose your select statement with nested BEGIN .. END block.

BEGIN
   -- some code before
   BEGIN    
     SELECT M.COE_COMPLETIONSTATUS, M.COE_ENROLDATE 
       INTO COM_ST, EN_DATE
       FROM COURSEOFFERING O
      INNER JOIN COURSE C
              ON C.C_ID = O.C_ID
      INNER JOIN CO_ENROLMENT M
              ON M.CO_ID = O.CO_ID
      WHERE M.S_REGNO LIKE REG_NO AND
            O.CO_TERMNUMBER LIKE TERM AND
            O.CO_YEAR LIKE YEAR AND
            C.C_TITLE LIKE CO_TITLE;
   EXCEPTION
     WHEN NO_DATA_FOUND
     THEN DBMS_OUTPUT.PUT_LINE('No data found') -- for example
  END;
  -- some code after
END ABC

Kirill Leontev · Accepted Answer · 2012-10-31 08:03:52Z

1

You probably lack wildcards in your procedure query.

16:02:06 SYSTEM@dwh-prod> select * from dual where 'abc' like 'ab'; 

no rows selected                                                    


16:02:18 SYSTEM@dwh-prod> select * from dual where 'abc' like 'ab%';

D                                                                   
-                                                                   
X

Without them, C.C_TITLE LIKE CO_TITLE is equal to C.C_TITLE = CO_TITLE, which is not neccessarily what you want.

Try C.C_TITLE LIKE '%'||CO_TITLE||'%' etc.

answered Oct 31, 2012 at 8:03

Kirill Leontev

11k8 gold badges46 silver badges50 bronze badges

2 Comments

Pravin Over a year ago

Cool dude..........yippe...ohh man after so much of struggle finally got the output!!! Thank You so much wild card was the culprit.....but I had used TRIM function, it should had given me result?

Kirill Leontev Over a year ago

maybe. maybe not. It depends upon what and how you were trimming. I can't tell without seeing actual code together with data.

Colin 't Hart · Accepted Answer · 2012-11-01 08:17:44Z

0

You're calling your stored procedure incorrectly. You have to specify parameters in the correct order, or use named parameter notation, ie

ABC (44444444, 2, 2009, 'PLSQL Programming', enrol_date, compl_status);

or

ABC (REG_NO => 44444444, YEAR => 2009, TERM => 2,
   CO_TITLE => 'PLSQL Programming', 
   EN_DATE => enrol_date, COM_ST =>compl_status);

You can see that if you use named parameter notation you can specify parameters in a different order (or not at all if the parameters have DEFAULT values).

edited Nov 1, 2012 at 8:17

answered Oct 31, 2012 at 8:09

Colin 't Hart

7,8084 gold badges34 silver badges53 bronze badges

Collectives™ on Stack Overflow

Procedures in PLSQL using ORACLE DB

4 Answers 4

6 Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related