Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Query into Cursor (Oracle 12c, Unix)
Dynamic Query into Cursor [message #680653] |
Fri, 29 May 2020 16:09 |
|
Rayam69
Messages: 43 Registered: May 2012
|
Member |
|
|
create table clobtab(clobcol clob);
create table orders(order_date date, open_orders number, hold_orders number);
insert into orders(order_date, open_orders, hold_orders) values('28-may-2020', 10, 5);
insert into clobtab(clobcol) values('select order_date, open_orders, hold_orders from orders');
commit;
set serveroutput on;
DECLARE
TYPE cur_typ IS REF CURSOR;
v_inv_cursor cur_typ;
v_inv_query VARCHAR2(2000);
l_order_date date;
l_open_orders number := 0;
l_hold_orders number := 0;
BEGIN
SELECT dbms_lob.substr(clobcol, 2000, 1)
INTO v_inv_query
FROM clobtab;
dbms_output.put_line(v_inv_query);
open v_inv_cursor for v_inv_query;
loop fetch v_inv_query into l_order_date, l_open_orders, l_hold_orders;
DBMS_OUTPUT.PUT_LINE(l_order_date||' -- '||l_open_orders||' -- '||l_hold_orders);
end loop;
END;
when i run the ananymous block, i get the error as follows.
ORA-06550: line 16, column 18:
PLS-00456: item 'V_INV_QUERY' is not a cursor
Please suggest how to fix this.
regards,
Balaji.
[Edit MC: add code tags]
[Updated on: Sat, 30 May 2020 00:23] by Moderator Report message to a moderator
|
|
|
Re: Dynamic Query into Cursor [message #680654 is a reply to message #680653] |
Sat, 30 May 2020 00:35 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Please read How to use [code] tags and make your code easier to read.
'28-may-2020 is not a DATE but just a STRING, you can't directly insert into a DATE column.
SQL> insert into tab_orders(order_date, open_orders, hold_orders) values('28-may-2020', 10, 5);
insert into tab_orders(order_date, open_orders, hold_orders) values('28-may-2020', 10, 5)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
You MUST used TO_DATE for this:
SQL> insert into tab_orders(order_date, open_orders, hold_orders)
2 values(to_date('28-may-2020','dd-mon-yyyy','nls_date_language=english'), 10, 5);
1 row created.
SQL> DECLARE
2 TYPE cur_typ IS REF CURSOR;
3 v_inv_cursor cur_typ;
4 v_inv_query VARCHAR2(2000);
5 l_order_date date;
6 l_open_orders number := 0;
7 l_hold_orders number := 0;
8
9 BEGIN
10
11 SELECT dbms_lob.substr(clobcol, 2000, 1)
12 INTO v_inv_query
13 FROM clobtab;
14 dbms_output.put_line(v_inv_query);
15 open v_inv_cursor for v_inv_query;
16 loop fetch v_inv_query into l_order_date, l_open_orders, l_hold_orders;
17 DBMS_OUTPUT.PUT_LINE(l_order_date||' -- '||l_open_orders||' -- '||l_hold_orders);
18 end loop;
19 END;
20 /
loop fetch v_inv_query into l_order_date, l_open_orders, l_hold_orders;
*
ERROR at line 16:
ORA-06550: line 16, column 18:
PLS-00456: item 'V_INV_QUERY' is not a cursor
ORA-06550: line 16, column 12:
PL/SQL: SQL Statement ignored
"v_inv_query" is indeed not a cursor, it is a VARCHAR2(2000).
Use the correct variable.
Note this is not the only error in your code: you have no condition to end the loop, you don't close the cursor you opened.
I advise you use a cursor loop.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:26:22 CDT 2024
|