Saturday, June 18, 2011
16.Difference between Char and Varchar2?
Varchar2 is similar to char but can store available number of characters and while querying the table varchar2 will trims the extra spaces and fetches the rows that exactly match the criteria.
17.Difference between LOB and LONG data types?
The maximum size of an LOB is 4GB. It will support random access to data where in LONG maximum size is 2GB. It will support sequential access to data.
18.Single Row functions: It will work on single row and give result for all the rows.
Ex: to_char, to_date etc.
19.Group Functions: It will work on group of rows in a table and gives a single row result. Ex: Sum(), Avg(), min(), max().. Etc.
20.String Handling Functions?
Instr – it returns the position of the string where it occur according to the parameters.
Instrb – instr and instrb returns same but in the form of bytes.
Substr – It returns the portion of a string depending on the parameters from and to.
Substrb – Substr and Substrb returns the same thing but Substrb returns in the form of bytes
21.Sign: Sign is a function it will take numbers, as inputs and it will give
i. 1 for positive integer
ii. -1 for negative integer
iii. 0 for ZERO
SQL> Select sign(-1234) from dual; O/P: -1
22.Differences between UNION and UNION ALL?
Union: The values of the first query are returned with the values of the second query eliminating the duplicates.
Union All: The values of the first query are returned with the values of the second query including the duplicates.
23.Difference between NVL and NVL2 functions?
NVL is used to fill a NULL value to known value. NVL2 will identify the NULL values and Filled values it returns exp3 if it is null otherwise it returns exp2. We have to pass 3 parameters for NVL2 and 2 parameters for NVL.
24.How can we compare range of values with out using the CASE?
By using Decode with in Decode.
25.Can we Decode with in a Decode?
YES
26.Decode and Case Difference?
Case compares a Range of values and Decode will work as if else statement.
27.Difference between Replace and Translate?
Replace is used to replace the whole string and we can pass null values in replace. Translate is used to translate character-by-character here we have to pass the three parameters.
28.Difference between where and having clause?
Where used to specify condition and used to restrict the data. Having used to specify the condition on grouped results and used to filter the data.
29.Difference between IN and EXISTS clause?
EXISTS gives the status of the inner query. If the inner query is success then it returns true other wise it returns false and IN will compare the list of values.
30.Difference between subquery and correlated subquery?
Query with in a query is subquery. Inner query will executes first and based on the result the outer query will be displayed. Correlated subquery outer query will executes first and then inner query will be executed.
Saturday, June 18, 2011 by Team search · 0
1. Difference between DCL and DML commands?
DCL commands will commit implicitly where in DML commands we have to commit explicitly.
2. Table, Rowid, Rownum
Table is a database object, which is having more than one column associated with its data type.
Rowid is the unique binary address of the row in a table
Rownum it is a temporary number in a memory and was assigned to each row selected by the statement.
3. Pseudo-Columns
Columns that are not created explicitly by the user and can be used explicitly in queries. The pseudo-Columns are rowid, rownum, currval, nextval, sysdate, and level
4. What is a View?
View is Virtual Table, which hold the data at runtime
5. Difference between ordinary view and complex view?
Simple views can be modified easily and these cant hold the data where as complex views cant be modified directly and complex view can hold the data to modify a complex view we have to use INSTEAD OF TRIGGERS.
6. Forced view
Creating a view on a table, which is not there in the database.
7. Inline view
Inline view is basically a subquery with an alias that you can use like a view inside a SQL statement.
8. What is an INDEX and what are the types?
INDEX is a database object used in oracle to provide quick access to rows.
9. Synonym
Synonym is an alias name for any database object like tables, views, functions, procedures.
10. SELECT statement syntax?
SELECT From
Where Group by
Having
Connect prior
11. What is Constraint? Different Constraints?
Constraints are representators of the columns to enforce data entity and consistency. UNIQUE, NOT NULL, Primary key, foreign key, Check.
12. Difference between Primary key and Unique + Not Null constraints?
Unique + Not Null is a combination of two constraints and we can use more than one Unique + Not Null in any table. Primary Key is a single constraint we can use only one time for a table. It can be a referential key for any column in any table.
13. What is NULL?
Default Value.
14. Dual Table
It is a one row, one column table with value X.
15. Difference between Truncate and Delete?
Truncate will delete all the rows from the table with out any condition. It will commit automatically when it fires Where delete will delete all or specified rows based upon the condition here we have to commit explicitly.
by Team search · 0
Wednesday, June 15, 2011
Below script will help you to release hold on AP invoice in oracle apps R12
API: AP_HOLDS_PKG.RELEASE_SINGLE_HOLD
Test Instance: R12.1.1
Tables Affected: AP_HOLDS_ALL
Script:
SET serveroutput on;
DECLARE
x_invoice_id NUMBER;
x_hold_lookup_code VARCHAR2 (200);
x_release_lookup_code VARCHAR2 (200);
x_held_by NUMBER;
x_calling_sequence VARCHAR2 (200);
v_context VARCHAR2 (10);
v_check_flag VARCHAR2 (1);
FUNCTION set_context (
i_user_name IN VARCHAR2,
i_resp_name IN VARCHAR2,
i_org_id IN NUMBER
)
RETURN VARCHAR2
IS
v_user_id NUMBER;
v_resp_id NUMBER;
v_resp_appl_id NUMBER;
v_lang VARCHAR2 (100);
v_session_lang VARCHAR2 (100) := fnd_global.current_language;
v_return VARCHAR2 (10) := 'T';
v_nls_lang VARCHAR2 (100);
v_org_id NUMBER := i_org_id;
/* Cursor to get the user id information based on the input user name */
CURSOR cur_user
IS
SELECT user_id
FROM fnd_user
WHERE user_name = i_user_name;
/* Cursor to get the responsibility information */
CURSOR cur_resp
IS
SELECT responsibility_id, application_id, LANGUAGE
FROM fnd_responsibility_tl
WHERE responsibility_name = i_resp_name;
/* Cursor to get the nls language information for setting the language context */
CURSOR cur_lang (p_lang_code VARCHAR2)
IS
SELECT nls_language
FROM fnd_languages
WHERE language_code = p_lang_code;
BEGIN
/* To get the user id details */
OPEN cur_user;
FETCH cur_user
INTO v_user_id;
IF cur_user%NOTFOUND
THEN
v_return := 'F';
END IF; --IF cur_user%NOTFOUND
CLOSE cur_user;
/* To get the responsibility and responsibility application id */
OPEN cur_resp;
FETCH cur_resp
INTO v_resp_id, v_resp_appl_id, v_lang;
IF cur_resp%NOTFOUND
THEN
v_return := 'F';
END IF; --IF cur_resp%NOTFOUND
CLOSE cur_resp;
/* Setting the oracle applications context for the particular session */
fnd_global.apps_initialize (user_id => v_user_id,
resp_id => v_resp_id,
resp_appl_id => v_resp_appl_id
);
/* Setting the org context for the particular session */
mo_global.set_policy_context ('S', v_org_id);
/* setting the nls context for the particular session */
IF v_session_lang != v_lang
THEN
OPEN cur_lang (v_lang);
FETCH cur_lang
INTO v_nls_lang;
CLOSE cur_lang;
fnd_global.set_nls_context (v_nls_lang);
END IF; --IF v_session_lang != v_lang
RETURN v_return;
EXCEPTION
WHEN OTHERS
THEN
RETURN 'F';
END set_context;
BEGIN
--1. Set applications context if not already set.
v_context := set_context ('&user', '&respname', '&org_id');
IF v_context = 'F'
THEN
DBMS_OUTPUT.put_line ('Error while setting the context');
END IF;
mo_global.init ('SQLAP');
x_invoice_id := 1425;
x_hold_lookup_code := 'LINE VARIANCE';
x_release_lookup_code := 'VARIANCE CORRECTED';
x_held_by := 5;
x_calling_sequence := NULL;
ap_holds_pkg.release_single_hold
(x_invoice_id => x_invoice_id,
x_hold_lookup_code => x_hold_lookup_code,
x_release_lookup_code => x_release_lookup_code,
x_held_by => x_held_by,
x_calling_sequence => x_calling_sequence
);
BEGIN
SELECT 'Y'
INTO v_check_flag
FROM ap_holds_all
WHERE invoice_id = x_invoice_id
AND release_lookup_code = 'VARIANCE CORRECTED';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_check_flag := 'N';
END;
DBMS_OUTPUT.put_line ('Hold Released (Y/N) : ' || v_check_flag);
END;
Wednesday, June 15, 2011 by Team search · 2
Subscribe to:
Posts (Atom)
Disclaimer
The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.