Using %ROWTYPE in Procedure in Oracle

Question

I am trying to use %ROWTYPE in my code and trying to insert value into it using a cursor for loop as below :

CREATE or REPLACE PROCEDURE test_acr (
                  PROJECT_START_DATE IN DATE,USER_ID IN VARCHAR2) 
IS TYPE acr_new IS TABLE OF acr_projected_new%ROWTYPE 
        INDEX BY SIMPLE_INTEGER;  
acr_projected_neww acr_new;

CURSOR WEEKENDING_DATE IS  
      SELECT WEEKEND_DATE   
      FROM weekending_table 
      WHERE WEEKEND_DATE BETWEEN PROJECT_START_DATE AND sysdate;

BEGIN

FOR WEEKEND_DATE_REC in WEEKENDING_DATE LOOP 
INSERT INTO acr_projected_neww(WEEKEND_DATE,USERID,TIMESTAMP,ACR_PROJECTED,artificial_id)
       SELECT WEEKEND_DATE_REC.WEEKEND_DATE,USER_ID,sysdate,
                  (select sum(acr_h.activity_impact) 
       FROM ACR_HISTORY acr_h
       LEFT JOIN Activity act on act.activity_id = acr_h.activity_id
       LEFT JOIN Activity_Date_Duration act_d on act_d.activity_id = act.activity_id),1 from dual;

 END LOOP;
END test_acr;

When i try to run this i get below error:

Error(54,14): PL/SQL: ORA-00942: table or view does not exist

My Requirement is to create virtual table and insert the data into it using cursor for loop if not then any other means is appreciated.

Please help it will be greatly appreciated!

