Alex Poole

United Kingdom

stackexchange.com/users/98359

Age: 42

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
comment How do I create custom data format like these?
You should not use a trigger for this. You can use a varchar2 field with a regex check constraint. This looks like homework so I'll leave that as a hint rather than a full answer *8-)
18h
comment utlpwdmg.sql Oracle DB 10g, 11g
That file contains sample password verification functions. The file is only run manually; the functions are applied whenever a user with a function attached via their profile changes their password, or has it changed for them, as that manual page describes. Why do you think the file itself would be run automatically?
19h
comment I am unable to login Sql* Plus
The database isn't up properly, as the message suggests. Start it up, or check the logs.
1d
comment How to display table from returned cursor in Oracle?
You're returning SYS_REFCURSOR in both questions; you've just changed how you're calling the function.
1d
comment How to display table from returned cursor in Oracle?
You can do that but it assumes whoever runs it is displaying dbms_output; which isn't really an issue here since it's an anonymous block. From SQL*Plus or SQL Developer you can use the variable and print commands around the exec to display the cursor more easily.
1d
comment How to return multiple row by pl/sql stored function?
possible duplicate of How do you get nicely formatted results from an Oracle procedure that returns a reference cursor?
1d
comment inconsistent datatypes: expected NUMBER got BLOB
But where are you doing that - from Java, from SQL*Plus, some other client? What would 'displaying properly' mean for a BLOB' value? When I try this, your code is inserting properly (as expected), and querying through SQL*Plus shows the hex representation of the file, e.g. FFD8FFE000104A4649460001020000640064.... So what are you doing differently, or not telling us? Are you querying it through Java, but not retrieving it as a stream? Does select dbms_lob.getlength(photo) from imagetable1 show you a size that matches the original image file?
1d
answered How to Connect to a Remote Oracle 12c Database
1d
comment How to Connect to a Remote Oracle 12c Database
So is there a firewall on the server? You mentioned that possibility, but we can't tell if you have one. It certainly sounds likely to be the problem. If the address and port are definitely right then you might have to open up port 1521 in that firewall. Or possibly somewhere else on the network, but the server itself seems more likely.
1d
comment inconsistent datatypes: expected NUMBER got BLOB
Showing the code that executes and processes the select would help. Unless you're just doing that directly in your client, like SQL Developer; how would you expect the binary data to be displayed? You could query the size of the BLOB field maybe, to compare with the original file?
2d
comment SQL, Oracle, column formatting
I don't think it should but does set tab off affect the first issue? Don't think you can fix issue 2 with formatting, it might need some reworking of the query, but I giess you know that. Not sure why you have full outer joins though, aren't left joins sufficient here? You could put you schema set-up in an SQL Fiddle if you want people to play with it.
2d
comment Update the table using triggers
You can use show errors immediately after creating the trigger, or query user_errors later, to see what is actually wrong. But it won't have created the trigger if you already had a table with that name; perhaps you already have one from an earlier attempt with a different name?
2d
comment Oracle - Trigger
In a previous question you mentioned that you'd changed your Squirrel command separator character from ; to /. Is that still the case when you are running these statements? And do you have a / after the trigger statement, either way?
2d
revised Oracle - Trigger
Strings as floats? *8-)
2d
answered Import Oracle data dump and overwrite existing data
2d
awarded Yearling
2d
comment SQL statement ignored while doing arithmetic
@RohitShinde - wow, yes, I had no idea APEX's SQL Workshop gave you so little information for errors. If you can I'd suggest you connect to your database using a different client, like SQL Developer.
2d
comment SQL statement ignored while doing arithmetic
@RohitShinde - do you mean 11g Express Edition; or are you using APEX as the client to run this, rather than something like SQL*Plus, SQL Developer, Toad etc.? (SQL Workshop? I don't use APEX...)
Apr
17
awarded Autobiographer
Apr
17
comment Check all entries of column of type DATE if they are in the businessday calendar
@AskQuestion - yes, exactly, or mixed case, etc. I'm sure you're far to sensible to have any of those though *8-)
1 2 3 4 5