Create table from Excel file by SQL developer

Create table from Excel file by SQL developer

Oracle schedule job status and log

Oracle schedule job status and log


select * from ALL_SCHEDULER_JOB_RUN_DETAILS order by log_date desc;

select * from ALL_SCHEDULER_JOBS

SELECT VALUE
FROM V$PARAMETER
WHERE NAME = '';

SELECT *
FROM DBA_SCHEDULER_JOB_RUN_DETAILS where job_name=''
ORDER BY LOG_DATE DESC;

APEX Interactive Report Checkbox Row Selection

Back in page designer click the Employees > Attributes in the rendering tree. Set Link Column to Exclude Link Column. Set Maximum Rows Per Page to 5 to make it easier to test that selection works across pages.

Add the page item that will hold the list of selected employees. Its value will be a string of bar (|) separated EMPNO values. From the item gallery drag a hidden item to the Employees region. Notice that the hidden item is not shown. This is to be expected because hidden items don’t take up any space in the layout grid. Look in the rendering tree and you will see it there. Select it and change the name to P1_SELECTED. Then add P1_SELECTED to the Interactive Report Page Items to Submit.

Next change the Interactive Report SQL Query to include a selected column. This will be used to determine if the checkbox should be checked initially.

select EMPNO,
    case when instr(:P1_SELECTED, '|' || EMPNO || '|') > 0 then 
      'checked' 
    else 
      '' 
    end as selected,
    ENAME,
    JOB,
    MGR,
    HIREDATE,
    SAL,
    COMM,
    DEPTNO
  from EMP

Expand the Columns node in the rendering tree and select the SELECTED column. Set the Type to Hidden Column.

We will use the EMPNO column as the checkbox column. Select it in the rendering tree. For the Heading property enter:

<input type="checkbox" value="all">

Set the Column Alignment to center. For the HTML Expression property enter:

<input type="checkbox" #SELECTED# value="#FILE_ID#" name="f40" id="f40_#FILE_ID#">

Create a Page Processing process with the following settings:

• Type: PL/SQL
• Name: Set ADMIN_USER flag
• Sequence: 0 (Important: Has to be before all other processes)
• Point: On Submit – Before Computation and Validations
• Enter PL/SQL Page Process:

  BEGIN
      -- Reset the hidden ADMIN_USER flag for all visible records to N
      -- Note: g_f04 maps to the hidden ADMIN_USER column
     
  
      -- Set the hidden ADMIN_USER flag for those records where the
      -- checkbox has been set by the user to Y
     
      FOR ii IN 1 .. APEX_Application.g_f40.COUNT
      LOOP
          APEX_Application.g_f40(ii);
      END LOOP;
  END;

more

http://hardlikesoftware.com/weblog/2015/07/24/apex-interactive-report-checkbox-row-selection/

http://www.inside-oracle-apex.com/checkboxes-in-tabular-forms-the-easy-way/

Oracle Apex Interactive Grids APEX$ROW_SELECTOR APEX$ROW_STATUS

Oracle Apex Interactive Grids APEX$ROW_SELECTOR APEX$ROW_STATUS

begin

case :APEX$ROW_STATUS   --- I insert, U update, D delete , C
    when 'U' then
         insert into debug values (1);
  end;


end;



more


https://jeffkemponoracle.com/2016/06/interactive-grid-apex-5-1-ea/

https://community.oracle.com/message/13309213#13309213

Oracle Apex image issue after upgraded to 5.1

Apex image issue after upgrade to 5.1

Issue: After upgraded , can not use the old path   #WORKSPACE_IMAGES#Logo.png  or #APP_IMAGES#Logo.png

We can image directly into Tomcat server ORDS
/u01/app/oracle/product/ords/conf/ords/standalone/doc_root/i


Then from Oracle express application  we can use path /i/Logo.png

Hope it help!

Truncate table of another schema user

From schema A we need to call a procedure of B to truncate table of B


create or replace PROCEDURE DO_TRUNCATE_B AS
BEGIN

execute immediate 'truncate table B';

END DO_TRUNCATE_B;
/

grant execute on DO_TRUNCATE_B to A;
/

DBMS_SCHEDULER.run_job in PLSQL

begin


DBMS_LOCK.SLEEP(1);

DBMS_SCHEDULER.run_job(job_name =>'"DBschemaname"."run_upload_batch_program"');

DBMS_LOCK.SLEEP(1);

end;