1

I am creating a stored procedure in oracle to find the row count of all the tables. I have to run these procedure daily to find the daily data that is being loaded after ETL. I have created the below procedure.

create or replace Procedure Proc_RSE_TABLE_ROW_Count AS

TYPE c_table_list IS REF CURSOR ;
C_table_name c_table_list;

RSE_ROW_COUNT NUMBER;
SQL_11g  VARCHAR2(500);
SQL_CURSOR VARCHAR2(500);
V_TABLE_NAME VARCHAR2(30);

BEGIN
SQL_CURSOR := 'SELECT 
TRIM(table_name) table_name
FROM RSE_TABLE_COUNT ';

OPEN C_table_name FOR SQL_CURSOR;
LOOP
FETCH C_table_name INTO V_TABLE_NAME;
EXIT WHEN C_table_name%NOTFOUND;

SQL_11g := 'SELECT COUNT(1) FROM '||V_TABLE_NAME;
EXECUTE IMMEDIATE SQL_11g INTO RSE_ROW_COUNT;

INSERT into RSE_TABLE_COUNT (TABLE_NAME, ROW_COUNT, DATE_LAST_UPDATED)
values ('vtable_name', rse_row_count, sysdate)


/*UPDATE RSE_TABLE_COUNT SET ROW_COUNT=RSE_ROW_COUNT,DATE_LAST_UPDATED=sysdate
WHERE table_name=V_TABLE_NAME;
commit; */

END LOOP;
CLOSE C_table_name;

END;

but instead of which i am currently using i want to insert the daily data. With update the history is getting over ridden. And with insert i am getting error. I want to insert table_name, row_count, and sysdate in the table when i run the procedure without affecting previous data. My i am new to oracle syntax so please help..

Blossom
  • 55
  • 1
  • 3
  • 14
  • Could you be more specific about your requirement and what exactly is your error? – Jacob Jun 24 '14 at 06:22
  • error is as follows: /* Error(25,1): PL/SQL: SQL Statement ignored /* Error(26,28): PL/SQL: ORA-01756: quoted string not properly terminated /* Error(37): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:; With update i get what i want but the previous days count is deleted. – Blossom Jun 24 '14 at 06:28
  • Ah you mean to say, when you update, previous row is getting updated with current? – Jacob Jun 24 '14 at 06:33
  • Yes. I get todays count but i want yesterdays count to remain with yesterdays date. – Blossom Jun 24 '14 at 06:35
  • Then you shouldn't be updating that record, instead you should add a new row by inserting a record. – Jacob Jun 24 '14 at 06:36
  • In your insert statement what exactly is `vtable_name`, I guess you should be using `V_TABLE_NAME` correct? – Jacob Jun 24 '14 at 06:37
  • And terminate insert statement with a `semicolon ;`. – Jacob Jun 24 '14 at 06:46
  • yes i corrected that. Still the procedure has same errors. Something is wrong with the insert. I am fetching the the table name with the help of a cursor in V_TABLE_NAME – Blossom Jun 24 '14 at 06:49
  • `DATE_LAST_UPDATED` is not defined – haki Jun 24 '14 at 06:55
  • And you don't need a ref cursor here – haki Jun 24 '14 at 06:56

2 Answers2

2

See the following code snippet, quite similar to what you have and it insert rows correctly.

CREATE TABLE RSE_TABLE_COUNT
(
  TABLE_NAME         VARCHAR2(500 BYTE),
  ROW_COUNT          NUMBER,
  DATE_LAST_UPDATED  DATE
);

CREATE OR REPLACE PROCEDURE Proc_RSE_TABLE_ROW_Count
AS
   TYPE c_table_list IS REF CURSOR;

   C_table_name    c_table_list;

   RSE_ROW_COUNT   NUMBER;
   SQL_11g         VARCHAR2 (500);
   SQL_CURSOR      VARCHAR2 (500);
   V_TABLE_NAME    VARCHAR2 (30);
BEGIN
   SQL_CURSOR := 'SELECT 
object_name
FROM user_objects
WHERE object_type=''TABLE''';

   OPEN C_table_name FOR SQL_CURSOR;

   LOOP
      FETCH C_table_name INTO V_TABLE_NAME;

      EXIT WHEN C_table_name%NOTFOUND;

      SQL_11g := 'SELECT COUNT(1) FROM ' || V_TABLE_NAME;

      EXECUTE IMMEDIATE SQL_11g INTO RSE_ROW_COUNT;

      INSERT INTO RSE_TABLE_COUNT (TABLE_NAME, ROW_COUNT, DATE_LAST_UPDATED)
           VALUES (V_TABLE_NAME, rse_row_count, SYSDATE);         

   END LOOP;

   CLOSE C_table_name;
END;
Jacob
  • 14,463
  • 65
  • 207
  • 320
1

Why don't you analyze the schema (estimate_percent=100) and select num_rows from user_tables? Probaly allready there is a gather_schema_stats in your ETL. After that:

insert into rse_table_count
select table_name,num_rows,sysdate
from user_tables;
Rob van Laarhoven
  • 8,737
  • 2
  • 31
  • 49