Posts by Nige Ridd

Don't know of any view, but to not alter the database you could get the last SCN and for each table output whats changed since then.
You can use:
SELECT current_scn, SYSTIMESTAMP FROM gv$database;
to get the current SCN and then for each table:
SELECT * FROM tablea AS OF SCN nnnnn;
to see if any changes have been made.
Nige
( http://www.oracle-base.com/articles/10g/Flashback10g.php for more details )

You could always write some PL/SQL which adds a trigger to each table, as the table gets changed you could produce some sort of log.

Nige

You could always have Googled and found something like
http://www.oracle.com/technology/pub/articles/oracle_php_cookbook/fuecks_sps.html

Nige

Not sure triggers are the best way to do this. BUT if you want to stick with triggers, you'll also have to write update and delete triggers ( If you delete an order for an item it's availability would increase, updating an order could affect it either way ).
Nige

It depends on wether you are inserting images, Or just text...

Not sure what you mean by that? Wouldn't have thought the content would have affected it.

Nige

Wouldn't be much better and much more maintainable if you converted the values to a date datatype?
Nige

The cache size is how many Oracle will cache in memory, so when you first select a next value it will read the sequence from disk (n), and write back a next value of n+20. Then as you keep getting a next value it will just give you the next value from cache until all 20 have been used, then repeat the process.
The down side is that if the server crashes or is restarted, those sequence numbers cached in memory will be lost so if you only used 10 values - there will be a gap of 10 in the sequence. This may not be a problem, but some systems require all values to be accounted for ( e.g. some invoice systems )

Nige

Or even look in the Help menu in JDeveloper itself - the 'Tutorials on OTN' might be worth a look at, but I'm sure they've already tried that!
Nige

You can always try XSQL, which allows you to use SQL to generate XML and then apply XSL to produce a web page. But it's probably easier to use something like PHP instead.
Nige

Sorry - I thought this person wanted to create Oracle users, this isn't a case of adding a record to a table, it's more a case of executing various DDL statements to create the user, assign default tablespaces and grant permissions to allow them to do simple things like connect to a database.
Nige

Have a look at http://www.a1vbcode.com/snippet-3876.asp, it's not my code so i can't validate if it works, but best just to try it.
Nige

You could use

select Column2
	from table1 t
	where Column1 = ( select min ( Column1	 )
				from table1 ta
				where ta.Column1 > [I]NumberRequired[/I]);

Nige

In SQL Server Management Studio, connect to your server. Then right mouse click on the Server in on Object Explorer window and select Properties.
This should show a new dialog, and a 'Select a page' panel on the left - choose Security and the you should see the settings to change the Authentication mode.
As the option is SQL server and windows authentication - I can't see it affecting your existing work. Just be careful that things don't just work as it's you logged into windows!
Nige

Unfortunately Select top n isn't supported by Oracle.
Nige

If you follow your arguments - then why not use sql plus for PL/SQL development, after all you don't need an IDE to do PL/SQL programming! Just that people prefer to have one system to do most of there work - wonder how many people using VB/VC++ would use VSS if it wasn't integrated with their IDE?
I use JDeveloper because it's free - and with Oracle behind it, its moving on quite a lot. I've been using 11gR3 and it's starting to make a development with disparate technologies easy to work on. Mostly being PL/SQL - but Java modules for those areas that PL/SQL just can't reach!
Nige

Not the best solution but...

SELECT * FROM EMPLOYEE
WHERE EMPLOYEEID IN (
SELECT EMPLOYEEID FROM ABSENCEHISTORY GROUP BY EMPLOYEEID HAVING count(*) = ALL(SELECT count(*) FROM ABSENCEHISTORY GROUP BY EMPLOYEEID))

You should take into account that there may be more than one person with the worst attendance.
Nige

I was more referring to working with a project, which if your doing any reasonable level of PL/SQL then your more likely working with multiple files and some form of version control system for controlling source code versions.
SQL Developer has no links to projects or version controlling and therefore is fine if your working with a single PL/SQL package, but not much more.
As for JDeveloper designed to work for Java - how about XML, HTML, CSS.... It's an IDE which ( like Eclipse ) isn't aimed at one technology but aimed as being a platform which can be used to develop a full project rather than one component of it.
Nige

How are you trying to run it? If you are using strings for the values of date1 & date2 then you'll have to put to_date() round each one as it uses the date arithmetic features in Oracle.
Nige

Oracle have J Developer and SQL Developer, both free from their web site.
If you develop more PL/SQL I'd go for J Developer as it's more aimed at a project style development, where as SQL Developer is more around working with SQL scripts.
Nige

