What are stored-procedures? And what are the advantages of using them.

What are stored-procedures? And what are the advantages of using them.
by

1 Answer

Answer :

Stored procedures are database objects that perform a user defined operation. A stored  procedure can have a set of compound SQL statements. A stored procedure executes the SQL  commands and returns the result to the client. Stored procedures are used to reduce network  traffic.
by

Related questions

What are advantages fo Stored Procedures?

Description : What are advantages fo Stored Procedures?

Last Answer : Extensibility,Modularity, Reusability, Maintainability and one time compilation.

How will you enforce security using stored procedures?

Description : How will you enforce security using stored procedures?

Last Answer : Don't grant user access directly to tables within the application. Instead grant the ability to access the procedures that access the tables. When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.

How will you enforce security using stored procedures?

Description : How will you enforce security using stored procedures?

Last Answer : Don't grant user access directly to tables within the application. Instead grant the ability to access the procedures that access the tables. When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.

What are Database Triggers and Stored Procedures

Description : What are Database Triggers and Stored Procedures

Last Answer : Database Triggers :: Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table. Database triggers have the values old and new to ... procedures is that many users can use the same procedure in compiled and ready to use format.

What is Overloading of procedures ?

Description : What is Overloading of procedures ?

Last Answer : The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures. e.g. DBMS_OUTPUT put_line

What are different modes of parameters used in functions and procedures?

Description : What are different modes of parameters used in functions and procedures?

Last Answer : IN -OUT -INOUT

What is an UTL_FILE.What are different procedures and functions associated with it?

Description : What is an UTL_FILE.What are different procedures and functions associated with it?

Last Answer : UTL_FILE is a package that adds the ability to read and write to operating system files. Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to output data to a file PUT, ... , PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN.

What is difference between Procedures and Functions ?

Description : What is difference between Procedures and Functions ?

Last Answer : A Function returns a value to the caller where as a Procedure does not.

How packaged procedures and functions are called from the following?

Description : How packaged procedures and functions are called from the following?

