Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2011
    Posts
    3

    Unanswered: Returning a rowid into a select after an insert

    Hello,

    I want to insert a new record in an Oracle database, and then get the rowid of the new record.

    I have tried the following code.

    DECLARE
    UpdatedRowId ROWID;
    BEGIN
    INSERT INTO MeasurementTypeTable (MeasurementType)
    VALUES (1007)
    RETURNING RowId INTO UpdatedRowId;
    SELECT
    t.RowId
    FROM
    MeasurementTypeTable t
    WHERE
    t.RowId = UpdatedRowId;
    END;

    This code doesn't work, and Oracle returns PLS-00428: an INTO clause is expected in this SELECT statement.

    My goal is to retrieve this RowId, so I can pass it to a SELECT command, and use a fetch on the query result.
    I use the ODBC library, with SQLPrepare, SQLBindCol, SQLExecute and SQLFetch functions.

    How should I form my query for this purpose?
    Remark : this is the first time I use PL/SQL. Up to now, I have only used SQL.

  2. #2
    Join Date
    Sep 2003
    Location
    NE Florida w/ view of co-workers
    Posts
    32
    In PL/SQL, a SELECT statement needs to put the returned data somewhere. The proper syntax would be
    Code:
    SELECT
    t.RowId
    INTO selectedRowId
    FROM
    MeasurementTypeTable t
    WHERE
    t.RowId = UpdatedRowId;
    And, of course, you would need to declare selectedRowId
    Code:
    selectedRowId ROWID;

  3. #3
    Join Date
    Mar 2011
    Posts
    3
    Many thanks for your reply.

    Quote Originally Posted by skempins View Post
    In PL/SQL, a SELECT statement needs to put the returned data somewhere. The proper syntax would be
    Code:
    SELECT
    t.RowId
    INTO selectedRowId
    FROM
    MeasurementTypeTable t
    WHERE
    t.RowId = UpdatedRowId;
    And, of course, you would need to declare selectedRowId
    Code:
    selectedRowId ROWID;
    My query now looks like as follow.

    DECLARE
    UpdatedRowId ROWID;
    selectedRowId ROWID;
    BEGIN
    INSERT INTO MeasurementTypeTable (MeasurementType)
    VALUES (1007)
    RETURNING RowId INTO UpdatedRowId;
    SELECT
    t.RowId
    INTO
    selectedRowId
    FROM
    MeasurementTypeTable t
    WHERE
    t.RowId = UpdatedRowId;
    END;

    But this still doesn't work.
    The SQLExecute command no longer returns any error.
    But the SQLFetch does.
    It says [Oracle][ODBC]Invalid cursor state.

    Here is the sequence of code I have.

    string query;

    SQLRETURN ret;
    SQLCHAR rowId [21];
    SQLLEN rowIdIND;

    //...

    ret = SQLPrepare (hstmt, (unsigned char *) query.c_str (), SQL_NTS);
    ret = SQLBindCol (hstmt, 1, SQL_C_CHAR, rowId, sizeof (rowId), &rowIdIND);
    ret = SQLExecute (hstmt);

    ret = SQLFetch (hstmt);

    It sounds for me that the SELECT query is ignored, resulting in an error when calling SQLFetch. The INSERT one works, since a record has been inserted.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by alhano View Post
    It sounds for me that the SELECT query is ignored, resulting in an error when calling SQLFetch.
    Sounds right to you. In fact, as it is fetched to the local variable of the block and then exits it, the value is thrown away and not used at all.

    I do not understand, why do you insist on SELECT rather than passing the ROWID obtained from the RETURNING clause to the caller. In ODBC, it should be achieved by using '?' placeholder. Then, there probably will not be any need for PL/SQL as well:
    Code:
    INSERT INTO MeasurementTypeTable (MeasurementType)
    VALUES (1007)
    RETURNING RowId INTO ?

  5. #5
    Join Date
    Mar 2011
    Posts
    3
    Quote Originally Posted by flyboy View Post
    Sounds right to you. In fact, as it is fetched to the local variable of the block and then exits it, the value is thrown away and not used at all.

    I do not understand, why do you insist on SELECT rather than passing the ROWID obtained from the RETURNING clause to the caller. In ODBC, it should be achieved by using '?' placeholder. Then, there probably will not be any need for PL/SQL as well:
    Code:
    INSERT INTO MeasurementTypeTable (MeasurementType)
    VALUES (1007)
    RETURNING RowId INTO ?
    Many thanks for your solution. It solved my problem.

    I just had to use the SQLBindParameter with the SQL_PARAM_OUTPUT option.

    I wasn't aware the ? could be used that way. Up to now, I was mostly using that to pass string with UTF8 characters.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •