0

I'm trying to do an upsert with below query but I'm getting oracledb.exceptions.DatabaseError: DPY-4009: 11 positional bind values are required but 5 were provided. Meaning I'm unable to reuse my bind variables.

UPDATE Competition 
SET abbreviation = :1, descriptions = :2, levels = :3, source = :4, competitionId = :5
WHERE competitionId=:5;
IF ( sql%notfound ) THEN
    INSERT INTO Competition
    VALUES (:1, :2, :3, :4, :5);
END IF;

The query is executed in the context:

cursor.executemany(upsert_string, parsed_data)

where the upsert_string is the above query and parsed_data is a list of tuples.

How do I reuse bind variables in this context, is there any way?

1 Answer 1

4

Yes, there is. There are a few options available to you.

  1. You can use bind by name instead of bind by position. In your example it might look like this:
BEGIN
    UPDATE Competition SET
        abbreviation = :abbrev,
        descriptions = :descr,
        levels = :levels,
        source = :source
    WHERE competitionId = :compet;
    IF ( sql%notfound ) THEN
        INSERT INTO Competition
        VALUES (:abbrev, :descr, :levels, :source, :compet);
    END IF;
END;
  1. Since this is an anonymous PL/SQL block, you can also store the bind values in temporary variables, like this:
DECLARE
    t_Abbrev        varchar2(10);
    t_Description   varchar2(100);
    t_Levels        varchar2(50);
    t_Source        varchar2(100);
    t_CompetitionId number;
BEGIN
    t_Abbrev := :1;
    t_Description := :2;
    t_Levels := :3;
    t_Source := :4;
    t_CompetitionId := :5;
    UPDATE Competition SET
        abbreviation = t_Abbrev,
        descriptions = t_Description,
        levels = t_Levels,
        source = t_Source
    WHERE competitionId = t_CompetitionId;
    IF ( sql%notfound ) THEN
        INSERT INTO Competition
        VALUES (t_Abbrev, t_Description, t_Levels, t_Source, t_CompetitionId);
    END IF;
END;
  1. Finally, you can use the MERGE statement instead of an anonymous PL/SQL block:
MERGE INTO Competition C USING (
    SELECT
        :1 as ABBREV,
        :2 as DESCRIPTION,
        :3 as LEVELS,
        :4 as SOURCE,
        :5 as COMPETITION_ID
    FROM DUAL
) S
ON (C.CompetitionId = S.COMPETITION_ID)
WHEN MATCHED THEN
    UPDATE COMPETITION SET
        Abbreviation = s.ABBREV,
        Descriptions = s.DESCRIPTION,
        Levels = s.LEVELS,
        Source = s.SOURCE
WHEN NOT MATCHED THEN
    INSERT INTO Competition VALUES (
        s.ABBREV,
        s.DESCRIPTION,
        s.LEVELS,
        s.SOURCE,
        s.COMPETITIONID
    )
Sign up to request clarification or add additional context in comments.

7 Comments

This is a great answer, thank you for providing all these examples and mainly thank you for pointing out that I was trying to bind by position (did not know that). Thanks for taking your time!
Just to provide some feedback, the second snippet worked right of the bat! While the first oddly enough still had the same complaint (oracledb.exceptions.DatabaseError: DPY-4009: 10 positional bind values are required but 5 were provided). The third throw oracledb.exceptions.DatabaseError: ORA-00969: missing ON keyword (guessing something in the syntax). Again thank you for taking your time to help!
Read on the docs, that I need to pass a dict so that the name binding works, right now I am passing a tuple! Will test passing a dict instead
Yes, you need to pass a dict for bind by name. :-) I found the syntax error in the MERGE statement and have updated my answer accordingly.
Thank you! I'm a bit stunned regarding the first query, it works seamlessly in the oracle cloud console, but throws oracledb.exceptions.DatabaseError: ORA-00933: SQL command not properly ended when executed from the python library
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.