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?

CodePudding user response：

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:

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;

2. 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;

3. 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
    )