Home/Database/PL/SQL/Page 3
  1. Asked: June 21, 2016In:

    How to Send e-mail from PL/SQL ?

    Hassan AbdElrahman
    Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | 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
  2. Asked: June 14, 2016In:

    How to read excel file from oracle pl/sql ?

    Hassan AbdElrahman
    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | 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
  3. Asked: June 14, 2016In:

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

    Hassan AbdElrahman
    Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | 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
  4. Asked: June 13, 2016In:

    How to create array variable in oracle pl/sql ?

    Hassan AbdElrahman
    Hassan AbdElrahman Master Oracle ACE Pro Alum ♠ | 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