Search Results


Thursday, February 26, 2015

Setting Bind Variables in ADF

In this post we will see some of the ways to programmatically set bind variables in ADF ViewObject.


Setting Value to Bind Variable


The following code sets the value for the the bind variable defined in the whereClause of the ViewObject.

ViewObject viewobj = am.findViewObject(“OrdersVO”);
viewobj.setNamedWhereClauseParam(“BndOrderNumber”, 1001);
viewobj.executeQuery();


Bind Variable in View Criteria


But most often we use ViewCriterias to define the whereClause of the viewObject so that the ViewObject definition could be reused in multiple screens. The following code sets the value for the bind variable defined in the whereClause.

ViewObject vo = getOrdersVO();
ViewCriteria vc = vo.getViewCriteriaManager().getViewCriteria("OrdersViewCriteria");
vc.resetCriteria();
vo.ensureVariableManager().setVariableValue(“BndOrderNumber”, order_number );
vo.applyViewCriteria(vc);
vo.executeQuery();


Dynamic Bind Variable


In some scenarios we would have to set whereClause dynamically. In this case we could create a bind variable on the viewObject and then set its value.

viewobj.setWhereClause(“order_no = :BndOrderNumber”);
viewobj.defineNamedWhereClauseParam(“BndOrderNumber”,null ,null);
viewobj.setNamedWhereClauseParam(“BndOrderNumber”,234)
viewobj.executeQuery();






SQL Notes and Tips

SQL Tips and Notes

Some SQL queries and useful sql statements for ADF/SOA Developers or at least I use them so often.

Database size


Find out table sizes
select table_name,(num_rows*avg_row_len)/(1024*1024) MB from user_tables order by 2 desc;

Gather Schema status
EXEC DBMS_STATS.gather_schema_stats('DEV_SOAINFRA');

Find out the schema sizes
SELECT *
FROM
( SELECT
   c.tablespace_name,
   ROUND(a.bytes/1048576,2)                    MB_Allocated,
   ROUND(b.bytes/1048576,2)                    MB_Free,
   ROUND((a.bytes-b.bytes)/1048576,2)          MB_Used,
   ROUND(b.bytes/a.bytes * 100,2)              tot_Pct_Free,
   ROUND((a.bytes-b.bytes)/a.bytes,2) * 100    tot_Pct_Used
 FROM
   ( SELECT
       tablespace_name,
       SUM(a.bytes) bytes
     FROM
       sys.DBA_DATA_FILES a
     GROUP BY
       tablespace_name
   ) a,
   ( SELECT
       a.tablespace_name,
       NVL(SUM(b.bytes),0) bytes
     FROM
       sys.DBA_DATA_FILES a,
       sys.DBA_FREE_SPACE b
     WHERE
       a.tablespace_name = b.tablespace_name (+)
       AND a.file_id = b.file_id (+)
     GROUP BY
       a.tablespace_name
   ) b,
   sys.DBA_TABLESPACES c
 WHERE
   a.tablespace_name = b.tablespace_name(+)
   AND a.tablespace_name = c.tablespace_name
 )
WHERE
 tot_Pct_Used >=0
ORDER BY
 tablespace_name;


Database Back and Restore


Cold Backup
Find out files:
select name from sys.v_$datafile;
select member from sys.v_$logfile;
select name from sys.v_$controlfile;

Shutdown the Database
alter system checkpoint;
shutdown abort
startup restrict
shutdown immediate


Change DB password

select username, account_status from dba_users;
alter user dev_soainfra identified by welcome1;
alter user test2_soainfra account unlock;
ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED;

change password for all expired users

set hea off
select 'alter user '||username||' identified by welcome1;' from dba_users where account_status like 'EXPIRED%';
select 'alter user '||username||' account unlock;' from dba_users where account_status like 'LOCK%';

-- To find out the profiles
select profile, username from dba_users;

-- To find out LOGIN ATTEMPT LIMIT for the profiles
select LIMIT, RESOURCE_NAME from dba_profiles
where PROFILE = (select profile
                 from dba_users
                where username = 'DEV_SOAINFRA');

Logging in SQL

drop table test_log;
create table test_log (a number(10), b varchar2(4000));
create sequence test_seq;

create or replace PROCEDURE test_logger(p_text IN varchar2)
as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
 insert into test_log values (test_seq.nextval, p_text);
 commit;
END;
/

CALL TEST_LOGGER('HELLO');
SELECT * FROM TEST_LOG ORDER BY A DESC;
TRUNCATE TABLE TEST_LOG;

Redo Log generation rate

SELECT TRUNC(NEXT_TIME,'HH24'),ROUND(SUM(BLOCKS*BLOCK_SIZE/1024/1024/1024),2) REDO_GB
FROM GV$ARCHIVED_LOG
where next_time > sysdate -4
GROUP BY TRUNC(NEXT_TIME,'HH24')
ORDER BY TRUNC(NEXT_TIME,'HH24');

Creating procedure objects to use with SOA

CREATE TYPE security_challenge_obj
AS
 OBJECT
 (user_name varchar2(254),
  max_val_attempts number(3),
  val_attempts_period varchar2(3),
  activity_type varchar2(30));   
 /

set serveroutput on;

DECLARE
 P_SECURITY_CHALLENGE AR_SOA.SECURITY_CHALLENGE_OBJ;
 P_PERMIT_VALIDATION VARCHAR2(200);
 P_STATUS VARCHAR2(200);
 P_ERROR_MESSAGE VARCHAR2(200);
 p_count number;