Last Answer : a. Stored procedure or anonymous block b. an application program such a PRC *C, PRO* COBOL c. SQL *PLUS?? a. PACKAGE NAME.PROCEDURE NAME (parameters); variable := PACKAGE NAME.FUNCTION NAME ( ... .PROCEDURE if the procedures does not have any out/in-out parameters. A function can not be called.

Explain how procedures and functions are called in a PL/SQL block ?

Description : Explain how procedures and functions are called in a PL/SQL block ?

Last Answer : Function is called as part of an expression. sal := calculate_sal ('a822'); procedure is called as a PL/SQL statement calculate_bonus ('A822');

What are the unrestricted procedures used to change the popup screen position during run time ?

Description : What are the unrestricted procedures used to change the popup screen position during run time ?

Last Answer : Anchor-view Resize -View Move-View.

What are stand-alone procedures?

Description : What are stand-alone procedures?

Last Answer : Procedures that are not part of a package are known as stand-alone because they independently defined. A good example of a stand-alone procedure is one written in a SQL*Forms application. These ... limitation of stand-alone procedures is that they are compiled at run time, which slows execution.

What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?

Description : What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?

Last Answer : % TYPE provides the data type of a variable or a database column to that variable. % ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in ... database definition of a column in a table changes, the data type of a variable changes accordingly.

Where the integrity constraints are stored in Data Dictionary ?

Description : Where the integrity constraints are stored in Data Dictionary ?

Last Answer : The integrity constraints are stored in USER_CONSTRAINTS.

Can cursor variables be stored in PL/SQL tables.If yes how. If not why?

Description : Can cursor variables be stored in PL/SQL tables.If yes how. If not why?

Last Answer : No, a cursor variable points a row which cannot be stored in a two- dimensional PL/SQL table.

What are the different types of PL/SQL program units that can be defined and stored in ORACLE database ?

Description : What are the different types of PL/SQL program units that can be defined and stored in ORACLE database ?

Last Answer : Procedures and Functions,Packages and Database Triggers.

What is a stored procedure ?

Description : What is a stored procedure ?

Last Answer : A stored procedure is a sequence of statements that perform specific function.

Where the Pre_defined_exceptions are stored ?

Description : Where the Pre_defined_exceptions are stored ?

Last Answer : In the standard package. Procedures, Functions & Packages ;

Name the tables where characteristics of Package, procedure and functions are stored ?

Description : Name the tables where characteristics of Package, procedure and functions are stored ?

Last Answer : User_objects, User_Source and User_error.

What is the job of the information stored in data-dictionary?

Description : What is the job of the information stored in data-dictionary?

Last Answer : The information in the data dictionary validates the existence of the objects, provides access to them, and maps the actual physical storage location.

Name the buffer in which all the commands that are typed in are stored

Description : Name the buffer in which all the commands that are typed in are stored

Last Answer : ‘Edit’ Buffer

What information is stored in Control File?

Description : What information is stored in Control File?

Last Answer : Oracle Database must have at least one control file. It's a binary file contains some of the following information: ▪ The database name and unique ID ▪ The timestamp of database creation ... app/oracle/oradata/DB1_SID /control01.ctl, /u01/app/oracl e/flash_recovery_area/DB1_SID/c ontrol02.ctl

What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode ?

Description : What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode ?

Last Answer : Complete database recovery from disk failure is possible only in ARCHIVELOG mode. Online database backup is possible only in ARCHIVELOG mode.

What are the advantages of VIEW ?

Description : What are the advantages of VIEW ?

Last Answer : To protect some of the columns of a table from other users.To hide complexity of a query.To hide complexity of calculations.

What are the advantages of having a Package ?

Description : What are the advantages of having a Package ?

Last Answer : Increased functionality (for example,global package variables can be declared and used by any proecdure in the package) and performance (for example all objects of the package are parsed compiled, and loaded into memory once)

What is a package ? What are the advantages of packages ? What is Pragma EXECPTION_INIT ? Explain the usage ?

Description : What is a package ? What are the advantages of packages ? What is Pragma EXECPTION_INIT ? Explain the usage ?

Last Answer : The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error. e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

It is possible to use raw devices as data files and what is the advantages over file. system files ?

Description : It is possible to use raw devices as data files and what is the advantages over file. system files ?

Last Answer : Yes. The advantages over file system files. I/O will be improved because Oracle is bye-passing the kernnel which writing into disk. Disk Corruption will be very less.

What are the advantages of views?

Description : What are the advantages of views?

Last Answer : Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table. - Hide data complexity. - Simplify commands for the user. - Present the data in a different perspective from that of the base table. - Store complex queries.

Advantages of DBMS?

Description : Advantages of DBMS?

Last Answer : ∙ Redundancy is controlled. ∙ Unauthorized access is restricted. ∙ Providing multiple user interfaces. ∙ Enforcing integrity constraints. ∙ Providing backup and recovery.

How does one backup a database using the export utility? (for DBA

Description : How does one backup a database using the export utility? (for DBA

Last Answer : Oracle exports are "logical" database backups (not physical) as they extract data and logical definitions from the database into a file. Other backup strategies normally back-up the physical data ... exports include more information about the database in the export file than user level exports.

Give the reasoning behind using an index.

Description : Give the reasoning behind using an index.

Last Answer : Faster access to data blocks in a table.

How you will avoid your query from using indexes?

Description : How you will avoid your query from using indexes?

Last Answer : SELECT * FROM emp Where emp_no+' '=12345; i.e you have to concatenate the column name with space within codes in the where condition. SELECT /*+ FULL(a) */ ename, emp_no from emp where emp_no=1234; i.e using HINTS

What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function?

Description : What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function?

Last Answer : 1,000,00

Why is Oracle not using the damn index? (for DBA

Description : Why is Oracle not using the damn index? (for DBA

Last Answer : This problem normally only arises when the query plan is being generated by the Cost Based Optimizer. The usual cause is because the CBO calculates that executing a Full Table Scan would be faster ... with AUTOTRACE to see the statistics. Compare this to the explain plan when not using an index.

How does one backup a database using RMAN? (for DBA

Description : How does one backup a database using RMAN? (for DBA

Last Answer : The biggest advantage of RMAN is that it only backup used space in the database. Rman doesn't put tablespaces in backup mode, saving on redo generation overhead. RMAN will re-read database ... directly to tape. Alternatively one can backup to disk and then manually copy the backups to tape.

How can one see who is using a temporary segment? (for DBA

Description : How can one see who is using a temporary segment? (for DBA

Last Answer : For every user using temporary space, there is an entry in SYS.V$_LOCK with type 'TS'. All temporary segments are named 'ffff.bbbb' where 'ffff' is the file it is in and 'bbbb' is ... For usage stats, see SYS.V_$SORT_SEGMENT From Oracle 8.0, one can just query SYS.v$sort_usage. Look at these

What is Enterprise Structure Configurator (ESC) and what are the benefits of using it?

Description : What is Enterprise Structure Configurator (ESC) and what are the benefits of using it?

Last Answer : The ESC is an interview-based tool that guides you through the configuration of your enterprise structures. Use the ESC as part of your set up to define the organization structures and job and position structures of the enterprise.

When creating a user, what permissions must you grant to allow them to connect to the database?

Description : When creating a user, what permissions must you grant to allow them to connect to the database?

Last Answer : Grant the CONNECT to the user.

What is pseudo columns ? Name them?

Description : What is pseudo columns ? Name them?

Last Answer : A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. This section describes these pseudocolumns: * CURRVAL * NEXTVAL * LEVEL * ROWID * ROWNUM

If two groups are not linked in the data model editor, What is the hierarchy between them?

Description : If two groups are not linked in the data model editor, What is the hierarchy between them?

Last Answer : Two group that is above are the left most rank higher than the group that is to right or below it.

Where are my TEMPFILES, I don't see them in V$DATAFILE or DBA_DATA_FILE? (for DBA

Description : Where are my TEMPFILES, I don't see them in V$DATAFILE or DBA_DATA_FILE? (for DBA

Last Answer : Tempfiles, unlike normal datafiles, are not listed in v$datafile or dba_data_files. Instead query v$tempfile or dba_temp_files: SELECT * FROM v$tempfile; SELECT * FROM dba_temp_files;

Following are the advantages of tank irrigation a. In a seasonal rainfall, it is very useful b. Only options a and c c. Abundant rain water can be stored d. None of the above

Description : Following are the advantages of tank irrigation a. In a seasonal rainfall, it is very useful b. Only options a and c c. Abundant rain water can be stored d. None of the above

Last Answer : b. Only options a and c

Quality control in the final analysis is : 1. using the technical procedures of monitoring project results to decide if the outputs meet the requirements 2. a production system 3. an inspection system 4. fitness for purpose

Description : Quality control in the final analysis is : 1. using the technical procedures of monitoring project results to decide if the outputs meet  the requirements  2. a production system  3. an inspection system  4. fitness for purpose

Last Answer : 1. using the technical procedures of monitoring project results to decide if the outputs meet  the requirements

The strategy of adjusting and optimising energy using systems and procedures so as to reduce energy requirements per unit of output while holding constant or reducing total cost of producing the output from the systems is…………. a) Energy Management b) Energy audit c) Energy utilisation d) Energy wastage

Description : The strategy of adjusting and optimising energy using systems and procedures so as to reduce energy requirements per unit of output while holding constant or reducing total cost of producing the output from the ... is . a) Energy Management b) Energy audit c) Energy utilisation d) Energy wastage

Last Answer : Energy Management

11) Designing research procedures that produce reliable marketing data means that: A)others using the same procedure will get almost identical data B)the procedure gives results that support the hypothesis C)the procedure gives results that contradict other research studies D)sampling must be done in a completely random manner

