What’s PL/SQL?
PL/SQL may be a procedural language extension with SQL Language.
Oracle 6.0 introduced PL/SQL
It is a mix of SQL and Procedural Statements and used for making applications.
Basically PL/SQL may be a block structure programming language whenever we tend to square measure submitting PL/SQL
Blocks then all SQL statements square measure execution one by one by victimization SQL engine and conjointly all procedure statements square measure dead one by one.
What is sequences in PL SQL?
Sequences area unit want to generate sequence numbers while not associate degree overhead of protection. Its disadvantage is that the sequence range is lost if the group action is rolled back.
What is the fundamental structure of PL/SQL?
PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks is utilized in PL/SQL.
What square measure the foremost necessary characteristics of PL/SQL?
A list of some notable characteristics:
PL/SQL may be a block-structured language.
It is moveable to all or any environments that support Oracle.
PL/SQL is integrated with the Oracle information wordbook.
Stored procedures facilitate higher sharing of application.
Are you Looking for Oracle PLSQL Training? Please Enroll for Demo Oracle PLSQL..! |
How may be a method of PL/SQL compiled?
Compilation method includes syntax check, bind and p-code generation processes. Syntax checking checks the PL/SQL codes for compilation errors. once all errors square measure corrected, a storage address is assigned to the variables that hold information. it’s known as Binding. P-code may be a list of directions for the PL/SQL engine. P-code is keep within the information for named blocks and is employed subsequent time it’s dead.
How will we tend to connect associate degree automaton App to associate degree Oracle information and use the PL/SQL procedural code?
Before moving into the PL/SQL, we tend to should produce a REST API victimisation JSON for establishing association. you’ll import the Retrofit libraries in conjunction with dependencies to determine communication along with your automaton App. Then prepare the functions and procedures victimisation PL/SQL, then once your oracle information is connected.
Name few schema objects that may be created victimisation PL/SQL?
Stored procedures and functions
Packages
Triggers
Cursors
What area unit the 3 basic sections of a PL/SQL block?
Declaration section
Execution section
Exception section
What is a trigger?
A trigger could be a PL/SQL program that’s hold on within the info and dead at once before or when the INSERT, UPDATE, and DELETE commands.
What area unit the uses of triggers?
Basically, triggers area unit want to produce consistencies, access restriction and implement securities to the info. Triggers also are used for −
Creating validation mechanisms involving searches in multiple tables
Creating logs to register the employment of a table
Update different tables because of inclusion or changes within the current table.
How will we tend to rectify in PL/SQL?
We can build use of the DBMS_OUTPUT for printing breakpoint activities. we can conjointly use DBMS_DEBUG.
How will we tend to implement Rollback or Commit statement in a very Trigger?
We cannot. it’s not logical to place a Rollback or Commit inside a Trigger because of these statements impose a save point that affects the logical group action process.
What is associate degree Oracle sequence?
A Sequence generates a serial list of distinctive numbers for numerical columns of a database’s tables.
What square measure the parts of a PL/SQL block?
A collection of connected declarations and procedural statements is termed block.
What square measure the parts of a PL/SQL Block?
Declarative half, feasible half and Exception half.
Datatypes PL/SQL
What square measure the datatypes a on the market in PL/SQL?
Some scalar knowledge varieties admire variety, VARCHAR2, DATE, CHAR, LONG, BOOLEAN.
Some composite knowledge varieties admire RECORD & TABLE.
Make a case for the usage of wherever CURRENT OF clause in cursors?
Wherever CURRENT OF clause in associate UPDATE, DELETE statement refers to the most recent row fetched from an indicator.
What’s an information trigger? Name some usages of information trigger?
Information trigger is keep PL/SQL program unit related to aninformation table. Usages are Audit information modifications, Log events transparently, enforce complicated business rules Derive column values mechanically, Implement complicated security authorizations. Maintain replicate tables.
What is information link used for?
Database links area unit want to communicate between two databases. Database links area unit created to make communication between varied databases, or totally different environments like look at, development and production. When user desires to insert or update information from one atmosphere to a different atmosphere then information links area unit used.
Justify character, range and date operate in detail?
Character functions: settle for character input and come back each character and range values. kinds of character operate are:
- a) Case-Manipulation Functions: LOWER, UPPER, INITCAP
- b) Character-Manipulation Functions: CONCAT, SUBSTR, LENGTH, INSTR, LPAD/RPAD, TRIM, REPLACE
Number Functions: settle for Numeric input and come back numeric values. range Functions are: spherical, TRUNC and MOD
Date Functions: operates on values of the Date knowledge kind. (All date functions come back a worth of DATE knowledge kind except the MONTHS_BETWEEN operate, that returns variety. Date Functions square measure MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, TRUNC.
What’s a twin Table?
Dual table is closely-held by the user SYS and might be accessed by all users. It contains one column Dummy and one row with the worth X. the twin Table is helpful once you wish to come back a worth just one occasion. the worth is often a continuing, pseudo column or expression that’s not derived from a table with user knowledge.
Are you Interested for Oracle PLSQL Online Training? Please Enroll for Demo Oracle PLSQL..! |
What’s a MERGE statement?
The MERGE statement inserts or updates rows in one table, victimisation information from another table. it’s helpful in information reposting applications.
What’s a distinction between “VERIFY” and “FEEDBACK” command?
VERIFY Command: Use VERIFY Command to substantiate the changes within the SQL statement (Old and New values). outlined with SET VERIFY ON/OFF.
Feedback Command: Displays the quantity of records came by a question.
What’s a distinction between Entity, Attribute and Tuple?
Entity: a major factor concerning that some data is needed. For ex: worker (table). Attribute: one thing that describes the entity. For ex: employ, employ name, employ address (columns). Tuple: A row during a relation is named Tuple.
What’s a choice during a table?
A column may be given a default price by victimisation the choice. this selection prevents null values from coming into the column if a row is inserted while not a worth for that column. The DEFAULT price may be a literal, Associate in Nursing expression, or a SQL perform admire SYSDATE and USER however the worth can’t be the name of another column or a pseudo column admire NEXTVAL or CURRVAL.
What’s a distinction between USER TABLES and information DICTIONARY?
USER TABLES: could be an assortment of tables created and maintained by the user. Contain USER data. information DICTIONARY: could be an assortment of tables created and maintained by the Oracle Server. It contains information data. All information lexicon tables area unit owned by the SYS user.
Describe few restrictions on exploitation “LONG” knowledge type?
A LONG column isn’t derived once a table is formed employing a sub question. a protracted column cannot be enclosed Associate in Nursing exceedingly in a very} cluster BY or an ORDER BY clause. just one LONG column is used per table. No constraint is outlined on a protracted column.
What’s a group UNUSED option?
SET UNUSED choice marks one or a lot of columns as unused so they’ll be born once the demand on system resources is lower. Unused columns area unit treated as if they were born, albeit their column knowledge remains within the table’s rows. once a column has been marked as unused, you have got no access thereto column. a get * question won’t retrieve knowledge from unused columns. additionally, the names and kinds of columns marked unused won’t be displayed throughout a DESCRIBE, and you’ll be able to augment the table a replacement column with an equivalent name as an unused column. The SET UNUSED info is keep within the USER_UNUSED_COL_TABS lexicon read.
What’s Mutating Trigger?
Into a row level trigger supported a table trigger body cannot browse information from same table and conjointly we tend to cannot perform DML operation on same table.
If we tend to try this oracle server returns mutating error oracle-4091: table is mutating.
This error is named mutating error, and this trigger is named mutating trigger, and table is named mutating table.
Mutating errors aren’t occurred in statement level trigger as a result of through these statement level trigger after we square measure performing arts DML operations mechanically information committed into the information, whereas in row level trigger {when we have a tendency to oncewe after we} square measure performing arts dealings information isn’t committed and conjointly once more we square measure reading this information from an equivalent table then solely mutating errors is occurred.
What’s Overloading Procedures?
Overload is referring to same name are often used for various purpose, in oracle we will conjointly implement overloading procedure through package. Overloading procedure having same name with totally {different completely different} kind or different variety of parameters.
What’s world Variables?
In oracle we tend to square measure declaring world variables in Package Specification solely.
What’s Forward Declaration?
In oracle declaring procedures among package body is named forward declaring usually before we tend to square measure business non-public procedures into public procedure initial we tend to should implements non-public into public procedure initial we tend to should implements non-public procedure among body otherwise use a forward declaration among package body.
What’s a keep procedure?
A keep procedure could be a sequence of statements that perform specific operate.
What’s distinction between a PROCEDURE?
An operate forever come backs a worth victimization the return statement.
A PROCEDURE could come back one or additional values through parameters or might not come back in the least.
What are blessings field-grade officer keep Procedures?
Extensibility, Modularity, Reusability, Maintainability and just the once compilation.
Make a case for the distinction between IN and OUT parameters.
An IN parameter permits US to pass values into PL/SQL code whereas the OUT parameter permits US to pass values back out of PL/SQL code. Also, keep in mind that a parameter may be fixed as each IN and OUT with the IN OUT declaration.
What’s module overloading and why would possibly you employ it?
Overloading modules is nothing quite a mechanism that permits the computer programmer to reprocess an equivalent name for various programs that area unit among an equivalent scope. Overloading is maybe one in every of my favourite mechanisms to share and increase usability among code.
What area unit packages?
A package is nothing quite the simplest way to consolidate/group/organize/etc., common parts of PL/SQL code into one named entity. whereas packages do facilitate improve things like moveableness and maintainability of code, packages may also facilitate improve the performance of the code.
However, may you hide/protect your ASCII text file if distributed to customers?
Oracle’s wrap utility provides a decent method of concealing PL/SQL supply code; protective it and creating it troublesome for others to look at. you’ll be able to either wrap ASCII text file with the wrap utility or use the DBMS_DDL subprograms.
What is a table, view, snapshot?
Table: A table is that the basic unit of knowledge storage in associate Oracle info. The tables of an info hold all the user accessible information. Table information is keep in rows and columns. A
Views: A read could be a virtual table. each read contains a question hooked up to that. (The question could be a choose statement that identifies the columns and rows of the table(s) the read uses.)
Snapshot: A photograph could be a recent copy of a table from info or in some cases, a set of rows/columns of a table. it’s additionally referred to as Materialized read.
What is Associate in Nursing Oracle index?
Associate in Nursing index is Associate in Nursing optional structure related to a table to possess direct access to rows, which might be created to extend the performance of knowledge retrieval. Index will be created on one or additional columns of a table. Index might also be thought-about as an ordered list of content of a column.
What is a schema?
The set of objects in hand by user account is termed the schema.