Download Text File With Unlimited Character

1. Make a Region with PL/SQL Dynamic Content  type .
==========
2. Assign ID to the region using  HTP.p (<span id="TEST_ID">) and Select data from table into  a  CLOB Type Variable  and Print the variable using HTP.p
==========
Example :

HTP.p ('<a download="Subject_Info.txt" id="downloadlink"  ><b>Download</b></a> <br> <br> <br>');  

DECLARE
  
   v_text     CLOB;
BEGIN
HTP.p ('<span id="TEST_ID">');      
      FOR i
         IN (SELECT    EMPLOYEE_ID  -- Select data from table
                 || FIRST_NAME
                 || LAST_NAME
                 || JOB
                 || HIRE_DATE
                 || SALARY
                 || DEPARTMENT_ID
                    AS Alias
            FROM EMPLOYEES )
      LOOP
  v_text := i."Alias";
         
      /*   HTP.p ('' || replace(v_text,'&amp;','&') || '');*/
     HTP.p (''|| replace (replace (UPPER(v_text), chr (13), ''), chr (10), ' ')|| '');
       
      END LOOP;

      HTP.p ('</span>');

END;



3.Copy Pest the JavaScript code into Page >> Function and Global Variable Declaration
==========

(function () {
    var textFile = null,
    makeTextFile = function (text) {
    var data = new Blob([text], {type: 'text/plain'});
    if (textFile !== null) {
    window.URL.revokeObjectURL(textFile);}
    textFile = window.URL.createObjectURL(data);
    return textFile;};
    var create = document.getElementById('create'),
    textbox = document.getElementById('TEST_ID'); 

    var link = document.getElementById('downloadlink');
    link.href =          makeTextFile(textbox.innerHTML.replace(/&amp;/g,"&").trim());
    link.style.display = 'block';

})();




Click On Download

Remove Enter/Newline from a String By PL/SQL

DECLARE
   temp   VARCHAR2 (32767) := 'This is a string using
ENTER button. ';
BEGIN
   DBMS_OUTPUT.put_line (temp);
   DBMS_OUTPUT.put_line (
      REPLACE (REPLACE (temp, CHR (13), ''), CHR (10), ' '));
END;

Delete Multiple Row From A Table in Oracle

DECLARE
   V_COUNT   NUMBER;
BEGIN
   DELETE FROM EMPLOYEES
         WHERE ROWID IN (SELECT ROWID
                         FROM (SELECT ROWID,
                               ROW_NUMBER ()
                               OVER (PARTITION BY EMPLOYEE_ID
                                     ORDER BY 1)ROW_NO
                                   FROM EMPLOYEES)
                          WHERE ROW_NO > 1);
   V_COUNT := SQL%ROWCOUNT;
   DBMS_OUTPUT.PUT_LINE(V_COUNT);
END;

Short Description On Oracle Users and Security

In This part we discuss about Oracle users and security.  To perform the administrative tasks of an Oracle Database DBA, you need specific privileges within the database.

The SYSDBA and SYSOPER are two terms what will mandatory for oracle database. The unique thing about SYSDBA and SYSOPER privileges is that you must explicitly activate these privileges for your login session by specifying "AS SYSDBA" in the connection string BY THE FOLLOWING COMMAND:

SQL> CONNECT SYS AS SYSDBA

The default location of the password file in windows is 

‘%ORACLE_HOME%\database\PWD%ORACLE_SID%.ora’.

HERE ‘%ORACLE_HOME%\database’ MEANS PATH DIRECTORY FOR PASSWORD

The following three steps show how to create a password file, how to enable remote login as SYSDBA, and how to grant SYSDBA access to a certain user:

Step 1: Create the password file using the ORAPWD utility:

CMD> ORAPWD FILE=‘%ORACLE_HOME%\DATABASE\PWDORCL.ORA ENTRIES=25

Step 2: you need to set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to ENABLE REMOTE LOGIN AS SYSDBA(In SPFILE or PFILE). To do this for SPFILE, execute the following command:

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

You must restart the database to make this change into effect.

Step 3: Grant SYSDBA or SYSOPER to HR user as follows:

SQL> GRANT DBA, SYSDBA to HR

Connecting Using Password File

SQL> CONNECT HR@ORCL AS SYSDBA

Verify who has the SYSDBA privilege in Database

SQL> SELECT * FROM V$PWFILE_USERS;

USERNAME             SYSDB        SYSOP

-------------------- ---------    ---------

SYS                  TRUE         TRUE

Creating and Managing Users, Roles, and Privileges

Creating a New User

