Home/ AhmedRagab/Followers Answers
  1. Asked: August 14, 2016In:

    How can i convert CLOBS TO VARCHAR2 column ?

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

    hello Beter, you can use dbms_lob.substr( clob_column, for_how_many_bytes, from_which_byte ); and here basic two example : first by using SQL but note the max length is 4000 characters: select dbms_lob.substr( clob_col, 4000, 1 ) from Tablename; second by using PL/SQL max length is 32000 charactersRead more

    hello Beter,
    you can use

    dbms_lob.substr( clob_column, for_how_many_bytes, from_which_byte );

    and here basic two example :
    first by using SQL but note the max length is 4000 characters:

    select dbms_lob.substr( clob_col, 4000, 1 ) from Tablename;

    second by using PL/SQL max length is 32000 characters :

    DECLARE
  var1   LONG;
BEGIN
  FOR i IN (SELECT clob_col FROM tablename)
  LOOP
    var1 := dbms_lob.substr (i.clob_col, 32000, 1);
  END LOOP;
END;

    hope this may help.
    you can refer to DBMS_LOB from oracle

    See less
    • 0
  2. Asked: August 13, 2016In:

    Load image

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

    firstly, try to read more about the webutil library.second, find below a basic code snippet to do the job: --this code to open uploading dialog box V_CLIENT_PATH := CLIENT_GET_FILE_NAME(DIRECTORY_NAME => NULL ,FILE_NAME => NULL ,FILE_FILTER => 'ALL FILES (*.*)|*.*' ,MESSAGE => 'Open a fiRead more

    firstly, try to read more about the webutil library.
    second, find below a basic code snippet to do the job:

    --this code to open uploading dialog box

    V_CLIENT_PATH := CLIENT_GET_FILE_NAME(DIRECTORY_NAME => NULL
                                 ,FILE_NAME    => NULL
                                 ,FILE_FILTER  => 'ALL FILES (*.*)|*.*'
                                 ,MESSAGE      => 'Open a file'
                                 ,DIALOG_TYPE  => NULL
                                 ,SELECT_FILE  => NULL);

    -- create directory on local machine at client server
    HOST ( 'cmd /c mkdir'||' "'|| 'C:tempapplication' || '"',NO_SCREEN); 

-- this code to show uploading progress to user
    V_SUCCESS := 
WEBUTIL_FILE_TRANSFER.CLIENT_TO_AS_WITH_PROGRESS
    (CLIENTFILE       => V_CLIENT_PATH
    ,SERVERFILE       => V_SERVER_PATH
    ,PROGRESSTITLE    => 'Uploading file in progress'
    ,PROGRESSSUBTITLE => 'Please wait'
   ,ASYNCHRONOUS     => FALSE
   ,CALLBACKTRIGGER  => NULL);

    hope this may help 🙂

    See less
    • 0
  3. Asked: July 17, 2016In:

    How to add ' "single quotes" for string using Java?

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

    hello beter, basically if you want to add single code to any string you can use : /' "any string you want here" /' and if you want to use it inside sql statement hence you are building VO at run time (onthefly :)) so you can write : SELECT column1 FROM table WHERE column2 = + "'"+ Vstr+"'"; where VsRead more

    hello beter,
    basically if you want to add single code to any string you can use :

    /' "any string you want here" /'

    and if you want to use it inside sql statement hence you are building VO at run time (onthefly :)) so you can write :

    SELECT column1 FROM table WHERE column2 = + "'"+ Vstr+"'";

    where Vstr is your String variable in your example.
     
    hope this helpful 🙂
    share today to gain tomorrow.

    See less
    • 0
  4. Asked: July 11, 2016In:

    How to pass date parameter to oracle report builder ?

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

    hello albert,  your code is correct can you write what is the error exactly to help you. in addition you can check your user parameter  in your report builder property specifically these properties mentioned in below image

    hello albert,
     your code is correct can you write what is the error exactly to help you.
    in addition you can check your user parameter  in your report builder property specifically these properties mentioned in below image
    pass date parameter to report builder

    See less
    • 0
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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