pipelined

create or replace type obj_cdm_type as object

(id number,name varchar2(250));

create or replace type obj_cdm_t is table of obj_cdm_type; 

create or replace function obj_cdm_fun(pid number) return  obj_cdm_t PIPELINED

as

  l_obj_cdm_type obj_cdm_type:=obj_cdm_type(null,null);

  cursor cur is select id,name from student;

  type t_cur is table of cur%rowtype index by pls_integer;

  r_cur t_cur;

begin

    open cur;

    fetch cur bulk collect into r_cur;

    close cur;

    for idx in 1 .. r_cur.count

    loop

     l_obj_cdm_type.id :=r_cur(idx).id;

     l_obj_cdm_type.name :=r_cur(idx).name;

     pipe row(l_obj_cdm_type);

    end loop;

end;

Business event query

 SELECT a.msg_state, 

       a.user_data.event_name, 

       a.user_data.send_date

FROM applsys.aq$wf_deferred a

WHERE a.user_data.event_name LIKE 'oracle.apps.ar.transaction.Invoice.complete' --Here We need to pass Business event name

GROUP BY a.msg_state, a.user_data.event_name, a.user_data.send_date

ORDER BY a.user_data.event_name,a.user_data.send_date DESC;

select w_even.name

  ,w_even.status event_status

  ,w_e_subs.status subscription_status

  ,nvl(w_e_subs.phase,0) subscription_phase

  ,w_e_subs.rule_function

from wf_events w_even

  ,wf_event_subscriptions w_e_subs

where

  w_even.name like 'oracle.apps.ar.transaction.Invoice.complete'

  and w_e_subs.event_filter_guid = w_even.guid;

  

  SELECT wd.user_data.event_name,

       wd.user_data.event_key,

       rank() over(PARTITION BY wd.user_data.event_name, wd.user_data.event_key ORDER BY n.NAME) AS serial_no,

       n.NAME parameter_name,

       n.VALUE parameter_value,

       wd.user_data.error_message,

       wd.user_data.error_stack,

       wd.msgid,

       wd.delay

  FROM apps.wf_deferred wd, TABLE(wd.user_data.parameter_list) n

 WHERE  1=1

 AND wd.user_data.event_name  ='oracle.apps.ar.transaction.Invoice.complete'

 ORDER BY wd.user_data.send_date DESC,

          wd.user_data.event_name,

          wd.user_data.event_key,

          n.NAME;

PLSQL Import Java loadjava

 public class Math{

  public static int Sum (int x, int y){

  return (x + y );

  }

  public static int difference (int x, int y ){

  return (x-y);

  }

}

loadjava -schema hr -user hr/hr@localhost:1521/XEPDB1 -verbose Math.java

loadjava -user USERNAME/PASSWORD@DBNAME -resolve -synonym activation.jar

select * from user_objects where created>sysdate-1;

CREATE OR REPLACE FUNCTION do_sum (x NUMBER, y NUMBER)

-- Return type should match the return type of java member function

RETURN NUMBER

AS

   LANGUAGE JAVA

   NAME 'Math.Sum(int,int) return int';

   

select do_sum(1,2) from dual

import java.sql.*;

import java.io.*;

import oracle.jdbc.*;

public class RowCounter

{

  public static int rowCount (String tabName) throws SQLException

  {

    Connection conn = DriverManager.getConnection("jdbc:default:connection:");

    String sql = "SELECT COUNT(*) FROM " + tabName;

    int rows = 0;

    try

    {

      Statement stmt = conn.createStatement();

      ResultSet rset = stmt.executeQuery(sql);

      while (rset.next())

      {

        rows = rset.getInt(1);

      }

      rset.close();

      stmt.close();

    }

    catch (SQLException e)

    {

      System.err.println(e.getMessage());

    }

    return rows;

  }

}

[appldev@ct11bzapp181 java]$ loadjava -force -user apps/fnd EchoInput.class

[appldev@ct11bzapp181 java]$ vi RowCounter.java

[appldev@ct11bzapp181 java]$ javac -target 1.5 RowCounter.java

[appldev@ct11bzapp181 java]$ loadjava -force -user apps/fnd RowCounter.class

[appldev@ct11bzapp181 java]$

CREATE FUNCTION row_count (tab_name VARCHAR2) RETURN NUMBER

AS LANGUAGE JAVA

NAME 'RowCounter.rowCount(java.lang.String) return int';

Command Preparations

 find . -name "*.*" -exec grep -il 13985206 {} \;

sed -n '$p' sample.txt

du -kh sample.txt --> 4.5KB

cat -uv filename

grep -A 3 -B 3 findword filename.txt

sed -n '2,5p' sample.txt

awk '{if(NR==3) print $0}' sample.txt

awk 'NR==3,NR==5{ print $0}' sample.txt

$ cat chid.csv

100,800,300,,chidam,ram

$  awk -F, '{print $5}' chid.csv

chidam

find $file_ack_path \( -name "*file*" -o -name "*trans*" \) -type f 2>/dev/null

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

