Home/ isouravghosh/Followers Answers
  1. Asked: August 12, 2017In:

    How to open a PDF output directly when click of a SubmitButton in OAF ?

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

    Hi Waqas, it's applicable by using Standard API [code]fnd_webfile.get_url (file_type => fnd_webfile.request_out, -- for output file. Use request_log to view log file ID => l_request_id, gwyuid => l_gwyuid, two_task => l_two_task, expire_time => 500 -- minutes, security!. );[/code] notRead more

    Hi Waqas,

    it’s applicable by using Standard API

    [code]fnd_webfile.get_url
    (file_type => fnd_webfile.request_out,
    — for output file. Use request_log to view log file
    ID => l_request_id,
    gwyuid => l_gwyuid,
    two_task => l_two_task,
    expire_time => 500 — minutes, security!.
    );[/code]

    note : there are two profile options this API must take as following

    l_gwyuid : Gateway User ID

    [code]oadbtransactionimpl.getAppsContext().getEnvStore().getEnv(“GWYUID”)[/code]

    l_two_task: Two Task(TWO_TASK)

    [code]oadbtransactionimpl.getAppsContext().getEnvStore().getEnv(“TWO_TASK”)[/code]

    you can register out parameter of calling this API to String variable then use

    [code]pageContext.sendRedirect[/code]

     

    Hope this helpful 🙂

     

     

    See less
    • 0
  2. Asked: August 11, 2017In:

    How to Put session parameter on link oracle OAF ?

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

    Hello Aashish, There are three common means of passing parameters between pages : Request Transaction Session and you can use VO attributes for passing values Values stored in VO attributes are available in all pages within the transaction with same Root AM. URL Parameters : Encryption and EncodingRead more

    Hello Aashish,

    There are three common means of passing parameters between pages :
    Request
    Transaction
    Session

    and you can use VO attributes for passing values
    Values stored in VO attributes are available in all pages within the transaction with same Root AM.

    URL Parameters : Encryption and Encoding
    When we are passing parameters in URL, following need to be considered:

    [code]
    {@Attr} – encodes. Changes Prince Kapoor to Prince%20Kapoor
    {!Attr} – encrypts. Encrypts sensitive information.
    {$Attr} – plain token substitution (no encoding or encryption)
    {@@RETURN_TO_MENU} – Used for E-Business Suite Personal Home Page. Same as OAWebBeanConstants.RETURN_TO_MENU_URL.
    {@@RETURN_TO_PORTAL} – Return the user to a launching Portal page. Same as OAWebBeanConstants.RETURN_TO_PORTAL_URL.
    [/code]

    See less
    • 1
  3. Asked: June 23, 2016In:

    How to query a CLOB column in Oracle SQL ?

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

    Hi Albert, You can use DBMS_LOB.substr like this for example: DBMS_LOB.substr(column, 3000) but don't forget that substring of a CLOB column has size/buffer restrictions sometimes you would need to set the BUFFER to a larger size. For example while using SQL Plus use the SET BUFFER 10000 to set it tRead more

    Hi Albert,

    You can use DBMS_LOB.substr like this for example:

    DBMS_LOB.substr(column, 3000)

    but don’t forget that substring of a CLOB column has size/buffer restrictions sometimes you would need to set the BUFFER to a larger size.

    For example while using SQL Plus use the SET BUFFER 10000 to set it to 10000 as the default is 4000

    and you can refer to Oracle Substr Function Article for more information about the substr function

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

    Error ORA-00933: SQL command not properly ended – update

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

    hello, here you can find the cause and action to correct your statement : Cause: The SQL statement ends with an inappropriate clause. For example, an ORDER BY clause may have been included in a CREATE VIEW or INSERT statement. ORDER BY cannot be used to create an ordered view or to insert in a certaRead more

    hello,

    here you can find the cause and action to correct your statement :

    Cause: The SQL statement ends with an inappropriate clause. For example, an ORDER BY clause may have been included in a CREATE VIEW or INSERT statement. ORDER BY cannot be used to create an ordered view or to insert in a certain order.

    Action: Correct the syntax by removing the inappropriate clauses. It may be possible to duplicate the removed clause with another SQL statement. For example, to order the rows of a view, do so when querying the view and not when creating it. This error can also occur in SQL*Forms applications if a continuation line is indented. Check for indented lines and delete these spaces.

    you can use :

    [code]
    UPDATE employees e
    SET e.last_name = ‘test’
    WHERE e.department_id = (SELECT d.department_id
    FROM departments d
    WHERE d.department_id = e.department_id);
    [/code]

     

    See less
    • 0
  5. Asked: June 20, 2016In:

    Return more than one row from stored procedure in pl/sql

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

    hello aya, as you know the procedure you can't use inside query so if you need to create procedure to return multiple row or values you can use : create or replace procedure myprocedure(retval in out sys_refcursor) is begin open retval for select employee_id,last_name from employees; end myprocedureRead more

    hello aya,

    as you know the procedure you can’t use inside query so if you need to create procedure to return multiple row or values you can use :

    create or replace procedure myprocedure(retval in out sys_refcursor) is
    begin
    open retval for
    select employee_id,last_name from employees;
    end myprocedure;

    and here you can call procedure inside anonymous pl/sql block :

    [code]

    DECLARE
    myrefcur SYS_REFCURSOR;
    employee_id employees.employee_id%TYPE;
    last_name employees.last_name%TYPE;
    BEGIN
    myprocedure (myrefcur);

    LOOP
    FETCH myrefcur INTO employee_id,last_name;

    EXIT WHEN myrefcur%NOTFOUND;
    DBMS_OUTPUT.put_line (‘Employee Id: ‘ || employee_id || ‘ Last Name: ‘|| last_name);
    END LOOP;

    CLOSE myrefcur;
    END;
    [/code]

    hope this help.

    See less
    • -1
  6. Asked: July 30, 2017In:

    How to Create the TKPROF Trace File in oracle ?

    Mmorsy Junior mahmoudmorsymm1985@gmail.com

    Hello , Solution : For the TKPROF a) tkprof rawtrace.trc output_file explain=apps/apps/sort=(exeela,fchela) sys=no b) rawtrace.trc: Name of trace file output_file: tkprof out file explain: This option provides the explain plan for the SQL statements sort: This provides the sort criteria in which allRead more

    Hello ,

    Solution :

    • For the TKPROF
      a) tkprof rawtrace.trc output_file explain=apps/apps/sort=(exeela,fchela) sys=no
      b) rawtrace.trc: Name of trace file
      output_file: tkprof out file
      explain: This option provides the explain plan for the SQL statements
      sort: This provides the sort criteria in which all SQL statements will be sorted. This will bring the bad SQL at the top of the outputfile.
      sys=no: Disables SQL statements issued by user SYS

    Regards ,

    Mahmoud Morsy

    See less
    • 0
  7. Asked: July 31, 2017In:

    How to see lock on table and query?

    Mmorsy Junior mahmoudmorsymm1985@gmail.com

    Hi Mina , Try this query : [code] SELECT c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine FROM v$locked_object a, v$session b, dba_objects c WHERE b.sid = a.session_id AND a.object_id = c.object_id AND xidsqn != 0; [/code] Regards , Mahmoud Morsy

    Hi Mina ,

    Try this query :

    [code]
    SELECT c.owner,
    c.object_name,
    c.object_type,
    b.sid,
    b.serial#,
    b.status,
    b.osuser,
    b.machine
    FROM v$locked_object a, v$session b, dba_objects c
    WHERE b.sid = a.session_id AND a.object_id = c.object_id AND xidsqn != 0;
    [/code]

    Regards ,

    Mahmoud Morsy

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

    What is the difference between schema and user ?

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

    SCOTT is a schema that includes the EMP, DEPT and BONUS tables with various grants, and other stuff. SYS is a schema that includes tons of tables, views, grants, etc etc etc. SYSTEM is a schema.....   Technically -- A schema is the set of metadata (data dictionary) used by the database, typicalRead more

    SCOTT is a schema that includes the EMP, DEPT and BONUS tables with various grants, and other stuff.

    SYS is a schema that includes tons of tables, views, grants, etc etc etc.

    SYSTEM is a schema…..

     

    Technically — A schema is the set of metadata (data dictionary) used by the database, typically generated using DDL. A schema defines attributes of the database, such as tables, columns, and properties. A database schema is a description of the data in a database.

    See less
    • 0
  9. Asked: July 30, 2017In:

    I need query to get Apps_user_name , Date , SID , SERIAL#, oracle process , application process , Module , responsibility name in EBS R12.

    Mmorsy Junior mahmoudmorsymm1985@gmail.com

    Hello , The below query help you : select usr.user_name apps_username ,ses.osuser ,ses.machine ,i.first_connect "First Connect Date" ,ses.sid ,ses.serial# ,ses.module ,v.spid "Oracle Server Process" ,ses.process "Application Server Process" ,rsp.responsibility_name "Responsibility Name" ,null "RespoRead more

    Hello ,

    The below query help you :

    select usr.user_name apps_username
    ,ses.osuser
    ,ses.machine
    ,i.first_connect "First Connect Date"
    ,ses.sid
    ,ses.serial#
    ,ses.module
    ,v.spid "Oracle Server Process"
    ,ses.process "Application Server Process"
    ,rsp.responsibility_name "Responsibility Name"
    ,null "Responsibility Start Time"
    ,fuc.function_name "Function Name"
    ,i.function_type "Function Type"
    ,i.last_connect function_start_time
    from icx_sessions i
    ,fnd_logins l
    ,fnd_appl_sessions a
    ,fnd_user usr
    ,fnd_responsibility_tl rsp
    ,fnd_form_functions fuc
    ,gv$process v
    ,gv$session ses
    where i.disabled_flag = 'N'
    and i.login_id = l.login_id
    and l.end_time is null
    and i.user_id = usr.user_id
    and l.login_id = a.login_id
    and a.audsid = ses.audsid
    and l.pid = v.pid
    and l.serial# = v.serial#
    and i.responsibility_application_id = rsp.application_id(+)
    and i.responsibility_id = rsp.responsibility_id(+)
    and i.function_id = fuc.function_id(+)
    and i.responsibility_id not in (select t1.responsibility_id
    from fnd_login_responsibilities t1
    where t1.login_id = l.login_id)
    and rsp.language(+) = 'US'
    union
    select usr.user_name apps_username
    ,ses.osuser
    ,ses.machine
    ,l.start_time
    ,ses.sid
    ,ses.serial#
    ,ses.module
    ,v.spid
    ,ses.process
    ,rsp.responsibility_name
    ,r.start_time
    ,null
    ,null
    ,null function_start_time
    from fnd_logins l
    ,fnd_login_responsibilities r
    ,fnd_user usr
    ,fnd_responsibility_tl rsp
    ,gv$process v
    ,gv$session ses
    where l.end_time is null
    and l.user_id = usr.user_id
    and l.pid = v.pid
    and l.serial# = v.serial#
    and v.addr = ses.paddr
    and l.login_id = r.login_id(+)
    and r.end_time is null
    and r.responsibility_id = rsp.responsibility_id(+)
    and r.resp_appl_id = rsp.application_id(+)
    and rsp.language(+) = 'US'
    and r.audsid = ses.audsid
    union
    select usr.user_name
    ,ses.osuser
    ,ses.machine
    ,l.start_time
    ,ses.sid
    ,ses.serial#
    ,ses.module
    ,v.spid
    ,ses.process
    ,null
    ,null
    ,frm.user_form_name
    ,ff.type
    ,f.start_time function_start_time
    from fnd_logins l
    ,fnd_login_resp_forms f
    ,fnd_user usr
    ,fnd_form_tl frm
    ,fnd_form_functions ff
    ,gv$process v
    ,gv$session ses
    where l.end_time is null
    and l.user_id = usr.user_id
    and l.pid = v.pid
    and l.serial# = v.serial#
    and v.addr = ses.paddr
    and l.login_id = f.login_id(+)
    and f.end_time is null
    and f.form_id = frm.form_id(+)
    and f.form_appl_id = frm.application_id(+)
    and frm.language(+) = 'US'
    and f.audsid = ses.audsid
    and ff.form_id = frm.form_id
    order by function_start_time

    Regards,

    Mahmoud Morsy

    See less
    • 0
  10. Asked: July 30, 2017In:

    How to check Archive log sequence in Oracle DB 11g?

    Mmorsy Junior mahmoudmorsymm1985@gmail.com

    Hi, Use the below query : [code] SELECT * FROM v$archived_log ORDER BY stamp DESC [/code] Regards, Mahmoud Morsy

    Hi,

    Use the below query :

    [code]
    SELECT *
    FROM v$archived_log
    ORDER BY stamp DESC
    [/code]

    Regards,

    Mahmoud Morsy

    See less
    • 0