Home/ Gomaa Elmokhtar/Followers Answers
  1. Asked: June 21, 2016In:

    How to Send e-mail from PL/SQL ?

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

    hi there,starting from oracle 8i we are eligible to send emails directly from PL/SQL by using either UTL_SMTP or UTL_TCP packages. However, oracle provides us valuable package called UTL_MAIL for sending emails in Oracle 10g.here is example of how to use UTL_MAIL to send emails from PL/SQL : BEGIN ERead more

    hi there,

    starting from oracle 8i we are eligible to send emails directly from PL/SQL by using either UTL_SMTP or UTL_TCP packages. However, oracle provides us valuable package called UTL_MAIL for sending emails in Oracle 10g.

    here is example of how to use UTL_MAIL to send emails from PL/SQL :

    BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
  UTL_MAIL.send(sender => 'me@address.com',
            recipients => 'you@address.com',
               subject => 'Test Mail',
               message => 'Hello World',
             mime_type => 'text; charset=us-ascii');
END;

    Note : for security purpose, UTL_MAIL is not enabled by default. so you should enable it by connecting to SYS user and executing the utlmail.sql and prvtmail.plb scripts in the $ORACLE_HOME/RDBMS/admin directory. In addition, you must configure an initialization parameter, SMTP_OUT_SERVER, to point to an outgoing SMTP server.
    in addition, you must give EXECUTE permission to PUBLIC by run below script :

    grant execute on UTL_MAIL to public

    hope this helpful 🙂
    share with others to stretch our experiences

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

    How to read excel file from oracle pl/sql ?

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

    first thing data must be saved as extension .CSV not .xls second we need to create directory and give (EXECUTE, READ, WRITE) privilege to appropriate user which will use this directory later into procedure we will create create directory syntax CREATE OR REPLACE DIRECTORY TEMP_DIR AS 'C:temp'; GRANTRead more

    first thing data must be saved as extension .CSV not .xls
    second we need to create directory and give (EXECUTE, READ, WRITE) privilege to appropriate user which will use this directory later into procedure we will create
    create directory syntax

    CREATE OR REPLACE DIRECTORY 
TEMP_DIR AS 
'C:temp';
GRANT EXECUTE, READ, WRITE ON DIRECTORY TEMP_DIR TO USER WITH GRANT OPTION;

    Note : “C:temp” this statement to create directory on windows server it will be little deference from Linux server.
    third thing you need to create stage table which we will insert data into it by mapping excel column to be the same with columns name :

    CREATE TABLE DOCARCH.TEST
(
  TYPE_ID       NUMBER
)

    and now it’s time to read the data inside csv file and inserted to stage table by this procedure

    create or replace procedure imp_proc_test
is
  file_handle   utl_file.file_type := UTL_FILE.FOPEN('TEMP_DIR','imptestdata.csv','R',5000);
  var1          test.TYPE_ID%type;
  v_string      varchar2 (1000);
begin
    loop
        begin
        UTL_FILE.get_line(file_handle,v_string);
        var1 := substr(v_string,1,instr(v_string,',',1,1)-1);
        insert into test (TYPE_ID) values (var1);
        exception
          when no_data_found then
              UTL_FILE.fclose (file_handle);
              exit;
           WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE(sqlcode||sqlerrm);
        end;
    end loop;
    commit;
end;

    so now after created this procedure just call it by :

    exec imp_proc_test

    at the final there is too many ways to achieve this requirement but we pick easiest one for you so please if you have any addition just share with others here.

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

    what is FNDLOAD commands to download and upload forms personalization ?

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

    FNDLOAD command for downloading personalization in form : FNDLOAD user/pass 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct PO_POXSCERQ_PERSONALIZ.ldt FND_FORM_CUSTOM_RULES function_name="PO_POXSCERQ" user : this your instance user name pass : this your instance password PO_POXSCERQ_PERSONALIZ.lRead more

    FNDLOAD command for downloading personalization in form :

    FNDLOAD user/pass 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct PO_POXSCERQ_PERSONALIZ.ldt FND_FORM_CUSTOM_RULES function_name="PO_POXSCERQ"

    user : this your instance user name
    pass : this your instance password
    PO_POXSCERQ_PERSONALIZ.ldt : this generated file after download rename appropriate name
    PO_POXSCERQ : this function name which you want to download all personalization from.

    just after you run above command LDT file will generated in home directory just take this LDT file and copy it to another home instance then

    FNDLOAD command for uploading personalization in form :

    FNDLOAD user/pass 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct PO_POXSCERQ_PERSONALIZ.ldt

    hope this helpful 🙂

    See less
    • 0
  4. 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
  5. 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
  6. Asked: June 14, 2016In:

    how can i use one cursor inside two or more procedure in oracle pl/sql?

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

    The only way to achieve this is trying to declare that cursor in the package level then you can use it in any procedure or function in that package and the logic behind that is any cursor delcared in any function or procedure the scope for this cursor is only in that function below you can find sampRead more

    The only way to achieve this is trying to declare that cursor in the package level then you can use it in any procedure or function in that package and the logic behind that is any cursor delcared in any function or procedure the scope for this cursor is only in that function below you can find sample example :

    package body my_pkg is

       cursor test_cur is select * from table;

       procedure proc1 is 
       begin
          open test_cur;
          ...
          close test_cur;
       end proc1;

       procedure proc2 is 
       begin
          open test_cur;
          ...
          close test_cur;
       end proc2;
    end;


    Note: keep in mind in that example below if you open declared cursor in procedure 1 and you didn’t close it if you trying to open the same cursor in the procedure 2 you will get an exception raising

    See less
    • 0
  7. 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
  8. Asked: June 15, 2016In:

    How to check whether partitioning enabled or not in my database ?

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

    we need to execute this select statement to check select * from v$option where parameter = 'Partitioning'; PARAMETER VALUE Partitioning TRUE

    we need to execute this select statement to check

    select * from v$option where parameter = 'Partitioning';
    PARAMETER VALUE
    Partitioning TRUE
    See less
    • 0
  9. 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
  10. 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