replace for stirng

variable:=REPLACE(PARM_ECLS_EXC,',',''',''');

 system flush::
 
alter system flush shared_pool;

DECODE::

decode(T_CONTRACT_NAME,null,decode(T_STREET_LINE1,null,'No Contract Name',T_STREET_LINE1),T_CONTRACT_NAME ) decode,

Ref CUrsor Example

I ref cursor is a variable that holds a cursor. Underneath, it is a "regular" cursor. It is a pointer to a cursor. 

Normally a cursor is a static thing, it points to A query: 


declare 
c1 cursor for select * from emp; 
.... 


It is clear that C1 will run that query and none other. A ref cursor can have an arbitrary query associated with it: 

declare 
type rc is ref cursor; 
c1 rc; 
begin 
if ( a = b ) then 
open c1 for select * from emp; 
else 
open c1 for select * from dept; 
end if;

How to use parameters in a 'where value in…' clause? (integer)

DECLARE
EX varchar2(100):='10,20,01,02,03,04';
param1 varchar2(100):='vishnu';
param2 varchar2(100):='teja';
retval BOOLEAN;
BEGIN
retval:=F_DEMO(EX);
END;

//////////////////////////////////////////////////
CREATE OR REPLACE FUNCTION F_DEMO(EX VARCHAR2)
RETURN BOOLEAN
IS


  l_rc       sys_refcursor;
  l_tbbestu_rec tbbestu%rowtype;
  begin
   open l_rc for 'SELECT  *
FROM tbbestu
WHERE   xxxxxxxx =('|| param1||')
and yyyyyyyy=('|| param2||')
 tbbestu_exemption_code  IN(' || EX || ')';
   loop
    fetch l_rc into l_tbbestu_rec;
     exit when l_rc%notfound;
     dbms_output.put_line( l_tbbestu_rec.tbbestu_exemption_code );
   end loop;
  close l_rc;
  return true;
 end F_DEMO;

\\ 

How to use parameters in a 'where value in…' clause? (Char)

CREATE OR REPLACE FUNCTION F_DEMO(pprccmt_cmty_code0 VARCHAR2,stu_pidm VARCHAR2)
RETURN BOOLEAN
IS
QUERY24       sys_refcursor;
l_pprccmt_rec pprccmt%rowtype; 
BEGIN
dbms_output.put_line( stu_pidm||'  '||pprccmt_cmty_code0);
OPEN QUERY24 FOR 'select
*
from payroll.pprccmt
where pprccmt_pidm = '|| stu_pidm ||'
AND pprccmt_cmty_code IN ('|| pprccmt_cmty_code0 ||')';
LOOP
  FETCH QUERY24 INTO l_pprccmt_rec;
  EXIT WHEN QUERY24%notfound;
  dbms_output.put_line( l_pprccmt_rec.pprccmt_cmty_code||'  '||l_pprccmt_rec.pprccmt_pidm);
END LOOP;
close QUERY24;
return true;
end F_DEMO;
//////////////////////////////
DECLARE
pprccmt_cmty_code varchar2(100):='''TRS'',''HD4'',''ORH''';
stu_pidm VARCHAR2(100):='5564';
retval BOOLEAN;
BEGIN
retval:=F_DEMO(pprccmt_cmty_code,stu_pidm);
END;

SELECT
  CASE
    WHEN level = 1
    THEN '01'
    WHEN level = 2
    THEN '02'
    WHEN level = 3
    THEN '04'
    WHEN level = 4
    THEN '05'
  END TEST
FROM dual
  CONNECT BY level <= 4;

How to increase dbms_output buffer?

 

dbms_output.enable(buffer_size IN INTEGER DEFAULT 20000);
exec dbms_output.enable(1000000);

ODI Creating Projects and Models 03:

https://www.youtube.com/watch?v=kR8zjsT35AU

Topology Dataserver, Physical and Logical Schema creation in ODI www.oditraining.com

https://www.youtube.com/watch?v=ejtgeygPgV4

