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 |
|
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 1460 times)
[Updated on: Thu, 11 August 2016 21:02] by Moderator Report message to a moderator
|
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:22:00 CDT 2024
|