CODEWITHSUNDEEP
sqloracle-databaseplsql
user1835958
user1835958

Reputation: 51

Error in PL/SQL and SQL%ROWCOUNT

I am trying to create a PL/SQL Procedure. But it's not working properly. Please have look at the code and advise on this.

CREATE OR REPLACE PROCEDURE TEST IS

DECLARE
EMPLOYEENUM EMPLOYEE.E#%type;
EMPLOYEENAME EMPLOYEE.NAME%type;
NUMRECORDS NUMBER(2);

BEGIN

SELECT EMP.E# INTO EMPLOYEENUM ,EMP.NAME INTO EMPLOYEENAME
FROM EMPLOYEE EMP
WHERE EXISTS
(SELECT * FROM MECHANIC, DRIVER
WHERE EMP.E# = DRIVER.E# 
AND EMP.E# = MECHANIC.E#);

SELECT COUNT(*) INTO NUMRECORDS
FROM (
SELECT EMP.E#,EMP.NAME
FROM EMPLOYEE EMP
WHERE EXISTS
(SELECT * FROM MECHANIC, DRIVER
 WHERE EMP.E# = DRIVER.E# 
 AND EMP.E# = MECHANIC.E#));

IF (NUMRECORDS > 0) THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: CANNOT MAKE MORE THEN 2 TRIPS PER DAY');
    ROLLBACK;
ELSE
    COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    ROLLBACK;
END VERIFY;

And I am sure I don't need to run the code again to see the number of row counts. I understand there is something called

SQL%ROWCOUNT

which returns the row count. Please advise how to use it.

Upvotes: 1

Views: 1777

Answers (2)

Isha
Isha

Reputation: 31

There are some issues...

  1. DECLARE keyword is not necessary to use in the procedure or function.
  2. Procedure name started as "TEST" and end is "VERIFY"
  3. You used "SELECT EMP.E# INTO EMPLOYEENUM ,EMP.NAME INTO EMPLOYEENAME", you should use all columns first and then the variable names. It should be SELECT EMP.E#,EMP.NAME INTO EMPLOYEENUM, EMPLOYEENAME
  4. You did not use any DML in beetween the procedure and you are using ROLLBACK, Its no of use.

If you can provide the table structure of "Mechanic" and "Driver", then I can send you the code which may help you.

Thanks.

Upvotes: 0

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

You can use an IDE and compile to see where it is failing. If you are trying to compile this in SQLPLUS and you just see "Compiled with errors" message, Type SHOW ERRORS and you'll see the errors. I would still recommend an IDE like sql developer though.

Here are some issues that I can see....

  1. Declare keyword : Not necessary when you are creating a procedure or function
  2. Your create has procedure name as "TEST" and your end has "VERIFY"
  3. SELECT EMP.E# INTO EMPLOYEENUM ,EMP.NAME INTO EMPLOYEENAME : you should list all columns first and then the variable names. It should be SELECT EMP.E#,EMP.NAME INTO EMPLOYEENUM, EMPLOYEENAME
  4. Not sure what you are committing, since there is no DML here, but if your plan is to call this procedure from a trigger, you might hit the mutating table error if the insert is into Mechanic or Driver.

More background and more error information would be useful.

Upvotes: 1

Related Questions