How to get next value of Sequence dynamically in Oracle

we can execute sequence and get its value using Execute Immediate.
here is the Example Code;

CREATE SEQUENCE mySEQ START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 1000000 NOCYCLE NOCACHE ORDER

declare

  SequenceValue number;

begin

    EXECUTE IMMEDIATE 'select mySEQ.nextval  from dual' INTO SequenceValue;

    ---Now you can Use SequenceValue anywhere

end;

/

drop sequence mySEQ 

/

Rownum in SQL

The Oracle/PLSQL ROWNUM function returns a number that represents the order that a row is selected by Oracle from a table or joined tables.

Example

select * from all_objects where rownum < 2

Oracle DDL with EXECUTE IMMEDIATE -- Drop Multiple Object at once

If You want to Drop Multiple Object at once use this script. Please using

This script check cursor query and take update of your database

/* Formatted on 3/31/2020 1:40:01 am (QP5 v5.139.911.3011) */

DECLARE

   CURSOR OBJECTLIST

   IS

      SELECT    'Drop '

             || OBJECT_TYPE

             || '  '

             || OWNER

             || '.'

             || OBJECT_NAME

             || ''

                DROPCOMMAND

        FROM ALL_OBJECTS

       WHERE OWNER = 'APPS' AND OBJECT_NAME LIKE 'EBA%';

BEGIN

   FOR LDX IN OBJECTLIST

   LOOP

      DBMS_OUTPUT.PUT_LINE (TO_CHAR (LDX.DROPCOMMAND));

      BEGIN

         EXECUTE IMMEDIATE LDX.DROPCOMMAND;

         DBMS_OUTPUT.PUT_LINE ('Object has been dropped.');

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.

            PUT_LINE (

                  SQLERRM

               || ' While performing this Command,'

               || TO_CHAR (LDX.DROPCOMMAND));

      END;

   END LOOP;

END;

select 'Drop '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';'

from all_objects

where owner='APPS'

AND OBJECT_NAME LIKE 'EBA%'

How to access APEX application from other Computer

If you want to access/operate Apex application from other computer then Case 1 applicable Otherwise if you want locally then Case 2 applicable.

Case 1. URL : http://ComputerName:Port/apex
or http://IPaddress:Port/apex

Case 2. URL : http://localhost:/apex

For Case 1 if you are not able to access the application then have to enable network access true.
To do so, you have to log on as SYS with SYSDBA privilege and execute the following command.

execute dbms_xdb.setListenerLocalAccess(l_access => FALSE);

Just copy the above command and paste into your SQL Editor and execute. After scuccessfull execution of the above command, if the remote computer name is ABC and apex access port 8080 then your url will be as bellow-

http://abc:8080/apex

If the remote computer IP is 119.18.144.201 and apex access port 8080 then your url will be as bellow-

http://119.18.144.201:8080/apex

index Table Type in PLSQL

DECLARE

  TYPE TABLENAMETYPE IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;

  L_TABLENAMETYPE TABLENAMETYPE;

BEGIN

  --YOU CAN USE THIS L_TABLENAMETYPE TYPE HERE

  L_TABLENAMETYPE(1) := 'AB';

  L_TABLENAMETYPE(2) := 'ABC';

  L_TABLENAMETYPE(3) := 'ABD';

  L_TABLENAMETYPE(4) := 'ABE';

  FOR LDX IN 1 .. L_TABLENAMETYPE.COUNT

 

   LOOP

 

    DBMS_OUTPUT.PUT_LINE(L_TABLENAMETYPE(LDX));

 

  END LOOP;

  ---DELETE ALL VALUES

  L_TABLENAMETYPE.DELETE;

END;

--RESULT

AB

ABC

ABD

ABE

---USE TABLE TYPE WITH BULK COLLECT INTO

DECLARE

CURSOR C IS

  SELECT * FROM EMP;

TYPE ANY_TABLE_TYPE IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;

EMP_REC ANY_TABLE_TYPE;

BEGIN

  OPEN C;

  FETCH C BULK COLLECT

 

    INTO EMP_REC;

  CLOSE C;

  FORALL I IN EMP_REC.FIRST .. EMP_REC.LAST

 

    UPDATE EMP

   

       SET COMM = EMP_REC(I).SAL * 10 / 100

   

     WHERE EMPNO = EMP_REC(I).EMPNO;

  COMMIT;

END;

Perform Dynamic DLL in Oracle

EXECUTE IMMEDIATE statement can be used to execute dynamic SQL statements.

Syntax: EXECUTE IMMEDIATE 'User_Code';

--

-- Create/Drop Table using this PLSQL Script or Procedure

-- DLL Script

