top 100 plsql

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);18. What is the parameter substitution symbol used with INSERT INTO command?Which command displays the SQL command in the SQL buffer, and then executes it?What are the wildcards used for pattern matching?State true or false. EXISTS, SOME, ANY are operators in SQL.State true or false. !=, <>, ^= all denote the same operation.What are the privileges that can be granted on a table by a user to others?What command is used to get back the privileges offered by the GRANT command?Which system tables contain information on privileges granted and privileges obtained?Which system table contains information on constraints on all the tables created?What is the difference between TRUNCATE and DELETE commands?What command is used to create a table by copying the structure of another table?Why does the following command give a compilation error?What is the use of the DROP option in the ALTER TABLE command?Which is the subset of SQL commands used to manipulate Oracle Database structures, including tablesWhat operator performs pattern matching?What is the difference between Single row sub-Query and Scalar sub-Query?How do I write a cron which will run a SQL query and mail the results to a group? SubscribeI have a table with duplicate names in it. Write me a query which returns only duplicate rows with number of times they are repeated?How to copy SQL table?Difference between Store Procedure and Trigger?Difference between Store Procedure and Trigger?Cursor Syntax brief history?Explain normalization with examples?How to find out the database name from SQL*PLUS command prompt?How can we backup the sql files & what is SAP?Whats the back end processes when we type "Select * from Table"?What is the difference between SQL and SQL Server?What is table space?What is difference between Co-related sub query and nested sub query?When we give SELECT * FROM EMP; How does oracle respond?What is reference cursor?What operator performs pattern matching?What is difference between Oracle and MS Access? What are disadvantages in Oracle and MS Access? What are features&advantages in Oracle and MS Access?Can we call user defined packages in SQL statements?What is normalazation,types with examples. with queries of all types?Difference between a equijoin and a union?What is database?How to store directory structure in a database?Difference between VARCHAR and VARCHAR2?Why do i get "Invalid Cursor State" errors when I insert/update/delete data with executeHow to write a sql statement to find the first occurrence of a non zero value?Given an unnormalized table with columns:Name,Phone number,Address You notice that some records in Name column are duplicate. Write a T-SQL statement to delete all duplicate record under Name column.How write a SQL statement to query the result set and display row as columns and columns as row?What is cluster.cluster index and non cluster index?If Delete Any Table In Back-End Then. What Are The Triggers will Fire Automatically(Those TriggersHow can i hide a particular table name of our schema.What is the main difference between the IN and EXISTS clause in subqueries??In subqueries, which is efficient ,the IN clause or EXISTS clause? Does they produce the same result?What is difference between DBMS and RDBMS?How to test the complex view using manually testing?When using a count(distinct) is it better to use a self-join or temp table to find redundant data,What is the advantage to use trigger in your PL?What is materialized View?What the difference between UNION and UNIONALL?How to find out the 10th highest salary in SQL query?