[Feature] affected row count

[tmirko]

Describe the Feature

I would like to see a feature that shows affected row count, similar to sql%rowcount from PL/SQL. I'm aware of the variable "rows_affected" in run_results.json but currently it is always showing 0.

Describe alternatives you've considered

No response

Who will this benefit?

No response

Anything else?

No response

[aosingh]

@Mirko-T

rows_affected is set using cursor.rowcount as seen here

Documentation for cursor.rowcount states the following:

This read-only attribute specifies the number of rows that have currently been fetched from the cursor (for select statements), that have been affected by the operation (for insert, update, delete and merge statements), or the number of successful executions of the statement (for PL/SQL statements).

I added a log line to print rows_affected.

18:29:30.036878 [debug] [Thread-3  ]: oracle adapter: SQL status: OK in 0.2434229850769043 seconds
18:29:30.038162 [info ] [Thread-3  ]: oracle adapter: Affected row count 1

[tmirko]

Hi @aosingh, Thanks for the answer.

I can't find this "Affected row count" log line in my dbt.log file.

I'm using:

dbt version: 1.3.1
python version: 3.10.8
os info: Windows-10-10.0.19042-SP0
Oracle version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

[aosingh]

The new log line is local to my development branch. I added it for debugging to verify the value that is set in AdapterResponse

[tmirko]

Can I somehow test it on my end?

[aosingh]

@Mirko-T
Let me publish the debug log to a new git branch. You should be able to pip install pointing to the git branch

[aosingh]

@Mirko-T

You can install the code using the following command. The only change is a new log statement to print cursor.rowcount

pip install git+https://github.com/oracle/dbt-oracle.git@debug/rowcount

[tmirko]

@aosingh

I've installed dbt-oracle from debug/rowcount branch but I can't see any changes regarding additional Affected row count line in log file.

Please reach out if you need more info from my end.

[aosingh]

@Mirko-T

Did you add --debug option to dbt command ? I re-installed from the branch and tested it again

16:54:11.103799 [debug] [Thread-2  ]: oracle adapter: SQL status: OK in 0.18878388404846191 seconds
16:54:11.105054 [info ] [Thread-2  ]: oracle adapter: Affected row count 1

Also, we directly use python driver's cursor.rowcount without any additional changes. That is the way to get this information. Not sure if we can add anything extra in dbt-oracle.

if you see this as 0 and need clarification you can also raise a question on python driver's issues page

[tmirko]

I don't see any line regarding affected rows.

My log after table creation looks like this:

  create  table tomicm.tomicm_test__dbt_tmp
  
  as
    


select 3 as id, sysdate as created_at from dual
union 
select 4 as id, sysdate as created_at from dual



  
�[0m17:23:23.743247 [debug] [Thread-1 (]: oracle adapter: SQL status: OK in 0.3298184871673584 seconds
�[0m17:23:23.761792 [debug] [Thread-1 (]: oracle adapter: Using oracle connection "model.poc_dbt.tomicm_test".

[aosingh]

@Mirko-T

What is the installation output of the following command ?

pip install git+https://github.com/oracle/dbt-oracle.git@debug/rowcount

And the following ?

which dbt

Are you working with a python virtual environment ? I am trying to understand if it is the correct environment where we are installing dbt-oracle and invoking dbt cli from

I will also add this log line in the next release version of dbt-oracle which will released on PyPI soon.

[tmirko]

On first command I get a bunch of Requirement already satisfied messages. On second command I get /c/Users//Miniconda3/envs/dbtpoc/Scripts/dbt, I'm using Conda virtual environment.

If you need more info from my side we can perhaps schedule a debugging call.

[aosingh]

@Mirko-T

Not sure, if the package gets installed correctly from a git branch in your conda environment

The first release cut v1.3.2rc1 is out. You can also test the log statement using this version.

pip install dbt-oracle==1.3.2rc1

Let me know you feedback

[aosingh]

@Mirko-T @geoHeil

I discussed this feature request of affected rowcount with the python driver team and I will keep you posted on the resolution.

Meanwhile, I will remove the log line as @geoHeil reported that the log line is too noisy and interferes with important dbt runtime information shown in the logs

BTW, I recommend using the new package python-oracledb which is the new renamed interface of cx_oracle. It is already installed with dbt-oracle and you can switch it using

export ORA_PYTHON_DRIVER_TYPE=thin

Read more about the new python driver on dbt docs

Let me know if you have any questions

[geoHeil]

do you have any progress @aosingh on the python driver team?

[aosingh]

@geoHeil @Mirko-T

For dbt-oracle, you should get the expected row count using thin driver.

1. Set the following environment variable

export ORA_PYTHON_DRIVER_TYPE=thin

2. Run dbt

dbt run ...

[tmirko]

@aosingh

While using ORA_PYTHON_DRIVER_TYPE=thin I'm getting this error on dbt run:

09:40:07.223119 [error] [MainThread]: Encountered an error: Database Error   DPY-3015: password verifier type 0x939 is not supported by python-oracledb in thin mode

While using ORA_PYTHON_DRIVER_TYPE=thick I'm not getting affected row count line. I'm using dbt version 1.3.2

[geoHeil]

The think driver should also work right? @Mirko-T

export ORA_PYTHON_DRIVER_TYPE=thick

does it work then for you?

[tmirko]

thick driver works but I'm not seeing any affected row count lines in my logs.

[aosingh]

@Mirko-T

Please note that log line is removed because it was noisy.

You will have to verify in run_results.json. I have verified run_results.json and below is an example of thin driver.

"execution_time": 2.3757221698760986,
"adapter_response": {
   "_message": "OK",
   "rows_affected": 8
},
"failures": null,
"unique_id": "model.dbt_adbs_test_project.countries"

With thick mode, rows_affected will be zero and we have a internal bug logged for that

[cjbj]

@Mirko-T the general python-oracledb solution for DPY-3015 is:

• If you’re connecting to Oracle Database 11gR2 or earlier, then use python-oracledb Thick mode, or upgrade the DB.

• For later DBs:

  • make sure the database initialization parameter sec_case_sensitive_logon is not FALSE. In SQL*Plus as SYSDBA, run show parameter sec_case_sensitive_logon to check the value.

    Note sec_case_sensitive_logon has been removed in DB 21c:
    https://docs.oracle.com/en/database/oracle/oracle-database/21/nfcon/security-solutions.html#GUID-FAF4C7A6-A2CD-4B9B-9A64-3705F693ECF0

  • Regenerate passwords e.g. run ALTER USER x IDENTIFIED BY y in SQL*Plus, if necessary