Useful Linux Commands for oracle applications

Changing directory (cd) with variables

To resolve the environment variables (e.g. $XXX_TOP/test) passed as concurrent program argument to Host script


dirpath="$(echo $8)"
eval cd "$dirpath"
echo pwd


or to resolve path using eval

dirpath="$(echo $8)"
dirpath1=$(eval echo $dirpath)
echo "dirpath --> $dirpath1"


Call Pl-Sql procedure from shell script with out variable

 SQLVAR=`sqlplus -s $APPS_USER_ID <<ENDOFSQL  
 SET SERVEROUTPUT ON SIZE 1000000  
 whenever sqlerror exit 1;  
 declare  
 v_errorlog VARCHAR2(4000);  
 begin  
 pkg.prc(p_arg1 => $arg1, p_arg2 => $arg2, p_error_out => v_errorlog);    
 dbms_output.put_line('v_errorlog  --> '||v_errorlog);  
 end;  
 /  
 exit;  
 ENDOFSQL`  
 echo " SQLVAR output is - $SQLVAR"  

For only package call

 SQLVAR=`sqlplus -s $APPS_USER_ID <<ENDOFSQL  
 set heading off newpage 0 pagesize 0;  
 exec pkg.prc(p_arg1 => $arg1, p_arg2 => $arg2);  
 exit;  
 ENDOFSQL`  
 echo " SQLVAR output is - $SQLVAR"       

To Cut a string from the end by identifier
Cut string from the end by identifier "/" eg: /abc/def/fdf/abc.dat

OUTPUT='/abc/def/fdf/abc.dat'

 l1=$(echo $OUTPUT | awk -F"/" '{print length($0)-length($NF)}')  
 l2=`expr $l1 + 1`  
 echo "l2 $l2"  
 l3=$(echo $OUTPUT | wc -m)  
 echo "l3 $l3"  
 filename=$(echo $OUTPUT | cut -b $l2-$l3)  
 echo "filename --> $filename"  

Joining multiple queries in Data template XML Publisher

Joining multiple queries in XML Publisher can be done by 2 methods

1) Query Link for below dataQuery

 <link name="LINK1" parentQuery="Q1" parentColumn="VALUE1" childQuery="Q2" childColumn="COLUMN1"/>  
 <link name="LINK2" parentQuery="Q2" parentColumn="COLUMN1" childQuery="Q3" childColumn="COLUMN6"/>  

2) Column link between multiple queries
 Multiple queries can be linked in XML Data Template by passing the column from parent query as parameter to the child sqlStatement. This approach is recommended over using Query link and there is no limit for number of queries to be linked.

 <dataQuery>  
   <sqlStatement name="Q1">  
   <![CDATA[  
           SELECT XT1.VALUE1 from   
                XX_TABLE1 XT1  
                WHERE 1=1  
                     AND XT1.COLUMN1 = :PARAMETER1 <your parameter is optionalter>  
   ]]>  
   </sqlStatement>  
   <sqlStatement name="Q2">  
   <![CDATA[  
           SELECT XT2.COLUMN1, XT2.COLUMN2 from   
                XX_TABLE2 XT2  
                WHERE 1=1  
                     AND XT2.COLUMN1 = :VALUE1  
   ]]>  
   </sqlStatement>       
   <sqlStatement name="Q3">  
   <![CDATA[  
           SELECT XT3.COLUMN5, XT3.COLUMN6 from   
                XX_TABLE3 XT3  
                WHERE 1=1  
                     AND XT3.COLUMN6 = :COLUMN1  
   ]]>  
   </sqlStatement>  
 </dataQuery>       

The same can be represented in datastructure of XML Data template.

 <dataStructure>  
   <group name="G_PARENT"  source="Q1">  
           <element name="VALUE1"                value="VALUE1"/>       
           <group name="G_HDR_DETAILS"  source="Q2">  
                <element name="COLUMN1"                value="COLUMN1"/>  
                <element name="COLUMN2"                value="COLUMN2"/>  
                          <group name="G_LINE_DETAILS"  source="Q3">  
                               <element name="COLUMN5"        value="COLUMN5"/>  
                               <element name="COLUMN6"        value="COLUMN6"/>       
                          </group>                 
           </group>  
      </group>  
 </dataStructure>  

Oracle Procedure returns table Pl-Sql

Create a table type

CREATE OR REPLACE TYPE XX_REC_TYPE AS     OBJECT
    (
        a1 VARCHAR2(50) ,
        a2      VARCHAR2 (50)
        );


