File Name: advanced pl sql interview questions and answers .zip
Here is a list of these interview questions and answers:. Explain the uses of a database trigger. What is a stored procedure?
PL Procedural language is an extension to SQL Structured Query Language where a developer can write complex database interactions using procedures, control structures like branching and iteration, modules and functions. A nswer: A procedural language where code can be executed in blocks.
It is an extension of SQL. The cursor is used to hold records returned by the SQL query. There are 2 types of cursors — implicit and explicit. Answer: The trigger is a stored procedure that is automatically invoked when an event happens.
The event could be: insert, update, delete, etc… Syntax —. Answer: Firstly, the syntax check is performed. When the developer corrects any syntax errors, Oracle binds all the variables holding data with a storage address. Finally, the p-code generation process takes place. When an error occurs, an error handling code is included in the program itself. There are 3 types of exceptions —. Answer: Packages are schema objects that place functions, procedures, variables, etc… in one place.
Packages should have —. Answer: Database links, triggers, stored procedures, functions and packages, views, synonyms, external procedure libraries, sequences, etc…. Answer: Predefined exceptions are internally defined exceptions that occur during the execution of a program. Some more examples:. For the complete list of predefined exceptions, check out Oracle docs.
Source: Oracle docs. Answer: Character functions are functions that manipulate character data. These are more popularly called as string functions. Answer: Merge reduces the number of table scans and performs parallel operations if required. MERGE inserts or updates data conditionally from one table to another. For example,. In this example, if a record with the matching condition is found, then the address of the same record is updated, else a new row is inserted.
The executable section is mandatory. There are two types of blocks: named and anonymous. Named blocks are functions and procedures which are stored in the database server and can be reused. Anonymous blocks are for one time use and are not stored in the server. Answer: Records contain a set of data of various data types that can be related to each other as fields.
All the save points are erased and the transaction ends. Once committed, a transaction cannot be rolled back. Answer: The parameters that are used to call a procedure are called as actual parameters. Example —. The variables declared in a procedure header used in the body are called formal parameters.
Answer: DECLARE is used as the first statement for stand-alone files that consist of anonymous block of code which are not stored procedures, functions or triggers. These are globally defined variables. Answer: Rollback erases all the database changes including all the savepoints.
It ends a transaction. The transaction will still be active. Answer: Yes, it is possible. It is not a physical column but the logical address of a row. Example — CHAR If the length of the string is less than the specified number, it will be padded with white spaces. Answer: By using this package, developers can get the code read and write files to and from the computer. For doing this, the developer will need access grant from DBA user. Answer: Both can be used for debugging the code. Answer: We can achieve consistency by setting the appropriate isolation level.
Answer: The error end-of-file on communication channel ORA means that there is a broken connection between the client and server channels. It could be a timeout because of which the connection was lost.
You can troubleshoot by pinging the server and checking the connectivity. Answer: SYS. If do you have some other questions, which we have not covered in this list, and you want to know the answer to those questions, just comments below. A cheerful, full of life and vibrant person, I hold a lot of dreams that I want to fulfill on my own.
My passion for writing started with small diary entries and travel blogs, after which I have moved on to writing well-researched technical content. I find it fascinating to blend thoughts and research and shape them into something beautiful through my writing. View all posts by the Author. Thank you , it is very nice questions and answers. Thanks, Manoj. I got one question, in the answer to the the question of 'Difference between Procedure and Function', you saying 'A stored procedure can not be called from a function' is this statement correct?
Also you said 'Exception handling is not possible' in Function, is this correct also. Pasting a simple code to disapprove the above two statements. Came across "TOP 30 SQL Interview Coding Tasks" by Matthew Urban, which contains deeply-explained, most commonly used data manipulation queries, relationships modeling, aggregation, sorting, table joins, index and other top topics to discuss during the job interview, on less then pages.
Really good book. Don't have an account? Sign Up. Already have an account? Interview Questions. Related Tutorials Data Science. Intro to Programming. Ramya Shankar. Table of Contents. Question: Define cursor and its use. Question: Why do we use database triggers? Give the syntax of a trigger. Give an example. Question: What are the various predefined exceptions?
Question: What is the difference between syntax and runtime errors? Question: Explain Character Functions? Explain with examples. Question: Explain the difference between procedure and function.
Question: Explain the difference between procedure and trigger. Question: What are the different types of constraints? Question: What are the differences between triggers and constraints? Question: Explain the difference between commit and savepoint. Question: What is the difference between actual and formal parameters? Question: What is rollback? How is it different from rollback to statement? Question: Is it possible to accept user inputs during runtime?
Question: Give a simple way to run a query faster. Question: What is the purpose of NVL? Question: On a table, how many triggers can be applied? Question: Write a simple procedure to select some records from the database using some parameters. Question: Explain the error ORA Question: What is SYS.
Ramya Shankar A cheerful, full of life and vibrant person, I hold a lot of dreams that I want to fulfill on my own.
Software Testing Interview Questions and Answers. Leave a comment. Submit Cancel.
Welcome to Mindmajix - The leading global online training platform. There are a lot of opportunities from many reputed companies in the world. This course will help you to achieve excellence in this domain. SQL is a language used to communicate with the server to access, manipulate, and control data. Ans: Alias is a user-defined alternative name given to the column or table. By default column, alias headings appear in upper case.
Have you prepared to attend the job interview? Are you confused in job research? Then no problem we have the right answer to you in in our site page.
Now the client wants to insert a record after the identity value 7 with its identity value starting from Write a single query to calculate the sum of all positive values of x and he sum of all negative values of x. Although there are 4 customers not referred by Jane Smith including Jane Smith herself , the query will only return one: Pat Richards. This will return the same faulty set as the original.
Your email address will not be published. Required fields are marked *