Description : 11) Designing research procedures that produce reliable marketing data means that: A)others using the same procedure will get almost identical data B)the procedure gives results that support the ... that contradict other research studies D)sampling must be done in a completely random manner

Last Answer : A)others using the same procedure will get almost identical data

What is Digital Forensic? A. Process of using scientific knowledge in analysis and presentation of evidence in court B. The application of computer science and investigative procedures for a legal purpose involving the analysis of digital evidence after proper search authority, chain of custody, validation with mathematics, use of validated tools, repeatability, reporting, and possible expert presentation C. A process where we develop and test hypotheses that answer questions about digital events D. Use of science or technology in the investigation and establishment of the facts or evidence in a court of law

Description : What is Digital Forensic? A. Process of using scientific knowledge in analysis and presentation of evidence in court B. The application of computer science and investigative procedures for a legal ... technology in the investigation and establishment of the facts or evidence in a court of law

Last Answer : B. The application of computer science and investigative procedures for a legal purpose involving the analysis of digital evidence after proper search authority, chain of custody, validation with mathematics, use of validated tools, repeatability, reporting, and possible expert presentation

What is Digital Forensic? a. Process of using scientific knowledge in analysis and presentation of evidence in court b. The application of computer science and investigative procedures for a legal purpose involving the analysis of digital evidence after proper search authority, chain of custody, validation with mathematics, use of validated tools, repeatability, reporting, and possible expert presentation c. A process where we develop and test hypotheses that answer questions about digital events d. Use of science or technology in the investigation and establishment of the facts or evidence in a court of law

