Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

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