Get database message on SQL statement execution

[KhASQ]

Hello

What I am trying to do is to get the database response message when I execute a SQL statement

for example when I run this command on SQL plus

drop user TESTUSER

If the statement executed successfully
the response will be

User dropped.

What I want to do is to get this response in my python code with cx_Oracle

Thank you

[anthony-tuininga]

I think you're referring to the message that SQL*Plus prints when it executes a command. Is that correct? If so, that is something built-in to SQL*Plus and not something that the database will tell you when using any other program (like cx_Oracle). If no exception is raised, the user has indeed been dropped. I wrote a simple parser of SQL statements that you can find here and I used it to produce something similar to SQL*Plus. Perhaps it may be of some use?

[santhoshpsk]

@anthony-tuininga I am merely surprised when I heard that there is no way of getting the ddl statement output. Because I am designing a GUI tool using PyQt5 where I am using cx_Oracle for Oracle database connectivity. I am really in a need to pop up a message dialog with the message "Table created" when the user entered the "create table" statement, "Index created" when the user entered a "create index" statement.
I am kind of confused too, because I have good experience in using "Toad for Oracle" tool which is really displaying what was actually created when I create something like table, index, type, synonym or whatever. I can't understand how that tool is able to identify what was actually created in the database. By the way, I am aware of that "Toad for Oracle" is not using cx_Oracle at all. But it still uses the OCI for oracle database connectivity.

[anthony-tuininga]

Yes, Toad for Oracle is using the Oracle Client library (OCI) just like cx_Oracle is. It is doing something like what I did (see my previous comment which has a link for the parser I wrote) in order to tell you what statement was just executed. This is not provided directly. A simple parser (which only looks at the first few words) would not be terribly difficult to write. That is something that might be possible if cx_Oracle was reworked to have a pure Python top-end with a Cython-based bottom-end that interacts with ODPI-C. I'm considering that possibility -- which might make your request relatively simple to implement as an enhancement. Did you want such an enhancement?

[santhoshpsk]

If the parser looks only for first few words to identify what kind of statement it is, it might be prone to misunderstand the actual statement. The actual statement may contain any kind of comments at the beginning. That's merely an example how such parser might not have any clue to identify the statement.
If the real algorithm or something which is implemented in SQL*Plus can be identified and implemented in cx_Oracle too, it would be great improvement for cx_Oracle.

[santhoshpsk]

If we consider psycopg2 module for PostgreSQL, it's having one variable called statusmessage where these kind of output like ddl output or insert, update, delete outputs are available at the client side. I was expecting the similar kind of implementation in cx_Oracle.

[anthony-tuininga]

Ok. I'll leave this as an enhancement.

[santhoshpsk]

Thank You! for your quick response. @anthony-tuininga

[santhoshpsk]

@anthony-tuininga I would like to ask you something that did you mean to say that SQLPlus is actually guessing (parsing few words or something) the statement's nature like whether it's a "create table" or "create synonym" before it execute the statement and after executing the statement if there is no exceptions, SQLPlus is just spitting what it guessed already?

[cjbj]

That's internal information :) and not going to help you. In truth, I forget. It's a long, long time since I saw the SQL*Plus source code.

[santhoshpsk]

:) Thanks for that information @cjbj . I am expecting the same feature available in SQL*Plus will be implemented in cx_Oracle too. After all, that's what I meant to ask.

[anthony-tuininga]

I was discussing a simplistic parser that simply examines the first few words of the SQL statements being executed -- after first stripping any leading spaces and comments. I have no idea if SQL*Plus does this or has access to a more capable parser. :-) The parser I wrote myself (and linked in an earlier comment) is a more capable parser and it does exactly what you are hoping cx_Oracle would do.