maha anna

Hi everyone,
Currently I am in middle of a production problem in my work and researching for a solution to do a 'search and replace' operation inside an Oracle 9i CLOB column.
Last week end we moved from Oracle 8i to 9i. Some of our user notes saved inside a CLOB column in oracle 8i now show a weird (upside down question mark) char in 9i. We could not know at what stage these weird chars got into database.
As a quick solutuon we decided to go for a search-and-replace operation to replace these special char with correct one.
I wrote a custom search and replace function also. But when I call this function from SQLPlus I get an ORA error. Please note that I am not an expert in PL/SQL. I am a Java person. But for this production problem I volunteered to work.
Could anyone help with this situation? Thanks a lot for any pointers/help.
Regards,
Maha Anna
create or replace FUNCTION SEARCHANDREPLACE4(src_clob CLOB) RETURN CLOB AS ret_clob CLOB; temp_clob CLOB; last_weird_posNUMBER; last_copied_pos NUMBER; copy_chunk_size NUMBER; INVALID_CHAR CHAR := 'f'; BEGIN IF (src_clob is not null) THEN -- init the temp_clob to be an empty one temp_clob := empty_clob(); last_copied_pos := 1; last_weird_pos := DBMS_LOB.INSTR(src_clob,INVALID_CHAR,1, 1); -- for now assume the weird char is 'f' WHILE(last_weird_pos <> 0 ) LOOP copy_chunk_size := (last_weird_pos - last_copied_pos); DBMS_LOB.COPY(temp_clob,src_clob,copy_chunk_size,1,last_copied_pos); DBMS_LOB.APPEND(ret_clob,temp_clob); --reset values temp_clob := empty_clob(); last_weird_pos := DBMS_LOB.INSTR(src_clob,INVALID_CHAR,(last_weird_pos+1), 1); END LOOP; END IF; return ret_clob; END; /
SQL> update patient_note 2 set note = SEARCHANDREPLACE4(NOTE) 3 where patient_note_key = 1390; set note = SEARCHANDREPLACE4(NOTE) * ERROR at line 2: ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275 ORA-06512: at "SYS.DBMS_LOB", line 347 ORA-06512: at "R40_DEV_BETA.SEARCHANDREPLACE4", line 32
The error occurs at this line:
DBMS_LOB.COPY(temp_clob,src_clob,copy_chunk_size,1,last_copied_pos);
Thanks
Maha
[ April 07, 2004: Message edited by: maha anna ]

Just to be clear, once you login to the remote machine, no one else can use that machine. No body can see what I am doing. The machine is locked. If someone else try to login to that machine, you will get a message saying, this computer is currently used by "Name of the login"
On the reputation side, I feel honoured and happy with the team and management to make this happen. This is the first time they are doing something like this. (allowing an employee work from India).
Just last week, as a surprice, the CEO gave 'Canopy Excellence Award' for the work I am doing here. They gave a nice certificate and took me for a special lunch. :-)
Thanks
Maha
[ August 14, 2003: Message edited by: maha anna ]

Hello Thomas,
Thanks for the wishes. The data sent back and forth is encrypted by the RDC technology. Basically the remote machine which you are going to logon to is configured to allow remote access.
All that we need is a browser at the client side. The Client machine needs to install a small client side RDC program. That's all we need. The OS needs to be Win98 /Win NT/Win XP
My IT department has titened the security so that only requests from my Home Computer's IP address can be allowed to access the RDC.
Thanks
Maha

Hi all,
Thought of writing an update.My company has finished up with the legal decisions with HR. Currently we are trying out different working mode.
Windows XP comes with an inbuilt feature called 'Remote Desktop Connection'. The concept is not new. It's like 'Go to My PC' / 'PCAnywhere' programs. Basically you login to the Remote Machine through internet and get full control of it and other network resources exposed to the remote machine.
My IT department is suggesting this as a working mode to work from India.
I am in USA and have used this feature and worked from home few days using DSL with 350K connection. I did some research about Broad Band conneciton availablity in India and it looks like 350K is not common. DishNet DSL seems to be the major player in this field in Chennai,India.
Win XP's Remote Desktop Connection is basically a screen-scrapping technology. All the programs are run at the Remote machine itself, only the display data and keyboard/mouse (input and output) clicks are transferred back and forth.
Microsoft articles say through this mode we should see the response faster than VPN technologies.
I have used both dialup (48K) and DSL (350K) to work from home using RDC.
Dialup is very slow. I can not use for regular work. With DSL 350K, it's almost feels like me sitting in my office before the machine.
I would appreciate your experiences regarding working remotely especially from India using Broad Band connections.
Thanks,
Take care.
Regards,
Maha Anna

Ram,
I have not statred the resume posting/ interview process. My company is in the process of allowing me to work from India through VPN and come onsite on a need basis. So I am waiting to hear back from the parent company's HR personal.
Thanks for getting back.
Regards,
Maha Anna

Your previous experience would really help to get a work back easily. Do not worry. Your health is more important right now. Take care.
Regards,
Maha Anna

9-10 hrs are normal in my company. But the management does not expect you to be in office and work after 6 PM. People get in to office early. Mostly around 8 AM and start winding up by 6 PM.
Regards
Maha Anna

Thank you Monu. I really appreciate all your help. You are correct. I would like to develop for some more time as I find it as a joy. I realized that in small companies we are recognized more than big ones. I keep my options open though.
Thanks.
Regards,
Maha Anna

Thank you Ajith.
Did you do the whole interview process over telephone iteslf? Or were you asked to take some online tests etc? Just wanted to know your experience.
Once again Thank You.
Regards,
Maha Anna

Thanks a lot Jyothi. That's comforting and very useful information.
Regards,
Maha Anna

I guess, we all ignored the option of working for the same company through DSL and VPN (over internet) from India.
Our current company has VPN already and there are 2 employees working from home for 2 days per week and another working from another state (Pittsburgh,PA) within US.
Anybody has this experiance of working from India over VPN (internet) with a broad band DSL connection from home to ISP? Is it workable solution?. Essentially, I will have a laptop with Jrun installed locally and accessing database located here in USA. Snce I am in development side I will be often accessing data from database for new feature development with GUI work,servlet,jsp(Business Layor) etc.
Thank you all once again.
-Maha
[ March 12, 2003: Message edited by: maha anna ]

I really really wish I could be cloned . One to take care of my son's education and growth in India and another to make myself happy. I am struggling between the two. [ We love India and we want our son to grow in Indian cuture. The ideal dream would be to get the kind of work I like and be with family in India and travel when needed for short-term (1 month) in 6 months. It's completely our family's personal preference and this is the main reason for the move. I DO NOT WANT this thread to get side-tracked with any culture arguments. ]
Thanks
Regards,
Maha Anna
[ March 12, 2003: Message edited by: maha anna ]

Thank You Ram.
I would be interested in (and have been) in development area. Hope to get one.
Regards,
Maha Anna

Thank you Ravish. Chennai is first preference since it works out good for my husband,my son and myself. Bangalore comes next.
Regards,
Maha

Thanks a lot Ajith for your thoughtful, informative reply.
Take care.
Regards,
Maha Anna