-- by mfaisal 02-Jan-2020

Declare

---Create

begin

  execute immediate 'CREATE TABLE X_DDL_ON_APPS (APPS_ID NUMBER)';

end;

Declare

--Drop with Oracle EBS Package

begin

  system.ad_apps_private.do_apps_ddl(schema_name => 'APPS',

                                     ddl_text => 'DROP TABLE X_DDL_ON_APPS ');

end;

Backup of all Oracle Database objects on Server with Procedure

----Backup With Procedure

---Create Directory

CREATE OR REPLACE DIRECTORY PS_DIR AS '/u01/oracle/PROD/fs2/EBSapps/appl/au/12.0.0/forms/BackUp';

---Create Procedure

  

   CREATE OR REPLACE PROCEDURE    EXPORT_DDL

AS

    V_DDL_CLOB  CLOB;

    VPATH VARCHAR2(255);

BEGIN

    FOR C IN (SELECT OBJECT_NAME, OBJECT_TYPE

             FROM USER_OBJECTS

             WHERE OBJECT_TYPE IN ('TABLE','VIEW','FUNCTION','PROCEDURE','PACKAGE','PACKAGE_BODY')

             and OBJECT_NAME like '%TGC%')

    LOOP

        V_DDL_CLOB := DBMS_METADATA.GET_DDL(C.OBJECT_TYPE, C.OBJECT_NAME);

        DBMS_XSLPROCESSOR.CLOB2FILE(V_DDL_CLOB, 'PS_DIR', C.OBJECT_TYPE || '_' || C.OBJECT_NAME||'.SQL');

    END LOOP;

END;

--- Call procedure

Exec EXPORT_DDL;

Internal Concurrent Manager status could not be determined.

To resolve this, do the following steps.

1. Make sure that there are no concurrent processes still running. ps -ef | grep FNDLIBR
2. Use sqlplus, login as APPLSYS
3. delete  from fnd_concurrent_processes;
   Don't panic if it deletes a lot of rows this table holds a lot of useless history information and should be cleaned up every so often.
4. update fnd_concurrent_queues set running_processes=0;

Now restart your concurrent managers, which should start up without any errors in the log file at all.
Then see if the requests go through OK.

n_to_nth convert number to char plsql

CREATE OR REPLACE PACKAGE n_to_nth

IS

   FUNCTION n_to_nth (n_in IN NUMBER)

      RETURN VARCHAR2;

   /* Alias for n_to_nth */

   FUNCTION nth_from_n (n_in IN NUMBER)

      RETURN VARCHAR2;

   /* Convert day of month to word */

   FUNCTION dd_to_nth (dd_in IN NUMBER)

      RETURN VARCHAR2;

END;

/*****************************************************************************************/

CREATE OR REPLACE PACKAGE BODY n_to_nth

IS

   /* Utilize date functions to conver "1" to "1st" and first" etc. */

   FUNCTION n_to_nth (n_in IN NUMBER)

      RETURN VARCHAR2

   IS

   BEGIN

      RETURN CASE

                WHEN n_in IS NULL

                THEN

                   NULL

                WHEN n_in = 0

                THEN

                   NULL

                ELSE

                   LOWER (

                      TO_CHAR (TO_DATE ('1-1-' || n_in, 'dd-mm-yyyy'),

                               'FMYYYYth'))

             END;

   END;

   FUNCTION nth_from_n (n_in IN NUMBER)

      RETURN VARCHAR2

   IS

   BEGIN

      RETURN n_to_nth (n_in);

   END;

  

   FUNCTION dd_to_nth (dd_in IN NUMBER)

      RETURN VARCHAR2

   IS

   BEGIN

      RETURN CASE

                WHEN dd_in IS NULL

                THEN

                   NULL

                ELSE

                   LOWER (

                      TO_CHAR (

                         TO_DATE ('2010-01-' || LPAD (dd_in, 2, '0'),

                                  'YYYY-MM-DD'),

                         'DDSPth'))

             END;

   END;

END;

/*******************************************************************************/

BEGIN

   DBMS_OUTPUT.put_line (n_to_nth.n_to_nth (1));

   DBMS_OUTPUT.put_line (n_to_nth.n_to_nth (3));

   DBMS_OUTPUT.put_line (n_to_nth.n_to_nth (100));

   DBMS_OUTPUT.put_line (n_to_nth.dd_to_nth (1));

   DBMS_OUTPUT.put_line (n_to_nth.dd_to_nth (3));

   DBMS_OUTPUT.put_line (n_to_nth.dd_to_nth (27));

   DBMS_OUTPUT.put_line (INITCAP (n_to_nth.dd_to_nth (27)));

END;