Description : What is Digital Forensic? a. Process of using scientific knowledge in analysis and presentation of evidence in court b. The application of computer science and investigative procedures for a legal ... technology in the investigation and establishment of the facts or evidence in a court of law

Last Answer : b. The application of computer science and investigative procedures for a legal purpose involving the analysis of digital evidence after proper search authority, chain of custody, validation with mathematics, use of validated tools, repeatability, reporting, and possible expert presentation

What is Digital Forensic? (A) Process of using scientific knowledge in analysis and presentation of evidence in court (B) The application of computer science and investigative procedures for a legal purpose involving the analysis of digital evidence after proper search authority, chain of custody, validation with mathematics, use of validated tools, repeatability, reporting, and possible expert presentation (C) process where we develop and test hypotheses that answer questions about digital events (D) Use of science or technology in the investigation and establishment of the facts or evidence in a court of law

Description : What is Digital Forensic? (A) Process of using scientific knowledge in analysis and presentation of evidence in court (B) The application of computer science and investigative procedures for a ... or technology in the investigation and establishment of the facts or evidence in a court of law

Last Answer : (B) The application of computer science and investigative procedures for a legal purpose involving the analysis of digital evidence after proper search authority, chain of custody, validation with mathematics, use of validated tools, repeatability, reporting, and possible expert presentation

What is Digital Forensic? A. Process of using scientific knowledge in analysis and presentation of evidence in court B. The application of computer science and investigative procedures for a legal purpose involving the analysis of digital evidence after proper search authority, chain of custody, validation with mathematics, use of validated tools, repeatability, reporting, and possible expert presentation C. process where we develop and test hypotheses that answer questions about digital events D. Use of science or technology in the investigation and establishment of the facts or evidence in a court of law

Description : What is Digital Forensic? A. Process of using scientific knowledge in analysis and presentation of evidence in court B. The application of computer science and investigative procedures for a legal ... technology in the investigation and establishment of the facts or evidence in a court of law

Last Answer : B. The application of computer science and investigative procedures for a legal purpose involving the analysis of digital evidence after proper search authority, chain of custody, validation with mathematics, use of validated tools, repeatability, reporting, and possible expert presentation

The objective of quality control mandates that a public accounting firm should establish policies and procedures for professional development which provide reasonable assurance that all entry-level personnel a. Prepare working papers which are standardized in form and content. b. Will advance within the organization. c. Develop specialties in specific areas of public accounting. d. Have the knowledge required to enable them to fulfill responsibilities assigned

Description : The objective of quality control mandates that a public accounting firm should establish policies and procedures for professional development which provide reasonable assurance that all entry-level ... accounting. d. Have the knowledge required to enable them to fulfill responsibilities assigned

Last Answer : Have the knowledge required to enable them to fulfill responsibilities assigned