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

How to Generate and Download Text File By Oracle Apex 5.1 Using JavaScript

It is very easy to generate a text file from oracle apex by using JavaScript . Here I describe it step by step.

1.  First create a page and create a region with (Type: Static Content).

2. Create a textarea type item. (Name: P21_CONTENT ) 

3. Then copy-pest the JavaScript code into  Page >>JavaScript >>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('P21_CONTENT');

    var link = document.getElementById('downloadlink');

    link.href = makeTextFile(textbox.value);

   link.style.display = 'block';

})();

4.  P21_CONTENT it is the CSS  ID of  P21_CONTENT of  textarea item.

5. Create a button with Behavior>>Action>>Submit Page. And another one button for clearing the data of the textarea.  

6. Then  copy-pest the HTML code into the Region >> Source 

   <a download="info.txt" id="downloadlink" width="107" 

   height="10" ><b>Download</b></a> 

JOB DONE...

Now you can see a page like this. Here you write any thing on the textarea and click on Generate Text button.  Finally Click on Download link.

Demo:

1.

2.

If Not Working You One Please Comment or Contact With Me.

For any type of mistake Advance Sorry and Please inform me.

Meaning of Color

When we design a software sometime we confused to use color in GUI.
So this meaning can help you to build a good GUI.

What does the color mean?

RED Color => warmth, love, anger, danger, boldness, excitement, speed,
strength, energy, determination, desire, passion, courage, socialism

PINK Color => feminine, love, caring, nurture

ORANGE Color => cheerfulness, low cost, affordability, enthusiasm,
stimulation, creativity, aggression, food, halloween, liberal (politics)

YELLOW Color => attention-grabbing, comfort, liveliness, cowardice,
hunger, optimism, overwhelm, Summer, comfort, liveliness, intellect,
happiness, energy, conflict

GREEN Color => durability, reliability, environmental, luxurious,
optimism, well-being, nature, calm, relaxation, Spring, safety, honesty,
optimism, harmony, freshness

BLUE Color => peace, professionalism, loyalty, reliability, honor, trust,
melancholia, boredom, coldness, Winter, depth, stability, professionalism,
conservatism

PURPLE Color => power, royalty, nobility, elegance, sophistication,
artificial, luxury, mystery, royalty, elegance, magic

GRAY Color => conservatism, traditionalism, intelligence, serious, dull,
uninteresting

BROWN Color => relaxing, confident, casual, reassuring, nature, earthy,
solid, reliable, genuine, Autumn, endurance

BLACK Color => Elegance, sophistication, formality, power, strength,
illegality, depression, morbidity, night, death

WHITE Color => Cleanliness, purity, newness, virginity, peace, innocence,
simplicity, sterility, snow, ice, cold

Thank You

JavaScript Function to Convert Lower Case to Upper Case

function fn_upper(pThis){
$(pThis).keyup(function(){
this.value= this.value.toUpperCase();
});
}

Show Application Manu Bar On Mobile Oracle Apex 5.1

Some times Oracle Apex Applications manu bar may not show on mobile device. You can solve this problem by doing this Dynamic Action.

1. Create a Dynamic Action in  0 page.

2.Set Event  : Page Load  Execute JavaScript Code

3. Set Action : Execute JavaScript Code Now you can try on mobile device

4. Then copy pest this JavaScript  Code :

$(".t-Header-nav").removeClass("t-Header-nav");

Now you can try on mobile device.

Number in Word Using Oracle Sql

select salary, to_char(to_date(salary,'j'), 'jsp') from EMPLOYEES;

Manually / Dynamically Make Text Item In Oracle Apex

DECLARE
v_column    VARCHAR2 (20);
v_value     VARCHAR2 (20);
CURSOR c_columns
IS
    SELECT Column_Name_To_Count
      FROM Table_Name
     WHERE Condition
  ORDER BY ROWNUM;

TYPE column_rec IS RECORD (column_name VARCHAR2 (30));
TYPE columns_table IS TABLE OF column_rec;
t_columns   columns_table;
BEGIN
OPEN c_columns;
FETCH c_columns
BULK COLLECT INTO t_columns;
CLOSE c_columns;
HTP.p ('<br/>');
HTP.p ('<table><tbody>');

FOR i IN 1 .. t_columns.COUNT
LOOP
  HTP.p ('<tr>');
  HTP.p (
        '<td><label for="P42_C'
     || LPAD (i, 3, 0)
     || '" id="P42_C'
     || LPAD (i, 3, 0)
     || '_LABEL">'
     || t_columns (i).column_name
     || ' :  &nbsp &nbsp  &nbsp &nbsp &nbsp  '
     || '</label></td>');
  HTP.p (
        '<td>'
     || APEX_ITEM.TEXT (1, p_item_id => 'P42_C' || LPAD (i, 3, 0))
     || '</td>');
  HTP.p ('</tr>');
END LOOP;
HTP.p ('</tbody></table>');

EXCEPTION
WHEN OTHERS
THEN
  IF c_columns%ISOPEN
  THEN
     CLOSE c_columns;
  END IF;
END;