Create a Job in PL SQL

Step 1:- Create a table

CREATE TABLE tblTest(

  ID VARCHAR(50),

  DATE_VALUE VARCHAR(50)

);

Step 2:- Create a store procedure

CREATE OR REPLACE PROCEDURE spTest AS

BEGIN

  INSERT INTO tbltest ("ID", date_value) VALUES (SYS_GUID(), SYSDATE);

END;

Step 3:- Create a Job it will run the "spTest" store procedure

begin

  sys.dbms_scheduler.create_job(job_name        => 'TESTJOB', --Job Name

                                job_type        => 'STORED_PROCEDURE', --Job Type i.e. PL/SQL Block,Store Procedure,Executable,Chain

                                job_action      => 'spTest', --Procedure Name

                                start_date      => to_date('28-06-2017 00:00:00',

                                                           'dd-mm-yyyy hh24:mi:ss'), -- Job Start Date

                                repeat_interval => 'Freq=Minutely;Interval=1', -- Job Frequency i.e.

                                                                               -- yearly,Monthly,Weekly,Daily,Hourly

                                                                               -- Minutely,Secondly and Job Running Interval

                                end_date  => to_date('29-06-2017 00:00:00',

                                                     'dd-mm-yyyy hh24:mi:ss'), -- Job End Date

                                job_class => 'DEFAULT_JOB_CLASS',

                                enabled   => true,

                                auto_drop => false,

                                comments  => '');

end;

Step 4:- Select a record

select * from tbltest;

Note:- Job will run each minute because we have set the Job Frequency "Minutely" and Interval "1"

Step 5:- Drop the Job

BEGIN

  dbms_scheduler.drop_job(job_name => 'TESTJOB');

END;

--Using UI Interface

Step 1:- Right on Job => New 

Step 2:- DBMS Scheduler Screen will display

Step 3:- Fill Necessary field of DBMS Scheduler

Step 4:- Click on Apply Button to Create the Job

Step 5:- Expand the Job Folder from left pane where you can view your Job

Upload File In ASP.NET MVC

Read Excel File in ASP.NET MVC

View

@{

    ViewBag.Title = "FileUpload";

}

<h2>

    FileUpload</h2>

@using (Html.BeginForm("FileUpload", "Upload", FormMethod.Post, new { @enctype = "multipart/form-data" }))

{

    <input name="uploadfile" type="file" />

    @Html.ValidationMessage("uploadfile", "")

    <br />

    <input type="submit" value="Upload" />

}

Controller

using System.Web;

using System.Web.Mvc;

using System.IO;

using System.Data.OleDb;

using System.Data;

using System.Text;

namespace MvcApplication1.Controllers

{

    public class UploadController : Controller

    {

        //

        // GET: /Upload/

        public ActionResult FileUpload()

        {

            return View();

        }

        [HttpPost]

        public ActionResult FileUpload(HttpPostedFileBase uploadfile)

        {

            string ext = string.Empty;

            string fileName = string.Empty;

            string filePath = string.Empty;

            string strConnectionString = string.Empty;

            OleDbConnection con = null;

            OleDbCommand cmd = null;

            OleDbDataAdapter da = null;

            DataSet ds = null;

            StringBuilder sb = null;

            // validate file

            if (uploadfile == null)

            {

                ModelState.AddModelError("uploadfile", "Please select file to upload.");

            }

            else

            {

                ext = Path.GetExtension(uploadfile.FileName);

                ext = ext.ToLower();

                if (!(ext.Equals(".xls") || ext.Equals(".xlsx")))

                {

                    ModelState.AddModelError("uploadfile", "Please upload .xls / .xlsx files only.");

                }

            }

            // Check validation ok

            if (ModelState.IsValid)

            {

                // Get file name

                fileName = Path.GetFileName(uploadfile.FileName);

                // Save file in root folder

                filePath = Path.Combine(Server.MapPath("~/UploadedFiles"), fileName);

                uploadfile.SaveAs(filePath);

                // based on file extension define connection string

                if (ext.Equals(".xls"))

                {

                    strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";

                }

                else

                {

                    if (ext.Equals(".xlsx"))

                    {

                        strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0";

                    }

                }

                // create connection string

                using (con = new OleDbConnection(strConnectionString))

                {

                    // Open connection

                    con.Open();

                    // Get all sheet in excel file

                    DataTable Sheets = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                    // getting first sheet name only

                    string worksheets = Sheets.Rows[0]["TABLE_NAME"].ToString();

                    // create select query

                    string query = string.Format("SELECT * FROM [{0}]", worksheets);

                    cmd = new OleDbCommand(query, con);

                    da = new OleDbDataAdapter(cmd);

                    ds = new DataSet();

                    da.Fill(ds);

                }

            }

            return View("FileUpload");

        }

    }

}

