Amber Vaidya

Thanks for the suggestions. My initial thought was to do it all in Java which was validated by you guys.
Thanks again !

I have a requirement in which I need to load multipl files into an Oracle database.
The actual code to read and load these files is written in Java because there a lot of business validations etc that need to be done on these files prior to loading.
There is a shell script that ftps/scps these files from a remote location to the working area (Linux server).
If a file fails due to some reason, the rest of the files should continue to be loaded.

My question is where should I loop and why? The concern is more around trying to find the better option when loading huge files.

Option 1 - Loop in the shell script
-----------------------------------
for $filename in "$filelist" do java com.blah.Processor $filename done

Option 2 - Loop in the java file
-----------------------------------
Iterator<String> fileList = getListOfFiles().iterator(); while(fileList.hasNext()){ String fileName = (String)fileList.next(); processFile(); }

Hi,

I have a system that needs to update two databases (both Oracle 10g) in a single transaction.
The question is; if I create a DBLink between the two Oracle databases and treat this transaction as a normal transaction (not XA) would it work?

The system is a standalone java application (not deployed on any web/app server) and I am using Spring for JDBC.

If you think that the above approach will not work, can you please suggest an alternative?

Thanks in advance !!

Nope.. That does not work because I need the ACCTMGRIDs in a comma separated list on one row.

Here's the one that finally worked
SELECT customerid, MAX (SUBSTR (SYS_CONNECT_BY_PATH (acctmgrid, ','), 2)) AS acctmgrid FROM (SELECT DISTINCT t.customerid, am.acctmgrid, DENSE_RANK () OVER (PARTITION BY t.customerid ORDER BY am.acctmgrid DESC) AS seq FROM targetlist t, account_manager am WHERE t.customerid = am.customerid (+)) START WITH seq = 1 CONNECT BY PRIOR seq + 1 = seq AND PRIOR customerid = customerid GROUP BY customerid ORDER BY customerid

I have 2 tables, TARGETLIST and ACCOUNT_MANAGER as follows

TARGETLIST Name Type ------------------- ---------------------------- CUSTOMERID VARCHAR2(20) TREATMENTCODE NUMBER(19) ACCOUNT_MANAGER Name Type ------------------ ---------------------------- CUSTOMERID VARCHAR2(20) ACCTMGRID VARCHAR2(20)
For all the CUSTOMERIDs in TARGETLIST, I am trying to find out the ACCTMGRIDs from ACCOUNT_MANAGER in a comma separted string

The data conditions are

1. There can be one or more ACCTMGRIDs per CUSTOMERID in the ACCOUNT_MANAGER table.
2. Not all the CUSTOMERIDs from TARGETLIST will be in the ACCOUNT_MANAGER table.
3. The TARGETLIST table may have multiple rows with the same CUSTOMERID

So, the final result should ideally look like this:

CUSTOMERIDACCTMGRID ------------------------------------------- 12349876 23459876,6543,2342 34566543,2342 4567 5678 6789
Here's the query I came up with, but its not working .
select CUSTOMERID, substr(SYS_CONNECT_BY_PATH(ACCTMGRID, ','),2) AMID from ( select DISTINCT(T.CUSTOMERID) CUSTOMERID ,AM.ACCTMGRID ACCTMGRID, count(*) OVER ( partition by T.CUSTOMERID ) cnt, ROW_NUMBER () OVER ( partition by T.CUSTOMERID order by AM.ACCTMGRID) seq FROM TARGETLIST T, ACCOUNT_MANAGER AM where T.CUSTOMERID = AM.CUSTOMERID (+) ) where seq=cnt start with seq=1 connect by prior seq+1=seq and prior CUSTOMERID=CUSTOMERID

It gives out a result like
CUSTOMERIDACCTMGRID ------------------------------------------- 12349876,9876,9876 - Once for each row of CUSTOMERID in the TARGETLIST table 23459876,6543,2342 34566543,2342 4567,,,, 5678,, - When TARGETLIST table has 3 rows for the CUSTOMERID 5678 6789, - When TARGETLIST table has 2 rows for the CUSTOMERID 6789

Can someone please help me?

Thanks in advance

Alright guys, so what you are saying is, I should write a shell script that submits the job to the utility and track the progress of the PID in the script (wait till the pid is active before finishing the script).
From my Java code, I should call the shell script and wait for the script to finish executing.

That seems feasible. Let me try it out.
Thanks !!

Hi All,

I have some java code that submits jobs to a utility running on Sun Solaris using the ProcessBuilder class of 1.5

The code is fairly simple. I am reading the error and output streams in separate threads too.

ProcessBuilder builder = new ProcessBuilder(....); Process proc = builder.start(); proc.waitFor();

Now, my problem is that the utility, I am using, does not wait for the job to complete. Rather, it returns as soon as it submits the job(And returns the Pid of the process that is running the job).
This makes proc.waitFor() to return with 0 even though actually the job/process is still executing.

Any idea on how I can make Java wait for the actual process to finish?

Any help is appreciated.

Thanks,
Amber
[ October 26, 2007: Message edited by: Bear Bibeault ]

Here's what I was trying to do