The reason why your not getting any data back is that your query is effectively looking for customers who are both business and home customers at the same time.
You'll have to use outer joins to allow you to retrieve one or the other.
Nigel

You could try something like

select date1 + rownum
    from all_objects
    where rownum < date2 - date1;

Nige

The ALL_ views, list all objects for a particular type across all schemas in the database. The USER_ views list those of just the user that is logged in.
As you can imagine - the ALL_ views ( in some databases ) will contain much larger volumes of data than the USER_ views and therefore I'd recommend selecting from the USER_ views whenever possible.
Nige

If your doing the connection pooling at the client end then I'd have thought you'd know how many connections your using.
There are quite a few caching algorithms for various systems around. Depends on what language or in Java - which j2ee engine your using.
Nige

Is the category just an additional record in a table - if so why not just write a Coldfusion page to do it.
You can go into the CF Admin web site and look at it from there, thats assuming the old admin left the password with someone.
Nige

Your question is a bit vague, can you provide more details and then people may be able to help.
Nige

You can't use Windows security, you'll have to hardcode the user name and password somewhere.
Nige

Erm can I make a suggestion that you look at using cfqueryparam as well, if anyone ever put a ' in any of the fields on the screen they can then do some nasty SQL injection into your code.
Nige
( See many references on the web about SQL Injection inculding http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/ )

I've been using SQL Developer ( and JDeveloper ) from Oracle themselves. It's a free download from their site.

Nige
( http://www.oracle.com/technology/software/products/sql/index.html for download )

An alternative which you may see commonly is instead of using multiple references to a field with 'or' is to write something like

Select * from RG_TAB where CMCode in ( 4, 5 )

You can imagine when you have 5 or 6 possible values that this is much shorter and easier to read.
Nige

The value of a check box can be set using something like
<cfinput type="checkbox"
name="SelectedDepts"
value="4">
BUT this value will only be returned if the user checks the box, so either do an isDefined or use cfparam to default it to something.

One thing you should consider as well is changing your query to use cfqueryparam instead of puttine the actual value in quotes into the statement. This aids performance and also helps stop quotes in the data causing problems.
Nige

You would have to use client side cookies to store their identity to be able to pick this up on subsequent logins, so why not store the configuration entirely using cookies on the users machine?
Nige

It's easier to BS and try and confuse someone else than actually sit back and listen to what a customer wants and deliver it.
The only thing I would say though is that sometimes the client has to be guided - and by this I don't mean told what to do but given good advice based on experience ( Including DON'T put a 4.5MB+ video clip on each page! ).
Nige

Your intermediate table ONLY needs to contain the member and training class. You could include extra data if you wanted ( and if applicable ) like Dates of when the person applied/paid for the course, completion of the course, perhaps even their feedback on the course itself.
Nige

Sorry - not sure of the point of this post.
Also not sure what you mean by 'Data Management Software', not a term I'm used to.
Nige

To be honest, it's actually quite difficult to read the code. Try and break it down into logical segments ( i.e. retrieving data, extracting data, update data ) and it may be easier to track whats done where.
One thing I've asked people to try before is to use <cflog> just prior to the point they are getting the error. This allows them to see the values that the system is expecting to use, which isn't always what they were expecting and they find they've done some minor error.
One thing from coding point of view, you should be using <cfqueryparam> for all of your parameters to any query. This can be both for performance and security reasons ( stops SQL injection for example and annoying problems with quotes in strings! ).

Nige

I'm fairly new to GUI coding, but what I would have done is to separate out the routine that populates the table, passing into it the sort order as a parameter. Then when the user asks the data to be resorted, you can simply call this routine with the sort order required.
A couple of things about the code as well, make sure that you ALWAYS close any database resources you use, ResultSets, Statements and ( for Oracle anyway ) Connections. To help in this I've always put the close of these resources in the finally clause of the try/catch block that allocates and uses these resources. This means that if your ResultSet fails for some reason, the Statement and Connection are closed, or if your statement is invalid and the executeQuery fails - the Connection gets closed. Unless anyone know a better way of doing this.
Nige

I'd be interested to know how much people use the features of their development environment to do the work for them and how much they prefer doing all the leg work themselves.
This relates to everything from laying out visual components to producing the configuration required for the run time environment ( i.e. automatically producing a deployment file for whichever runtime they use )?
As I've said before in this thread I use JDeveloper, should I start using Eclipse just to get used to the environment?
Nige

As I also use Oracle PL/SQL I've tended to use JDeveloper which is free from Oracle ( always a help ).
Nige

