-
Convert SQL to Oracle Procedure using cursor
SELECT APE.DAT_INSERT_DATE as "Payment Entry Date", Case when APE.TXT_INTERMEDIARY_CD is null or APE.TXT_PAYER_CUSTOMER_ID APE.TXT_INTERMEDIARY_CD then (Select TXT_CUSTOMER_NAME from GENMST_CUSTOMER where TXT_CUSTOMER_CD = APE.TXT_PAYER_CUSTOMER_ID) else(Select TXT_INTERMEDIARY_NAME from GENMST_INTERMEDIARY where TXT_INTERMEDIARY_CD = APE.TXT_PAYER_CUSTOMER_ID) End as "Payer Name" , APE.TXT_PAYER_CUSTOMER_ID...
joybanerjee
- Dec 20th, 2007
create or replace procedure sql_to_procedure as declare cursor c1 is SELECT APE.DAT_INSERT_DATE as "Payment Entry Date",APE.TXT_INTERMEDIARY_CD AS "Intermediary ID",APE.TXT_PAYER_C...
-
What is the systematic way of tuning a query?
dinu78
- Dec 21st, 2007
1) First identify the current excecution plan by using showplan.In most cases, creating and then forcing usage of the indexes result in drastic improvement.1) Ensure that indexes(single or composite)&...
-
Oracle refcursor and procedure
How to pass result set using refcursor from one package procedure to another package procedure? and code also
MAQDOOMAAMER
- Dec 25th, 2007
CREATE TABLE employees (empid NUMBER(5),empname VARCHAR2(30));INSERT INTO employees (empid, empname) VALUES (1, 'Dan Morgan');INSERT INTO employees (empid, empname) VALUES (2, '...
-
What is the difference between IS/AS while creating a procedure ?i.e Create or Replace procedure is/as .....
kirangsv
- Jan 6th, 2008
Well there is no difference between is/as as they said both r one and the same when i have gone thru the book u can use either is / as
cutesree
- Dec 17th, 2007
There is no difference between is/as ... is is used for creating a new procedure with a new name where as 'as' is used to create a new procedure with existing procedure name... it will not display "procedure already exists".
-
What is Oracle collections
kirangsv
- Jan 6th, 2008
A collection can be loosely defined as a group of ordered elements, all of the same type that allows programmatic access to its elements through an index. Commonly used collection types used in the pr...
dev5000
- Dec 26th, 2007
It is kind of datatype.Collection is nothing but?a convenient?way to manipulate many variables at once.There are 3 types of collection.1. INDEX BY TABLE2. NESTED TABLE3. VARRAYS
-
How can you send the output from a Function or Procedure to a 'Flat file'?
jayanthanr
- Jan 8th, 2008
To created flat files, or read flat files in a procedure or function, the built-ins in the UTL_FILE package can be used.
-
What do you mean by OCI, Data guard and Advance queue responsibilities for a Oracle developers?
nitingalande
- Jan 13th, 2008
OCI stands for Oracle Call Interface, it is a interface api used to access oracle database from C programs. Data Guard stands for it is multiple DB servers enviornment configured for Physical as well ...
-
What does a following command do. DELETE (6,3);
-
For each hour, list the number of called, total transactions' values, and total duration.
For each hour, list the number of called, total transactions' values, and total duration?if you have these tables:1- 'calls'columns: called_number, Customer_ID, call_date, call_time_started, Call_time_ended,transaction_no.2- 'tariff'columns: Tarriff_per_Sec, country_code, time_started, time_ended.
-
How do you compile a package?
Larry
- Nov 6th, 2007
Execute package_name;
-
Explain the usage of WHERE CURRENT OF clause in cursors ?
WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor. Database Triggers
g_sidhu
- Jan 31st, 2008
When referencing the current row from an explicit cursor, use the WHERE CURRENT OF clause. This allows you to apply updates and deletes to the row currently being addressed, without the need to explic...
Shefali
- Aug 16th, 2006
"WHERE CURRENT OF" clause is used for updating or deleting rows selected by the cursor with FOR UPDATE cluase.It explicitily locks the row for updating or deleting.
-
Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?
It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.
g_sidhu
- Jan 31st, 2008
COMMIT, ROLLBACK, and SAVEPOINT statements are not allowed within the trigger body. It is possible to commit or rollback indirectly by calling a procedure, but it is not recommended because of side effects to transactions.
jockey
- Aug 2nd, 2007
Using autonomous_transaction it is possible
-
What is a stored procedure ?
A stored procedure is a sequence of statements that perform specific function.
g_sidhu
- Jan 31st, 2008
A procedure is a named PL/SQL block that can accept parameters (sometimes referred to as arguments), and be invoked. Generally speaking, you use a procedure to perform an action. A procedure has ...
ramesh_resides
- Jun 23rd, 2007
Stored procedure is a sub program and stored in memory for repated execution
-
What are two parts of package ?
The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY. Package Specification contains declarations that are global to the packages and local to the schema.Package Body contains actual procedures and local declaration of the procedures and cursor declarations.
g_sidhu
- Jan 31st, 2008
A package usually has a specification and a body, stored separately in the database.The specification is the interface to your applications. It declares the types, variables, constants, exceptions, cu...
Mahesh Argade
- Jun 28th, 2006
In addition to this Both Package Spec and Package Body are stored separately in the Data basePackage specifications act as an interface for the Package
-
What are advantages fo Stored Procedures / Extensibility,Modularity, Reusability, Maintainability and one time compilation.
g_sidhu
- Jan 31st, 2008
In addition to modularizing application development, stored procedures and functions have thefollowing benefits:1) Improved performanceAvoid reparsing for multiple users by exploiting the shared SQL a...
deviji2000
- Oct 16th, 2007
procedures and function hv many benefits in addition to modularizing application development.1) easy maintenance : sub programs are lo...
-
What are the return values of functions SQLCODE and SQLERRM ?
SQLCODE returns the latest code of the error that has occurred.SQLERRM returns the relevant error message of the SQLCODE.
g_sidhu
- Jan 31st, 2008
• SQLCODE: Returns the numeric value for the error code• SQLERRM: Returns the message associated with the error numberSQLCODE Value &nbs...
acpsrinivas
- Feb 25th, 2006
Pl / Sql Provides Error Information via two Built-in functions, SQLCODE & SQLERRM.SQLCODE Returns the Current Error Code. &nb...
-
What are ref cursors ?
g_sidhu
- Feb 1st, 2008
Cursor variables are like C or Pascal pointers, which hold the memory location (address) of some item instead of the item itself. Thus, declaring a cursor variable creates a pointer, not an item. In P...
nisha
- Aug 24th, 2006
ref cursor is a simple cursor but it is a datatype that allow developers to declare cursor variableref cursor has 2 types:-strong ref cursor where we mention the return type with rowtype.weak cursor w...
-
What happens when DML Statement fails?A.User level rollbackB.Statement Level RollbackC.Sustem Level Rollback
g_sidhu
- Feb 1st, 2008
If a single DML statement fails during execution of a transaction, it s effect is undone by a statement-level rollback, but the changes made by the previous DML statements in the transaction are not discarded. They can be committed or rolled back explicitly by the user.
Chiru
- Jun 21st, 2006
When a DML statement executes (fails/sucess) an automatic Commit is executed. Eg : Create a table t1. Insert a record in t1. Then again to create the same object t1.
-
What is the difference between User-level, Statement-level and System-level Rollback? Can you please give me example of each?
g_sidhu
- Feb 1st, 2008
Statement-level: Part of a transaction can be discarded by an implicit rollback if a statement execution error is detected. If a single DML statement fails during execution of a transaction, it s effe...
kameshakundy
- Nov 13th, 2006
Dear Kamini,May I know what do you mean by PG
-
How can one view all the procedures,functions,triggers and packages created by the user
g_sidhu
- Feb 1st, 2008
SELECT object_name, object_typeFROM user_objectsWHERE object_type in ('PROCEDURE','FUNCTION')ORDER BY object_name;
Sphurti
- Aug 14th, 2006
Basically, here USER_OBJECTS will have all the objects which are created/own by a particular user whereas ALL_OBJECTS will have information of the objects for which a user has access, it may or may not be created by that user.
PL/SQL Interview Questions
Ans