Category Archives: SQL/PLSQL

Queries to get path of Concurrent Request Log, Output, Manager Log and FNDOPP log

Below are the queries to get path of Concurrent Request Log, Output, Manager Log and FNDOPP log

a) Concurrent Request Log:
select logfile_name from fnd_concurrent_requests where request_id = <request_id>;

b) Concurrent Output file:
select outfile_name from fnd_concurrent_requests where request_id = <request_id>;

c) Concurrent Manager Worker Log:
select logfile_name from fnd_concurrent_processes where concurrent_process_id=(select controlling_manager
from fnd_concurrent_requests where request_id=<request_id>);

d) FNDOPP log file

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
AND fcpp.action_type = 6
AND fcpp.concurrent_request_id =<request_id>;

Advertisements

Prepared Statement and Callable Statement in OAF

We use Prepared Statement to Execute a Query or a SQL Statement and we use callable statement to call a package, function, procedure

In general, to invoke a stored procedure from within an entity object or an application module, you need to:
1. Create a JDBC CallableStatement with the PL/SQL block containing the stored procedure invocation
2. Bind any variables.
3. Execute the statement.
4. Optionally retrieve the values of any OUT parameters.
5. Close the statement.

For Prepared Statement we use directly for any DML operations. To Execute the SELECT Query we have to use the class Result set to get the Data..

if(pageContext.getParameter(EVENT_PARAM).equals(“TEST”))
{
String   valuetobePassed1=repId+””;
String valuetobePassed2 = null;
System.out.println(“Condition called for test”);
CallableStatement callableStatement = txn.createCallableStatement(“declare begin :outVariable := RET_QUERY_REP.RET_QUERY(:2, :3); end;”,OADBTransaction.DEFAULT); //Refer to the Syntax where the String is a procedure
//Create a JDBC CallableStatement with the PL/SQL block containing the stored procedure invocation
//Bind any variables.
try
{
callableStatement.registerOutParameter(1, Types.VARCHAR); //First Bind Varible should give us out Parameter
callableStatement.setString(2, valuetobePassed1); //Passing a variable to Second Variable
callableStatement.setString(3, valuetobePassed2); //Passing a variable to Second Variable
callableStatement.execute(); // Execute the statement.
outParamValue = (callableStatement.getString(1)).toString(); //Retrieve the values of any OUT parameters.
System.out.println(“The Out Parameter Obtained from the Procedure is “+outParamValue);
}
catch (SQLException sqle)  //Be cautious that execute() throws an SQL Exception
{
System.out.println(“Exception occured”);
}
//Sql Validation whether Query is Right or wrong
pageContext.writeDiagnostics(this, repId+outParamValue, 4);
System.out.println(“The output value is “+repId+”This “+outParamValue);
PreparedStatement transaction = (PreparedStatement)am.getOADBTransaction().createPreparedStatement(outParamValue,0);
OracleResultSet rs = null;  //Result Set to get the Data from the Query
try
{
rs=(OracleResultSet)transaction.executeQuery();
if(rs.next())
{
pageContext.writeDiagnostics(this, “Column one value”+rs.getString(1)+”Column two value”+rs.getString(2), 4 ); //We can see the Output
System.out.println(“Column one value”+rs.getString(1)+”Column two value”+rs.getString(2)); //We can see the Output
}
else
{
rs.close(); //Close the statement.
}
}
catch(Exception e)
{
pageContext.writeDiagnostics(this, “Exception Has occured”, 4 );
System.out.println(“Exception came Hence SQL query is wrong”);
}
}

 

 

 

The package that returns a Query from the table which is used in callable statement

 

CREATE OR REPLACE PACKAGE RET_QUERY_REP
IS
FUNCTION RET_QUERY (P_REP_ID NUMBER, P_VAL VARCHAR2)
RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY RET_QUERY_REP
IS
FUNCTION RET_QUERY (P_REP_ID NUMBER, P_VAL VARCHAR2)
RETURN VARCHAR2
IS
QUERYSTR   VARCHAR2 (10000);
TYPE R_CURSOR IS REF CURSOR;
C_REF      R_CURSOR;
LV_TEMP    VARCHAR2 (1000);
LV_TEST    VARCHAR2 (1000);
LV_COLV    VARCHAR2 (1000);
BEGIN
IF P_VAL IS NULL THEN
SELECT   (‘ FROM ‘|| FROM_CLAUSE|| ‘ WHERE ‘|| NVL (WHERE_CLAUSE, ‘1=1’)|| NVL2 (GROUPBY_CLAUSE, ‘ GROUP BY ‘ || GROUPBY_CLAUSE, ”)|| ‘ ORDER BY ‘|| NVL (ORDERBY_CLAUSE, ‘1’))
INTO   QUERYSTR
FROM   XXCUST_REPORT_V
WHERE   REP_ID = P_REP_ID;          –Query without column names
ELSE
SELECT   (‘ FROM ‘|| FROM_CLAUSE|| ‘ WHERE ‘|| NVL2 (WHERE_CLAUSE, WHERE_CLAUSE||’ AND ‘||P_VAL, ‘1=1’)|| NVL2 (GROUPBY_CLAUSE, ‘ GROUP BY ‘ || GROUPBY_CLAUSE, ”)|| ‘ ORDER BY ‘|| NVL (ORDERBY_CLAUSE, ‘1’))
INTO   QUERYSTR
FROM   XXCUST_REPORT_V
WHERE   REP_ID = P_REP_ID;
END IF;

