Declare a variable in oracle in a python SQL query

Question

I am porting code from SQL Server to Oracle.

DECLARE @DispatchCount int,
@HeadCount int;

SELECT @DispatchCount = (
    select 200 -- business query would replace sample
);

select @HeadCount = (
    select 50 -- business query would replace sample 
);

select @DispatchCount / @HeadCount;

I tried oracle declare syntax.

DECLARE
    head_count INTEGER;

BEGIN

select 100 as DUMMY into head_count from dual;
dbms_output.put_line(head_count);

END;

I am querying a read only oracle 11g database in python from cx_oracle:

cursor.execute(sql)
rows = cursor.fetchall()

This throws the error: 'not a query' on the fetchall statement. Is there a way to declare variables in oracle that will work in a SQL query?

I see various answers so I'll just add some side comments: the dbms_output.put_line(head_count); line won't directly do anything in cx_Oracle unless you do some extra steps like shown in the cx_Oracle example DbmsOutput.py. The other comment is that 11g is very old. If you are using the "XE" edition, then you should think about installing XE 18c. — Christopher Jones
– Christopher Jones, Commented Jan 29, 2021 at 12:07
Why do you need a database to calculate a division at all? — Marmite Bomber
– Marmite Bomber, Commented Jan 29, 2021 at 12:30
There is a list of SQL Server database statements that need to be ported to Oracle. — oxdeadbeef
– oxdeadbeef, Commented Jan 29, 2021 at 12:35

Popeye · Accepted Answer · 2021-01-29 11:24:26Z

1

In oracle, One way is to use the CTE as follows:

with cte as 
(select 200 as dispatchcount, 50 as headcount from dual)
select dispatchcount/headcount from cte

If you want both variables in different table then use the multiple cte as follows:

with cte1 as 
(select 200 as dispatchcount from dual),
cte2 as (select 50 as headcount from dual)
select dispatchcount/headcount from cte1 , cte2

edited Jan 29, 2021 at 11:24

answered Jan 29, 2021 at 10:14

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

oxdeadbeef Over a year ago

it would be great to have a solution that works even if dispatchcount and headcount are in different tables. in my case I will look into restructuring the query like that, thanks.

Popeye Over a year ago

Ok, Updated the answer. Please check the updated section

Barbaros Özhan · Accepted Answer · 2021-01-29 11:15:43Z

1

Yes, you can use prepared SQL statement along with dummy dual table, including placeholders for parameters those are stated within a tuple, through use of cursor.fetchone() considering your case needs to return only one row such as

DispatchCount=200
HeadCount=50

sql  = """
          SELECT :1 / :2
            FROM dual
       """

cursor.execute(sql,(DispatchCount,HeadCount,))
rows = cursor.fetchone()
    
print(rows[0])

For a table with multiple rows you can replace fetchone() with fetchall() too.

edited Jan 29, 2021 at 11:15

answered Jan 29, 2021 at 11:10

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

1 Comment

oxdeadbeef Over a year ago

Valid answer. I accepted Popeye's answer because I need to avoid modifications to the python code.

Collectives™ on Stack Overflow

Declare a variable in oracle in a python SQL query

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related