I've been picking up hints, tips and information from a variety of sources over the years, but somehow have always been a taker and not a giver. I'm finally getting involved and trying to give something back to the community. Still learning far more than contributing though.
I'm a bit of a jack-of-all-trades in a small UK-based team within a US subsidiary of a Japanese company, doing work for a large UK telecoms provider. Production support, developer, part-time (a.k.a accidental) DBA in dev/test, analyst, consultant... whatever needs doing right now, generally. Mostly Oracle (since about 1995, versions 6 to 11g), Java, WebLogic, shell scripting, HTML, CSS, a bit of C when pushed, but whatever's needed today. Work is mostly on Oracle Linux, but dragged into Windows and networking issues fairly often, and a Mac user at home.
|
7h
|
|
revised |
how to fix this function in pl-sql Added cursor loops and join versions |
|
7h
|
|
revised |
how to fix this function in pl-sql Added cursor loops |
|
7h
|
|
answered | how to fix this function in pl-sql |
|
8h
|
|
comment |
Oracle Function In Package Fails as Part of Select It doesn't like the boolean types either, also because they're PL/SQL only. I wanted to check because the error seems slightly odd, but it's definitely those. The amounttyperec type will be a problem with your approach though since you can't use boolean in an object either, for the same reason again. Maybe you could do a wrapper function to do the conversion from native types to package types but it wouldn't be too pretty.
|
|
10h
|
|
reviewed | Approve suggested edit on Oracle Function In Package Fails as Part of Select |
|
10h
|
|
comment |
Oracle Function In Package Fails as Part of Select Is your select in PL/SQL too then, as a cursor since there is no into? With the variables used as parameters defined exactly the same, from that other package? Might be helpful to show the function spec, particulary the return type, and also maybe show more context of what you're passing in.
|
|
10h
|
|
comment |
Oracle VM Virtual Box and Clipboard functionality with host Have you installed the guest additions? |
|
10h
|
|
comment |
Oracle Function In Package Fails as Part of Select Are amounttype etc. columns on entity? Where are the custom data types, and where are they defined - in the package? Oh yes, and what's the error you get?
|
|
10h
|
|
revised |
Oracle Function In Package Fails as Part of Select Formatting |
|
13h
|
|
comment |
Oracle ORDERED hint cost vs speed @monitorjbl - my main point was the same one David Aldridge made in his answer (one second before my comment, apparently), so I don't think there's any point duplicating that. Vincent also made some good points, of course. |
|
14h
|
|
comment |
Oracle ORDERED hint cost vs speed The estimated cost might increase for retrieving all rows, making that potentially slower than it would have been before (but again this is an estimate); perhaps it doesn't take the pagination into account in the cost, since different values would otherwise cause the same plan to vary quite a bit? Without seeing the query, this might be one of those that is fast for early pages and slows down later on (beyond reasonable use), so you're kind of seeing an average over the whole range of possible pages. Or, you're just reading too much into it *8-) |
|
14h
|
|
comment |
Using Oracle PL/SQL table of record with multiple %rowtype fieldsselect * from a join b doesn't give you separate rows you can split like that, as I think you realise. Is there a reason you can't explicitly list the fields from both tables (or at least those you actually want to retrieve) in the record definition, with %type rather than %rowtype? Other than it needing more typing, obviously...
|
|
17h
|
|
comment |
Execute empty nested sql script 'Just stopped' means it waits at a prompt? Does your master script have an exit, or is expecting the last child script to include that?
|
|
18h
|
|
revised |
Execute empty nested sql script Formatting |
|
18h
|
|
comment |
Execute empty nested sql script What error do you get? There should be no problem doing what you describe (even with the unnecessary trailing ;).
|
|
21h
|
|
comment |
Why does the wm_concat not work here? @MuhammadA - without being able to reproduce the effect it's hard to offer any further advice. The only thing that comes to mind to try is to add a `/*+ MATERIALIZE */ hint to the select from the view, but grasping at straws a bit. This sounds like a bug to me, you might be better off raising an SR with Oracle? |
|
21h
|
|
comment |
Why does the wm_concat not work here? @MuhammadA - interesting. Sounds like something in your table function is confusing things, or more likely (perhaps) in your view, though I can't immediately see how. Can you see the same effect if you replace the view (I assume cr_object_group_entries is a view from the name) with the base tables?
|
|
21h
|
|
comment |
top-N query doing too much work in spite of STOPKEY optimization @DavidAldridge - I tried that on the original queries (without the extra index) and I couldn't make it have any effect, possibly through incorrect use or because there's no join for it to act on. I'm just throwing out ideas for the OP to consider though, I don't have any confidence about what will work/be stable for the real situation (or even this test, TBH). |
|
22h
|
|
revised |
top-N query doing too much work in spite of STOPKEY optimization added 3502 characters in body |
|
1d
|
|
comment |
top-N query doing too much work in spite of STOPKEY optimization Quite possibly an optimizer tweak in the patchset, yes. I was hoping I could think of a hint that would make my query behave like yours in my env, and thus might make yours behave how you want, but haven't come up with anything yet. |
