Output with multiple queries

Question

I am executing oracle queries and writing the result of said query into a txt file.

Now I need to change the schema if a custom query is being handled (set by the boolean value isCustom)

The schema is changing and the query is being executed but for some reason the query output is not written into the output file.

Everything works fine if isCustom is set to false.

I would be grateful for any suggestions / hints.

public void OracleConnect(string queryString, string fileName, bool isCustom = false)
    {
        //downloaded oracle managed data access.dll
        //changed reference
        //changed connection to long unfunny one
        //http://stackoverflow.com/questions/659341/the-provider-is-not-compatible-with-the-version-of-oracle-client#25412992
        log.Debug("Task " + Task.CurrentId + ": executing " + queryString);
        string oradb = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" + host + ")(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=" + instance + ")));User Id = " + oracleUser + "; Password =" + oraclePassword;
        try
        {
            //create connection
            OracleConnection conn = new OracleConnection(oradb);
            conn.Open();

            OracleCommand cmd = new OracleCommand();
            //if a custom query is being handled change the schema 
            if (isCustom == true)
            {
                log.Debug("##Test executing custom query!");
                string changeSessionString = "ALTER SESSION SET CURRENT_SCHEMA = " + resultUser;
                 cmd.CommandText =
                    "begin " +
                        "  execute immediate '"+ changeSessionString +"';" +
                        "  execute immediate '"+ queryString+"';" +
                     "end;";
                log.Debug("##Test custom query command = " + cmd.CommandText);
            }
            else
            {
                queryString = queryString.Replace("%pumaUser%", resultUser);
                cmd.CommandText = queryString;
            }

            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;

            OracleDataReader reader = cmd.ExecuteReader();

            //get and write content to file
            List<string> columnName = new List<string>();
            columnName = GetColumnNames(reader);
            using (StreamWriter writer = new StreamWriter(fileName))
            {
                //write query command to file
                writer.WriteLine(queryString + Environment.NewLine);

                //write header / column names into first line
                StringBuilder header = new StringBuilder();
                for (int i = 0; i < columnName.Count; i++)
                {
                    header.Append(columnName[i].ToString());

                    if (i != columnName.Count - 1)
                        header.Append("\t");

                }
                writer.WriteLine(header);

                //fill rest of lines with data
                while (reader.Read())
                {
                    StringBuilder row = new StringBuilder();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        row.Append(reader[i]);

                        if (i != reader.FieldCount - 1)
                            row.Append("\t");
                    }
                    writer.WriteLine(row);
                }

            }
            log.DebugFormat("Task: {0} Writing query result into file: {1}", Task.CurrentId, fileName);

            conn.Dispose();
        }
        catch (OracleException e)
        {
            log.ErrorFormat("Task {0} had an OracleError: {1}", Task.CurrentId, e.Message);
            Helpers.writeErrorFile(fileName, e.Message);
        }
        catch (Exception e)
        {
            log.ErrorFormat("Task {0} Could not connect to database: {1} ", Task.CurrentId, e.Message);
        }
    }

score 0 · Answer 1 · edited Apr 18 '17 at 18:24

0

> -for pl/sql procedures:   set serveroutput on

To write output to text file

>     1.spool <location\xxx.txt>   after finishing 
>     2.spool off

edited Apr 18 '17 at 18:24

j.a.estevan

3,029
16
31

answered Apr 18 '17 at 11:12

Gujjula Paramesh

41
1
2
10

I changed my query to: `cmd.CommandText = "begin " + " execute immediate 'SPOOL \"" + fileName + "\"';" + " execute immediate '" + changeSessionString + "';" + " execute immediate '" + queryString + "';" + " execute immediate 'SPOOL off';" + "end;";` – user2811630 Apr 20 '17 at 08:33
the resulting query looks like `OracleObject: 10:24:48,949 [3] DEBUG - ##Test custom query command = begin execute immediate 'SPOOL "D:\CHHC\manual\Debug\output\20-Apr-2017--10-24-43\ATGRZSW2240_HOST153\custom1.txt"'; execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = HOST153'; execute immediate 'SELECT COUNT(*) FROM project_2'; execute immediate 'SPOOL off';end;` but it gives no output – user2811630 Apr 20 '17 at 08:39
Set serveroutput on And. Set echo on should help. – Gujjula Paramesh Apr 25 '17 at 09:22

Output with multiple queries

1 Answers1