Skip to main content

TABLE() casting in oracle - Useful to generate values in a flash

--create a type with number datatype
SQL> create or replace type type1
2 is table of number;
3 /

Type created.


--Create function for generating numbers and assign them in the type TYPE1
--Return the list
SQL> create or replace function function1(n IN number)
2 return type1
3 as
4 list_1 type1:= type1(0);
5 i number;
6 begin
7 for i in 1..n
8 loop
9 list_1(i):=i;
10 list_1.extend;
11 end loop;
12
13 return list_1;
14 end;
15 /

Function created.


--Now table() casting would help us list the values in table-like format
SQL> Select * from table(function1(10))
2 where column_value is not null;

COLUMN_VALUE
------------
1
2
3
4
5
6
7
8
9
10

10 rows selected.

SQL>

Comments

Post a Comment

Popular posts from this blog

Design and Operations ( Employee- Model )

-- DB design drop table emp_Salary; drop table emp_Designation; drop table emp; drop table Designations; drop table Departments; drop table Salary_Bands; drop table Addresses; Create table emp( emp_id number, emp_Name varchar2(20), emp_address_id varchar2(10), emp_created_date date, emp_created_by varchar2(20), constraint e_pk primary key(emp_id) ); Create table Departments( dept_id number, dept_name Varchar2(20), constraints dept_pk primary key(dept_id) ); Create table Designations( dept_id number references Departments(dept_id), desg_id number, desg_description varchar2(20), constraints desg_pk primary key(dept_id,desg_id) ); Create table emp_Designation( emp_id number references emp(emp_id), emp_des_dept_id number references departments(dept_...
Post a Comment
Read more

Minimum / Maximum from the given list

File: Execute.java -------------------------------------------------------------------------------------- package org.developersbrain.Solutions; public class Execute { public static int min(int[] arrList){ int minV1=0; int minV2=0; int aLen=arrList.length; minV1=arrList[0]; minV2=arrList[aLen-1]; for(int i=0,j=aLen-1;i<=aLen/2;i++,j--){ if(minV1 > arrList[i]){ minV1=arrList[i]; } if(minV2 > arrList[j]){ minV2=arrList[j]; } } if(minV2<=minV1){ return minV2; }else{ return minV1; } } public static int max(int[] arrList){ int minV1=0; int minV2=0; int aLen=arrList.length; minV1=arrList[0]; minV2=arrList[aLen-1]; for(int i=0,j=aLen-1;i<=aLen/2;i++,j--){ if(minV1 < arrList[i]){ minV1=arrList[i]; } if(minV2 < arrList[j]){ minV2=arrList[j]; } } if(minV2>=minV1){ return minV2; }else{ return minV1; } } ...
Post a Comment
Read more

SQL - Binary Search Tree

Problem Statement You are given a table,   BST , containing two columns:   N  and   P,  where   N   represents the value of a node in   BST , and   P   is the parent of   N . Write a query to find the node type of   BST   ordered by the value of the node. Output one of the following for each node: ·          Root : If node is root node. ·          Leaf : If node is leaf node. ·          Inner : If node is neither root nor leaf node. Sample Input                 N          P 1 2 3 2 6 8 9 8 2 5 8 5 5         null Sample Output 1 Leaf 2 Inner 3 Leaf 5 Root 6 Leaf 8 Inner 9 Leaf Explanation The   BST ...
Post a Comment
Read more