BEGIN
 -- Modify the code to initialize the variable
 p_security_challenge := ar_soa.SECURITY_CHALLENGE_OBJ(null,null,null,null);
 P_SECURITY_CHALLENGE.user_name := 'helasdfasdfasdfasdfasdfasdfasdfasdflo';
 P_SECURITY_CHALLENGE.max_val_attempts := '3';
 P_SECURITY_CHALLENGE.val_attempts_period := '24';
 P_SECURITY_CHALLENGE.activity_type := 'accounter';

  USER_VALIDATION_PKG.CHECK_VALIDATION_ATTEMPTS(
   P_SECURITY_CHALLENGE => P_SECURITY_CHALLENGE,
   P_PERMIT_VALIDATION => P_PERMIT_VALIDATION,
   p_validation_count => p_count

  );

  --:P_PERMIT_VALIDATION := P_PERMIT_VALIDATION;

DBMS_OUTPUT.PUT_LINE('P_STATUS = ' || P_STATUS);

  --:P_STATUS := P_STATUS;

DBMS_OUTPUT.PUT_LINE('P_ERROR_MESSAGE = ' || P_ERROR_MESSAGE);
DBMS_OUTPUT.PUT_LINE('P_count = ' || p_count);
DBMS_OUTPUT.PUT_LINE('P_PERMIT_VALIDATION  = ' || P_PERMIT_VALIDATION);

--  :P_ERROR_MESSAGE := P_ERROR_MESSAGE;
END;
/

Find and Kill Table Locks

SELECT s.username dbuser,owner || '.' || object_name "Object",
s.osuser,
s.PROGRAM,
lk.sid,
lk.type lock_type,
decode(lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', to_char(lk.lmode)) lockmode,
decode(request, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', to_char(lk.request)) requestmode
FROM v$lock lk,
v$session s,
dba_objects ao
WHERE lk.lmode > 1
AND object_name LIKE 'USER%'
AND s.username IS NOT NULL
AND lk.sid = s.sid
AND ao.object_id(+) = lk.id1;

select session_id from dba_dml_locks where name = 'USER_REMINDER_STATUS' ;

select sid, serial# from v$session where sid in ( 9);

alter system kill session '9,10679';

Monitor JDBC connections

select
   inst_id,
   machine,
   program,
   username,
   osuser,
   failed_over,
   to_char(logon_time,'DD/MM/YYYY HH:MI'),
   count(*) COUNT
from gv$session
where username IN ('PERF_SOAINFRA','AR_SOA_RW','PERF_MDS')
group by inst_id,
   machine,
   program,
   username,
   osuser,
   failed_over,
   to_char(logon_time,'DD/MM/YYYY HH:MI')
order by inst_id,
   machine,
   program,
   username,
   osuser,
   failed_over,
   to_char(logon_time,'DD/MM/YYYY HH:MI')

  INST_ID MACHINE                   PROGRAM                        USERNAME        OSUSER          FAI  COUNT
---------- ------------------------- ------------------------------ --------------- --------------- --- ------
        1 fang                      JDBC Thin Client               AR_SOA_RW       oracle          NO       2
        1 feral.ref.london.02.net   JDBC Thin Client               AR_SOA_RW       oracle          NO       2
        2 fang                      JDBC Thin Client               AR_SOA_RW       oracle          NO       2
        2 feral.ref.london.02.net   JDBC Thin Client               AR_SOA_RW       oracle          NO       2

         1 fang                      JDBC Thin Client               PERF_SOAINFRA   oracle          NO      29
        1 feral.ref.london.02.net   JDBC Thin Client               PERF_SOAINFRA   oracle          NO      29
        2 fang                      JDBC Thin Client               PERF_SOAINFRA   oracle          NO      28
        2 feral.ref.london.02.net   JDBC Thin Client               PERF_SOAINFRA   oracle          NO      29


Sleep Function using Java


CREATE OR REPLACE PROCEDURE Sleep_fn(sec1 NUMBER)
AS LANGUAGE JAVA
NAME 'java.lang.Thread.sleep(long)';
/

Find Database processes limit

select resource_name, current_utilization, max_utilization, limit_value
   from v$resource_limit
   where resource_name in ('sessions', 'processes');


Purge AQ table


DECLARE
 po_t dbms_aqadm.aq$_purge_options_t;
BEGIN
  dbms_aqadm.purge_queue_table('XXCUST_TABLE', NULL, po_t);
END;
/

Monday, February 23, 2015

How to get current userId in ADF

How to get username from ADF Bc - EOs and VOs

The current username can be set to the default expressions and BindVariables using the groovy expressions like this

adf.context.securityContext.getUserPrincipal().getName()
or
adf.context.securityContext.getUserName()

Another way to get User ID in Application Module 

String userName = this.getUserPrincipalName();

How to get Logged in User Roles  in Application Module ?

List userRoles = Arrays.asList(ADFContext.getCurrent().getSecurityContext().getUserRoles());


How to get username from ADF Managed Beans


SecurityContext object in ADF can be used to get the current usename from the managed beans and business components implementation classes (AMImpl, VOImpl, RowImpl etc).

    ADFContext adfCtx = ADFContext.getCurrent();
    SecurityContext secCntx = adfCtx.getSecurityContext();
    String user = secCntx.getUserPrincipal().getName();
    String _user = secCntx.getUserName();

How to get current user using EL Expressions on the pages

SecurityContext object can be accessed from EL expressions on pages as follows

#{securityContext.userName}