oracle apps stuff

1 TkprofWhen Oracle SQLTRACE facility is enabled, all the statistics for all SQL statements executed after that are placed into trace files. These trace files are not in readable format. Here comes the usage of TKPROF. TKPROF is used for formatting the trace files to produce a more readable format. It stores the following statistics of a Query:TKPROFtracefile outputfile TKPROF trace_output.trc            outputfile.txt      insert=tkprof.sql             record=record.sql2. What is a mutating table error and how can you get around it?Level: IntermediateExpected answer: This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.3. What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.4. How to find the dependency of table?select NAME, TYPE, REFERENCED_NAME, REFERENCED_TYPE from user_dependencieswhere REFERENCED_NAME        ='EMP'5. view and materialized view?A VIEW is nothing but a saved query. Instead of writing the subquery in another statement, you can use the VIEW. But internally, the view just executes like a subquery and provides the result to the outer query.A Materialized view is a Snapshot of data. It could be refreshed at intervals so that the data is fetched fresh from the underlying tables. So what does that mean? A Materialized view CAN STORE DATA.One common use of Materialized view is, if the query of a VIEW is so complex and takes long time to execute, the program calling the VIEW should wait until it fetches the data. So, Materialized views are of much help in such scenarios. MVs has got the Data stored in them, and act like a Table so that direct fetch could be done. This happens since the underlying query had run at some point of time and have already fetched/refreshed the data.Another use is to store data from Remote table/tables and store them in the local database.A materialized view is a snapshot of data which could be refreshed at defined intervals. From the name, it is unclear that a materialized view can hold data, since views don’t.6. What is normalization? what is the advantage of normalization?Normalization is the process of removing redundant data from your tables7. How to find out nth highest salary from emp table?SELECT DISTINCT(A.SAL) FROM    EMP AWHERE &TESTING =  (SELECT COUNT(DISTINCT(B.SAL)) FROM    EMP B WHERE A.SAL<=B.SAL);8. force View?SQL> create force view v_emp as select *  from emp;Warning: View created with compilation errors.And it will throw error if you try to selectSQL> select * from v_emp;select * from v_emp *ERROR at line 1:ORA-04063: view "RXC.V_EMP" has errors9. Can one read/write files from PL/SQL? Answer: Included in Oracle 7.3 is a UTL_FILE package that can read and write files.The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=...parameter).Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.DECLAREfileHandler UTL_FILE.FILE_TYPE; BEGINfileHandler := UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput','W'); UTL_FILE.PUTF(fileHandler, 'Value of func1 is %sn', func1(1)); UTL_FILE.FCLOSE(fileHandler); END; 10. synonym:Synonyms is the alias name for table, views, sequences & procedures and are created for reasons of Security and Convenience.Two levels are Public - created by DBA & accessible to all the users. Private - Accessible to creator only. Advantages are referencing without specifying the owner and Flexibility to customize a more meaningful naming convention.11.packing slip report?Packing Slip report prints all the shipment information, usually packing slip is used by packers to verify the content before it leaves the warehouse, it is used by carrier as an evidence the goods he is carrying (may not belong to him). Customer receiving team also uses this packing slip to verify al the contents they received are as printed in packing slip.12.Pick slip report:Pick Slip report is used by warehouse personnel (pickers). This report tells them what item(s) need to be picked up from what location, quantity, lot/serial number and finally where to deliver the goods. Picking activity happens right before shipping. Pick slip can be for a sales order or partial sales order or across sales orders.13. What is back order in OMThere are few reasons that a scheduled order could be Backordered during pick release process.1. In-sufficient onhand2. Order might be put as ‘Pick release hold’3. If it’s a lot controlled item being involved then some of the factors will be checking at picking rule setup (like Lot- expiration date, etc) and tured to ‘Backordered’ incase the criteria does not meet.14.sending mail form plsql?CREATE OR REPLACE PROCEDURE SEND_MAIL (msg_from varchar2 : 'oracle' msg_to varchar2 msg_subject varchar2 : 'E-Mail message from RAJIV' msg_text varchar2 : '' )ISc utl_tcp.connection;rc integer;BEGINc : utl_tcp.open_connection('127.0.0.1' 25); -- open the SMTP port 25 on local machinedbms_output.put_line(utl_tcp.get_line(c TRUE));rc : utl_tcp.write_line(c 'HELO localhost');dbms_output.put_line(utl_tcp.get_line(c TRUE));rc : utl_tcp.write_line(c 'MAIL FROM: '||msg_from);dbms_output.put_line(utl_tcp.get_line(c TRUE));rc : utl_tcp.write_line(c 'RCPT TO: '||msg_to);dbms_output.put_line(utl_tcp.get_line(c TRUE));rc : utl_tcp.write_line(c 'DATA'); -- Start message bodydbms_output.put_line(utl_tcp.get_line(c TRUE));rc : utl_tcp.write_line(c 'Subject: '||msg_subject);rc : utl_tcp.write_line(c '');rc : utl_tcp.write_line(c msg_text);rc : utl_tcp.write_line(c '.'); -- End of message bodydbms_output.put_line(utl_tcp.get_line(c TRUE));rc : utl_tcp.write_line(c 'QUIT');dbms_output.put_line(utl_tcp.get_line(c TRUE));utl_tcp.close_connection(c); -- Close the connectionEXCEPTIONwhen others thenraise_application_error(-20000 'Unable to send e-mail message from pl/sql because of: '||sqlerrm);END;/16. How can we schedule the procedure to run automatically ?dbms_scheduler.create_job(ob_name IN VARCHAR2 job_type IN VARCHAR2 job_action IN VARCHAR2 number_of_arguments IN PLS_INTEGER DEFAULT 0 start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL repeat_interval IN VARCHAR2 DEFAULT NULL end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS' enabled IN BOOLEAN DEFAULT FALSE auto_drop IN BOOLEAN DEFAULT TRUE comments IN VARCHAR2 DEFAULT NULL);17. What are the different types of collections?plsql supports mainly 3 types of collections1> index by binary_integer2> nested table3>varraysindex by binary_integer and nested table are collectivily known as plsql tablesand nested table and varray can be stored in database table but index by binary_integer cant be stored in database tablethe syntax for index by binary_integerex: declaretype basu is table of varchar2(10)index by binary_integer;if you leave the key word binary_integer it becomes nested tableex: declaretype basu is table of varchar2(10);the syntax for varrays is ex: declaretype basu is varray(5) of varchar2(10);