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);