Generate Random number using SQL Query

Generate Random number

Here is the script to generate the random 10 digits number using SQL. And of course, this can be modified accordingly to suit the requirements.


Select SUBSTR('0000000000'||TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1,10000000000),0)),-10) from dual;


This Select query generates a 10 digit random number. This will be useful for using generating primary keys when using on the primary key tables.

Example of FORALL Insert statement

FORALL Insert statement.

As we all know, FORALL is much faster than the FOR LOOP for processing.It is usually used for the BULK processings records.

Here is simple example of the usage of the FORALL Insert statement.

TYPE cm_per_tbl_type IS TABLE OF per_table%ROWTYPE INDEX BY PLS_INTEGER

l_per_tbl_type cm_per_tbl_type;

IF l_per_tbl_type .count > 0
THEN

          FORALL l_cnt IN l_per_tbl_type.FIRST .. l_per_tbl_type.LAST
          INSERT INTO cmx_per_table
          values  (l_per_tbl_type(l_cnt).per_id,l_per_tbl_type(l_cnt).per_name);
END IF;

The same can be modified according to the requirements and be used.

Command to use SQL*LOADER

SQL*LOADER , a oracle based bulk loading utility. The following shows the way to load the data using the SQL*LOADER.

Using SID

sqlldr <Username>/<password>@"(DESCRIPTION\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=<HostName>)(PORT\=1521))(CONNECT_DATA\=(SERVER\=DEDICATED)(SID\=<SID>)))" control="Control_file.ctl" log="log.txt",bad="bad.txt" data="Data_file.csv"

Creating DataModel and Report in BI Publisher - Part 2

Part2 :

Once the RTF development is completed, start the creation of Report

Click on New Report:

 

Click on existing data model:

 

Select the datamodel which is developed

 

After selecting the Datamodel click on Next, Then select Use Report Editor and click on finish

 

Save the Report .

Click on Upload and enter the name and the RTF location: 

Click on View Report. Pass the parameter and the report gets generated:

 

 

Click on HTML/PDF/EXCEL etc to view the Report.