Can't insert recors fetched by cursor in the table

10-11  Source: Network gathering  Views:0 

Advertisement
HI
i am fetching records from my table total_budget.All the fetched records are displayed in a tabular data block(data block name is forecast_result).Now i want to insert these record fetched by cursor in my table forecast_resul which is empty.how can i do it.I am sending the code if any one can help............
When i execute this code it give me error unable to insert(FRM-40508) .I have carefully checked table columns names and their data types.All r fine but i dont know y it is not working.Here is the code
DECLARE
cursor c1 IS
SELECT ministry_id,fiscal_year ,t_amount
FROM total_budget
WHERE ministry_id=:global.var11 AND (fiscal_year BETWEEN :syear and :eyear);
BEGIN
T_XSUMX := 0;
     OPEN C1;
     X:=1;
     SUMX := 0; SUMY := 0; SUMXY:=0; SUMX2 := 0;
     go_block('forecast_result');
     first_record;
     LOOP
          FETCH C1 INTO Y1,Y2,Y;
          EXIT WHEN C1%NOTFOUND;
          SUMY := SUMY +Y;
          XY := X*Y;
          X2 := X*X;
          SUMX := SUMX + X;
SUMXY := SUMXY + XY;
          SUMX2 := SUMX2 + X2;
               :forecast_result.ministry_id:=Y1;
               :forecast_result.fiscal_year:=Y2;
          :forecast_result.t_amount:=Y;
     NEXT_RECORD;
          X := X + 1;
     END LOOP;
     close C1;
z:=X-2;
     X_BAR := z;
     X := z+1;
     T_XSUMX := X * SUMX;
     T_SUMY := SUMY * SUMX;
     T_SUMX := SUMX * SUMX;
     T_SUMXY := SUMXY * X;
     T_SUMX2 := SUMX2 * X;
     R1 := T_SUMY - T_SUMXY;
     R2 := T_SUMX - T_SUMX2;
     B := ROUND(R1/R2,1);
     A := ROUND((T_SUMXY -(T_SUMX2*B))/T_XSUMX,1);
     Y_BAR := A+(B*X_BAR);
:forecast_result.ministry_id:=Y1;
:forecast_result.fiscal_year:=Y2;
:forecast_result.t_amount:=Y_BAR;
next_record;
     insert into forecast_result
     values(:forecast_result.ministry_id,:forecast_result.fiscal_year,:forecast_result.t_amount);
     commit;
END;
Can anyone tell me wat i am doing wrong
looking for instant reply
nida
Hi alma,
Plz u dnt wrt babytalk rr SMS-language -> mi! Becoz mi don lejk dat.
I suppose that your data block "forecast_result" are based on the table of the same name. Otherwise this may be the cause of some of your trouble.
Your code would properly work better like this:
DECLARE
  X NUMBER(38);
  SUMX NUMBER(38);
  SUMY NUMBER(38);
  Y NUMBER(38) ;
  Y1 varchar2(10);
  Y2 varchar2(10);
  X2 NUMBER(38);
  XY NUMBER(38);
  SUMXY NUMBER(38);
  SUMX2 NUMBER(38);
  X_BAR NUMBER(38);
  Y_BAR NUMBER(38);
  T_SUMY NUMBER(38);
  T_SUMX NUMBER(38);
  T_SUMXY NUMBER(38);
  T_SUMX2 NUMBER(38);
  T_XSUMX NUMBER(38);
  R1 NUMBER(38);
  R2 NUMBER(38);
  A NUMBER(38);
  z number(38);
  B NUMBER(38);
  final number(38);
  -- Just curious, where does :syear and :eyear come from ??
  cursor c1 IS
   SELECT ministry_id, fiscal_year, t_amount
    FROM total_budget
   WHERE ministry_id=:global.var11
     AND (fiscal_year BETWEEN :syear and :eyear);
