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

2. 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;

3. 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