Zafar

Are you eager to delve deeper into the world of SQL and elevate your database management skills to new heights? Let's explore the realm of Advanced SQL together, specifically tailored for Oracle databases. 💡💻 SQL, or Structured Query Language, forms the backbone of data management, enabling users to retrieve, manipulate, and analyze data with precision and efficiency. While mastering the basics of SQL is essential, embracing advanced SQL techniques opens up a world of possibilities for extracting insights and driving actionable intelligence from your data. 📊🔍 So, what exactly does Advanced SQL entail, particularly within the Oracle ecosystem? Let's dive into some key concepts and functionalities: 1. **Analytic Functions**: These powerful functions allow for sophisticated data analysis within SQL queries. From calculating moving averages to ranking data sets based on specified criteria, analytic functions provide invaluable insights into your data's patterns and trends. ...

Many times we have a requirement to send a common email to all the suppliers who are registered in Oracle EBS . We usually tend to take the supplier email from the supplier sites, though the email address of the supplier is available at different levels like Parties and/or contact points level in the TCA table. The below function consolidates the email defined to a specific supplier at various levels, eliminates the repetitive email addresses and concatenates them as one string and returns it.   So that it can be used in the sql for reports or any concurrent programs which are intended to send Remittance advices/common letters to Suppliers using XML Bursting. The below example refers to Oracle EBS R12.1.3 CREATE OR REPLACE FUNCTION xx_get_vendor_email(p_vendor_id in ap_suppliers.vendor_id%type)    RETURN VARCHAR2 IS    l_supplier_emails VARCHAR2(2000); BEGIN    l_supplier_emails := NULL;    FOR j IN (SELECT DISTINCT a.email_address ...

As an Oracle developer, many times we want to quickly check the code of a given function/view/package or a package body to see the current code in the database may be as an output of a given SQL. Take example of an object AP_INVOICES_PKG where we want to check the output  SELECT     'select dbms_metadata.get_ddl('''        || object_type        || ''','''        || object_name        || ''') from dual;'   FROM all_objects   WHERE object_name = 'AP_INVOICES_PKG'        AND object_type NOT IN ( 'PACKAGE BODY' ) ; The above SQL will give an output as below select dbms_metadata.get_ddl('PACKAGE','AP_INVOICES_PKG') from dual; And when we run this SQL , we will get an output which is nothing but the code for package spec and package body for ap_invoices_pkg.  

If we want to build a Custom Web ADI using custom table or standard table.  Or we want to define a custom dff or custom kff on a custom form then just creating a table may not be enough.  We also need to register this table so that the entry for this table exists in fnd_tables.  And the entries for its columns exist in fnd_columns table.  By using the below procedure we can define a concurrent program and run it by passing the input parameters of the concurrent program as table name.  When this concurrent program completes normal it would have registered the table and all its columns by using a standard API ad_dd.  This program can be rerun any number of times, especially if there are new columns are created or modified. This program will delete the entries and register it again.  CREATE OR REPLACE PROCEDURE xxregtc (errbuf       OUT VARCHAR2,                         ...

UPDATE fnd_concurrent_requests    SET phase_code = 'C', status_code = 'X'  WHERE     status_code IN ('Q', 'I')        AND requested_start_date > SYSDATE        AND hold_flag = 'N'        AND (phase_code <> 'C' OR status_code <> 'X') /

CREATE OR REPLACE FORCE VIEW apps.XX_timesheet AS      SELECT a."OBJECTTYPE",             a."USER_DEFINE_NAME",             a."SHORT_NAME_OR_STATUS",             a."DESCRIPTION",             a."APPLICATION_OBJECT_ID",             a."OWNER",             (SELECT user_name                FROM fnd_user fu               WHERE fu.user_id = a.created_by)                created_by,             a."CREATION_DATE"        FROM (SELECT 'Concurrent Programs' objecttype,                     fct.user_concurrent_program_name user_define_name,             ...

CREATE OR REPLACE FUNCTION f_policy_orders (schema   IN VARCHAR2,                                             tab      IN VARCHAR2)    RETURN VARCHAR2 AS    v_resp_id    NUMBER := 0;    out_string   VARCHAR2 (400) DEFAULT '1=2 '; BEGIN    BEGIN       SELECT fnd_global.resp_id INTO v_resp_id FROM DUAL;    EXCEPTION       WHEN NO_DATA_FOUND       THEN          v_resp_id := 0;    END;    IF v_resp_id = 62130    THEN       out_string := ' party_id = 1041 ';       else       out_string := ' 1=1';    END IF; --out_string := ' party_id = 1041 ';    RETURN out_string; END; / begin dbms_rls.add_policy('AR','HZ_CUST...

To set the buffer size.. set serveroutput on size 1000000 To change the password of oracle apps user.. DECLARE v_success BOOLEAN; BEGIN v_success := apps.fnd_user_pkg.changepassword('OPERATIONS','welcome123'); COMMIT; END; BEGIN if apps.fnd_user_pkg.changepassword('OPERATIONS','welcome321') then  dbms_output.put_line('Password changed'); else  dbms_output.put_line('Unable to change password '); end if; COMMIT; END; To create a tar file, give the following command: tar -cvf filename.tar directory-to-tar/* To see what files are in a tar archive, give the following command: If the file is a .tar.Z, then you use the uncompress command instead of the gzip command as follows: uncompress filename.tar.Z tar -xvf filename.tar tar -tvf filename Explanation of options: -c tells it to create -z tells it to compress -x tells it to expand -t is for table of contents -v gives you verbose output (instead of no o...