Monday, June 11, 2018

LINK PL/SQL

http://www.rebellionrider.com/index.htm


https://www.youtube.com/watch?v=BKclbF559fE     ---Analytical Functions


https://www.youtube.com/watch?v=eHJ6FDmnnuE   ---Materialized Views

https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/01_oview.htm   ----Oracle Docs

https://docs.oracle.com/cd/B19306_01/server.102/b14200.pdf    ----Oracle® Database SQL Reference


ORA-00979 occurs when the GROUP BY clause does not contain all theexpressions in the SELECT clause. Any SELECT expression that is not included in the GROUP function must be listed in the GROUP BY clause




1.       Oracle function can return multiple values? Example/How to return multiple values from a function in Oracle PL/SQL?
A.    Generally we write functions for computing some value. Function can return only one value, though it’s definition may contains more than one return statements. Let us see the below defined function func_test. Its definition is having multiple return statements.
CREATE OR REPLACE
  FUNCTION func_test
    RETURN NUMBER
  AS
  BEGIN
    RETURN 1;
    RETURN 2;
  END;
  /

SELECT func_test AS value FROM dual;

VALUE
------
1
But, upon executing this function, it is returning only one value. Now, Let us see how to return more than one value from function. For this, we need to create a collection/record object.
For example, I am creating numbers collection of NUMBER data type.
CREATE OR REPLACE TYPE numbers IS TABLE OF NUMBER;
/
Below function simply returns 10 numbers values from 1 to 10 in the collection objects of numbers.
CREATE OR REPLACE
  FUNCTION func_multi_val
    RETURN numbers 
  AS
    l_numbers numbers := numbers();
  BEGIN
    FOR i IN 1 .. 10
    LOOP
      l_numbers.EXTEND();
      l_numbers(i) := i;
    END LOOP;
    RETURN l_numbers;
  END;
  /
Now we will check whether our function is returning multiple values or not.

SELECT * FROM TABLE(func_multi_val);
 
COLUMN_VALUE
-------------
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10

  1. SQL Query to get ZERO records when it contains N number of records?
  1. We can get in Two ways as per my knowledge
SELECT * FROM MAK_EMP WHERE 1=2;

SELECT * FROM MAK_EMP
MINUS
SELECT * FROM MAK_EMP;
  1. How many LONG columns are allowind in table?
  1. Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.
4. How can we find select statement is indexed or not?
A.

select INDEX_NAME, TABLE_OWNER, TABLE_NAME, UNIQUENESS from USER_INDEXES
or
select INDEX_NAME, TABLE_OWNER, TABLE_NAME, UNIQUENESS from ALL_INDEXES

No comments:

Post a Comment