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 Go to next message
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 Go to previous message
Michel Cadot
Messages: 67367
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.

Previous Topic: oracle -rearrange columns values based on value type
Next Topic: Username length limitation in Oracle
Goto Forum:
  


Current Time: Wed Sep 23 02:24:01 CDT 2020