CODEWITHSUNDEEP
sqloracle-databasegenericsstored-procedures
user223541
user223541

Reputation: 1265

writing a generic procedure in oracle

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

Upvotes: 2

Views: 947

Answers (4)

g00dy
g00dy

Reputation: 6778

I am just posting here to note that all answers gravitate around dynamic SQL, and do not turn the attention to the implied problems using it. Consider passing the following string as first or second parameter:

dual where rownum = 0 intersect 
SELECT 0 FROM dual WHERE exists (select 1 from user_sys_privs where UPPER(privilege) = 'DROP USER')

I'll leave it to that.

To answer your question - Oracle actually stores these values in the data dictionary, so if you have access to it:

CREATE OR REPLACE PROCEDURE COMPARE_ROW_COUNT(T1 IN VARCHAR2, T2 IN VARCHAR2) AS
  v_text varchar2(1000);
BEGIN
  select listagg(owner || ' ' || table_name || ' count = ' || num_rows, ',')
   into v_text
   from all_tables --user, all or dba tables depends on requirements
   where table_name in (T1, T2);

 dbms_output.put_line(v_text);

 exception
   when others then raise; -- Put anything here, as long as you have an exception block
END COMPARE_ROW_COUNT;

Upvotes: 0

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

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.

Upvotes: 1

Matthew Watson
Matthew Watson

Reputation: 14233

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;

Upvotes: 3

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36977

DBMS_SQL is your friend for such operations.

Upvotes: 1

Related Questions