Home/Database/Oracle SQL/Page 5
  1. Asked: March 20, 2017In:

    Convert number of seconds to Hours:Minutes:Seconds format

    ashishpandey Explorer

    Hi, SELECT TO_CHAR(SYSDATE,'SSSSS') FROM DUAL; -- will return like 54071SELECT TO_CHAR(TO_DATE(54071,'SSSSS'),'HH24:MI:SS') FROM DUAL;-- will RETURN like 15:01:11 It will help

    Hi,

     

    SELECT TO_CHAR(SYSDATE,’SSSSS’) FROM DUAL; — will return like 54071

    SELECT TO_CHAR(TO_DATE(54071,’SSSSS’),’HH24:MI:SS’) FROM DUAL;– will RETURN like 15:01:11

     

    It will help

    See less
    • 0
  2. Asked: June 14, 2016In:

    get number of days between two dates column in oracle sql?

    ashishpandey Explorer
    This answer was edited.

    Hi, Like this we can use: SELECT pol_fm_dt, pol_to_dt, ROUND(pol_to_dt - pol_fm_dt) FROM pgit_policy;  

    Hi, Like this we can use:

    SELECT pol_fm_dt, pol_to_dt, ROUND(pol_to_dt - pol_fm_dt) FROM pgit_policy;

     

    See less
    • 1
  3. Asked: July 3, 2016In:

    is there oracle function to remove spaces in the string ?

    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    hi albert, try this : select replace('Oracle Forms 10G', chr(32), '') from dual; hope this help.

    hi albert,
    try this :

    select replace('Oracle Forms 10G', chr(32), '') from dual;

    hope this help.

    See less
    • 0
  4. Asked: June 19, 2016In:

    how to create dynamic tables ,columns, and datatype for each column in oracle ?

    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    hello @Tiwari Sandeep what i meant is i want create table at run time like create table tablename (col1 varchar2(50), col2 number); actually we can do this by dynamic SQL but it's unfortunately not recommended by oracle because this will open a loophole for hackers by (SQL INJECTION) if you have anyRead more

    hello @Tiwari Sandeep what i meant is i want create table at run time like

    create table tablename (col1 varchar2(50), col2 number);

    actually we can do this by dynamic SQL but it’s unfortunately not recommended by oracle because this will open a loophole for hackers by (SQL INJECTION) if you have any idea to avoid this will be very appreciated

    thank you for your reply.

    See less
    • 0
  5. Asked: August 16, 2016In:

    How to Insert data include symbol '&' into a table ?

    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    it's very simple just using this : set define off insert into table values ('& yourvalues'); and then press 'F5' to execute the above commands. also from sqlplus you can use this : set define off

    it’s very simple just using this :

    set define off
insert into table values ('& yourvalues');

    and then press ‘F5’ to execute the above commands.
    also from sqlplus you can use this :
    set define off

    See less
    • 0
  6. Asked: June 26, 2016In:

    How to create a column with AUTO_INCREMENT in Oracle?

    Best Answer
    rahul.gour25 Explorer

    You can create a sequence then call a before each row trigger on this table for ID value.

    You can create a sequence then call a before each row trigger on this table for ID value.

    See less
    • 0
  7. Asked: June 21, 2016In:

    how can I know reserved words in oracle ?

    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    just execute this query to list all oracle reserved words select *from v$reserved_wordswhere reserved = 'Y' hope this helpful :) share with others to stretch our experiences

    just execute this query to list all oracle reserved words

    select *
    from v$reserved_words
    where reserved = 'Y'

    hope this helpful 🙂
    share with others to stretch our experiences

    See less
    • 0
  8. Asked: June 18, 2016In:

    How can i Update a table by data in another table ?

    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    This called correlated update you can find examples below UPDATE TABLE(<SELECT STATEMENT>) <alias1> SET <column_1> = (   SELECT <column_1>   FROM <table_2> <alias2>   WHERE <alias2.table_name> = <alias1.table_name>); in you sample : UPDATE table1 t1   Read more

    This called correlated update you can find examples below

    UPDATE TABLE(<SELECT STATEMENT>) <alias1>
SET <column_1> = (
  SELECT <column_1>
  FROM <table_2> <alias2>
  WHERE <alias2.table_name> = <alias1.table_name>);

    in you sample :

    UPDATE table1 t1
   SET (name, desc) = (SELECT t2.name, t2.desc
                         FROM table2 t2
                        WHERE t1.id = t2.id)
 WHERE EXISTS (
    SELECT 1
      FROM table2 t2
     WHERE t1.id = t2.id )

    alternative way you can do this

    UPDATE (SELECT t1.id, 
               t1.name name1,
               t1.desc desc1,
               t2.name name2,
               t2.desc desc2
          FROM table1 t1,
               table2 t2
         WHERE t1.id = t2.id)
   SET name1 = name2,
       desc1 = desc2

    and now the final example is

    UPDATE Table1 T1 SET
T1.name = (SELECT T2.name FROM Table2 T2 WHERE T2.id = T1.id),
T1.desc = (SELECT T2.desc FROM Table2 T2 WHERE T2.id = T1.id)
WHERE T1.id IN (SELECT T2.id FROM Table2 T2 WHERE T2.id = T1.id);
    See less
    • 0
  9. Asked: June 14, 2016In:

    How to combine “LIKE” and “IN” condition in sql ?

    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    There is no combination of LIKE & IN in SQL, but you can use REGEXP_LIKE condition it's similar LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE. Also this condition evaluates strings using characters as defined by iRead more

    There is no combination of LIKE & IN in SQL, but you can use REGEXP_LIKE condition it’s similar LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE. Also this condition evaluates strings using characters as defined by input character set.
    Example 1 :

    select first_name from employees where regexp_like (first_name,'^Walker|Ken|^Mr')

    First Name

    Walker, Mr. Kenneth (Ken)

    Example 2 : 

    SELECT first_name
FROM employees
WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');

    FIRST_NAME

    Steven

    Stephen

    Example 3 :

    SELECT last_name
FROM employees
WHERE REGEXP_LIKE (last_name, '([aeiou])1', 'i');

    LAST_NAME

    De Haan

    Greene

    Bloom

    Feeney

     

    See less
    • 0
  10. Asked: June 14, 2016In:

    What is the difference between varchar and varchar2 data type?

    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage VARCHAR is reserved by Oracle to support distinction between NULL and empty string in future, as ANSI standard prescribes. VARCHAR2 does not distinguish between a NULL andRead more

    Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage
    VARCHAR is reserved by Oracle to support distinction between NULL and empty string in future, as ANSI standard prescribes.
    VARCHAR2 does not distinguish between a NULL and empty string, and never will.
    If you rely on empty string and NULL being the same thing, you should use VARCHAR2

    See less
    • 0