Oracle 11g Different Decimal Char While Using Sproc
Jul 6, 2011
I am using an portuguese(brazil) Oracle 11g db,which should have ',' as decimal char. it seems to be working fine when i m trying to get numerical data via select query,but when i am trying to retrive the same data using out parameter of an sproc.I am getting data with '.' as decimal char.
I want to pass english character as a parameter and search a string that having swedish character, this needs to be done for all the swedish characters.
test _t table having the below 3 values
päiväp metervara flerfärgad
1. If user searches based on english char like below then they should get all the 3 values bcz fist & last have swedish ä and second one having english a.
Query : select name from test_t where name like '%a%'
Regsult: päiväp metervara fräg
2. If user searches based on swedish char like below then they should get only 2 values bcz fist & last have swedish ä and second one having english a.
select name from test_t where name like '%ä%'
päiväp fräg
Is any in-built function available, for Text alternative string search. If not how to search the string based on the query.
Need to create Sproc(loop) to insert data..We have 2 table:
- BE Table Name Null Type ---------------------- -------- -------------- BID NOT NULL NUMBER(10) BUSINESSENTITYTYPEID NOT NULL NUMBER(10) PARENTID NUMBER(10) VERSION NOT NULL NUMBER(10) FULLNAME VARCHAR2(255) PHONE VARCHAR2(255) FAX VARCHAR2(255) [code]....
Whenever the BE table is read it gives more than 80K records from cust_id=1 and so on and becomes a bit slow ..
Now, when we copy the records from BE to CUSTPAYMETHOD (assume 30k). Next time when we read the table it should show us the customerid from 3001based on New Customer's(user created) Customerid.
CUSTID column is logically linked with BID column (FK not enforced) We don't have to pass parameter as we know the cust_id=1,flag for suspended='Y' and Eff_date between date1 and date2..We have accept from/to Period.Probably a loop to insert...This particular SP will run in all env and the id is going to be different how we are going to handle this...
We want to have a procedure. This is to purge BE table.
1) Insert a new parameter CUST_PMNT_MTHD_RETENTION_PERIOD into system_parameter table(which is only a parameter table) -- this can be done outside of Proc.
2)Create a new Customer(user id) through the Customer application and then find out the custID for the new customer -- this can be done outside of Proc.
SP should be for:
- Store Proc will Update the CustID in the CustPayMethod table to the new customer ID (that was created) when the EFFECTIVESTARTDATE older than CUST_PMNT_MTHD_RETENTION_PERIOD(from system_parameter table) days and suspended=’Y’.
- Copy records with custid=1(anonymous user) and startdata older than retention_period and suspended='Y' to Table CUSTPAYMETHOD
Eventually,delete the record from BE table.BusinessEntity(parent) table is information about customer and customerpaymentmethod(child) table is about payment mode(how payment is made by customer).
Assume If BID=1 then we can find Cust_id=1(which can have many records as the payment mode may change because the customer pay by different mode(credit,debit etc).We have to use loop and commit every 200 or 500 records.
FIrst we have to update the table with new cust_id and then insert...
Well the company i work for has just recently upgraded from Oracle 9i to 10g. We are having various problems with the migration, and with certain code breaking. My question regards this piece of code;
[code]
to_number(to_char('a_date','YYYY'))
[code]
There are various statements like the one above scattered throughout a query i am trying to fix. When run, the query returns an "invalid number ora-01722" error, which i know is caused by the above code.
if this method of converting from date, to character was discontinued from 9i to 10g?it just seems strange as there are a lot of statements like the one above and they must have worked at some point, but now i cant even get one to work on its own.
Printing a bar code (such as a pick slip number e.g. 223456) in Oracle Applications 10.7 char, reports 2.5 on a genicom (dot matrix)..I was unable to find any fonts for this when I searched.
I have to copy a table from oracle 8 to oracle 10 - dblink don't work between 8 - 10 - can I dump table ? - when I try to use sqlload via TXT file, I have error messages due to a column containing 'special' caracters ( ,..) and line too long (splitted in several rows)
I have a field in a table that is declared in the CREATE statement as an INT datatype. However, when I query that table using vb.net, the value comes back as a decimal.
How do you declare a field in Oracle as a true integer data type?
Actually I got a data in hex values and i want to convert it to datetime format. But before i convert the hex value into datetime format, i must convert the hex values into decimal val first for each 2 digits of hex values. This is an example of the input data:-
This is input data in hex value:- STARTTIME : 080b1317021a
This is decimal value after convert from hex to decimal:- STARTTIME : 081119230226
My problem is how could i convert hex to decimal in oracle? Below are my coding:-
case when substr(LOAD_NGNSM.STARTTIME, 1, 2) not between '00' and '99' or substr(LOAD_NGNSM.STARTTIME, 3, 2) not between '01' and '12' or substr(LOAD_NGNSM.STARTTIME, 5, 2) not between '01' and '31' or substr(LOAD_NGNSM.STARTTIME, 7, 2) not between '00' and '59' or substr(LOAD_NGNSM.STARTTIME, 9, 2) not between '00' and '59' [code]....
I am using Oracle Forms 6i. In my program unit i write into a .CSV and .TXT File. Here if the amount is 100.00 then it prints as 100 only. That is if the 2nd decimal digit is 0 it doesnt print it.
For example 10.00 -->10 10.01--> 10.01 10.10-->10.1 10.12-->10.12
I used to_char(nvl(&amount,0),'9999999.99') so if query select to_char(nvl(10.10,0),'9999999.99') from dual, then i get the output as 10.10.
Do you think this is a problem with Oracle forms or SQL or the formatting of .TXT & .CSV.
Recently i have started working on PLSQL coding. I have a requirement. Either error or un-processed record count is 90% of to be processed records then the script has to fail. Currently I am having a situation where error count is 1 and total to be processed is also 1.
in the below V_ERR is error count V_UPS is un processed count V_PROCESSED_COUNT is total to be processed.
I am expecting PASS result but it is giving FAIL.
DECLARE V_ERR NUMBER:=0; V_UPS NUMBER:=0; V_PROCESSED_COUNT NUMBER:=0; NIN NUMBER; BEGIN V_PROCESSED_COUNT:=1; [Code] .......
Using Oracle SQL Developer 2.1.1.64 to run the queries & Oracle 11g.
I have two numbers in two colomns of an oracle table(colomn a & colomn b). I am trying to divide colomn a/colomn b and putting the results in colomn c & also in colomn d (all in the same table) using update commands
Eg: UPDATE MTOTABLE_PWELD pw SET pw.WELDO=(pw.pipe_length/12000);
But here is the real issue. In colomn d I only need the integer portion of the division value.
For example , when I divide colomn a/colomn b , let us assume that we are getting a value of 2.56. Then I want the value of 2 to go to colomn d.
I tried round((colomn a/colomn b),0). But it rounds off 2.56 to 3. I dont want that. I need the exact integer portion of the value to be seperated.
Insert into TEST(CREDIT_BALANCE, AWARDS_BALANCE) Values ('1678.09', '1678.9'); Insert into TEST(CREDIT_BALANCE, AWARDS_BALANCE)
[Code]....
From valid records from test table those two columns,need to be insert into test1 table. We should validate the decimal values while selecting from test table.
load data infile 'C:\Documents and Settings\xxxxx\Desktop\abc.txt' APPEND PRESERVE BLANKS INTO TABLE table1
[code]...
When I run the above control file in sqlldr, I'm getting the error as Record 1: Rejected - Error on table table1, column column3. ORA-01481: invalid number format model
In the table the column3 data type is NUMBER(6,2).: The column size in table is 6 and position of column3 in control file is only 4. Also if possible let me know how the same data (send me 2 dummy records) which exactly works for the above control file especially for column3 where decimal number comes in the flat file.
For generating the flat file, for column3, i'm using LPAD(:value,4,0) in the select query column list.
I have a small prob. I want an amount to be always 2 decimal places. I've used the Round function - Round(amount,2). the problem is that if the amount is only to 1 dp like 1.4. the above function will return 1.4. I want it to appear like 1.40
I have loaded shape files with SQL developer Georaper and limited number of decimals to 8. There is option to limit number of decimals in georapter.But there are some X, Y with many decimals. This may create problem in spatial functions in which geometries are compared and filtered
how to control the decimal points of X,Y to be loaded in geometry column. -0.4695623500000000172249770002963487058878 51.47160360000000167701728059910237789154-0.4702653600000000211345252409955719485879 51.47174944999999723904693382792174816132-0.4713475400000000092859409051015973091125 51.47330664999999783049133839085698127747-0.4717886499999999760923685698799090459943