Remove sum in interactive grid

 Set the following code into 

Interactive grid >> Attribute >>JavaScript Initialization Code

function(config) {  

    config.editable.autoAddRow = true;

    config.submitSelectedRows = false;

    config.defaultGridViewOptions = {

skipReadonlyCells: true,

    multiple: true,

    selectAll: false,

    footer:false,

    

}; 

    return config;

} 

Dates between two parameter date

 SELECT ( :psdate) + LEVEL - 1     dt
      FROM DUAL
CONNECT BY LEVEL <= (( :pedate) - ( :psdate) + 1);

Edit Link icon in oracle apex

For Edit Button 

<span aria-label="Edit"><span class="fa fa-edit" aria-hidden="true" title="Edit"></span></span>

For Print Button

<span aria-label="Edit"><span class="fa fa-print" aria-hidden="true" title="Edit"></span></span>

Open in new tab

Link attribute :

target='_blank'

For Delete icon

<span class="t-Icon fa fa-trash-o" aria-hidden="true"></span>

Remove last character from a string in oracle sql

 SELECT SUBSTR('ABCD', 1, LENGTH('ABCD') - 1) FROM DUAL;

Insert Script to Primary table/ Master table in oracle apex

--Stored procedure for generating DML Script in apex

CREATE OR REPLACE PROCEDURE GEN_APEX_DML_SCRIPT (

    p_table_name   IN VARCHAR2,

    p_page_item_pk IN VARCHAR2 DEFAULT NULL,  -- e.g. 'P17_ID'

    P_PAGE         IN VARCHAR2  -- e.g. 'P17_'

)

IS

    v_table_name     VARCHAR2(100);

    v_columns        VARCHAR2(4000);

    v_bind_columns   VARCHAR2(4000);

    v_update_columns VARCHAR2(4000);

    v_pk_column      VARCHAR2(100);

    v_pk_page_item   VARCHAR2(100);

BEGIN

    v_table_name := UPPER(p_table_name);

    -- Try to detect primary key automatically

    BEGIN

        SELECT acc.column_name

          INTO v_pk_column

          FROM user_constraints uc

               JOIN user_cons_columns acc

                  ON uc.constraint_name = acc.constraint_name

         WHERE uc.table_name = v_table_name

           AND uc.constraint_type = 'P'

           AND ROWNUM = 1;

    EXCEPTION

        WHEN NO_DATA_FOUND THEN

            v_pk_column := NULL;

    END;

    -- If user passed a page item pk name, use it, else build default

    IF p_page_item_pk IS NOT NULL THEN

        v_pk_page_item := p_page_item_pk;

    ELSE

        v_pk_page_item := 'Pxx_' || v_pk_column;

    END IF;

    -- Build column lists

    SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id),

           LISTAGG(':' ||P_PAGE|| column_name, ', ') WITHIN GROUP (ORDER BY column_id),

           LISTAGG(column_name || ' = :' ||P_PAGE|| column_name, ', ')

      INTO v_columns, v_bind_columns, v_update_columns

      FROM user_tab_cols

     WHERE table_name = v_table_name

     ORDER BY COLUMN_ID;

    -- Output APEX DML script

    DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');

    DBMS_OUTPUT.PUT_LINE('-- Generated DML for table: ' || v_table_name);

    DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');

    DBMS_OUTPUT.PUT_LINE('BEGIN');

    DBMS_OUTPUT.PUT_LINE('   IF :' || v_pk_page_item || ' IS NULL THEN');

    DBMS_OUTPUT.PUT_LINE('      INSERT INTO ' || v_table_name || ' (' || v_columns || ')');

    DBMS_OUTPUT.PUT_LINE('      VALUES (' || v_bind_columns || ');');

    DBMS_OUTPUT.PUT_LINE('');

    DBMS_OUTPUT.PUT_LINE('   ELSE');

    DBMS_OUTPUT.PUT_LINE('      UPDATE ' || v_table_name);

    DBMS_OUTPUT.PUT_LINE('         SET ' || v_update_columns);

    IF v_pk_column IS NOT NULL THEN

        DBMS_OUTPUT.PUT_LINE('       WHERE ' || v_pk_column || ' = :' || v_pk_page_item || ';');

    ELSE

        DBMS_OUTPUT.PUT_LINE('       -- No primary key found. Add WHERE clause manually.');

    END IF;

    DBMS_OUTPUT.PUT_LINE('   END IF;');

    DBMS_OUTPUT.PUT_LINE('END;');

    DBMS_OUTPUT.PUT_LINE('/');

    DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');

EXCEPTION

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END GEN_APEX_DML_SCRIPT;

/

SET SERVEROUTPUT ON;

BEGIN

    GEN_APEX_DML_SCRIPT(:TABLE_NAME, :PAGE_ITEM_PK, 'P17_');

END;

DECLARE

    CURSOR c1 IS SELECT :P_TABLE_NAME P_TABLE FROM DUAL;

    CURSOR C2 IS

          SELECT COLUMN_NAME || ','     COLUMN_NAME

            FROM USER_TAB_COLS

           WHERE TABLE_NAME = :P_TABLE_NAME

        ORDER BY COLUMN_ID;

    CURSOR c3 IS SELECT 'INSERT INTO (' || :P_TABLE_NAME P_TABLE FROM DUAL;

    CURSOR C4 IS

          SELECT COLUMN_NAME || ','     COLUMN_NAME

            FROM USER_TAB_COLS

           WHERE TABLE_NAME = :P_TABLE_NAME

        ORDER BY COLUMN_ID;

    CURSOR C5 IS

          SELECT COLUMN_NAME

            FROM USER_TAB_COLS

           WHERE TABLE_NAME = :P_TABLE_NAME

        ORDER BY COLUMN_ID;