Note:- 

Create “UploadedFiles” folder in application root.

Split comma separated string in PL/SQL?

-- Oracle 10G or 11G

-- built-in Apex function apex_util.string_to_table()

DECLARE

  V_String VARCHAR2(100) := 'A,B,C,D';

  V_Array  apex_application_global.vc_arr2;

BEGIN

  V_Array := apex_util.string_to_table(V_String, ',');

  FOR i IN 1 .. V_Array.COUNT LOOP

    DBMS_OUTPUT.Put_Line(V_Array(i));

    -- insert table syntax

  END LOOP;

END;

-- Using REGEXP_SUBSTR()

SELECT REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) AS data

  FROM dual

CONNECT BY REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) IS NOT NULL;

-- Using xmltable()

-- Split number to table

-- Note: It's only work for number

SELECT TO_NUMBER(COLUMN_VALUE) as data from xmltable('1,2,3,4,5');

-- VARRAY(10) :- Size of array

-- VARCHAR2(20) :- Size of String Value

DECLARE

  TYPE V_Array IS VARRAY(10) OF VARCHAR2(10);

  V_String V_Array;

BEGIN

  V_String := V_Array('A', 11, 22, 'B');

  FOR i IN 1 .. V_String.Count LOOP

    dbms_output.put_line(V_String(i));

    -- insert table syntax

  END LOOP;

END;

How to get sheet name of the uploaded excel file in ASP.NET?

using System.IO;

using System.Data.OleDb;

using System.Data;

protected void btnUpload_Click(object sender, EventArgs e)

{

string ext = string.Empty;

string filePath = string.Empty;

string strConnectionString = string.Empty;

OleDbConnection con = null;

OleDbCommand cmd = null;

OleDbDataAdapter da = null;

DataSet ds = null;

if (!FileUpload1.HasFile)

{

DisplayMessage("Please select file to upload.");

return;

}

else

{

ext = Path.GetExtension(FileUpload1.PostedFile.FileName);

ext = ext.ToLower();

if (!(ext.Equals(".xls") || ext.Equals(".xlsx")))

{

DisplayMessage("Please upload .xls / .xlsx files only.");

return;

}

}

filePath = FileUpload1.PostedFile.FileName;

if (ext.Equals(".xls"))

{

strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";

}

else

{

if (ext.Equals(".xlsx"))

{

strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0";

}

}

con = new OleDbConnection(strConnectionString);

con.Open();

// Get sheet name

DataTable Sheets = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

string worksheets = Sheets.Rows[0]["TABLE_NAME"].ToString();

string query = string.Format("SELECT * FROM [{0}]", worksheets);

cmd = new OleDbCommand(query, con);

da = new OleDbDataAdapter(cmd);

ds = new DataSet();

da.Fill(ds);

}

Note:-

In above example selecting first row sheet only.

If you have multiple sheets in excel sheet and all sheets need to process, use following to get all sheet names

foreach (DataRow row in dt.Rows)

{

string worksheets = row["TABLE_NAME"].ToString();

// Your code here

}