Alex Poole

United Kingdom

stackexchange.com/users/98359

Age: 43

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.

46m
comment Assigning object type in plsql
Your rownum isn't applying any real ordering, you're relying on the order that Oracle happens to return the rows. Are the item numbers(?) significant - how do you know which row in your table maps to which object attribute? Your batch suggests you can get 50 values back, which doesn't map to your 4 attributes either.
3h
comment How to end the load infile process in oracle
Is there more text after the value you want to interpret as :COS, or just a line break? If you just have whitespace up to the end of line as you've shown (or next | separator) then you don't need to do anything special.
4h
comment How to end the load infile process in oracle
What effect are you expecting the ,TERMINATED BY WHITESPACE on line 15 to have? The comma is making it look like a new column called TERMINATED at the moment.
4h
comment Compare Count num_rows showing different results in oracle sql
@Rahul - like this. Not sure which question this should be closed as a duplicate of though.
5h
comment Ocilib application fails to get result
@user3307073 - are you using the same connection details though - same address/port, and same service name? Or are you maybe using the SID from NetBeans? The SID and service name can be the same but often are not.
6h
comment PLS-00103 error when creating object type
@MKS - from what you said as a comment on a_horse_with_no_name's answer: you don't need to use dynamic SQL if your values are dynamic, only if the data structures aren't known at compile time - which should be rare. It should be even rarer to modify your schema at run-time - arguably it should never happen. I think you're going down the wrong route entirely here.
6h
comment PLS-00103 error when creating object type
@MKS - that's true, but only in a PL/SQL context; you're dynamically creating schema-level types in the SQL context, which has the 4000 limit. Do you really want schema-level though? It looks like PL/SQL types would work here, but depends what you plan to do with picu_var. You also don't have a declare for the picu_var picu_obj_tab; line, which is the immediate cause of the error.
2d
comment SchemaCrawler couldn't connect to Oracle base
You don't seem to have the Oracle driver (e.g. ojdbc7.jar) in your class path. The documentation says the Oracle driver has to be downloaded separately; have you done that, and if so where have you put it?
Jul
31
comment Spooling Japanese data from oracle Database
How are you viewing the text file?
Jul
31
comment ORA-01722: invalid number on a varchar field?
Or a virtual column or function-based index referring to somefield? You ought to see an ORA-06512 if a trigger was causing this, so presumably it's something more subtle. Showing the actual DDL for the table and related objects might help, if they don't make it obvious to you.
Jul
31
revised Extend table of varchar with a varchar
added 697 characters in body
Jul
31
answered Extend table of varchar with a varchar
Jul
31
revised Meaning of Oracle's dump(systimestamp) bytes
Added fractional seconds breakdown
Jul
31
comment Meaning of Oracle's dump(systimestamp) bytes
@RaduGheorghiu that answer is about dates rather than timestamps; but the date portion of timestamps (and the time, down to second precision) is interpreted the same way. So what it says about type 13 applies to types 187 and 188 too (and others).
Jul
31
comment Meaning of Oracle's dump(systimestamp) bytes
For your underlying issue, have a look at your execution plan and see if it's doing sys_extract_utc on your base column; and try running that directly on the column to see if you get the same error.
Jul
31
answered Meaning of Oracle's dump(systimestamp) bytes
Jul
31
revised Date Conversion of one Timezone to different timezone
Added link, fixed typos
Jul
31
answered Date Conversion of one Timezone to different timezone
Jul
31
comment Date Conversion of one Timezone to different timezone
Won't a date in September be EEST rather than EET (in most places)? Do you have a region you can assign to the source timestamp, rather than EET? But the 'Z' in the string is supposed to mean it's a UTC time so something is already wrong...
Jul
30
comment EXECUTE IMMEDIATE AND DECLARE
So you've fixed your problem?
1 2 3 4 5