You use the CREATE USER SQL statement to create a new user. The following example creates a new user JOHN whose password is JOHNPASS

SQL> CREATE USER JOHN  IDENTIFIED  BY JOHNPASS;

User created.

Following command give table space for JHON

SQL> SELECT  DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE  FROM  DBA_USERS WHERE USERNAME='JOHN';

The new user can’t connect to the database without privilege. So you should give  privileges to new User. Following Command do this:

SQL> GRANT CREATE SESSION TO JOHN;

Grant succeeded.

There is a new term QUOTA which is determines the maximum space that the user can use.

Following command show the command of create user, give table space & quota.

SQL>   CREATE USER JOHN IDENTIFIED BY JOHNPASS

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

QUOTA 10M ON USERS;

User created

Altering a User: Using this statement, you can do the following work like Change a user’s password, Assign and modify tablespace quotas, Set and alter default and temporary tablespaces, Assign and modify a profile and default roles. Below, we show user ALTER USER command can be used to change previously created JOHN’s password. The new password will be JOHNNEWPASS

SQL> ALTER USER JOHN IDENTIFIED BY JOHNEWPASS;

User altered

In SQL*Plus, THIS command as :

SQL> PASSWORD Changing password for AHMED

Old password: *********

New password: *********

Retype new password: *********

Password changed SQL>

You can drop user with their objects by the Following Command

SQL> DROP USER JOHN CASCADE;

User Dropped.

If you aren’t sure whether you will need a user’s objects later, but you want to deny access.

SQL> REVOKE CREATE SESSION FROM JOHN;

Revoke succeeded. SQL>

Granting Roles and Privileges:

SQL> GRANT CREATE TABLE TO JOHN; Grant succeeded.

SQL> GRANT SELECT, UPDATE ON HR.EMPLOYEE TO JOHN; Grant succeeded.

Working with ROLES: We show the steps to create a role, assign privileges to the role, and then granting the role to a user by the following command:

SQL> CREATE ROLE APP; Role created.

Now, grant privileges to role APP.

SQL > GRANT CREATE SESSION TO APP; Grant succeeded.

SQL > GRANT CREATE TABLE TO APP;

Grant succeeded.

SQL > GRANT CREATE VIEW TO APP;

Grant succeeded.

SQL >

Now, you can grant the role APP to user JOHN. User JOHN will then receive all privileges assigned to the role APP.

SQL > GRANT APP TO JOHN;

Grant succeeded.

SQL >

SQL > GRANT DBA TO JOHN;

Grant succeeded.

SQL >

Creating and Using User Profiles: The Following Command Show How To Create Profile

SQL> CREATE PROFILE MISER  LIMIT     CONNECT_TIME 120     FAILED_LOGIN_ATTEMPTS 3     IDLE_TIME 60     SESSIONS_PER_USER 2;

Profile created.

Sort Number's String Order by Number by Oracle PL/SQL Function STRING_SORT (p_str VARCHAR2 )

Sort a string and separate numbers with ( ; )  contain with numbers separated ( , ) .

Example: 
Input:      "1,9,12,45,14,98,12,445,2" 
Output:    "1;2;9;12;12;14;45;98;445"

Or
Input:  "1622,49,454,458748,500/2,500/1,25/3,25/4,25/5,1"
Output:   "1;25/3;25/4;25/5;49;454;500/1;500/2;1622;458748"

You can do this Sort using this function:

CREATE OR REPLACE FUNCTION STRING_SORT (p_str VARCHAR2) 
--Muhammad Abdul Qaium
   RETURN VARCHAR2
IS
   v_1   VARCHAR2 (500);
   v_2   VARCHAR2 (500);
BEGIN
   FOR i
      IN (    SELECT REGEXP_SUBSTR (p_str,
                                    '[^,;&]+',
                                    1,
                                    LEVEL) D
                FROM DUAL
          CONNECT BY REGEXP_SUBSTR (p_str,
                                    '[^,;&]+',
                                    1,
                                    LEVEL)
                        IS NOT NULL
            ORDER BY TO_NUMBER (
                        DECODE (SUBSTR (D, 0, INSTR (D, '/') - 1),
                                NULL, D,
                                SUBSTR (D, 0, INSTR (D, '/') - 1))),
                     SUBSTR (D, INSTR (D, '/')) ASC)
   LOOP
      v_1 := v_1 || ';' || I.D;
   END LOOP;

   RETURN SUBSTR (v_1, 2);
EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error (-20001,
                               'You have must use (,) or (;) to separate !');
-- DBMS_OUTPUT.put_line (SUBSTR (v_1, 2));
END;
/

Thank You