Execute dynamic query in PL/SQL

1. SIMPLE DYNAMIC QUERY

CREATE OR REPLACE PROCEDURE USP_TEST(P_OUT OUT PKG_NONPINS.CUR_REF) AS

  Query VARCHAR2(1000);

BEGIN

  Query := ' select trunc(sysdate) "sysDate" ,

             trunc(current_date) "curDate"

             from dual';

  OPEN P_OUT FOR Query;

  --OR

  OPEN P_OUT FOR ' select trunc(sysdate) "sysDate" ,

             trunc(current_date) "curDate"

             from dual';

END;

2. PASS VARIABLE IN DYNAMIC QUERY

CREATE OR REPLACE PROCEDURE USP_TEST(P_OUT OUT PKG_NONPINS.CUR_REF) AS

  Query   VARCHAR2(1000);

  Date    VARCHAR2(1000);

  C_Date  VARCHAR2(1000);

BEGIN

  Date  := trunc(sysdate);

  C_Date:= trunc(current_date);

  Query := ' select ''' || Date || ''' "sysDate" ,

             ''' || C_Date || ''' "curDate"

             from dual';

  OPEN P_OUT FOR Query;

  --OR

  OPEN P_OUT FOR ' select ''' || Date || ''' "sysDate" ,

             ''' || C_Date || ''' "curDate"

             from dual';

END;

Read whole file into a string using utl_file in PL/SQL

declare

  V_FileHandle  utl_file.file_type;

  V_FileDirName VARCHAR2(100);

  V_FileName    VARCHAR2(100);

  V_LineString  CLOB;

  V_OutString   CLOB;

begin

  V_FileDirName  := 'DirName';

  V_FileName := 'FileName.txt';

  V_FileHandle := utl_file.fopen(V_FileDirName, V_FileName, 'R');

  loop

    begin

      utl_file.get_line(V_FileHandle, V_LineString);

   

      V_OutString := V_OutString || V_LineString || chr(10);

   

    EXCEPTION

      WHEN NO_DATA_FOUND THEN

        EXIT;

    end;

  end loop;

  --dbms_output.put_line(V_OutString);

  utl_file.fclose(V_FileHandle);

end;

--Create directory in  database server

create directory DirName as 'D:\Test';

grant read on directory DirName to dba;

grant write on directory DirName to dba;

'D:\Test' is a file path on the Server, where the file is located.

--Drop directory from database server

drop directory DirName;

--To Get file directory name run following query

SELECT * FROM ALL_DIRECTORIES

--Note

You require DB Administrator right or DB Administrator has given you a right to access the file directory path from database server to read the file.

If you don't have right, you will get the error "invalid directory path"

You can use other appropriate data type instead of CLOB, If your file data is lesser to read.

Chr(10) is use for New Line.

ORA-01460: unimplemented or unreasonable conversion requested

I got "ORA-01460: unimplemented or unreasonable conversion requested" error when I pass the large file data from Dot Net application to Oracle server. 

I found that When file data small 100kb or less I am not getting the error but when file data more than 650kb getting the error.

To resolve this issue, I put my cmd.ExecuteNonQuery() function between cmd.Transanction = con.BeginTransaction() and cmd.Transaction.Commit() function.

Before

            try

            {

                string file1Path = @"Text File Path";

                string file2Path = @"XML File Path";

                // Reading File1

                FileStream fs = new FileStream(file1Path, FileMode.Open);

                BinaryReader br = new BinaryReader(fs);

                byte[] file1 = br.ReadBytes((int)fs.Length);

                // Reading File2

                StreamReader sr2 = new StreamReader(file2Path, Encoding.ASCII);

                string file2 = sr2.ReadToEnd();

                sr2.Close();

                string connection = "Connection String";

                OracleConnection con = new OracleConnection(connection);

                con.Open();

                OracleCommand cmd = new OracleCommand("Procedure Name", con);

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new OracleParameter("BLOB_FILE", OracleDbType.Blob)).Value = file1;

                cmd.Parameters.Add(new OracleParameter("CLOB_FILE", OracleDbType.Clob)).Value = file2;

                int i = cmd.ExecuteNonQuery();

                con.Close();

            }

            catch (Exception ex)

            {

                ex.Message.ToString();

            }

After

            try

            {

                string file1Path = @"Text File Path";

                string file2Path = @"XML File Path";

                // Reading File1

                FileStream fs = new FileStream(file1Path, FileMode.Open);

                BinaryReader br = new BinaryReader(fs);

                byte[] file1 = br.ReadBytes((int)fs.Length);

                // Reading File2

                StreamReader sr2 = new StreamReader(file2Path, Encoding.ASCII);

                string file2 = sr2.ReadToEnd();

                sr2.Close();

                string connection = "Connection String";

                OracleConnection con = new OracleConnection(connection);

                con.Open();

                OracleCommand cmd = new OracleCommand("Procedure Name", con);

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new OracleParameter("BLOB_FILE", OracleDbType.Blob)).Value = file1;

                cmd.Parameters.Add(new OracleParameter("CLOB_FILE", OracleDbType.Clob)).Value = file2;

                cmd.Transaction = con.BeginTransaction();

                int i = cmd.ExecuteNonQuery();

                cmd.Transaction.Commit();

                con.Close();

            }

            catch (Exception ex)

            {

                ex.Message.ToString();

            }

Script

CREATE TABLE TestTable

(

  BLOB_FILE        BLOB,

  CLOB_FILE        CLOB

)

create or replace procedure USP_INS_TestTable(BLOB_FILE IN BLOB,

                                              CLOB_FILE IN CLOB) AS

begin

  insert into TestTable (BLOB_FILE, CLOB_FILE) values (BLOBFILE, CLOBFILE);

end USP_INS_TestTable;

Note:-

While sending large data from dot net application to oracle server It's send the chunk data instead of actual data for that cause oracle server send the error "ORA-01460: unimplemented or unreasonable conversion requested".

Kill process in c#

        using System.Diagnostics;

        using System.Collections;

        Hashtable hashtable;

        protected void Button1_Click(object sender, EventArgs e)

        {

            // Get already running process ids before running the Export to Excel method

            GetExcelProcesses();

            //Export to Excel Method

            ExportToExcel();

            // Kill the right process after Export to Excel completed

            KillExcel();

        }

        private void ExportToExcel()

        {

            // your export process is here...

        }

        private void GetExcelProcesses()

        {

            hashtable = new Hashtable();

            Process[] process = Process.GetProcessesByName("excel");

  

            int count = 0;

            foreach (Process ExcelProcess in process)

            {

                hashtable.Add(ExcelProcess.Id, count);

                count++;

            }

        }

        private void KillExcel()

        {

            Process[] process = Process.GetProcessesByName("excel");

            // check to kill the right process

            foreach (Process ExcelProcess in process)

            {

                if (hashtable.ContainsKey(ExcelProcess.Id) == false)

                {

                    ExcelProcess.Kill();

                }

            }

            process = null;

        }

Zip file in asp.net

Step 1:- Download 7-Zip from http://www.7-zip.org/download.html

Step 2:- Complete Code

using System.Diagnostics;

string SevenZipPath = @"C:\Program Files\7-Zip\7z.exe";

string FilePath = @"D:\Test\Files";

ProcessStartInfo processStartInfo = new ProcessStartInfo(SevenZipPath);

processStartInfo.Arguments = string.Format("a -t7z {0}.7z {0}", FilePath);

processStartInfo.RedirectStandardInput = true;

processStartInfo.UseShellExecute = false;

processStartInfo.CreateNoWindow = true;

processStartInfo.WindowStyle = ProcessWindowStyle.Hidden;

Process process = Process.Start(processStartInfo);

Step 3:-Explanation

Get the 7-Zip installed path

@"C:\Program Files\7-Zip\7z.exe"

Get the Files path  

@"D:\Test\Files"

Note:-

In this sample code file save in the Test folder with name Files.7z