Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Character changing when inserting into Oracle

979621Dec 12 2012 — edited Dec 13 2012
This is a re-post of a question that was incorrectly put in the globalization forum.
2477635

I am debugging an old web service which is using Enterprise Services. The service is sitting in IIS 6 using .NET 4. A new problem appeared when an insert statement was generated that attempted to insert a string which included a degree symbol. When executed, the degree symbol is changed to an upside down question mark. No error messages were generated.

The strange thing is that I took a copy of the assemblies and dropped them on a test machine (same OS and IIS) and the insert statement worked. I've been trying to find the difference between the two machines to determine why this is happening. It's the same code running so I know the code is not the issue. I also know that it's using the same Microsoft Oracle provider. I've examined the region/language settings and the config files in the core framework folders and everything matches.

Anyone have any idea what I'm missing or where I can look for additional settings?

Additional information:

NLS_CHARACTERSET - WE8ISO8859P1
NLS_LANGUAGE - AMERICAN
NLS_TERRITORY - AMERICA
NLS_CALENDAR - GREGORIAN
NLS_DATE_FORMAT - DD-MON-RR
NLS_DATE_LANGUAGE - AMERICAN
NLS_SORT - BINARY
NLS_TIME_FORMAT - HH.MI.SSXFF AM
This is the connection string from the object: OleDbConnection("Provider=MSDAORA.1;...

And the oracle client installed is version 10.

Comments

Zeeshan BaiG
well u can use Dynamic SQL in PL/SQL for this and REF Cursors refer Oracle documentation for REF CURSORS and DYNAMIC SQL

plz mark it helpful if it is
BluShadow
Answer
Are you wanting to update individual rows on each of those tables, or the whole table of rows where there is any row where std != 1?
Marked as Answer by Vicky007 · Sep 27 2020
Vicky007
select table_name from user_tables where PCT_FREE NOT equal to 10 and table_name like 'GenAgile%' or table_name like 'GenMicro%'


PCT_FREE is field of user_tables


if PCT_FREE not equal to 10 and table_name like GenAgile%' or 'GenMicro%' update PCT_FREE of user_tables to <input_number>
commit;


can any1 help me now ?

Edited by: user8713254 on Nov 30, 2009 8:24 AM
fsitja
Maybe something like this, not tested. I'm assuming you meant you want to update the individual rows with pct_free <> 10 for each of those tables located in all_tables.
create or replace procedure t_proc(p_numval in number) is
  cursor cur_tab is 
    select table_name
      from all_tables t
     where t.table_name like 'GENAGILE%'
           or table_name like 'GENMICRO%';    
begin
  for tab in cur_tab
  loop
    execute immediate 'update ' || tab.table_name || ' t
                          set t.pct_free = :1
                        where t.pct_free <> 10' using p_numval;
  end loop;
  commit;
exception
when others then
  rollback;
  raise;
end;
Vicky007
no its wrong i just want to update PCT_FREE column of "user_tables" table for those selected tables by INPUT
fsitja
umm just a second... do you want to update THE user_tables itself? Because that's not possible, you're dealing with a data dictionary view here, that describes your real table in the database. It contains metadata and you can't change it directly, in any other way besides changing the table described by it. It would be like telling a lie to your database about those tables, and Oracle relies and depends on that information inside USER_TABLES to be able to operate correctly.

Now if that's the case you could maybe explain to us what is it you need to accomplish please.

Edited by: fsitja on Nov 30, 2009 2:31 PM for clarification
Vicky007
Its required !
cant we do it ?
fsitja
You can't. Why do you need it? Let's not look into the microscope for a second now.

What's the requirement behind this idea, that moved you down this path in first place?
728534
Hi welcome,
Please go through the forum link mentioned below before you try this out.
3867075
As far as syntax is concerned
-- Created on 11/30/2009 by USER 
declare 
  -- Local variables here
  i INTEGER :=20; /* variable you will pass to the procedure */
begin
  -- Test statements here
  FOR x IN (SELECT table_name FROM user_tables WHERE pct_free !=10 AND (table_name LIKE 'GENAGILE%' OR table_name LIKE 'GENMICCRO%')) LOOP
  
 dbms_output.put_line (x.table_name); /* for debugging */
  EXECUTE IMMEDIATE 'ALTER table '||x.table_name||'  pctfree ' || i;
  END LOOP;
end;
Cheers!!!
Bhushan
1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 10 2013
Added on Dec 12 2012
3 comments
1,928 views