BEGIN
  T_XSUMX := 0;
  X:=1;
  SUMX := 0;
  SUMY := 0;
  SUMXY:=0;
  SUMX2 := 0;
  go_block('forecast_result');
  -- If you're making this dynamically, then clear the block to start with.
  clear_block(NO_COMMIT);
  first_record;
  OPEN C1;
  LOOP
    FETCH C1 INTO Y1, Y2, Y;
    EXIT WHEN C1%NOTFOUND;
    SUMY := SUMY + Y;
    XY := X*Y;
    X2 := X*X;
    SUMX := SUMX + X;
    SUMXY := SUMXY + XY;
    SUMX2 := SUMX2 + X2;
    :forecast_result.ministry_id := Y1;
    :forecast_result.fiscal_year := Y2;
    :forecast_result.t_amount := Y;
    -- NO, this will not work. You have to CREATE the record. You only get the
    -- first record for "free". Next_Record moves to allready existing records.
    -- NEXT_RECORD;
    Create_Record;
    X := X + 1;
  END LOOP;
  close C1;
  z := X - 2;
  X_BAR := z;
  X := z + 1;
  T_XSUMX := X * SUMX;
  T_SUMY := SUMY * SUMX;
  T_SUMX := SUMX * SUMX;
  T_SUMXY := SUMXY * X;
  T_SUMX2 := SUMX2 * X;
  R1 := T_SUMY - T_SUMXY;
  R2 := T_SUMX - T_SUMX2;
  B := ROUND(R1/R2,1);
  A := ROUND((T_SUMXY -(T_SUMX2*B))/T_XSUMX,1);
  Y_BAR := A+(B*X_BAR);
  -- What are you doing here ?? This is weird. The population of the records are done in
  -- the loop ! You don't have to add anything here.
  -- :forecast_result.ministry_id:=Y1;
  -- :forecast_result.fiscal_year:=Y2;
  -- :forecast_result.t_amount:=Y_BAR;
  -- Since your data block (hopefully) are based on the table of the same name, you DON'T
  -- need to do this. You just have to press the commit button (or F10 or the floppy disk
  -- icon or what-ever).
  -- insert into forecast_result
  -- values(:forecast_result.ministry_id,:forecast_result.fiscal_year,:forecast_result.t_amount);
  -- commit;
END;
Related articles
  • Can't insert recors fetched by cursor in the table 10-11

    HI i am fetching records from my table total_budget.All the fetched records are displayed in a tabular data block(data block name is forecast_result).Now i want to insert these record fetched by cursor in my table forecast_resul which is empty.how ca

  • Can we create cursor using the table created dynamically 10-11

    Dear all, Can we create the cursor using the table which is created dynamically using "Execute immediate" within the same procedure, or is there any way to call the table created dynamically .. please Do Help me in this thanks alot Edited by: kh

  • While Creating New Insert Form Existing Data Display from the Table 11-30

    Hi I am New To Sun Java Studio Creator and New to Java Also While Creating New Insert Form Existing Data Display from the Table while i am Run the Form. Can any one help me to Solve this oneDear Giri, As per your Advise, Literally I have Search the P

  • *Urgent*How to insert data from MS SQL to the table that create at the adobe form? 10-11

    Hi, I'm using Adobe life cycle designer 8 to do my interactive form. I would like to ask how to insert data from MS SQL to the table that i have created in my adobe interactive form? I really need the information ASAP as i need to hand in my project

  • How to fetch what are all the tables used in this TR no and Package name of 11-30

    Hi Friends, I have input of Transport Request no (E070-TRKORR). How to fetch what are all the tables used in this TR no and Package name of this Table.HI, FYI SELECT E071OBJECT_NAME, E070MDEVCLASS FROM E071, E070M WHERE TRKORR = YOU REQUEST NO.Read o

  • Inserting values from a cursor to a table 10-11

    Hi, I need to insert the values from a cursor into a table,this i the part of code which trieds to do it...i get error stating "select stmt missing"...pls help out... OPEN  p_cursor for V_SQLSTATEMENT;         ---for i in p_cursor         LOOP  

  • Can't insert schema-based xmltype into binary xmltype table 11-30

    I'm having issues trying to use binary storage along with the ALLOW ANYSCHEMA clause. I can't use the XMLSchema-instance mechanism for creating my schema-based XMLType instances, so I'm using CreateSchemaBasedXml. When I try to insert the XMLType int

  • How can I inserting file's line into a oracle table in physycal order 11-30

    How can I insert the file's line into a oracle table in the same physycal order that they exists in the file ? I am using "SQL to FILE" km to performing file load into Oracle table, but the order which the records are insered into the oracle tab

  • Dreamweaver: can i insert a background image in a layout table? 11-30

    i'm using a layout table and i can't figure out how to put a non-repeating image into the background of my layout table. is this possible? i need to center it too.Before anything else, I would say DON'T USE LAYOUT MODE. In my opinion, there are three

  • ORA-01776: I can't insert on a view comprised by two tables 10-11

    Good morning: I'm trying to do an insertion on a view comprised by the join of two tables (departments and employees) and it failed. The view is this one: CREATE OR REPLACE FORCE VIEW "EMPDPTO" ("EMPLOYEE_ID", "LAST_NAME", &q

  • How to fetch last record from the table to display in screen painter? 11-30

    Hi Gurus,               I have a requirement where i have to fetch the last record value in the table. I have a screen where the TEXT name is REQUEST and I/P field name is REQ and SAVE push button. In this I/P field automatically 1 should display whe

  • HR API User Hooks - Can I use to change a value in the table the API for? 11-30

    I am trying to initialize a descriptive flexfield on per_all_people_f. I originally tried doing the initialization using a "before row insert" database trigger directly on the table per_all_people_f. Seems to work great in the People form (PERWS

  • How can I insert, modify and delete entries on my table control? 11-30

    Hi, I already have build a table control. Now I want to make manipulations on it. Therefore I have created 3 buttons, insert, modify and delete. But how can I get actions on table control. This is my code for the internal table: BEGIN OF its OCCURS 0

  • How can I Insert a Text field in a DATABASE TABLE??? 10-11

    Hello at all, i have a database Table ZTEST with 2 columns. MANDT     TYPE     CLNT TEXT         TYPE     char     (255) In my internal table tab, i have a text content. I want insert this content in my database table ZTEST. DATA: tab TYPE TABLE OF Z

  • How to insert a sql query statement in the table 10-11

    I have a stored procedure which has different insert,update statements on different tables. I am writing a trigger on these tables such that when the record is updated or inserted in these tables a trigger should fire and should write the old value,n

  • In alv , how can i  display message like 'NO records in the table'in spool 11-30

    When there are no any records , i have to display a message in spool like 'No records exist'. how can i do this?/ Thanks, Suresh.hi check this.. if the final internal table is empty then you will show like this.. if not it_final[] is initial. call fu

  • Fetch next cursor/ invalid database interruption 11-30

    Hi Experts, I would appreciate your help for the following problem: I am trying to develope something like that: OPEN CURSOR ... WITH HOLD loop at ...          FETCH ... PACKAGE SIZE ... into lt_names          insert table ZZ_XXXX ... from lt_names  

  • Can I union several cursors for the same secondary db? 10-11

    I want to search several keys in one secondary database. select * from adb where a=6 or a=9 or .... certainly, this can be done by running several queries, but it is definitely slow. wondered if it is possible to union these several cursors together,

  • How can I insert furigana in Japanese script? 10-11

    How can I insert furigana (small phonetic characters above the line) in Japanese script? Thanks!Note that, as described in a forum thread from August, you can use the Japanese CC version with English-language menus.  For ID versions CS5 through CS6,

  • How can I make that the cursor of a table ... 10-11

    Hello, how can I make that the cursor of a table (on the frontpanel) go to the starting position? I have placed a table that give back the number of the row if I doubleclick a row on the frontpanel, but when I restart the vi the cursor of the table i