Qt5 QSqlDatabase: read PL/SQL variable from oracle
-
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!
-
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