My Coldfusion isn't the best - but it would probably be better to convert the GetOutOfOffice.cfm to a cfc which just has a function to return the display content as a string instead.
Alternatively you could test for having [peopleoutofoffice] in your text, then output everything up to that point from the string, then cfinclude the GetOutOfOffice.cfm and then output the rest of the original string. This would mean less changes to your code, but isn't a particularly neat solution.
Nige

if you don't know the acronym, assume you don't know the stuff it's meant to describe ;)

Your right - but it doesn't mean that I can't learn the concepts fairly quickly. Sometimes learning a new technology may sound more daunting than it actually is.
Also you have to take into account that some of the adverts posted by agencies are posted by people who don't understand what they are asking for either.
Nige

My sort of understanding is that it's a platform that runs Java code, having a read through some of the stuff posted by Ezzaral ( thanks ) it's the server side of web services ( including EJB's ) and provides additional API's to cope with network and other services.
Just have to see how much they expect and how much they are prepared to let me learn. I have a knowledge of Java, but mostly batch type work in Oracle. See what they come back with.
Nige

Sorry if this is the wrong group to post in - I couldn't think of another suitable one.
I'm applying for a job which talks about 'experience using Java/J2EE', I've used Java but not sure if by putting J2EE that they are expecting anything particular?

Thanks
Nige

Think you need to replace your select from

select count(i.column_name) into cnt from test;

to

execute immediate 'select count(1) from test where ' || i.column_name || ' is not null'
into cnt;

This is needed because you can't dynamically pick which column you want to include in a SQL statement by using binding ( as far as I know of anyway ). This replaced statement should count just rows where that column isn't null.

Nige

One thing you need to think about is do you actually need to store a row per hit of the web-site? Could you just not have a small table which counts the hits per site?
If you do need each hit - you could hold each hit in a log type table, then have a batch process which overnight summarises this data onto a report table which you then use to generate the reports. You can then use another batch process which copies any data which is no longer needed to produce your results into an archive - perhaps even just a flat file to save space. If you needed very up to date stats - you could combine the summary table values with the current log data to give you truly up to the minute figures.

Nige

If the table has been modified recently and is 10g, you can try

select scn_to_timestamp(max(ora_rowscn))
from test;

Think I've seen things say it's only up to 5 days you can use this.

If you need to and can modify the database, you could always create your own version of the user_tab_modifications by putting an INSERT/UPDATE/DELETE trigger on the tables you want to track and create a small table which holds the same sort of data as the user_tab_modifications does.

Nige

Don't you just need to group on expirationtime?

Something like
select expirationtime, count(expirationtime)
from messages
group by expirationtime;

This will give you a list of the distinct expirationtime and the amount of times they occur.

Of course you'd have to replace the expirationtime in the above statement with your calculation of how it's derived from the database.

Nige

You should get rid of the * after the delete.
Also I'm only having a guess here as I can't try it. But is it worth renaming the table 'SUM' to something else as in some systems 'SUM' is a reserved word in SQL.

That can be solved using SQL, which is always preferable to use rather than PL/SQL if you can. The following would give you the results, there may be better solutions but as it's not too horrendous I thought it should be OK.

select custid, custname, custnumber
from testtable
where mod(custnumber, 10000) in ( select mod(custnumber, 10000 )
from testtable
group by mod(custnumber, 10000 )
having count ( mod(custnumber, 10000 ) ) > 1 ) ;

You may have to change some of the column or table names, but hopefully it's OK.

Nigel

Thanks Steven
I had the same feeling about utPLSQL - although it's working with/for coders who should be able to code things quickly, there is still a lot to do!
I recently left my job as I'm a coder and not an infrastructure engineer as I ended up doing - but thats another story! So I've been working on mini projects at home - mainly aimed at Oracle PL/SQL, I recently finished my first project and so was going back to some XML load/unload Java code I'd started a while back.
This led me to think about how I've seen regression and unit testing done before - badly is the normal answer! As I've not used any tools so far I started to think about what I would want out of it, utPLSQL is a useful jump point to start thinking about how to get a testing mechanism which is driven by a set of database tables rather than a coded solution. This is more a home project than any attempt at a commercial product, but interesting enough to give me a challenge. I'll have a look through the Quest software although I think the front end is the thing that will be way better than anything I could do.

Nige

So far I've managed to locate utPLSQL, this seems to be based on JUnit type of Unit testing.
Seems to have most things, but fortunately seems to lack the main feature which I've been looking at :)
I've been working with unloading and loading XML to a database and one of the features I'd started to build in was the ability to compare the import file with the database. This goes beyond a simple table compare to ( for example ) an employee compare.