EBS : How to execute Dynamic SQL in PLSQL

 Dynamic SQL in PLSQL

In PL/SQL the SQL statement is created as a string, which is then executed using the EXECUTE IMMEDIATE clause

declare
v_techhards varchar2(2000);
begin

for r_techhards in (select * from user_tables) loop

v_techhards:= 'delete '||r_techhards.table_name;

dbms_output.put_line(v_techhards);

execute immediate v_techhards;

end loop;
end;
/ 

=========================================================================

#Oracle SQL#Oracle PLSQL#Oracle Apps#Oracle Database#SQL #Query #Dynamic SQL #execute immediate #Oracle dynamic DDL

EBS : Important Oracle Database related SQL Query

  Important Oracle Database related SQL Query

• Query to get the name of the instance

select INSTANCE_NAME from v$instance;

• Query to get the User name of theDatabase

select USER_ID from ALL_USERS Order by 1

=========================================================================

#Oracle SQL#Oracle Apps#Oracle Database#SQL #Query #v$instance #ALL_USERS

EBS : OPM (Process Manufacturing) Important Query in r12

Query to get Formula wise All Resource in OPM

SELECT   gor.resources, crm.resource_desc, crd.nominal_cost,

                  NVL (gor.resource_usage, 1) resource_usage,

                  NVL (gor.process_qty, 1) process_qty

             FROM fm_rout_dtl frd,gmd_operation_activities goa,

                  gmd_operation_resources gor,

                  cr_rsrc_mst crm,

                  cm_rsrc_dtl crd

            WHERE frd.oprn_id = goa.oprn_id

              AND routing_id IN (

                     SELECT gr.routing_id

                       FROM mtl_system_items_kfv ic,

                            fm_matl_dtl dtl,

                            gmd_recipes gr

                      WHERE dtl.formula_id = x_formula_id

                        AND line_no = 1

                        AND line_type = 1

                        AND ic.organization_id = p_inv_org_id

                        AND ic.inventory_item_id = dtl.inventory_item_id

                        AND gr.formula_id = dtl.formula_id

                        AND ic.item_type not like '%TEA%' --added 28nov14 TEA Routing Resource will not appeared

                        )

              AND gor.oprn_line_id = goa.oprn_line_id

              AND crm.resources = gor.resources

              AND crm.resources = crd.resources

              AND crd.cost_type_id = p_cost_type_id

              AND period_id = p_period_id

         ORDER BY 1;

=========================================================================

#Oracle Apps#EBS #OPM #Process manufacturing #Formula #Resources 

EBS : How to identify the SQL_ID of a statement in Oracle Apps R12

How to identify the SQL_ID of a statement in Oracle Apps R12

1) Run the below SQL Query.

SELECT /* ORACLE SQL */ * FROM dual;

2) Run below query to get above ran SQL ID.

SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text
FROM v$sql
WHERE sql_text like 'SELECT /* ORACLE SQL */%'

------Here is the Output---------------

SQL_ID SQL_TEXT
------------- ----------------------------------------
0xzpkrs5gktjs SELECT /* ORACLE SQL */ * FROM dual