–DBMS_OUTPUT.PUT_LINE (‘THE QUERY OUTLINE ——-> ‘ || QUERYSTR);
OPEN C_REF FOR
SELECT   XRCV.REP_COLUMN
FROM   XXCUST_REPORT_V XRV, XXCUST_REPORT_COLUMN_V XRCV
WHERE   XRV.REP_ID = XRCV.REP_ID AND XRCV.REP_ID = P_REP_ID;
LOOP
FETCH C_REF INTO   LV_COLV;
EXIT WHEN C_REF%NOTFOUND;
LV_TEST := LV_TEST || LV_COLV || ‘, ‘;
–DBMS_OUTPUT.PUT_LINE(‘THE COLUMN NAMES ARE ——-> ‘||LV_TEST);
END LOOP;
CLOSE C_REF;
LV_TEMP := ‘SELECT ‘ || RPAD (LV_TEST, LENGTH (LV_TEST) – 2) || QUERYSTR;
DBMS_OUTPUT.PUT_LINE (‘THE FINAL QUERY IS  ARE ——-> ‘ || LV_TEMP);
RETURN LV_TEMP;
END RET_QUERY;
END RET_QUERY_REP;

 

 

Bulk Loading of Lookups in Oracle Apps

When you have some lookups you can insert it into oracle apps by going to lookups and insert ahead. But when you have data in a large quantity it is a duplication to type and insert. We have some fnd_lookup_values_pkg, fnd_lookup_types_pkg API’s to rescue

So now lets go Step by Step to insert

The Steps are

Step-1 :  To identify the UI(oracle forms) from where user can be able to visualize

Step – 2 Digging into query

Step – 3 Inserting data into oracle seeded table
 Step – 1  : To identify the UI(oracle forms) from where user can be able to visualise

The path for navigation is

Application Developer -> Application  – > Lookups -> Common and you will get a page as shown below

1

Now Just go to insert mode by pressing F11 and put % symbol to Query and press Ctrl+F11 to Query.

2.

Now you will have records populated. Just click a record and go to Help -> Record History

You will get the table/view name from where the data is populated.

3.

If suppose you are not able to view the record history then try this

Help -> Diagnostics -> Examine

Then you will get the below screen and set the LOV’s as shown

Block  : System

Field   : Last_query

4.

And in Value you can get the SQL query. With this we can start our hard way.

Step – 2 Digging into query

Now we have to dig down the Query View Scripts from where the data comes.

SELECT text

FROM all_views

WHERE view_name = ‘%FND_LOOKUP_TYPES_VL%’;

By the above query we can get the scripts for the view.

Now the scripts will give a query which has joins to tables by this we will get to a idea that where it fetches the data.

Now on analyzing we get data from fnd_lookup_types table and on analysing the values we get the data from  fnd_lookup_values table.

Step – 3 Inserting data into oracle seeded table

When the requirement is to insert your data into oracle seeded table you have two options recommended. One is by Interface and another by API.

Here we are going to see the values to be inserted by API for a particular table.

API -> fnd_lookup_values_pkg, fnd_lookup_types_pkg

Table -> fnd_lookup_values, fnd_lookup_types

To insert into fnd_lookup_values, fnd_lookup_types  table which is oracle seeded we can use the API  fnd_lookup_values_pkg, fnd_lookup_types_pkg

which is a package.

Note:

To view the contents of the package

1 If you use TOAD place the cursor in the pkg  and press F4

2 Use the Below Query to find the content

select * from user_source where name=upper(‘fnd_lookup_values_pkg’);

select * from user_source where name=upper(‘fnd_lookup_types_pkg’);

Below is the script for inserting data into table by using fnd_lookup_values _pkg

DECLARE

XROW     ROWID;  –You cant directly give Row Id to the x_rowid parameter,

