Dynamic query, dynamically generate cursor or dynamically Execute SQL DML operations

Define PROCEDURE & nbsp; P1 & nbsp; (P_Asset_TypeINvarchar2, P_Asset_SubTypeINvarchar2, P_OrderIdsINvarchar2 ). You need to dynamically generate cursor or dynamically Execute SQL DML operations based on input parameters. 1. Simple query SELECT & nbsp; ABCfromTabl defines PROCEDURE P1 (P_Asset_Type IN varchar2, P_Asset_SubType IN varchar2, P_OrderIds IN varchar2 ).

You need to dynamically generate cursor or dynamically Execute SQL DML operations based on input parameters.

1. Simple Query

Select abc from TableA where ORDER_ID IN (P_OrderIds );

2. dynamically generate cursor

Define cursors and variables:

Type v_cursor is ref cursor;
C1 v_cursor; v_command varchar2 (2000 );

V_command: = ''select ABC from TableA where ORDER_ID IN (''| P_OrderIds | '')'';

OPEN C1 for v_command;

3. Dynamic DML operations

V_command2: = ''UPDATE TableA
SET ASSET_TYPE_CD = ''''' | P_Asset_Type | '''''',''
| ''Asset _ SUBTYPE_CD = ''''' | P_Asset_SubType | ''''''''
| ''Where ORDER_ID IN (''| P_OrderIds | '')'';

Execute immediate v_command2; (dynamic SQL)