BEGIN

    FOR I IN C1

    LOOP

        DBMS_OUTPUT.PUT_LINE (

            'BEGIN IF ' || ':' || :PAGE_NO_PK || ' IS NULL

   THEN 

    INSERT INTO ' || I.P_TABLE || '(');

        FOR X IN C2

        LOOP

            DBMS_OUTPUT.PUT_LINE (X.COLUMN_NAME);

        END LOOP;

        DBMS_OUTPUT.PUT_LINE (')');

        FOR J IN C3

        LOOP

            DBMS_OUTPUT.PUT_LINE ('VALUES(');

            FOR Y IN C4

            LOOP

                DBMS_OUTPUT.PUT_LINE (':' || :PAGE_NO_PK || Y.COLUMN_NAME);

            END LOOP;

            DBMS_OUTPUT.PUT_LINE (');');

        END LOOP;

        DBMS_OUTPUT.PUT_LINE (

            ' 

   ELSIF ' || ':' || :PAGE_NO_PK || ' IS NOT NULL

   THEN

      UPDATE ' || :P_TABLE_NAME || ' SET ');

        FOR Z IN C5

        LOOP

            DBMS_OUTPUT.PUT_LINE (

                   Z.COLUMN_NAME

                || ' = :'

                || :PAGE_NO_PK

                || Z.COLUMN_NAME

                || ',');

        END LOOP;

        DBMS_OUTPUT.PUT_LINE (

            'WHERE ' || :PAGE_NO_PK || ' = ' || ':' || :PAGE_NO_PK || ';');

        DBMS_OUTPUT.PUT_LINE ('END IF;

        END;');

    END LOOP;

END;

Archive log clearing in oracle

 rman target /

RMAN> delete archivelog all;

RMAN> exit;

-----------------------
> set oracled_sid=write_oracle_sid_here
> rman target sys/put_sys_password_here
> crosscheck archivelog all;
> delete noprompt expired archivelog all;
>exit;

Total calculation by java script in oracle apex

 (function($) {
    // This is the function that calculates over all the rows of the model and then
    // updates something else.
    // Change this to do whatever calculation is needed.
    // Call this whenever the model data changes.

    // Total is the column name which is to be calculated.
    function update(model) {
        var salKey = model.getFieldKey("TOTAL"),  //TOTAL is the column name would be calculated
            total = 0;
        console.log(">> starting sum AMOUNT column")
        model.forEach(function(record, index, id) {
            var sal = parseFloat(record[salKey]),  // record[salKey] should be a little faster than using model.getValue in a loop
                meta = model.getRecordMetadata(id);
            if (!isNaN(sal) && !meta.deleted && !meta.agg) {
                total += sal;
            }
        });
        console.log(">> setting sum AMOUNT column to " + total)
        $s("P68_TOTAL_AMNT", total);
    }
    // P68_TOTAL_AMNT is the field name summary field.
    // This is the general pattern for subscribing to model notifications
    //
    // need to do this here rather than in Execute when Page Loads so that the handler
    // is setup BEFORE the IG is initialized otherwise miss the first model created event
    $(function() {
        // the model gets released and created at various times such as when the report changes
        // listen for model created events so that we can subscribe to model notifications
        $("#BILL").on("interactivegridviewmodelcreate", function(event, ui) {
            var sid,
                model = ui.model;
            // note this is only done for the grid veiw. It could be done for
            // other views if desired. The imporant thing to realize is that each
            // view has its own model
            if ( ui.viewId === "grid" ) {
                sid = model.subscribe( {
                    onChange: function(type, change) {
                        console.log(">> model changed ", type, change);
                        if ( type === "set" ) {
                            // don't bother to recalculate if other columns change
                            if (change.field === "TOTAL" ) {
                                update( model );
                            }
                        } else if (type !== "move" && type !== "metaChange") {
                            // any other change except for move and metaChange affect the calculation
                            update( model );
                        }
                    },
                    progressView: $("#P68_TOTAL_AMNT") // this will cause a spinner on this field
                } );
                // if not lazy loaded there is no notification for initial data so update
                update( model ); 
                // just in case fetch all the data. Model notifications will
                // cause calls to update so nothing to do in the callback function.
                // can remove if data will always be less than 50 records
                model.fetchAll(function() {});
            }
        });
    });
})(apex.jQuery);

Execute immediate statement to create table

 DECLARE
    vCNT   NUMBER;
BEGIN
    SELECT COUNT (*)
      INTO vCNT
      FROM USER_TABLES
     WHERE UPPER (TABLE_NAME) = UPPER ('table_01');

    IF vCNT > 0

    THEN

        EXECUTE IMMEDIATE 'DROP table table_01 CASCADE CONSTRAINT';
        EXECUTE IMMEDIATE 'create table table_01 as
                            SELECT *
                              FROM table_name
                             WHERE column= ' || :value;
    ELSE
        EXECUTE IMMEDIATE 'create table table_01 as
                           SELECT *
                              FROM table_name
                             WHERE column= ' || :value;
    END IF;
END;