CREATE OR REPLACE TYPE XX_TBL_TYPE
IS
    TABLE OF XX_REC_TYPE;

Create a procedure with out arguments of table type


CREATE OR REPLACE PROCEDURE  XX_SVC(
        p_input in VARCHAR2,
        p_output1 out varchar2,
        p_XX_TBL_TYPE out XX_TBL_TYPE        )
AS
    ord_tbl XX_TBL_TYPE:=XX_TBL_TYPE ();
BEGIN

   select 'xxx' into p_output1 from dual;

    FOR r1 IN
    (
        SELECT  value1,value2 from xx_tbl
                AND value1 = p_input
    )
    LOOP
        ord_tbl.Extend;
        ord_tbl(ord_tbl.last) := XX_REC_TYPE (r1.value1,r1.value2);
    END LOOP;
   
    p_XX_TBL_TYPE := ord_tbl;

END;    


Call the procedure to return the values 

set serveroutput on
clear screen
declare
  lc_output varchar2(240);
  ord_tbl XX_TBL_TYPE;
begin
  XX_SVC(
        p_input =>'12345',
        p_output1 => lc_output,
        p_XX_TBL_TYPE => ord_tbl);
      
        dbms_output.put_line('p_output1-->'||lc_output);
      
        for i in ord_tbl.first .. ord_tbl.last loop
      
        dbms_output.put_line(ord_tbl(i).value1 || ' ' ||
                                ord_tbl(i).value2);
      
        end loop;
      
end;    

 

Oracle Function Returns Table Pl-Sql

Create a table type

CREATE OR REPLACE TYPE XX_REC_TYPE AS     OBJECT
    (
        a1 VARCHAR2(50) ,
        a2      VARCHAR2 (50)
        );


CREATE OR REPLACE TYPE XX_TBL_TYPE
IS
    TABLE OF XX_REC_TYPE;
    

create a function with return table type

 CREATE OR REPLACE FUNCTION XX_SVC(
        p_input VARCHAR2 )
    RETURN XX_TBL_TYPE
AS
    ord_tbl XX_TBL_TYPE:=XX_TBL_TYPE ();
BEGIN

    FOR r1 IN
    (
        SELECT  value1,value2 from xx_tbl
                AND value1 = p_input
    )
    LOOP
        ord_tbl.Extend;
        ord_tbl(ord_tbl.last) := XX_REC_TYPE (r1.value1,r1.value2);
    END LOOP;

RETURN( ord_tbl );

END;   

To call the function use the statement below

select *  from TABLE( XX_SVC('your_input') );

Oracle Form Personalization - Launch a Function Parameters

Calling one form to another form using form personalization needs parameters to be passed.

Skip below steps if you have already created Menu and directly go for parameters.

Create new form personalization with Menu Type

Create another row to and use  Special4 menu for action to be performed


Launch a function with parameters
 


Parameters value should be defined as below. Make sure they are case sensitive and in single line.
Replace the parameters with the form parameters you are calling to.

'P_ORGANIZATION_ID='||${item.line.org_id.value}||' P_INVENTORY_ITEM_ID='||${item.line.inventory_item_id.value}||' P_SUBINV_CODE='||NVL(${item.line.subinv.value},'ABC')

Close Form A upon opening Form B, Oracle Forms When-Button-Pressed

When other form closing options failed use this suggestion

Follow the steps.

create a parameter

1) close_form



2) Initialize parameter before calling form B code in When-Button-Pressed trigger

PARAMETER.CLOSE_FORM = 'Y'

3) On When-New-Item-Instance trigger of the button that used to open other form, use below code.



        declare
          form_id FORMMODULE;
        BEGIN
            IF :PARAMETER.CLOSE_FORM = 'Y' THEN
            form_id := FIND_FORM(NAME_IN('SYSTEM.CURRENT_FORM'));
            IF NOT ID_NULL(form_id) THEN
              CLOSE_FORM(form_id);
            END IF;
            END IF;
            :PARAMETER.CLOSE_FORM := 'N';
        END;

Upon opening the form B in the foreground, form A still resides in the background.Form A closes when brought to foreground. When form B is closed form A also closes.

Set system profile values from SQL Oracle Apps

Use fnd_profile.save to set system profile at site level from SQL.


Declare
value Boolean;
Begin
value := fnd_profile.save('put profile name code','new_profile_value','SITE');
commit;
End;