Skip to content

Invalid PL/SQL generated for bulk insert with RETURNING clause #48

@saumil-oracle

Description

@saumil-oracle

The driver generates invalid PL/SQL code when performing bulk inserts with RETURNING clauses. The issue occurs in the OUT parameter assignment section of the generated PL/SQL block.
The driver generates invalid assignment statements like:
IF l_inserted_records.COUNT > 0 THEN 0 := l_inserted_records(1)."id"; END IF; IF l_inserted_records.COUNT > 0 THEN NULL := l_inserted_records(1)."created_at"; END IF;

Should generate valid PL/SQL assignments to OUT parameters like:
IF l_inserted_records.COUNT > 0 THEN :1 := l_inserted_records(1)."id"; END IF; IF l_inserted_records.COUNT > 0 THEN :2 := l_inserted_records(1)."created_at"; END IF;

Error:
ORA-06550: line 73, column 70:
PLS-00382: expression is of wrong type
ORA-06550: line 73, column 40:
PL/SQL: Statement ignored

Below is the test that tries to bulk insert multiple records that have fields with default database values (which triggers RETURNING clause usage).
Bulk insert operations fail when RETURNING clauses are needed, which affects any struct with auto-increment IDs or default timestamp fields.
Try this function in serializer_test.go:
`
func TestSerializerBulkInsert(t *testing.T) {
schema.RegisterSerializer("custom", NewCustomSerializer("hello"))
DB.Migrator().DropTable(adaptorSerializerModel(&SerializerStruct{}))
if err := DB.Migrator().AutoMigrate(adaptorSerializerModel(&SerializerStruct{})); err != nil {
t.Fatalf("no error should happen when migrate scanner, valuer struct, got error %v", err)
}

// Test bulk insert with serialized fields
data := []SerializerStruct{
	{
		Name:                   []byte("user1"),
		Roles:                  []string{"admin", "user"},
		Contracts:              map[string]interface{}{"type": "full-time", "salary": 5000},
		CustomSerializerString: "world1",
	},
	{
		Name:                   []byte("user2"),
		Roles:                  []string{"user"},
		Contracts:              map[string]interface{}{"type": "part-time", "salary": 2000},
		CustomSerializerString: "world2",
	},
}

if err := DB.Create(&data).Error; err != nil {
	t.Fatalf("failed to bulk create data, got error %v", err)
}

var results []SerializerStruct
if err := DB.Find(&results).Error; err != nil {
	t.Fatalf("failed to query bulk data, got error %v", err)
}

tests.AssertEqual(t, len(results), 2)
// Verify serialized data was properly stored and retrieved
for i, result := range results {
	tests.AssertEqual(t, result.Roles, data[i].Roles)
	tests.AssertEqual(t, result.Contracts, data[i].Contracts)
}

}
`

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions