CODEWITHSUNDEEP
plsqlcursorprocedurerecords
Jyoti Ranjan Pattnaik
Jyoti Ranjan Pattnaik

Reputation: 723

ROWTYPE definition in pl/sql

I have written a PL/SQL Procedure which compares data between two tables and print the difference if any, but the twist is the table names to the procedure is dynamic. Here is the procedure

create or replace PROCEDURE compareTables(
tabA IN VARCHAR2, tabB IN VARCHAR2) AS
    cur_tab_name USER_TABLES%ROWTYPE;
    lv_sql varchar2(4000);
    lv_sql2 varchar2(4000);
BEGIN
--SELECT TABLE_NAME INTO cur_tab_name FROM USER_TABLES WHERE TABLE_NAME = tabA;
lv_sql2 := 'SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = :b_tabA';
EXECUTE IMMEDIATE lv_sql2 INTO cur_tab_name USING tabA;
    <<child>>
     DECLARE
         TYPE cursor_ref IS REF CURSOR;
         cur_comp_result cursor_ref;
         rec_comp_result cur_tab_name.TABLE_NAME%rowtype;
     BEGIN
          lv_sql := 'SELECT * FROM '||tabA||' MINUS SELECT * FROM '||tabB;
          OPEN cur_comp_result FOR lv_sql;
        LOOP
            FETCH cur_comp_result INTO rec_comp_result;
            EXIT WHEN cur_comp_result%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(rec_comp_result.empid || '' || rec_comp_result.name);
        END LOOP;
          CLOSE cur_comp_result;
     Exception 
        When others then 
                dbms_output.put_line('The Problem is '||sqlerrm); 

     END;
END compareTables;

Now the problem is when I compile this procedure I am getting the following error

Error at line 14: PLS-00310: with %ROWTYPE attribute, 'CUR_TAB_NAME.TABLE_NAME' must   name a table, cursor or cursor-variable

line 14:rec_comp_result cur_tab_name.TABLE_NAME%rowtype;

how will I solve it?

*NB: I don't have oracle installed in my system. I am using Oracle Apex Online tool which uses Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 and PL/SQL Release 11.2.0.3.0

Upvotes: 0

Views: 4057

Answers (2)

Plouf
Plouf

Reputation: 627

Use: cur_tab_name.table_name. The variable CUR_TAB_NAMEis of type USER_TABLE%ROWTYPE, thus it has several fields.

Upvotes: 0

EvilTeach
EvilTeach

Reputation: 28882

As a test, go to the last line, and after the semicolon, hit enter. I know that Pro*C in particular will gag without a line terminator at the end of the file. You may be encounting that issue.

Outside the scope of your question consider

SELECT columns 
FROM TABLE1

MINUS

SELECT columns
FROM TABLE2

and

SELECT columns
FROM TABLE2

MINUS

SELECT columns
FROM TABLE1

Upvotes: 2

Related Questions