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


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.