Oracle Apex ---oracle service instance xe failed message when install.

Solution :

1. Try to delete the service using the command prompt:

1. Click Start, type ‘cmd’ in the search field, and when ‘cmd’ shows up in the list of options, right click it and select ‘Run as Administrator’.
2. At the Command Prompt window, type “sc delete OracleServiceXE” without the quotes and press Enter.
3. Type “Exit” and press Enter.

Hope it helps. 

C:\> sc stop OracleServiceXE                      (to Stop service)

C:\> sc delete OracleServiceXE                      (to remove)

                             

Read More

row_number(),rank(),dense_rank().

Row_Number()

This function will assign a unique id to each row returned from the query.

Consider the following query:


DECLARE @Table TABLE (
      Col_Value varchar(2)
)

INSERT INTO @Table (Col_Value)
      VALUES ('A'),('A'),('A'),('B'),('B'),('C'),('C');

SELECT
      Col_Value,
      ROW_NUMBER() OVER (ORDER BY Col_Value) AS 'RowID'
FROM
      @Table;   
After executing it, we will get:

Col_Value RowID
A 1
A 2
A 3
B 4
B 5
C 6
C 7
As we notice, each and every row has a unique ID.

Rank()

This function will assign a unique number to each distinct row, but it leaves a gap between the groups. Let me explain with a query, we will use the same query we used above with Rank().


SELECT
      Col_Value,
      Rank() OVER (ORDER BY Col_Value) AS 'RowID'
FROM
      @Table;
This query will return:

Col_Value RowID
A 1
A 1
A 1
B 4
B 4
C 6
C 6
As we can see, rowid is unique for each distinct value, but with a gap. What is this gap?

This gap represents number of occurrence. For example: value ‘a’ is repeated thrice and has rank ‘1’, the next rank will be 1+3=4. Same with the next value 4+2=6 and so on.

Dense_Rank()

This function is similar to Rank with only difference, this will not leave gaps between groups.

So if we use the same query used for Rank, then:

SELECT
      Col_Value,
      DENSE_RANK() OVER (ORDER BY Col_Value) AS 'RowID'
FROM
      @Table;
We will get:

Col_Value RowID
A 1
A 1
A 1
B 2
B 2
C 3
C 3
So it is clear that it generates a unique id for each group and without repetition.

As we are clear now what these functions do, we can use them in different scenarios based on our need

Read More

PLSQL LOOP USE

declare
counter number :=0;
result number;
begin
loop
counter := counter+1;
result := 19*counter;
dbms_output.put_line('19'||'*'||counter||'='||result);
if  counter>=10
then  exit;
end if;
end loop;
end;

Same code
 declare
counter number :=0;
result number;
begin
loop
counter := counter+1;
result := 19*counter;
dbms_output.put_line('19'||'*'||counter||'='||result);
EXIT WHEN counter>=10;
 end loop;
end;

--while loop

declare
counter number :=1;
result number;
begin
while counter<=10 loop
result := 19*counter;
dbms_output.put_line('19'||'*'||counter||'='||result);
counter := counter+1;
end loop;
end;
--for loop

declare
result number;
begin
for i in 1..10 loop
result := 19*i;
dbms_output.put_line('19'||'*'||i||'='||result);
end loop;
end;



output will be shown as

19*1=19
19*2=38
19*3=57
19*4=76
19*5=95
19*6=114
19*7=133
19*8=152
19*9=171
19*10=190

---

Read More