create table student(id number, name varchar2(20));

create or replace package cpackage as

  function cfunc return number;

end cpackage;

create or replace package body cpackage as

  function cfunc return number as

  begin

    insert into student values (1, 'chidam');

    return 1;

  end;

end cpackage;

declare

lv number;

begin

  lv:= cpackage.cfunc;

  dbms_output.put_line(lv);

end;

select * from all_objects where object_name in ('CPROC','CFUNC','CPACKAGE');--check the status

ALTER TABLE STUDENT MODIFY NAME VARCHAR2(60); -- modify the table then check 

ALTER TABLE STUDENT add firstNAME VARCHAR2(60); -- modify the table then check 

select * from all_objects where object_name in ('CPROC','CFUNC','CPACKAGE');--check the status alll reference objects are invalid

alter function CFUNC compile;

alter procedure CPROC compile;

alter package  CPACKAGE compile body;

select * from all_objects where object_name in ('CPROC','CFUNC','CPACKAGE','STUDENT');

select * from all_dependencies where referenced_name='STUDENT';

create or replace view myview as select * from student;

select * from all_dependencies where referenced_name='STUDENT';--- it will tell what are the plsql objects using this table 

select * from all_dependencies where name='CPACKAGE';-- this will tell what are the object used inside the package 

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

PL/SQL Technical Questions List-1

1. How to create new table from existing table only structure of table ?

create table new_table as select * from existing_table where 1=2

2. How to copy all the records from one table to another table while new table creation ?

create table new_table as select * from existing_table 

3. Multiple insert statement in single insert query?

 insert all
 into  student1
 into  student2
 into  student3
 select * from student

structure of student1,student2,student3 should be same as student

4. Difference between DATE and TIMESTAMP in oracle?

TIMESTAMP and DATE vary in formats as follows:

• DATE stores values as century, year, month, date, hour, minute, and second.
• TIMESTAMP stores values as year, month, day, hour, minute, second, and fractional seconds.

5. Difference between char and varchar ?

 CHAR values have a fixed length, They are padded with space characters to match the specified length.

VARCHAR have used only assigned values, rest of the unallocated spaces are released from memory

Oracle Apps technical questions List-5

1. What are the interfaces you worked on?
2. What are the different validations you performed on the staging table?
3. Name some interface table?
4. What are the API’s you used?
5. Tell me about your role in your past projects.
6. What are the major customizations or achievements you got in previous projects?
7. How would you rate yourself in PLSQL?
8. What are varrays?
9. How table types are differ from database tables?
10. How will you load bulk data from a table type to a table?
11. What are the functions that are available for a table type?
12. How you delete a particular data in a table type?
13. What are the different exceptions that are available?
14. What if I define when others first in Exceptions block?
15. What are database triggers?
16. Name some database triggers.
17. Can we write triggers on a view?
18. Can we insert into two tables at a time using single insert statement?
19. How can we register a concurrent program?
20. Among xml publisher reports and RDF reports which is comfortable?
21. What is used in a concurrent program to pass as a parameter to a report?
22. What are the triggers available for reports?
23. How will you send a report as a mail?
24. What is bursting?
25. How comfortable are you with forms?
26. Name some triggers that are mandatory for a form?
27. What is process for RDF reports?
28. How to start the process of page personalization in OAF if you are given a base page?
29. How will you deploy a OAF page?
30. Why we are using Import after page deployment to server?
31. What are the tables that are related to OAF?
32. If I want the structure of the page how can I get and what are the different ways to achieve this?
33. What is substitution? Why we are doing it?
34. Tell the importer command? What are the different ways to import a page?
35. Why VO.xml files are not getting imported?
36. What is MDS data and how it is getting generated?
37. How will you extend a Controller for a page and what is the step by step process that needed to be followed?
38. How will you debug a Concurrent program?
39. Explain P2P cycle and O2C cycle with tables and different stages, tables involved in the process?
40. What are different value sets available?
41. What is ADF-DI?
42. How ADF-DI is different from forms? What are the advantages and disadvantages?
43. If I want to have headers and line details we are doing it by forms and ADF-DI which process is preferable and how much time will it take to complete?
44. What is Copy and No Copy?
45. What is the difference between View and Materialized View?
46. What is the hierarchy of tables for Purchase order?

Oracle Apps technical questions List-4

1. introduction, what modules you know
2. forms in profile
3. forms from scratch level - steps to be followed
4. forms personalization coparision with custom.pll
5. what are the tables for forms personailization.
6. triggers used in forms and forms personalization.
7. what are collections and how many types of collections. where did you use.
8. reports - RDF
9. User exits -
10. new report from scratch - steps
11. xml publisher - triggers used
12. Any advanced tech. ADF and OAF
13. workflows -  No experience
14. UNIX - unix commands where you have used in your experience
15. triggers - types of triggers and mutating error
16. how to get report name with conconrrent program name
17. How many procedures are there in SRW
18. wat is the purpose of userexits
19. diff. between case and decode