When and how to use dynamic Query
1. execute DDL
2. we need to concatenate query about the table name and condition.
we have three method to execute dynamic query
1.execute immediate
2. ref cursor
3.dbms_sql
1.execute immediate
sql_stmt :=
'SELECT substrb(oel.user_item_description,1,240)'
|| ' FROM oe_order_lines oel , mtl_system_items msi'
|| ' WHERE oel.ship_from_org_id = msi.organization_id'
|| ' AND oel.inventory_item_id = msi.inventory_item_id'
|| ' AND oel.line_id = :p_interface_source_line_id'
|| ' AND msi.organization_id = :p_destination_org_id'
|| ' AND msi.allow_item_desc_update_flag = :v_chr_pflag ';
EXECUTE IMMEDIATE sql_stmt
INTO v_chr_user_item_desc
USING p_interface_source_line_id,
p_destination_org_id,
v_chr_pflag;
2. ref cursor
TYPE cur_order_type IS REF CURSOR;
cur_get_order_details cur_order_type;
TYPE rec_order_type IS RECORD (
header_id NUMBER,
line_id NUMBER);
TYPE tabtype_order IS TABLE OF rec_order_type
INDEX BY PLS_INTEGER;
tabtype_order_detail tabtype_order;
V_CHR_SQL := ''
BEGIN
OPEN cur_get_order_details FOR v_chr_sql
USING v_org_id;
FETCH cur_get_order_details
BULK COLLECT INTO tabtype_order_detail;
FOR i IN 1 .. tabtype_order_detail.COUNT
LOOP
END LOOP;
3.dbms_sql
FOR rec_cur_element_val_3 IN cur_element_val_3 (i_num_inv_id)
LOOP
FOR rec_cur_element_val_4 IN cur_element_val_4 (i_num_inv_id)
LOOP
BEGIN
v_chr_sql_query := 'SELECT coo_code FROM ';
v_chr_sql_query := v_chr_sql_query || v_chr_qa_table_name;
v_chr_sql_query :=
v_chr_sql_query || ' WHERE VALVE_BODY=:element3';
v_chr_sql_query :=
v_chr_sql_query
|| ' AND DESCRIPTIVE_ELEMENT_NAME=:element4';
v_chr_sql_query :=
v_chr_sql_query || ' AND ACTUATOR=:element5';
v_chr_sql_query :=
v_chr_sql_query
|| ' AND DESCRIPTIVE_ELEMENT_NAME_2=:element6';
v_chr_sql_query :=
v_chr_sql_query || ' AND OPERATING_UNIT=:operating_unit3';
fnd_file.put_line (fnd_file.LOG,
'Query built for fetching COO is:'
|| v_chr_sql_query
);
v_int_sql_query := 0;
v_rows_processed := 0;
v_int_sql_query := DBMS_SQL.open_cursor;
fnd_file.put_line (fnd_file.LOG,
'v_int_sql_query : ' || v_int_sql_query
);
DBMS_SQL.parse (v_int_sql_query,
v_chr_sql_query,
DBMS_SQL.native
);
DBMS_SQL.bind_variable (v_int_sql_query,
':element3',
rec_cur_element_val_3.element_value
);
DBMS_SQL.bind_variable (v_int_sql_query,
':element4',
rec_cur_element_val_3.element_name
);
DBMS_SQL.bind_variable (v_int_sql_query,
':element5',
rec_cur_element_val_4.element_value
);
DBMS_SQL.bind_variable (v_int_sql_query,
':element6',
rec_cur_element_val_4.element_name
);
DBMS_SQL.bind_variable (v_int_sql_query,
':operating_unit3',
v_chr_operating_unit
);
v_rows_processed := DBMS_SQL.EXECUTE (v_int_sql_query);
DBMS_SQL.define_column (v_int_sql_query, 1, v_chr_coo, 100);
LOOP
v_num_rows_processed :=
DBMS_SQL.fetch_rows (v_int_sql_query);
--Added on 5-DEC-2011
fnd_file.put_line (fnd_file.LOG,
'v_num_rows_processed: '
|| v_num_rows_processed
);
IF (v_num_rows_processed > 0)
THEN
DBMS_SQL.COLUMN_VALUE (v_int_sql_query, 1, v_chr_coo);
fnd_file.put_line (fnd_file.LOG, 'working fine');
EXIT;
ELSE
fnd_file.put_line
(fnd_file.LOG,
'No data found while trying to get the coo code for valve body and actuator combination'
);
RAISE NO_DATA_FOUND;
EXIT;
END IF;
END LOOP;
DBMS_SQL.close_cursor (v_int_sql_query);