Saturday, June 18, 2011

SQL Basics - Interview Questions Part 2


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

SQL Basics - Interview Questions Part 1



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

API to release hold on AP invoice in R12 (AP_HOLDS_PKG.RELEASE_SINGLE_HOLD)

                                                                                        

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

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.