Re: rowid added to the select cluase of a statement

Hi Jeffrey,

It looks similar to this question:
https://stackoverflow.com/questions/67115953/oracle-err-918-unixudbc-instantclinet

Some ORMs and drivers add ROWID to the query text implicitly, if they don't contain it already or key column specified in the cursor description. So you can try to add a rowid of the most important table of your join manually.

Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner Oracle ACE Associate
http://orasql.org

On Thu, Jul 15, 2021, 19:55 Mark W. Farnham <mwf_at_rsiz.com> wrote:

> The next step would be to take off the allegedly added “ROWID,”
>
> and determine whether that is a legal statement.
>
>
>
> PS: It is a useful practice to include all the column table aliases in all
> references once you get beyond a single base object. (I claim it is good
> form to always fully decline column references, but there can be a
> simplicity argument against that being called a “useful practice.” Once you
> get beyond a single object, I do claim it is more than good form, but then
> I always forced programmers who worked for me to put in all the parentheses
> in formulae once it got beyond just addition and subtraction, and even then
> if the bundling was meaningful.)
>
>
>
> Again, good luck. Camel hump code is a good starting point for MiscellAnus
> results.
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Beckstrom, Jeffrey
> *Sent:* Thursday, July 15, 2021 12:27 PM
> *To:* Mark W. Farnham; oracle-l_at_freelists.org
> *Subject:* RE: rowid added to the select cluase of a statement
>
>
>
> Here is an example of one from the alert log. Rowid is ambiguous when run
> from sqlplus. This is being invoked from Oracle ODBC 12.2. Cannot get the
> before text because it is in a 3rd party application. The vendor states
> they are not coding the “rowid”.
>
>
>
> PARSE ERROR: ospid=28700, error=918 for statement:
>
> 2021-07-15T08:53:08.956483-04:00
>
> SELECT ROWID,NotificationTypeName, NP.NotificationTypeAbbr, DeviceAbbr
> FROM NotificationsForPASS NP LEFT OUTER JOIN NotificationType NT ON
> NP.NotificationTypeAbbr = NT.NotificationTypeAbbr WHERE ObjectId=:"SYS_B_0"
> AND ObjectTypeLong=:"SYS_B_1" ORDER BY NotificationTypeName
>
> Additional information: hd=0x6cf97b78 phd=0x809a7a70 flg=0x100476
> cisid=389 sid=389 ciuid=389 uid=389
>
> 2021-07-15T08:59:09.553625-04:00
>
>
>
> *From:* Mark W. Farnham <mwf_at_rsiz.com>
> *Sent:* Thursday, July 15, 2021 12:14 PM
> *To:* Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org>; oracle-l_at_freelists.org
> *Subject:* RE: rowid added to the select cluase of a statement
>
>
>
> The before and after sql text might be useful as well as what interface
> submitted the sql.
>
>
>
> IF you can grab the sql text and submit it directly at the local command
> line, that might help discover or rule out where to focus investigation.
>
>
>
> Whether the objects referenced in the statement are tables or something
> complex, such as join views, might also be of interest.
>
>
>
> Good luck!
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>] *On
> Behalf Of *Beckstrom, Jeffrey
> *Sent:* Thursday, July 15, 2021 12:04 PM
> *To:* oracle-l_at_freelists.org
> *Subject:* rowid added to the select cluase of a statement
>
>
>
> In Oracle 12.2, we are seeing parse warning messages. The sql shown
> includes rowid in the select which causes the sql to be invalid – column
> ambiguously defined. Google found the below entry from 2007.
>
>
>
>
> https://www.freelists.org/post/oracle-l/Anyone-seen-ROWIDs-added-to-the-SELECT-clause-of-a-statement,1
> <https://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.freelists.org%2Fpost%2Foracle-l%2FAnyone-seen-ROWIDs-added-to-the-SELECT-clause-of-a-statement%2C1&data=04%7C01%7Cjbeckstrom%40gcrta.org%7Cdedaeb0d92c4442531ba08d947ac6316%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637619628003019070%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=dNQujqU0oQQXn1Vz0PcX4jAoiNPPBvzcaF8NNeFD0Tg%3D&reserved=0>
>
>
>
> Does this sound familiar to anyone and if so what is the fix?
>
>
>
> Jeffrey Beckstrom
>
> Greater Cleveland Regional Transit Authority
>
> 1240 W. 6th Street
>
> Cleveland, Ohio 44113
>
>
>

--
http://www.freelists.org/webpage/oracle-l