Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Update statement Error (Oracle 9i)
Dynamic Update statement Error [message #654718] Thu, 11 August 2016 04:58 Go to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Hi,

During the update a value through dynamic statement.


The error is displayed : ORA-01007: variable not in select list

{ Run Time Value
Calc (85/131)*100000
lv_sql : Update dash.xxsc_pmis_master_entry Set Volume = ((85/131)*100000) Where preassign_id = 173
error : ORA-01007: variable not in select list
}



Kindly give suggestion for rectify the issue.

Regards,

C V S

Declare
   Cursor cal
   Is
      Select Name, project, vlog,
             (Select ms.volume
                From dash.xxsc_pmis_master_entry ms,
                     dash.xxsc_pmis_preassign pre
               Where ms.division_id = a.division_id
                 And ms.preassign_id = pre.preassign_id
                 And ms.period_id = :pn_period
                 And TO_CHAR (pre.order_by) = vlog) ments,
             dash.xxsc_pmis_process.check_val (vlog, 'N') val
        From (Select t.division_id, t.Name, t.project,
                     TRIM (REGEXP_SUBSTR (t.error,
                                          '[^,]+',
                                          1,
                                          Levels.COLUMN_VALUE
                                         )
                          ) As vlog
                From (Select division_id, preassign_id Name,
                             value_process project, value_process error
                        From dash.xxsc_pmis_preassign
                       Where preassign_id = :pn_assignid) t,
                     Table
                        (Cast
                            (Multiset
                                 (Select     Level
                                        From DUAL
                                  Connect By Level <=
                                                  Length
                                                     (REGEXP_REPLACE (t.error,
                                                                      '[^,]+'
                                                                     )
                                                     )
                                                + 1
                                 ) As Sys.odcinumberlist
                            )
                        ) Levels) a;
                        
                        
                         lv_sql          Varchar2 (2000);
      lv_retvalue     Varchar2 (100);
      lv_calc         Varchar2 (1000);
Begin
   For i In cal
   Loop
      If i.vlog = ';'
      Then
         Null;
      Else
         Select    lv_calc
                || DECODE (i.val,
                           1, NVL (TO_CHAR (i.ments), '0'),
                           0, DECODE (i.vlog,
                                      '%', '*100',
                                      'L', '100000',
                                      'C', '1000000',
                                      i.vlog
                                     )
                          )
           Into lv_calc
           From DUAL;
           dbms_output.put_line ('Calc ' || lv_calc);
      End If;
   End Loop;

   lv_sql :=
         'Update dash.xxsc_pmis_master_entry Set Volume = ('
      || Replace (Replace (lv_calc, 'D', ''), 'N', '')
      || ') Where preassign_id = '
      || :pn_assignid;

   dbms_output.put_line ('lv_sql : ' || lv_sql);
   Execute Immediate lv_sql
                Into lv_retvalue;
                
                Exception
                When Others Then
                 dbms_output.put_line ('error : ' || Sqlerrm);
End;


[mod-edit: contents of Query.txt inserted into messaged body by bb]
  • Attachment: Query.txt
    (Size: 3.11KB, Downloaded 1385 times)

[Updated on: Thu, 11 August 2016 21:02] by Moderator

Report message to a moderator

Re: Dynamic Update statement Error [message #654720 is a reply to message #654718] Thu, 11 August 2016 05:06 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
   Execute Immediate lv_sql
                Into lv_retvalue;
What are are you expecting to go into lv_retvalue ?
Re: Dynamic Update statement Error [message #654722 is a reply to message #654720] Thu, 11 August 2016 05:31 Go to previous message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Hi Mr. John,

Thanks for Reply,
Into lv_retvalue;
cause the issue, i removed it and executed.

Regards,

C V S
Previous Topic: Query help
Next Topic: Repeat data based on count column
Goto Forum:
  


Current Time: Thu Mar 28 15:32:02 CDT 2024