procedure in Oracle needed urgent

sandip3040 · **Joined:** Sat Mar 13, 2010 4:37 pm **Posts:** 3

Greetings to all of you. I need to write a procedure in Oracle 9i urgently. My requirement is - I have a table namely db.
Structure of db is (db_id(number) primary key,db_dt date,close_bal_cr number(10.2),close_bal_dr number(10,2),acc_cd number (5));
Now acc_cd is actually 3 digits from the beginning, for example if data in acc_cd is 10101 ,real acc_cd is 101,the end two number is branch code and varies from 01 to 09. Sometimes every acc_cd need not to be in any branch also
Now I have to write a procedure which takes two date ie
p_proc(starting_dt date,end_date date)
this procedure takes data from db and creates a temporary table tt
which contains db_dt, acc_cd , sum of close_bal_dr for all branches for every code every day ,sum of close_bal_cr for all branches for every acc_cd every day ) kindly let me know this asap.

usman · **Joined:** Fri Feb 13, 2009 11:37 am **Posts:** 118

Hi Sandip,
Welcome to the forum.
Please see below your request

Code:

CREATE TABLE db
(
   db_id          NUMBER PRIMARY KEY,
   db_dt          DATE,
   close_bal_cr   NUMBER (10, 2),
   close_bal_dr   NUMBER (10, 2),
   acc_cd         NUMBER (5)
);

Code:

CREATE OR REPLACE PROCEDURE p_proc (starting_dt DATE, end_date DATE)
AS
   sql_stm1   VARCHAR2 (4000);
   sql_stm2   VARCHAR2 (4000);
BEGIN
   sql_stm1 := 'Drop table tt';
   sql_stmt2 :=
      'CREATE TABLE tt
        AS
         SELECT   db_dt,
              acc_cd,
              SUM (close_bal_cr) close_bal_cr,
              SUM (close_bal_dr) close_bal_dr
       FROM   db
       where db_dt between starting_dt and end_date
     GROUP BY   db_dt, acc_cd';

   EXECUTE IMMEDIATE sql_stmt1;

   EXECUTE IMMEDIATE sql_stmt2;
END;

I havent tested the procedure with the data.. please try and let me know how it works?

usman · **Joined:** Fri Feb 13, 2009 11:37 am **Posts:** 118

It will be better if you create a view rather than a table...
why you are going for table?

sandip3040 · **Joined:** Sat Mar 13, 2010 4:37 pm **Posts:** 3

usman wrote:

It will be better if you create a view rather than a table...
why you are going for table?

I have done in similar way before but sometimes for a particular day,acc_cd may not be there in all branches as said before, in those case ,sum is neglecting the balance of acc_cd in all the branches for those days (it is same as the previous day balance)where it is not there,how to deal with that?

usman · **Joined:** Fri Feb 13, 2009 11:37 am **Posts:** 118

try this query

Code:

CREATE OR REPLACE VIEW 
your_view
AS
SELECT
db_dt,
new_acc_code acc_cd,
SUM (close_bal_cr) close_bal_cr,
SUM (close_bal_dr) close_bal_dr
FROM (
     SELECT   db_dt,
              acc_cd,
              SUBSTR(acc_cd,1,3) new_acc_code,
               close_bal_cr,
               close_bal_dr
       FROM   db)

   GROUP BY   db_dt, acc_cd;

usman · **Joined:** Fri Feb 13, 2009 11:37 am **Posts:** 118

can you give me sample data?

sandip3040 · **Joined:** Sat Mar 13, 2010 4:37 pm **Posts:** 3

Sample data, For db_dt = '01/01/2010', acc_cd-10101, close_bal_cr = 120,close_bal_dr = 100, acc_cd = 10102,close_bal_cr = 130,close_bal_dr= 50,for db_dt = '02/01/2010',acc_cd = 10102,close_bal_cr = 100
Then Sample out put of the procedure will
be
db_date acc_cd close_bal_dr close_bal_cr
01/01/2010 101 150 250
02/01/2010 101 300 350

Hope now my requirement is clear to you

usman · **Joined:** Fri Feb 13, 2009 11:37 am **Posts:** 118

you need this query to be implemented

Code:

SELECT
db_dt,
new_acc_code acc_cd,
SUM (close_bal_cr) close_bal_cr,
SUM (close_bal_dr) close_bal_dr
FROM (
     SELECT   db_dt,
              acc_cd,
              SUBSTR(acc_cd,1,3) new_acc_code,
               close_bal_cr,
               close_bal_dr
       FROM   db)
   GROUP BY   db_dt, new_acc_code;

usman · **Joined:** Fri Feb 13, 2009 11:37 am **Posts:** 118

Your procedure

Code:

CREATE OR REPLACE PROCEDURE p_proc (starting_dt DATE, end_date DATE)
   AUTHID CURRENT_USER
AS
   sql_stmt1      VARCHAR2 (4000);
   sql_stmt2      VARCHAR2 (4000);
   table_exists   NUMBER;
BEGIN
   sql_stmt1 := ' Drop table TT';
   sql_stmt2 :=
      'CREATE TABLE tt
        AS
        SELECT
        db_dt,
        new_acc_code acc_cd,
        SUM (close_bal_cr) close_bal_cr,
        SUM (close_bal_dr) close_bal_dr
        FROM (
             SELECT   db_dt,
                      acc_cd,
                      SUBSTR(acc_cd,1,3) new_acc_code,
                       close_bal_cr,
                       close_bal_dr
               FROM   db)
   -- where db_dt between starting_dt and end_date
   GROUP BY   db_dt, new_acc_code';

   SELECT   COUNT ( * )
     INTO   table_exists
     FROM   user_objects
    WHERE   object_name = 'TT';

   IF table_exists <> 0
   THEN
      EXECUTE IMMEDIATE sql_stmt1;
   END IF;

   EXECUTE IMMEDIATE sql_stmt2;
END;

Now execute

Code:

exec p_proc(sysdate,sysdate);

Code:

select * from tt;
DB_DT     ACC CLOSE_BAL_CR CLOSE_BAL_DR
--------- --- ------------ ------------
01-JAN-10 101          250          150
02-JAN-10 101          300          350

Hope this is what you were looking for.

procedure in Oracle needed urgent

Who is online