Qt5 QSqlDatabase: read PL/SQL variable from oracle

[Hans Hupe]

I want to execute the following PL/SQL block in my Qt-code using a QSqlDatabase connection to an oracle database.

DECLARE
var_id NUMBER;
BEGIN
INSERT INTO TEST_TABLE(NAME)
VALUES('TEST') RETURNING ID INTO var_id;
END;

The statement inserts a value into the PL/SQL variable 'var_id'. After executing the statement I want to read the value of 'var_id' in Qt, but it doesn't work. Any ideas how to read a PL/SQL variable with QSqlDatabase? Thanks!

[mjsurette]

I'm not familiar with Oracle, but this looks close enough to Postgresql that I'll show how I would do it in PG/SQL in hopes that it will be useful.

I would create the function in the database

CREATE OR REPLACE FUNCTIOM test_fucntion(_name TEXT)
RETURNS BIGINT AS $BODY$
DECLARE
_rv BIGINT
BEGIN
INSERT INTO test_table(name)
VALUES(_name) RETURNING id into _rv;
RETURN _rv;
END;
$BODY$
LANGUAGE plpgsql;

After adding that function to the database you can simply execute the query
SELECT * from test_function("name");
to get your ID.

As I said, this is Postgres code, but I'm pretty sure the Postgres authors imitated Oracle to some extent.

Mike