Home » SQL & PL/SQL » SQL & PL/SQL » cursors (windows)
cursors [message #661831] Tue, 04 April 2017 00:03 Go to next message
jay12
Messages: 2
Registered: April 2017
Junior Member
CURSOR lcur_shipment_noorder(p_archive_trans_order_shipment NUMBER) IS
      SELECT archive_obj_id_type(shipment_id),
             archive_obj_id_type(to_contact_info_id),
             archive_obj_id_type(from_contact_info_id),
             archive_obj_id_type(NULL)
        FROM shipment sh, stg_archive.yfs_ship_tmp yfs
       WHERE trim(sh.orig_shipment_id)=trim(yfs.shipment_no)
       AND sh.shipment_id NOT IN (SELECT shipment_id
                                   FROM shipment_line
                                  WHERE order_line_id IN (SELECT id
                                                            FROM order_line
                                                         )
                                )
         AND shipment_status_id IN (SHIPMENT_DELIVERED, SHIPMENT_CANCELLED);

Is it possible to fetch records from multiple tables using cursors? Please someone clarify my doubt.
Re: cursors [message #661832 is a reply to message #661831] Tue, 04 April 2017 00:24 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes it is possible.

Re: cursors [message #661835 is a reply to message #661832] Tue, 04 April 2017 00:54 Go to previous messageGo to next message
Littlefoot
Messages: 21439
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's what you are doing right now; FROM clause already contains two tables (shipment, yfs_ship_tmp).
Re: cursors [message #662040 is a reply to message #661835] Fri, 14 April 2017 15:49 Go to previous message
Bill B
Messages: 1802
Registered: December 2004
Senior Member
Also as long as the columns are the same data type you can do something like

for pnt in (select col1,col2,col3
            from my_table1
            union all
            select cola,colb,colc
            from my_other_table) loop
Previous Topic: testing rollback
Next Topic: Partition tablespace name does not change after moving subpartition
Goto Forum:
  


Current Time: Mon Oct 22 04:35:24 CDT 2018