writing a generic procedure in oracle

Question

i want to write procedure which accents name of 2 tables as arguments and then compare the number or rows of the 2. Also i want to each field of the 2 columns.The row which has a missmatch shold be moved to another error table.

Can anyone give a PL/SQL procedure for doing this. I want to achive this in oracle 9

Answer 1

DBMS_SQL is your friend for such operations.

Answer 2

You can use dynamic sql in PL/SQL. EXECUTE IMMEDIATE is your friend.

So, if you take two table names and trying to compare their row counts, you would do something like:

CREATE OR REPLACE PROCEDURE COMPARE_ROW_COUNT(T1 IN VARCHAR2(200), T2 IN VARCHAR2(200)) AS
  v_cursor integer;
  v_r1   integer;
  v_r2   integer;
  v_sql  varchar2(200);
BEGIN
  v_sql := "select count(1) into :1 from " || T1;
  EXECUTE IMMEDIATE v_sql USING v_r1;
  v_sql := "select count(1) into :1 from " || T2;
  EXECUTE IMMEDIATE v_sql USING v_r2;
  -- compare v_r1 and v_r2
END;

Not 100% sure about PL/SQL syntax. It's been a while since the last time I coded in great PL/SQL!

You can achieve same results with similar approach using DBMS_SQL. Syntax is a little bit more complicated though.

Answer 3

Pablos example wont work, the idea is right though.

Something like this do it.

create or replace PROCEDURE COMPARE_ROW_COUNT(T1 IN VARCHAR2, T2 IN VARCHAR2) AS
  v_r1   number;
  v_r2   number;
  v_sql1  varchar2(200);
  v_sql2  varchar2(200);
BEGIN
  v_sql1 := 'select count(1) from ' || T1;
  v_sql2 := 'select count(1) from ' || T2;
  EXECUTE IMMEDIATE v_sql1 into v_r1;
  EXECUTE IMMEDIATE v_sql2 into v_r2;
  dbms_output.put_line(T1 || ' count = ' || v_r1 || ', ' || T2 || ' count = ' || v_r2);
END;