Generate DDL source code using Oracle SQL
Objective: To generate DDL source code using Oracle SQL. Scenario: Customer asks us to generate all the DDL source code for some database objects. So I just found the little…
Read MoreSplit by count excel report in Oracle BI Publisher 10g
Objective: To create a template to split records in excel sheet in Oracle BI Publisher 10g. Solution: Template Creation – EXCEL Step 1: Open the Excel. (See below image) Step…
Read MoreStop the user to click a page submit button more than once in Oracle APEX 4.2
Objective: To stop the user to click a APEX page submit button more than once in Oracle APEX 4.2 Scenario: The standard way to push the page data back to…
Read MoreCall Oracle package in Oracle BI Publisher 10g
Objective: To call Oracle package in Oracle BI Publisher 10g. Scenario: Oracle BI Publisher is just a reporting tool. From which we can download excel, csv, xml, rtf, PDF reports.…
Read MoreSuppliers Report (XML)DescriptionSuppliersReport
Suppliers Report (XML) Description Suppliers Report (XML) is used to review the supplier, supplier site and supplier contacts details. 1. Query Fetching Vendor Details: SELECT p.vendor_id c_vendor_id, p.segment1 c_vendor_number, …
Read MoreAccountPayables Remittance Advice DescriptionAPRem..
Account Payables Remittance Advice Description AP Remittance Advice, a document that describes payments that are being made. The person or company that is making the payment will sometimes include a…
Read MoreInvisible columns
Oracle Database 12c supports invisible columns, which implies that the visibility of a column. A column marked invisible does not appear in the following operations: SELECT * FROM queries on the table SQL* Plus DESCRIBE command Local records of %ROWTYPE Oracle Call Interface (OCI) description A column can be made invisible by specifying the INVISIBLE clause against the column. Columns of all types (except user-defined types), including virtual columns, can be marked invisible, provided the tables are not temporary tables, external tables, or clustered ones. An invisible column can be explicitly selected by the SELECT statement. Similarly, the INSERT statement will not insert values in an invisible column unless explicitly specified. Furthermore, a table can be partitioned based on an invisible column. A column retains its nullity feature even after it is made invisible. An invisible column can be made visible, but the ordering of the column in the table may change. In the following script, the column NICKNAME is set as invisible in the table t_inv_col: /*Create a table to demonstrate invisible columns*/ CREATE TABLE t_inv_col (id NUMBER, name VARCHAR2(30), nickname VARCHAR2 (10) INVISIBLE, dob DATE ) / The information about the invisible columns can be found in user_tab_cols. Note that the invisible column is marked as hidden: /*Query the USER_TAB_COLS for meta data information*/ SELECT column_id, column_name, hidden_column FROM user_tab_cols WHERE table_name = ‘T_INV_COL’ ORDER BY column_id / COLUMN_ID COLUMN_NAME HID ———- ———— — 1 ID NO 2 NAME NO 3 DOB NO…
Read MoreRow limiting using FETCH FIRST
For Top’N’ queries , Oracle Database 12c introduces a new clause, FETCH FIRST, to simplify the code and comply with ANSI SQL standard guidelines. The clause is used to limit the number of rows returned by a query. The new clause can be used in conjunction with ORDER BY to retrieve Top-N results. The row limiting clause can be used with the FOR UPDATE clause in a SQL query. In the case of a materialized view, the defining query should not contain the FETCH clause. Another new clause, OFFSET, can be used to skip the records from the top or middle, before limiting the number of rows. For consistent results, the offset value must be a positive number, less than the total number of rows returned by the query. For all other offset values, the value is counted as zero. Keywords with the FETCH FIRST clause are: ·FIRST | NEXT—Specify FIRST to begin row limiting from the top. Use NEXT with OFFSET to skip certain rows. ·ROWS | PERCENT—Specify the size of the result set as a fixed number of rows or percentage of total number of rows returned by the query. ·ONLY | WITH TIES—Use ONLY to fix the size of the result set, irrespective of duplicate sort keys. If you want records with matching sort keys, specify WITH TIES. The following query demonstrates the use of the FETCH FIRST and OFFSET clauses in Top-N queries: /*Create the test table*/ CREATE TABLE t_fetch_first (empno VARCHAR2(30), deptno NUMBER, sal NUMBER, hiredate DATE) / The following PL/SQL block inserts sample data for testing: /*Insert the test data in T_FETCH_FIRST table*/ BEGIN INSERT INTO t_fetch_first VALUES (101, 10, 1500, ’01-FEB-2011′); INSERT INTO t_fetch_first VALUES (102, 20, 1100, ’15-JUN-2001′); INSERT INTO t_fetch_first VALUES (103, 20, 1300, ’20-JUN-2000′); INSERT INTO t_fetch_first VALUES (104, 30, 1550, ’30-DEC-2001′); INSERT INTO t_fetch_first VALUES (105, 10, 1200, ’11-JUL-2012′); INSERT INTO t_fetch_first VALUES (106, 30, 1400, ’16-AUG-2004′); INSERT INTO t_fetch_first VALUES (107, 20, 1350, ’05-JAN-2007′); INSERT INTO t_fetch_first VALUES (108, 20, 1000, ’18-JAN-2009′); COMMIT; END; / The SELECT query pulls in the top-5 rows when sorted by their salary:…
Read MoreInclude or Exclude column in APEX interactive report download
Include column only in download Hidden column cannot be downloaded in interactive report if, Display As : Hidden Include In Export : Yes This can be achieved using the PLSQL…
Read MoreMINIMUM DATE AND MAXIMUM DATE FOR A DATE PICKER IN A TABULAR FORM
MINIMUM DATE: The Minimum date can be set for a date picker in the tabular form using the following steps. 1. Set SYSDATE as the source value to an item…
Read More