PL/SQL Function is same as PL/SQL Procedure, The only difference is that function must return a value and a procedure may or may not return a value
Blog about Oracle ADF, JDeveloper, PL/SQL, Java, JavaScript, jQuery and Other Web Technologies
Search this blog
Monday 9 April 2018
PL/SQL Function
PL/SQL Function is same as PL/SQL Procedure, The only difference is that function must return a value and a procedure may or may not return a value
You might also like:
Labels:
Call PL/SQL Function
,
PL/SQL Function
,
PL/SQL Tutorials
,
SQL
,
SQL Developer
Saturday 7 April 2018
PL/SQL Procedure
Previously I have posted about PL/SQL Block structure , A piece of code that is organized in a properly defined sequence is called a block
PL/SQL provides two types of blocks
Function- A PL/SQL block that performs a task or set of tasks and returns a value
Procedure- A PL/SQL block that performs a task or set of tasks and may or may not return a value
You might also like:
Labels:
Call PL/SQL Procedure
,
PL/SQL Procedure
,
PL/SQL Tutorials
,
SQL
,
SQL Developer
Monday 2 April 2018
PL/SQL Exceptions
An error that occurs during execution of the program is called exception, Like other programming languages, PL/SQL offers a way to catch these exceptions and handle them.
There are two types of exceptions in PL/SQL
- System-Defined Exceptions
- User-Defined Exceptions
You might also like:
Labels:
Exception Handler
,
PL/SQL Exceptions
,
PL/SQL Tutorials
,
PL/SQL Variables
,
SQL
,
SQL Developer
,
SQL Exception
Friday 23 March 2018
PL/SQL For Loop
PL/SQL FOR loop is used when we need to execute set of statements for the specific number of times and loop operates between the start and end counter values. The counter is always incremented by one and once the counter reaches to end integer value, the loop terminates
The syntax of PL/SQL FOR Loop is like this
FOR counter_variable IN start value.. end value LOOP
statements to execute
END LOOP;
You might also like:
Labels:
For Loop
,
Iteration statements
,
PL/SQL Loops
,
PL/SQL Tutorials
,
PL/SQL Variables
,
SQL
,
SQL Developer
PL/SQL While Loop
PL/SQL WHILE loop is used to execute statements as long as given condition is true and the condition is checked at the beginning of each iteration
The syntax of PL/SQL While loop is like this
WHILE condition
statements to execute
END LOOP;
You might also like:
Labels:
Iteration statements
,
PL/SQL Loops
,
PL/SQL Tutorials
,
SQL
,
SQL Developer
,
While Loop
PL/SQL Basic Exit Loop
In PL/SQL Basic Loop all statements inside the block are executed at least once before loop termination, Basic loop encloses statement between LOOP and END LOOP and there must be an EXIT or EXIT-WHEN condition to terminate the loop
The syntax of Basic Exit loop is like this
LOOP
statements to execute
EXIT; or EXIT-WHEN
END LOOP;
See these examples for better understanding
You might also like:
Labels:
Exit loop
,
EXIT WHEN Loop
,
Iteration statements
,
PL/SQL Basics
,
PL/SQL Loops
,
PL/SQL Tutorials
PL/SQL Loops , Iterative Statement in PL/SQL
Loops are used to repeat execution of a statement or a set of statements multiple times on base of a condition or expression
EXIT and EXIT-WHEN keywords are used to terminate the loop
EXIT- terminates the loop unconditionally and passes control to the next statement after the loop
EXIT-WHEN- terminates the loop when EXIT-WHEN clause is checked and if returns true then the loop is terminated and control is passed to next statement after the loop
The basic syntax of Loop in PL/SQL is like this
LOOP
Set of statements
END LOOP;
You might also like:
Labels:
Exit loop
,
EXIT WHEN Loop
,
For Loop
,
Iteration statements
,
PL/SQL Loops
,
PL/SQL Tutorials
,
SQL
,
SQL Developer
,
While Loop
Thursday 22 March 2018
PL/SQL CASE Statement, Decision Making Statement in PL/SQL
Like real life in programming sometimes we need to execute some code on a specific condition, PL/SQL CASE statement allows us to execute a sequence of instructions based on a selector (A variable, function, expression etc)
and if selector value is equal to value or expression in WHEN clause then corresponding THEN clause will execute and process the statements
You might also like:
Labels:
Conditional Statement
,
Database
,
PL/SQL CASE Statement
,
PL/SQL Tutorials
,
PL/SQL Variables
,
SQL
,
SQL Developer
Wednesday 21 March 2018
PL/SQL Conditions, IF-ELSE Conditional Statement
Like other programming languages, PL/SQL supports decision making statements, These statements are also called conditional statement
Basic Syntax of IF-ELSE is like this in PL/SQL
IF (Condition 1)
THEN
Statement to execute (if condition 1 is true)
ELSIF (Condition 2)
THEN
Statement to execute (if condition 2 is true)
ELSE
Statement to execute (if condition 1& 2 both are false)
END IF;
For a better understanding of concept look at these examples
You might also like:
Labels:
Conditional Statement
,
Database
,
PL/SQL IF
,
PL/SQL Tutorials
,
SQL
,
SQL Developer
Friday 9 March 2018
PL/SQL Variables and Constants
A variable in any programming language is the name of space where values are stored and controlled by our code/program
- We can not use reserve keyword as a variable name
- Variable length should not exceed 30 characters
- The variable name consists of letters followed by other letters, dollar sign, underscore and numerals
- The variable name should be clear and easy to understand
Here we'll learn how to declare and initialize variables in PL/SQL
The basic syntax for declaring a variable in PL/SQL is following
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]
Here variable_name is the identifier of variable and datatype is a valid PL/SQL datatype. CONSTANT and DEFAULT are keywords used to define constants and set default values of variables
You might also like:
Labels:
Database
,
PL/SQL Basics
,
PL/SQL Constants
,
PL/SQL Tutorials
,
PL/SQL Variables
,
SQL
,
SQL Developer
Wednesday 7 March 2018
PL/SQL Basic Syntax, Block Structure and Anonymous block
PL/SQL is highly structured language and it's program units are written in the block of code, In this tutorial, we'll learn about basic syntax and the block structure of PL/SQL
A piece of code that is organized in a properly defined sequence is called a Block. A PL/SQL Block consists of 3 parts
DECLARE
<<declaration >>
--Declare Variables,Constants, Cursors and all elements
BEGIN
<<executable statements>>
--SQL, PL/SQL Commands
EXCEPTION
<<exception handling>>
--Code to handle the exception
END;
You might also like:
Labels:
Anonymous block
,
DBMS_OUTPUT
,
DBMS_OUTPUT.PUT_LINE
,
PL/SQL Basics
,
PL/SQL Tutorials
,
SQL Developer
,
Variables
Tuesday 6 March 2018
PL/SQL Tutorial - What is PL/SQL, Features and Advantages of PL/SQL
PL/SQL is developed by Oracle Corporation to increase/enhance capabilities of SQL, PL/SQL stands for Procedural Language extension to SQL . PL/SQL is highly structured and expressive language and because of its expressive syntax it is very easy to understand and learn
PL/SQL is integrated with Oracle Database and can be called from any other programming language. It is tightly integrated with SQL so it's easy to learn PL/SQL if you have knowledge of SQL
You might also like:
Labels:
new features
,
PL/SQL Basics
,
PL/SQL Function
,
PL/SQL Procedure
,
PL/SQL Tutorials
,
SQL
,
SQL Developer
Tuesday 20 February 2018
Enable DBMS_OUTPUT in Oracle SQL Developer
I hope all of you'll be familiar with Oracle SQL Developer tool , A tool used by database developers to perform DB related tasks efficiently
DBMS_OUTPUT package of PL/SQL enables user to show/print some debugging information and used by learners to run and check small chunks of pl/sql code
Here we'll see how to enable DBMS_OUTPUT package in SQL Developer
Wednesday 13 December 2017
Use ViewObject Query Mode for In-Memory Sorting of data in Oracle ADF
Hello All
Previously I have posted about In-Memory filtering of ViewObject by changing ViewCriteria's query execution mode, Now this post is about In-Memory sorting of records in viewObject. By default sorting and filtering in viewObject works on the rows retrieved from DB
We can change ViewObject Query mode as per our requirement, There are 3 different SQL query mode
You might also like:
Labels:
ADF
,
In-Memory Sorting
,
Model
,
Oracle ADF Tutorial
,
query mode
,
QUERY_MODE_SCAN_VIEW_ROWS
,
sorting
,
ViewObject
Wednesday 29 November 2017
Export ViewObject data to Excel File Using Apache POI in Oracle ADF
Hello All
Previously I have posted about importing data in ADF Table from Excel file
This post is about exporting viewObject data in Excel file using Apache POI API, Apache POI provides HSFF and XSFF to read , create and modify spreadsheets.
You can download POI jars from The APACHE Software Foundation or from here
Other than this you need to use xmlbeans and common-collections Jar
You might also like:
Labels:
ADF
,
Apache POI
,
excel
,
Excel Upload
,
Export
,
Jdeveloper 12.1.3
,
Oracle ADF Tutorial
,
Sample ADF Application
,
ViewObject
Monday 13 November 2017
ADF Basics: Add the row at the end of ViewObject's current RowSet in ADF
This post is about adding a row at the end of current rowset of viewObject without using table or any other UI components binding
Here I have a Department ViewObject (HR Schema), dropped as a table on page and a button to add new row, this button calls a method from model using binding layer
You might also like:
Labels:
add row at the end of ViewObject
,
ADF
,
ADF Basics
,
ADF Bindings
,
createRow
,
Model
,
Oracle ADF Tutorial
,
RowSetIterator
,
ViewObject
,
ViewObjectImpl
Wednesday 13 September 2017
ADF Basics: Filter ViewObject data using getFilteredRows and RowQualifier
Sometimes we need to get filtered data from ViewObject using one or multiple conditions,
Though this is the very basic of framework yet new developers find it confusing.
There are two ways of filtering ViewObject
1. In this we apply WHERE clause on ViewObject and it affects resultSet data, Suppose we have Department ViewObject and we want to see data of DepartmentId 4 on page after filtering, for this viewCritera, bind variables comes in action
ADF Basics: Apply and Change WHERE Clause of ViewObject at runtime programmatically
2. In this user want to get filtered data (Rows) in code only without any effect on ViewObject resultSet (page data), Here I am discussing this point
We can get filtered data from view object using two methods-
You might also like:
Labels:
ADF
,
ADF Basics
,
filter
,
getFilteredRows
,
Jdeveloper
,
Model
,
Oracle ADF Tutorial
,
RowQualifier
,
ViewObject
Wednesday 23 August 2017
Add new row and copy existing row to HTML table using JavaScript
You might also like:
Labels:
Add Row to Html table
,
cloneNode
,
copy row in html
,
CSS
,
Html
,
javascript
,
Web Technology
Sunday 23 July 2017
R Data Types - Vectors, Matrices, Lists, Factors, Data Frames
Like other programming languages, R supports many different data types. You must have seen that variables are used to store data in a program and a data type is assigned to a variable and that variable can hold only that type of data. In this post, we'll learn about R data types and R objects.
Basic data types in R programming are Numeric, Integer, Character, Logical and Complex and other than this R has some unique data types that are called R Objects.
Vectors in R
A Vector is basically a set of values of the same basic data type like numeric character etc. A vector in R is created using the c() function that represents a combination of elements. See this example# A Numeric Vector
numeric_vector <- c(10, 20, 30)
# A Character Vector
character_vector <- c("a", "b", "c")
# A Boolean Vector
boolean_vector <-c(TRUE,FALSE,TRUE)
The output on R Console is Matrices in R
R supports Matrices and a Matrix is a collection of data values in 2 dimensions of the same basic data type, R creates a matrix of values using a matrix() function. See this example Here c(1,2,3,4,5,6,7,8,9) is a numeric vector nrow is the number of rows in the matrix ncol is the number of columns in the matrix#Create a matrix using Vector
test_matrix<-matrix(c(1,2,3,4,5,6,7,8,9), nrow=3, ncol=3)
#Print matrix on console
print(test_matrix)
The output on R Console is Arrays in R
Arrays are the same as any other programming language and in R array is same as a matrix but it can have more than two dimensions. Array in R is created using array() function and uses a vector as input and dim value to create arrays. Here dim =c(2,2,4) means that 4 arrays will be created of 2x2.#We have two vectors here
v1 <- c(1,2,3,7,8,9)
v2 <- c(4,5,6,10,11,12,13,14,15,16)
#Create array using vectors
test_array <- array(c(v1,v2),dim = c(2,2,4))
#print the array on the console
print(test_array)
The output on R Console isLists in R
A List is a set of values that can have the different basic data type, In R List is created using list() function.#A list with different data types #Declare a numeric vector numeric_vector<-c(1,2,3) #Create list test_list<- list("Ashish Awasthi", numeric_vector, 5.3) #Print list print(test_list)The output on R Console
Factors in R
Factors are created using vectors as base and stores unique values as levels, In R Factor object is created using factor() function.# Create a vector with duplicate values emp_names <- c('James','Ram','James','Ashish','Ram','Ashish','James','Ram') # Create a factor object using vector factor_emp <- factor(emp_names) # Print the factor object print(factor_emp)The output on R Console is
Data Frames in R
Data Frame is used for storing data in tables, and this tabular data can have multiple types of vectors like numeric, characters etc. Data Frame can be created using data.frame() function.#A Character Vector string_vector <-c("Ashish", "Awasthi","R") #A Numeric Vector numeric_vector <- c(10, 20, 30.5) #A Boolean Vector boolean_vector<- c(TRUE, FALSE, TRUE) # Create the data frame using all 3 vectors test_df <- data.frame(string_vector,numeric_vector,boolean_vector) #Print result print(test_df)The output on R Console
Though this post gives an idea of using variables, In the next post, we'll learn more about using variables in R programming.
Cheers :) Happy Learning
You might also like:
Labels:
Array in R
,
Boolean
,
Data Frame in R
,
Factor in R
,
List in R
,
Matrix in R
,
R data types
,
R tutorial
,
Vector in R
Monday 17 July 2017
Reinitialise taskFlow in dynamic region and set focus to default activity
Hello All
We all use bounded task flows in ADF application development and to switch between multiple task flows we use concept of dynamic region
Recently I came across a problem about dynamic region and bounded task flows, Scenario is like this
I have dropped a BTF in dynamic region and there is a link on page to open that task flow and those who have used dynamic region would be familiar with this piece of code
You might also like:
Labels:
ADF
,
ADF Taskflow
,
Bounded
,
Oracle ADF Tutorial
,
Refresh
,
region
,
reset
,
ViewController
Wednesday 28 June 2017
Hide values from ADF SelectOneChoice using EL expression on UI
This post is about a specific requirement that is to hide some values from adf based select one choice from UI
Previously I have posted about disabling some items from select one choice and this post uses same steps to hide values from lov
You might also like:
Labels:
ADF
,
ADF UI
,
af:selectItem
,
af:selectOneChoice
,
f:selectItems
,
hide
,
Jdeveloper 12.1.3
,
Jdeveloper 12c
,
Oracle ADF Tutorial
,
Sample ADF Application
,
values
Saturday 3 June 2017
oracle.jbo.domain.DataCreationException: JBO-25009 while using multiple selection component in ADF Faces
Previously I have posted about using multi-selection components (af:selectManyCheckbox, af:selectManyChoice, af:selectManyListbox, af:selectManyShuttle) of ADF Faces. These components make use of list binding and work on base attribute and display attribute concept
You might also like:
Labels:
ADF
,
af:selectManyCheckbox
,
af:selectManyChoice
,
af:selectManyListbox
,
af:selectManyShuttle
,
get Value
,
JBO-25009
,
Jdeveloper 12.1.3
,
Jdeveloper 12c
,
Multiple Selection
,
Oracle ADF Tutorial
Access JAX-WS WebService from Java Class using Web Service Proxy in Jdeveloper
Web Service proxy class is a way to communicate with XML-based WebService using SOAP format,
In short, we can use service proxy class at client to access WebService
In JDeveloper IDE we can easily create client proxy class for WebService, Here in this post I am creating client proxy class to access a JAX-WS web service that I have created in previous blog post
Create POJO based JAX-WS WebService easily with Jdeveloper 12.1.3
Let's see how to implement this
You might also like:
Labels:
Consuming Web Services
,
Java Code
,
JAX-WS
,
Jdeveloper
,
Jdeveloper 12.1.3
,
Jdeveloper 12c
,
SOAP
,
Web Service Proxy
,
Web Services
Monday 22 May 2017
Populate select one choice using Web Service Data Control in ADF Application
My previous post was about creating a JAX-WS Web Service from Java Bean and consuming web service and showing data in ADF table. Now In this post, I am going to elaborate about consuming that Web Service in ADF Application and show Employees data in selectOneChoice component
So for this requirement, We need to use Web Service Data Control and from that WSDL we can create ADF Faces components
Let's see how to implement this
You might also like:
Labels:
ADF
,
af:selectOneChoice
,
choice list
,
Consuming Web Services
,
JAX-WS
,
Jdeveloper 12.1.3
,
Jdeveloper 12c
,
Oracle ADF Tutorial
,
POJO
,
Web Services
Thursday 18 May 2017
Populate data in ADF Table using Web Service Data Control
My previous post was about creating a JAX-WS Web Service from Java Bean. Now In this post, I am going to elaborate about consuming that Web Service in ADF Application and show Employees data in ADF Table
So for this requirement, We need to use Web Service Data Control and from that WSDL we can create ADF Faces components
Let's see how to implement this
You might also like:
Labels:
ADF table
,
af:table
,
Consuming Web Services
,
JAX-WS
,
Jdeveloper 12.1.3
,
Jdeveloper 12c
,
populate
,
SOAP
,
Web Services
,
WSDL
Monday 15 May 2017
Create POJO based JAX-WS WebService easily with Jdeveloper 12.1.3
Hello All
In this post, I am talking about creating a simple JAX-WS web service using Jdeveloper 12.1.3 .
JAX-WS is a Java API for XML Web Services and we can create a JAX-WS easily with Jdeveloper IDE
Idea is to create a web service that shows Employees list with their name, designation, salary and department name so for this, I am going to use POJO Java Class
Let's implement it
You might also like:
Labels:
ADF
,
JAX-WS
,
Jdeveloper 12.1.3
,
Jdeveloper 12c
,
Oracle ADF Tutorial
,
POJO
,
Sample ADF Application
,
SOAP
,
Web Services
Friday 12 May 2017
Understanding Nested Application Modules in Oracle ADF
Application Module acts as a container for view Object and view Link business components that are used in a specific task. It provides data model and data control to show required information and perform action for the client
An application module represents a single transaction and owns one database connection that's why commit and rollback works for all view Objects inside an application module
You might also like:
Labels:
ADF
,
Application Module
,
Data Control
,
Database Connection
,
Jdeveloper 12.1.3
,
Model
,
Nested Application Modules
,
Oracle ADF Tutorial
,
pagedef
,
Performance Tuning
Tuesday 9 May 2017
Check for dirty (Uncommitted) data of current view port (page) on a button click or any event in ADF
Sometimes we need to check for uncommitted data on page and perform actions according to that, Suppose there is a button to navigate to another page but only if there is no uncommitted data in current page
We can use uncommitted data warning property of af:document to show an alert dialog but in that way, we can't execute our custom logic
Previously I have posted about checking dirty data of a transactional data control but in that, we need to check that for each data control separately that is rendering on page
You might also like:
Labels:
ADF
,
ControllerContext
,
Data Control
,
FacesContext
,
FacesMessage
,
isDirty
,
Jdeveloper 12.1.3
,
Oracle ADF Tutorial
,
Uncommited Data
Subscribe to:
Posts
(
Atom
)