ln_rowid1   ROWID;

cursor c is     select * from xx_dept; –My Custom Query to have fetch in my custom table

cursor c1 is

select distinct lookup_type from xx_dept;

BEGIN

for i in c1   — For loop for Inserting lookup types

loop

fnd_lookup_types_pkg.insert_row (x_rowid                    => XROW,

x_lookup_type              => i.lookup_type,  –cursor values

x_security_group_id        => 0,

x_view_application_id      => 3,

x_application_id           => 20187,

x_customization_level      => ‘U’,

x_meaning                  => i.lookup_type,

x_description              => i.lookup_type,

x_creation_date            => SYSDATE,

x_created_by               => 1318,

x_last_update_date         => SYSDATE,

x_last_updated_by          => 0,

x_last_update_login        => 5513095

);

DBMS_OUTPUT.put_line (XROW);

end loop;  –Loop ends here

commit;

for i in c — For loop for Inserting lookup values

loop

fnd_lookup_values_pkg.insert_row (x_rowid                    => ln_rowid1,

x_lookup_type              => I.LOOKUP_TYPE,  –Secomd cursor values

x_security_group_id        => 0,

x_view_application_id      => 3,

x_lookup_code              => I.LOOKUP_CODE,

x_tag                      => NULL,

x_attribute_category       => NULL,

x_attribute1               => NULL,

x_attribute2               => NULL,

x_attribute3               => NULL,

x_attribute4               => NULL,

x_enabled_flag             => ‘Y’,

x_start_date_active        => TO_DATE (’01-JAN-1950′,

‘DD-MON-YYYY’

),

x_end_date_active          => NULL,

x_territory_code           => NULL,

x_attribute5               => NULL,

x_attribute6               => NULL,

x_attribute7               => NULL,

x_attribute8               => NULL,

x_attribute9               => NULL,

x_attribute10              => NULL,

x_attribute11              => NULL,

x_attribute12              => NULL,

x_attribute13              => NULL,

x_attribute14              => NULL,

x_attribute15              => NULL,

x_meaning                  => I.LOOKUP_MEANING,

x_description              => NULL,

x_creation_date            => SYSDATE,

x_created_by               => 1318,

x_last_update_date         => SYSDATE,

x_last_updated_by          => 1318,

x_last_update_login        => 5513095

);

DBMS_OUTPUT.put_line (ln_rowid1);

end loop;

exception

when others then dbms_output.put_line(‘Exception Occured’);

commit;

END;

Inserting values into fnd_lookup_values by fnd_lookup_value_pkg

When the requirement is to insert your data into oracle seeded table you have two options recommended. One is by Interface and another by API.

Here we are going to see the values to be inserted by API for a particular table.

API -> fnd_lookup_values_pkg

Table -> fnd_lookup_values

To insert into fnd_lookup_values  table which is oracle seeded we can use the API fnd_lookup_values_pkg which is a package.

Note:

To view the contents of the package

  1. If you use TOAD place the cursor in the pkg  and press F4
  2. Use the Below Query to find the content

select * from user_source where name=upper(‘fnd_lookup_values_pkg’);

Below is the script for inserting data into table by using fnd_lookup_values _pkg

DECLARE

xrow rowid;

j number;

CURSOR C IS select LOOKUP_TYPE, meaning, lookup_code, DESCRIPTION  from

xx4i_fnd_lookup_values

where lookup_type like ‘XXHLP%’;

BEGIN

FOR i IN c

LOOP

dbms_output.put_line(i.LOOKUP_TYPE||i.lookup_code||i.meaning||i.DESCRIPTION);

— Call the procedure

fnd_lookup_values_pkg.insert_row(x_rowid               => xrow,

x_lookup_type         => i.LOOKUP_TYPE,

x_security_group_id   => 1,

x_view_application_id => 0,

x_lookup_code         => i.lookup_code,

x_tag                 => null,

x_attribute_category  => null,

x_attribute1          => null,

x_attribute2          => null,

x_attribute3          => null,

x_attribute4          => null,

x_enabled_flag        => ‘Y’,

x_start_date_active   => null,

x_end_date_active     => null,

x_territory_code      => null,

x_attribute5          => null,

x_attribute6          => null,

x_attribute7          => null,

x_attribute8          => null,

x_attribute9          => null,

x_attribute10         => null,

x_attribute11         => null,

x_attribute12         => null,

x_attribute13         => null,

x_attribute14         => null,

x_attribute15         => null,

x_meaning             => i.meaning,

x_description         => i.DESCRIPTION,

x_creation_date       => sysdate,

x_created_by          => 1318,

x_last_update_date    => sysdate,

x_last_updated_by     => 1318,

x_last_update_login   => 1318);