Creating Master Repository Wizard ::

http://dwteam.in/creating-repositories-in-odi-11-1-1-5/

BI Publisher Guides

1. https://docs.oracle.com/cd/E28280_01/bi.1111/e22258/create_data_sets.htm#BIPDM149

XML Publisher Tag Components

1. https://docs.oracle.com/cd/E28280_01/bi.1111/e22254/create_rtf_tmpl.htm#BIPRD2391 
2. http://oracleapps88.blogspot.com/2012/05/xml-tags.html

IF Condition:

<?if:P_PM_YN=’N’?> Yes <?end if?>


IF ELSE Condition:

Method 1:

<?xdofx:if element_condition then result1 else result2 end if?>

Example:

<?xdofx:if INVOICE_AMOUNT > 5000 then ’Higher’
else
if INVOICE_AMOUNT <3000 then ’Lower’
else
’Equal’
end if?>

Method 2:
Syntax:

<?xdoxslt:ifelse(condition,true,false)?>

Example: 

<?xdoxslt:ifelse(20=21,’yes 20 and 21 are equal’,’No 20 and 21 are not equal’)?>

Ans: No 20 and 21 are not equal

Using OR Condition in XML RTF Template:

Syntax:

<?if:XMLfield=value1 or XMLfield=value2?> display value <?end if?>

Example:

<?if:sum(AVALUE)=0 or sum(BVALUE)=0?>0<?end if?>

You can use whichever is applicable to your requirement.

 -------------------------------------------------------------------------------------------
 VALUE-OF
This element is used to retrieve a value from a specified Element or Attribute. For example, if you used this element specifying ‘/DATA/DEPARTMENT/NAME’ then you can retrieve a value that is presented in the NAME element in the XML document.

With BI Publisher tags, you can just type the following to do the same.

<?/DATA/DEPARTMENT/NAME?>

 --------------------------------------------------------------------------------------------

FOR-EACH

With BI Publisher tags, you can type the following to do the same.

<?for-each:/DATA/DEPARTMENT?>
  <?NAME?>
<?end for-each?>

------------------------------------------------------------------------------------------------------------------------------------------------------------

XSL:SORT
Inside the previous ‘for-each’ loop you might want to sort the data by alphabetically or based on the ID, etc. You can use this ‘sort’ element to do the sorting.

With BI Publisher you can do the following to achieve the same. 

  <?sort:NAME;’ascending’;data-type=’text’?>

---------------------------------------------------------------------------------------------------------------------------------------------------------------- 

IF
You can use this element to have a condition in the XSL transformation logic. This is also pretty much the same as other programming language’s ‘if’ condition. For example, if you want to display manager name only when Department name is ‘Consulting’ you can specify something like the below.

You can do the same with BI Publisher tags as follows.

<?for-each:/DATA/DEPARTMENT?>
  <?if:NAME=’Consulting’?>
    <?NAME?>
  <?end if?>
<?end for-each?>

---------------------------------------------------------------------------------------------
 CHOOSE
As an alternative or for better reasons you can also use CHOOSE/WHEN elements to do the condition. One thing to note is that XSL doesn’t support IF/ELESE condition as native, so if you have multiple conditions to use together in a form of IF/ELSE then CHOOSE/WHEN/OTHERWISE elements would serve you better.

With BI Publisher you can do the below to have the same condition. 

<?for-each:/DATA/DEPARTMENT?>
  <?choose:?>
  <?when:NAME=’Consulting’>
    <?MANAGER_NAME?>
    <?DEPARTMENT_NAME?>
  <?end when?>
  <?otherwise:?>
    <?DEPARTMENT_NAME?>
  <?end otherwise?>
  <?end choose?>
<?end for-each?>

-------------------------------------------------------------------------------------------


 

Oracle Data Integrator 12c Installations and Configuration in Windows 7 (64x)

ODI 12.2.1.2.0 Enterprise Installation
----------------------------------------------
https://www.youtube.com/watch?v=L5NAkvzEIEM