Home/Database/Page 9
  1. Asked: June 14, 2016In:

    how to concatenate two string or two values in oracle sql ?

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

    It is ||, for example: select 'Mr ' || emp_name from employees; or concatenate two column values by : select first_name || '-' || last_namefrom employees;

    It is ||, for example:

    select 'Mr ' || emp_name from employees;

    or concatenate two column values by :

    select first_name || '-' || last_name
    from employees;
    See less
    • 0
  2. Asked: June 13, 2016In:

    How to create array variable in oracle pl/sql ?

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

    Basically VARRAY is varying array type that is typically used when the number of instances to be stored is small that means if we have one customer and this customer have number of phone numbers in our case if we know exact how many phone number he has so we will use VARRAY otherwise we will go forRead more

    Basically VARRAY is varying array type that is typically used when the number of instances to be stored is small that means if we have one customer and this customer have number of phone numbers in our case if we know exact how many phone number he has so we will use VARRAY otherwise we will go for using nested tables.
    You can use VARRAY data type for:

    1. A column in a relational table
    2. A PL/SQL variable
    3. A PL/SQL parameter of procedure or function
    4. A PL/SQL function return type
    5. A data attribute of an object type

    Now We can use VARRAY for a fixed-size array

    declare
   type array_t is varray(3) of varchar2(10);
   array array_t := array_t('Matt', 'Joanne', 'Robert');
begin
   for i in 1..array.count loop
       dbms_output.put_line(array(i));
   end loop;
end;

    Or TABLE for an unbounded array like:

    type array_t is table of varchar2(10);

    for clarification we submit question and it’s answer to help others, please if you have any addition don’t hesitate to share with others here.

    See less
    • 0
  3. Asked: June 11, 2016In:

    how to Order by string in SQL ?

    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant
    This answer was edited.

    In standard SQL, you need a case statement Select * From people ORDER BY (case when names = 'john' then 1 when names = 'steve' then 2 when names = 'bob' then 3 when names = 'bill' then 4 else 5 ); You can find this article helpful for you in case you are using Oracle database: Oracle Case StatementRead more

    In standard SQL, you need a case statement

    Select *
From people
ORDER BY (case when names = 'john' then 1
               when names = 'steve' then 2
               when names = 'bob' then 3
               when names = 'bill' then 4
          else 5
         );

    You can find this article helpful for you in case you are using Oracle database:

    Oracle Case Statement : (Simple Case, Searched Case)

    Some SQL engines have shortcuts to this, but you don’t mention which database you are using.
    For instance, in MySQL you can do:

    order by field(names, 'john', 'steve', 'bob', 'bill');
    See less
    • 0