SELECT ZZ.*, (case when ZZ.RANK = 1 then pkg_val.fnc_error_msgs(A.CUST_ID) else '' ) as VAL_MSG from (SELECT A.CUST_ID, A.AMOUNT_OWED, A.OWED_FOR, RANK() OVER(PARTITION BY A.CUST_ID ORDER BY A.AMOUNT_OWED DESC) RANK FROM T_CUSTOMER_DEBT A WHERE A.OWED_FOR='DESKTOPS' ) ZZ

Basically, run the function once per CUST_ID. Sorry if I did not explain it clearly.

Thanks anyway.

That's exactly where I am stuck . I need the first three columns to appear as they do in the expected result (with the same CUST_ID appearing multiple times) , but the value in the last column (VAL_MSG) can appear either once for every CUST_ID or multiple times.

The purpose is to determine if the customer has a valid bank account or not.

Is this possible with a single query?

Hi,

I have an oracle table called T_CUSTOMER_DEBT as follows
T_CUSTOMER_DEBT ---------------------------- CUST_IDVARCHAR2(10) AMOUNT_OWED VARCHAR2(10) OWED_FORVARCHAR2(10)
This table contains close to 200,000 records for 3000 unique CUST_IDs.

I need to print a report that lists down these 3 coulmns and also checks if a bank account exists for
the customer in the system.
There is a separate function to check if the bank account exists called pkg_val.fnc_error_msgs().

The query that I wrote was as follows
SELECT A.CUST_ID, A.AMOUNT_OWED, A.OWED_FOR, pkg_val.fnc_error_msgs(A.CUST_ID) as VAL_MSG FROM T_CUSTOMER_DEBT A WHERE A.OWED_FOR='DESKTOPS'

Now, the problem is that even though this query returns the correct results, it is very slow.
I assume its because it is running the function for all the rows (200,000).

Is there any way I can write a query that would return the same results as the above query but
execute the function only for unique/distinct CUST_IDs (3000) ?

Expected result is

CUST_IDAMOUNT_OWEDOWED_FORVAL_MSG ------------------------------------------------------------------- C_00112457.22DESKTOPS C_0012341.21DESKTOPS C_00187347.99DESKTOPS C_00278987.98DESKTOPSNO BANK ACCOUNT C_0027823.98DESKTOPSNO BANK ACCOUNT C_002878.76DESKTOPSNO BANK ACCOUNT C_0033427.87DESKTOPS C_0047823.98DESKTOPS C_0058792.98DESKTOPSNO BANK ACCOUNT

Please help !! Thanks in advance.

- Amber

[edited subject - was "URGENT !! Please help with this query .."]
[ March 13, 2007: Message edited by: Jeanne Boyarsky ]

Hi All,

I have a requirement to display 2 select boxes (say "left" and "right") and be able to move items from one select box to the other and vice-versa with the click of 2 buttons (say "Move to Right" and "Move to Left")

This is simple, but the catch is that when I move the items back from "right" to "left" they should go back to the position in which they were earlier.

For example, when the page is first displayed I have the following items in the "left" select box (dynamically generated by querying the database)

Director Chairman General Manager Regional Manager Divisional Manager Sales Manager Associate Contractor

Now, I move "General Manager" and "Sales Manager" from "left" to "right" and then move "General Manager" back from "right" to "left".
"General Manager" now appears at the botton in "left". My requirement is that it should appear at its original position i.e second in the list.

Is this even possible? And if yes, can someone please point me in the correct direction to get this done.

Thanks in advance !!

Hi All,

I have a webpage with 2 frames. One frame contains a HTML menu, with each link of this menu, opening a page (JSP) in the other frame.
The menu and most of the other pages are deployed on Websphere Application Server 3.5.
But, one of these pages is an external application deployed on Weblogic 6.1.

My problem is as follows:

If the user stays on the page hosted on Weblogic for more than 1 hour and then clicks on the menu, Websphere times out and throws the application login page. The same happens if the user stays on the Websphere part of the application for more than an hour and then tries to access the Weblogic part.
I want to prevent this because to the end user, its one single application.

Any suggestions on how to avoid this?
Any help is appreciated.

Thanks in advance.
Amber

Found the solution ...

I needed to put the HungServerRecoverSecs entry in the IfModule tag of httpd.conf file

It works now

Hi All,

I am using Apache 2.0 with Weblogic 7.0.

We have a report generation feature in our application which processes millions of records.
The SQL query that retrieves these records typically takes 8-10 min to return the result :roll:

Now, the problem is when my servlet calls the data access object to execute the query, I see a thread being started for this process(in the weblogic logs), but after about 4-5 min I get the following error in Apache logs

READ_TIMEOUT [os error=0, line 202 of ../nsapi/Reader.cpp]: no read after 300 seconds

and a new thread is started which starts the execution from the beginning (As if a new request has been made).
And it goes into an endless loop.

I tried increasing the Timeout and the ConnectTimeoutSecs parameters in the httpd.conf file, but it did not solve the problem.

Ideas, anyone ??
Any help in this regard will be appreciated.

Thanks in advance.

I think I got it ..
Tried this out

select uu.* from ( select rownum as row_num, u.* from (select a.* from customers a where state='CA' order by a.cust_id) u where rownum <= 5 )uu where uu.row_num >=1

and it seems to be working fine.

Please let me know incase anyone sees any problem with this approach.

Thanks !!