You don't insert into a local collection, you select into it with a single bulk-collect clause - not in a loop, either. (A naming convention for your types, local variables and formal parameters might be useful to make it clearer what things are referring to). Are project_start_date and user_id procedure arguments that you've for some reason removed from the code you posted? The empty parentheses are invalid so that seems likely. — Alex Poole
– Alex Poole, Commented Jul 24, 2017 at 11:05
Looks like table name is incorrect in your INSERT statement. — Himanshujaggi
– Himanshujaggi, Commented Jul 24, 2017 at 11:05
@Himanshujaggi - acr_projected_neww is collection, an instance of acr_new, which is what the OP said they are trying to insert into. An insert is just the wrong statement. (Having the collection name so similar to the original table name is really confusing though, and the OP doesn't seem to then so anything with the the collection anyway, so they may actually want a insert into the table - making the collection and %rowtype irrelevant...) — Alex Poole
– Alex Poole, Commented Jul 24, 2017 at 11:15
Hi Alex, YES, project_start_date and user_id procedure arguments — N.KH
– N.KH, Commented Jul 24, 2017 at 12:48
My requirement is to create virtual table which will store value in it using cursor for loop mentioned above — N.KH
– N.KH, Commented Jul 24, 2017 at 12:51

Himanshujaggi · Accepted Answer · 2017-07-24 11:06:05Z

1

Looks like table name is incorrect in your INSERT statement.

answered Jul 24, 2017 at 11:06

Himanshujaggi

3911 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kaushik Nayak · Accepted Answer · 2017-07-24 11:19:21Z

0

You need not use two queries. Instead, define your cursor such that it has all the columns of the records you want to store in the collection. Then use BULK COLLECT INTO instead of insert as shown. Define your collection as table of cursor%ROWTYPE.

CREATE OR REPLACE PROCEDURE test_acr
IS
  CURSOR WEEKENDING_DATE
  IS
    SELECT a.col1,a.col2,b.col1,b.col2 ,c.col1
    from table1 a , table2 b LEFT JOIN table3 c;       --Here include all the data from the required tables.

TYPE acr_new
IS
  TABLE OF WEEKENDING_DATE%ROWTYPE;
  acr_projected_neww acr_new;
BEGIN
    FETCH WEEKENDING_DATE BULK COLLECT INTO acr_projected_neww;
END test_acr;

answered Jul 24, 2017 at 11:19

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

3 Comments

N.KH Over a year ago

Hi Kaushik, Thanks for response.my requirement is i need virtual table and i need to insert data using my cursor for loop.

Kaushik Nayak Over a year ago

Not clear when you say a virtual table. Do you want to insert into another temporary database table or a collection?

N.KH Over a year ago

Yes i need to insert into temporary table or i am good with collections as well.My acr_pojected_new has multiple columns in it.

fg78nc · Accepted Answer · 2017-07-25 01:57:52Z

0

If you need to manipulate your data (access each row - then see script below, this is sequential access (inserts) into nested table (PL/SQL collection)

CREATE or REPLACE PROCEDURE test_acr (PROJECT_START_DATE IN DATE,USER_ID IN VARCHAR2) 
 IS  
 TYPE acr_new 
 IS TABLE OF acr_projected_new%ROWTYPE; // nested table, notice absence of INDEX by clause
    acr_projected_neww acr_new :=  acr_projected_neww(); // instantiation, constructor call

CURSOR WEEKENDING_DATE IS  
      SELECT WEEKEND_DATE   
      FROM weekending_table 
      WHERE WEEKEND_DATE BETWEEN PROJECT_START_DATE AND sysdate;

BEGIN

FOR WEEKEND_DATE_REC in WEEKENDING_DATE 
   LOOP
    acr_new.extend;  // make room for the next element in collection
    acr_new(acr_new.last) := WEEKEND_DATE_REC; // Adding seq. to the end of collection 
    ... 

 END LOOP;
END test_acr;

However if you want to BULK INSERT (there is no requirement to get access to each row) see script below

 CREATE or REPLACE PROCEDURE test_acr (PROJECT_START_DATE IN DATE,USER_ID IN VARCHAR2) 
  IS  
  TYPE acr_new IS TABLE OF acr_projected_new%ROWTYPE; // no INDEX BY clause
  acr_projected_neww acr_new = acr_new(); // Notice constructor call

CURSOR WEEKENDING_DATE IS  
      SELECT WEEKEND_DATE   
      FROM weekending_table 
      WHERE WEEKEND_DATE BETWEEN PROJECT_START_DATE AND sysdate;     

    BEGIN

    FETCH WEEKENDING_DATE BULK COLLECT INTO acr_projected_neww;  
    ...

     END LOOP;
    END test_acr;

answered Jul 25, 2017 at 1:57

fg78nc

5,2624 gold badges24 silver badges38 bronze badges

2 Comments

N.KH Over a year ago

My acr_projected_neww structure is like WEEKEND_DATE DATE, USERID VARCHAR2(20 BYTE), TIMESTAMP TIMESTAMP(6), ACR_PROJECTED NUMBER(7,3), ARTIFICIAL_ID NUMBER I want to store value WEEKEND_DATE(which is coming from cursor), USERID(from procedure parameter), TIMESTAMP(it is sysdate), ACR_PROJECTED(it contains sum) and ARTIFICIAL_ID is 1. I want all this value to be inserted into acr_projected_neww

fg78nc Over a year ago

Sorry, I did not get you.

N.KH · Accepted Answer · 2017-07-25 07:21:26Z

I have used temporary table outside my procedure:

  CREATE GLOBAL TEMPORARY TABLE "MY_TEMP" 
   (    "WEEKEND_DATE" DATE, 
    "USERID" VARCHAR2(255 BYTE), 
    "TIMESTAMP" TIMESTAMP (6), 
    "ACR_PROJECTED" NUMBER, 
    "ARTIFICIAL_ID" NUMBER
   ) ON COMMIT PRESERVE ROWS ;

i have just used the above temporary table inside my Procedure

create or replace PROCEDURE GET_ACR_TEST(
  PROJECT_START_DATE IN DATE ,
  USER_ID IN VARCHAR2,

)  AS 
CURSOR WEEKENDING_DATE IS
  SELECT WEEKEND_DATE, DURATION 
  FROM weekending_table where WEEKEND_DATE between PROJECT_START_DATE and sysdate;

Begin

FOR WEEKEND_DATE_REC in WEEKENDING_DATE 
    LOOP
    insert into  MY_TEMP (WEEKEND_DATE,USERID,TIMESTAMP,ACR_PROJECTED,artificial_id)
      select WEEKEND_DATE_REC.WEEKEND_DATE,USER_ID,sysdate,
                       (select sum(acr_h.activity_impact) 
                       from ACR_HISTORY acr_h
                       LEFT JOIN Activity act on act.activity_id = acr_h.activity_id
                       LEFT JOIN Activity_Date_Duration act_d on act_d.activity_id = act.activity_id),1
     from dual;         
    End Loop;
END GET_ACR_TEST;

The above method is working.

Thank you all for your comments!

Collectives™ on Stack Overflow

Using %ROWTYPE in Procedure in Oracle

4 Answers 4

Comments

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related