END loop;

COMMIT;

END;

Thank you for Reading

Importing and exporting data by TOAD(for oracle DB)

Inserting into database using Toad

When your Requirement is to import the data inside a database and you have your data in spreadsheet the below is very easy.

Requirements

  • Oracle Database.
  • TOAD tool configured to database.

Importing data

 

Go to Database->Import -> Import table Data

1

Select the schema, Object Type and Object Name

2

Click Next , Select the File, Click Next, Specify the Source Data Details and check the excel sheets, Columns and then Execute

3

 

At last you will find Execute Button. Press It to add the rows

Exporting from database

Go to Toad Menu, Database-> Schema Browser

00

 

Select Schema, Table

11

After Selecting right click table name -> Export  Data -> Choose Destination and click Ok

22

 

You can also add a additional where clause to specify the rows.

33

You can also try Create Script which gives you the DDL Statements like(Create table )

Thank you for Reading…

 

Some Fascinating Sql Programs-2

 

 

Addition of Salary with Previous

 

Query -1

 

SELECT R.FIRST_NAME||’,’||R.LAST_NAME “EMPLOYEE NAME”, R.SALARY, D.DEPARTMENT_NAME, R.SALARY+M.SAL1 INC_SAL

FROM(SELECT ROWNUM+1 MR1, SALARY SAL1 FROM (SELECT ROWNUM RO, SALARY FROM

EMPLOYEES) UNION

SELECT 1,0

FROM DUAL) M,

(SELECT SALARY, FIRST_NAME, LAST_NAME ,DEPARTMENT_ID, ROWNUM R1 FROM EMPLOYEES) R, DEPARTMENTS D

WHERE M.MR1=R.R1 AND R.DEPARTMENT_ID=D.DEPARTMENT_ID

 

Query-2

SELECT R.SALARY, ZZ.SS+R.SALARY WOW FROM (select st.mr1, case when mr1=1 then 0 else salary  end ss from

(SELECT   ROWNUM  MR1,

CASE WHEN (ROWNUM – 1) = 0 THEN salary – salary ELSE salary END sal1

FROM   (SELECT   ROWNUM RO, SALARY FROM EMPLOYEES)) st, (SELECT   ROWNUM+1 RO1, SALARY FROM EMPLOYEES) gt where st.mr1=gt.ro1(+)

order by mr1) zz, (SELECT SALARY, FIRST_NAME, LAST_NAME ,DEPARTMENT_ID, ROWNUM R1 FROM EMPLOYEES) R where zz.mr1=r.r1

Query-3

 

SELECT  s1.e1,s1.sal1,

case

when s1.e1=1 THEN s1.sal1+0

else s1.sal1+s2.sal2

end total

FROM

(SELECT  salary sal1,rownum e1

FROM employees) s1,

(SELECT salary sal2,

case

when rownum=1 then rownum+1

else rownum+1

end e2

FROM employees) s2

WHERE s1.e1=s2.e2(+)

order by e1
Query – 4

 

SELECT   R3,

SAL,

SALARY,

SAL + SALARY UP_SAL

FROM   (  SELECT   CASE WHEN R2 = 107 THEN R2 – 106 ELSE R2 + 1 END R3, SAL

FROM   (SELECT   ROWNUM R2,

CASE WHEN ROWNUM = 107 THEN 0 ELSE SALARY END

SAL

FROM   EMPLOYEES)

ORDER BY   R3) EZ,

(SELECT   SALARY, ROWNUM R4 FROM EMPLOYEES) EN

WHERE   EN.R4 = EZ.R3

 

 

Logic 

 

Almost Every query has the same logic that is changing the rownum and making a data zero or inserting a zero data.

 

 

 

 

Palindrome 

 

SELECT   CASE

WHEN SUM (EA) = LENGTH (:D) THEN ‘PALINDROME’

ELSE ‘ NOT PALINDROEM’

END

PALINDROME_CHECK

FROM   (SELECT  SUBSTR (:D, ROWNUM, 1), SUBSTR (:D, -ROWNUM, 1),  CASE

WHEN SUBSTR (:D, ROWNUM, 1) = SUBSTR (:D, -ROWNUM, 1)

THEN

‘1’

ELSE

‘0’

END

EA

FROM   EMPLOYEES)

 

 

Output

I/P: wow: Palindrome

I/P: hai: not a Palindrome

 

 

Logic

 

We split the word or string into rows in a column and then we inverse the string and make it in another column. Now we compare the both columns and when both match we give number 1 or 0. Now we check the length of the string and the sum of the column. When it is matched it is a palindrome. Else it is not a palindrome