PL/SQL :: Verify If Salary In Numbers / Decimals Is Converted Into Words?

Aug 30, 2013

How do i check in Table B whether it is converted correctly into words taking input or reference from table A

Consider below example:-

Table A Table B
$125 Dollar One Hundred twenty Five only
$45,542 Dollar Forty Five Thousand Five Forty Two Only
$145.56 Dollar One Forty Five and fifty six cents Only
$145,253
$35,256.65
$560,250.67

View 10 Replies


ADVERTISEMENT

SQL & PL/SQL :: Numbers To Words

Jul 6, 2012

I tried to convert numbers to words, it shows the below error.

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 6 11:00:29 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT TO_CHAR (TO_DATE (2447834, 'j'), 'jsp') FROM DUAL;

TO_CHAR(TO_DATE(2447834,'J'),'JSP')
---------------------------------------------------------------------------
two million four hundred forty-seven thousand eight hundred thirty-four

SQL> SELECT TO_CHAR (TO_DATE (244783400, 'j'), 'jsp') FROM DUAL;
SELECT TO_CHAR (TO_DATE (244783400, 'j'), 'jsp') FROM DUAL
*
ERROR at line 1: ORA-01830: date format picture ends before converting entire input string

SQL>

View 4 Replies View Related

SQL & PL/SQL :: Spell Out Numbers To Words

Mar 14, 2002

I found a message which explain how to spell out numbers to words. I'm french and the purpose of my question is how to convert numbers to word (to print cheque) but in french the function found in the newsgroup was :

select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL ) ||
decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )
from dual
/

I'm afraid but i don't undaunted how pass my number to this function (&num ???)

View 12 Replies View Related

PL/SQL :: Convert To Number Without Decimals

Sep 10, 2012

I have a value that I need in the number format, but without decimals. Is this possible?

TO_NUMBER(SY_KEY1,'9999999') AS SY_KEY1The result I get back is:

1114225.0So, I need a result of 1114225 and in the number format.

View 2 Replies View Related

SQL & PL/SQL :: Extracting Numbers And Few Portion Of Text From A String Containing Dates / Characters And Numbers

Jul 13, 2011

I have a table test with column containing dates, characters and numbers. I have to extract the number part and the three characters before the number . My data looks like :

TEST
ID DATA
1 3/12/2007
2 0
3 3/8/2010 ABC 217
4 NONE
5 COLM XYZ 469 6/8/2011
6 LMN 209

My expected results should look like :

ID DATA
1
2
3 ABC 217
4
5 XYZ 469
6 LMN 209

View 7 Replies View Related

Security :: Import XLS File That Converted Into CSV

Jul 27, 2010

I'm trying to import a xls file that I converted into a csv. I have a notes column that has carriage returns in it. This is causing SQL LDR to error out with the following error:

second enclosure string not present

It is only grabbing up until the first carriage return.how I can get it to load?

View 1 Replies View Related

Create Schema That Needs To Be Converted Using Non-oracle Syntax

Jan 27, 2011

I am new to oracle and sql in general, I received an oracle create schema that needs to be converted using non-oracle syntax. I have never seen this syntax before.

What does the following syntax mean?
CODE,line_status(1:20) char(2) null

CODE,file_line(1:6) char(40) null

View 4 Replies View Related

SQL & PL/SQL :: Timestamp Column Whose Values Are In CET Timezone Needs To Be Converted To EST

Mar 6, 2012

I have a requirement in which a particular a timestamp column (date1) whose values are in CET timezone needs to be converted to EST and day light savings should be taken care of in the conversion logic. I should not use any ddl statements for altering the timezone and all.

View 9 Replies View Related

PL/SQL :: Automatic Rounding Off Number Datatype While Converted To VARCHAR2

May 29, 2013

I am facing issue related to Number data while it is being converted to Varchar2, it is automatically getting rounded off after 32 decimal place.My database version is "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production".

1) create table test18 ( col1 varchar2(10), val Number);
create table succeeded.

2) insert into test18 values ('First', -347026.6408499652467480885711448714129679); -- After decimal 34 digits
1 rows inserted

insert into test18 values ('Second', -347026.64084996524674808857114487141296); -- After decimal 32 digits
1 rows inserted

3) select * from test18;

COL1 VAL
---------- ----------------------
First -347026.6408499652467480885711448714129679
Second -347026.64084996524674808857114487141296

4) As per the requirement, all the columns would need to be concatenated as a single string along with comma delimiter

select col1 || ',' || val as record_string
from test18;

RECORD_STRING
---------------------------------------------------
First,-347026.64084996524674808857114487141297
Second,-347026.64084996524674808857114487141296

"First" string got rounded off to 97 (last 2 digits) instead of 9679 but for "Second" record it holds the actual value.Only thing which I could figure out while the number is getting type casted to String, it is getting rounded off to 32 decimal place.throw off some light on it and provide the solution how the original record can be kept intact without rounding off.

Expected Output_
RECORD_STRING
---------------------------------------------------
First,-347026.6408499652467480885711448714129679
Second,-347026.64084996524674808857114487141296

View 10 Replies View Related

Performance Tuning :: Converted Non-partitioned Table (1 Million Data) Into Range-partition

Mar 28, 2013

As per Article mentioned in Oracle Base,I have converted non-partitioned table (1 million data) into range-partition table,but,I don't see performance improvement in explain .

View 9 Replies View Related

How To Verify Serverside TAF Working Properly

Apr 22, 2009

#service setup:

$ srvctl add service -d dc1ext -s addval.dc1 -r "dc1ext1" -a "dc1ext2" -P BASIC
$ srvctl start service -d dc1ext -s addval.dc1

begin
DBMS_SERVICE.MODIFY_SERVICE(
service_name=>'addval.dc1',
aq_ha_notifications => true,
failover_method=>DBMS_SERVICE.FAILOVER_METHOD_BASIC,
[code]......

my attempt at verifying the TAF is working.

SQL> run
1 SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*)
2 FROM GV$SESSION
3* GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER

MACHINE FAILOVER_TYPE FAILOVER_M FAI COUNT(*)
---------------------------------------------------------------- ------------- ---------- --- ----------
anetapp02.dc2 NONE NONE NO 10
anetapp01.dc2 NONE NONE NO 10
app02.st2 NONE NONE NO 5
racdb02 NONE NONE NO 44
anetapp03 NONE NONE NO 10
anetapp04 NONE NONE NO 10
GNOME\APTSEA-6GKJHF1 SELECT BASIC NO 3
app01.st2 NONE NONE NO 5
anetapp05 NONE NONE NO 10
anetapp01 NONE NONE NO 10
anetapp03 NONE NONE NO 10

so it looks as thought the sessions are failing over, but my app guy says he get a few errors during the process.. i need it setup to move the current query over without returning an error.. do i not have this setup properly?

View 3 Replies View Related

Cluvfy - Verify Node Connectivity?

Aug 12, 2010

I get the following output for node connectivity check as part of pre clusterware installation checks.oracle@cps-oracle-1.homer.com% runcluvfy.sh comp nodecon -n cps-oracle-1,cps-oracle-2 -verbose

Verifying node connectivity
Verification of node connectivity was unsuccessful on all the nodes.

The above output i get for node connectivity component check. I pinged each public and private interface on node and found them to be working.The CRS is for Oracle10Gr2.

View 3 Replies View Related

PL/SQL :: Verify The Number And Char Column

Oct 12, 2012

From a list of values from a number column, I need to verify is there any values not satisfying number(3).

Ex- I have a column col1 where we have following values

'qwq'
'12.3'
'1'
'15'

Can I write a query to check the value not satisfying number(3) datatype.Eg the query should return

'qwq'
'12.3'

as the above two will not fit in to number(3) datatype columns

View 13 Replies View Related

SQL & PL/SQL :: To Verify Huge Number Of Records In Two Different Databases

Jun 5, 2012

I need to verify huge number of records in two different databases. Basically i wanted to check if same record exist in other database's table or not? but as the number of records are more than billions who would i verify?

checking record one by one would be so hectic and time consuming. any other option is there?

View 11 Replies View Related

Server Administration :: Verify Tablespace Space

Jan 9, 2012

Application team requested hosting team to add some space to tablespace as it was exceeding 80% used.Now the hosting team have added the space as per recommendation and the application team wants us to verify if the space was added. How to check the space was added in GB to list of tablesapces ?

View 5 Replies View Related

Performance Tuning :: Verify If Procedure Has Been Completed

May 10, 2011

Is there a way to know if a procedure has been completed/ still running?

View 2 Replies View Related

Security :: Password Verify Function While Creating A Profile

Dec 5, 2011

I've created a password verification function (verify_pwd) in a schema which is not in SYS, but an equivalent of SYS. However, the problem arises when I'm trying to create a profie (MAIN_PROFILE) with the following attributes :

CREATE PROFILE MAIN_PROFILE LIMIT
PASSWORD_LIFE_TIME 90
PASSWORD_VERIFY_FUNCTION verify_pwd;

The above script is resulting in an error.

ORA-02376 : invalid or redundant resource...Can I create the function verify_pwd not in the schema SYS but instead in a schema equivalent to SYS?

View 3 Replies View Related

SQL & PL/SQL :: Verify And Perform Validations On Data - Table Partition

Oct 11, 2010

I have a staging table with 70 million rows and nearly 90 columns. We just want to verify and perform some validations on the data and then move this data to final tables. There staging table has a primary key in it.

We are planning to create the final table in following ways.

1. Create n number of final tables.
2. Create n number of final tables with partitions based on the primary key value in staging table.
3. Create single final table with partition based on the primary key value staging table.

View 2 Replies View Related

Performance Tuning :: How To Verify If A Row Exists (index Range Scan)

Oct 18, 2011

In the link below [URL] Thomas kyte has said, use the CBO and select /*+ FIRST_ROWS */ primary_key from table where rownum = 1; it'll read the index and stop at the first row. very fast on a big empty table (as the index is small and empty).

very fast on a big full table as the index is just read to find the first leaf node and then "stop".

It gives faster result if the primary key is used. But what if we have a table with around 1000 million rows and for the predicates there is a index range scan on the table.

What if we have a table say big_table (10000000000 rows) and the sql is something like

select /*+ first_rows */ 1 /* id, attribute_id*/
from big_table
where attribute_name ='Gross Premium'
and value ='10000'
and version_date is null
and rownum=1; --it's taking around 3 min

We observed that in such case there will be a range index scan for the index on the predicates. For a particular id there may be different values for attribute 'Gross Premium' and may have multiple versions.

How I would tune such a query where the purpose is to check if at least 1 records exists in the table for the input?

View 4 Replies View Related

Server Administration :: How To Verify If Rebuild Index Required In Database

Mar 26, 2012

How to verify if rebuild an index is required in database.

View 9 Replies View Related

PL/SQL :: Finding ID Max (salary)

Oct 31, 2012

Select * from one:

ID SALARY
----------------------
10 1000
20 2000
30 3200

Select * from two:

ID SALARY
----------------------
10     1000
20     2000
40     4000
10     3000
20     4000
30     3000

When i try to find ID,MAX(SALARY) from these two tables, i am getting this output:

Select id,max(salary)
from
(select * from one
union
select * from two)
group by id
order by max(salary) desc;

OUTPUT:

ID MAX(SALARY)
--------------------------------------
20     4000
40     4000
30     3200
10     3000

I want OUTPUT to be like this:

ID MAX(SALARY)
--------------------------------------
20     4000
40     4000

View 3 Replies View Related

Find Nth Highest Salary?

Jul 18, 2011

How does the following underlined query works to find the nth highest salary

SELECT DISTINCT(A.SALARY) FROM EMPLOYEE A
WHERE &SALARY = (SELECT COUNT(DISTINCT(B.SALARY)) FROM EMPLOYEE B
WHERE A.SALARY<=B.SALARY);

View 3 Replies View Related

SQL & PL/SQL :: Salary Hike Using Cursor

Sep 22, 2011

A.Create object to get the salary hike of an employee on the basis of completion of years in an organization.

Conditions:-
Create two tables with name as "Employee_yourname" and "Employee_Hike_Yourname".
"Employee_yourname" can have columns like EMPID, NAME, SALARY, DATE_OF_JOIN.
"Employee_Hike_Yourname" will have 3 more columns for new salary, Current salary and completed period in organization.
Consider the employees who have completed minimum one year in organization.
Salary hike for employee with tenure >= 1yr and < 2yrs should be 10% of current salary.
If tenure is greater than or equal to two years and less than 3 years then salary hike should be 20% of current salary.
For employees having tenure more than 3 years hike should be 30% % of current salary

View 11 Replies View Related

SQL & PL/SQL :: Display The Maximum Sum Of Salary?

Aug 14, 2010

I want to display the maximum sum of salary among sum of salary of each department.

deptno sal
10 1000
10 500
10 100
20 2000
20 200
30 500
30 1500
30 2000
30 200

Sum of salary for each department.
10 1600
20 2200
30 4200

The output should be
30 4200

Because this the highest sum of salary compare to sum of salary of reaming departments.

View 4 Replies View Related

SQL & PL/SQL :: How To Find The Nth Highest Salary

Jun 30, 2010

how to find the nth highest salary

View 11 Replies View Related

SQL & PL/SQL :: Amount In Words

Aug 4, 2009

I am using below code to spell number in words

create or replace
function spell_number( p_number in number )
return varchar2
-- original by Tom Kyte
-- modified to include decimal places

[Code]....

The number amount is : 9899.25

I am getting the output is:

NINE THOUSAND EIGHT HUNDRED NINETY-NINE POINT TWO FIVE

But i want output should be

NINE THOUSAND EIGHT HUNDRED NINETY-NINE AND Twenty five FILS ONLY

where i need to modify the code.

Files is equal to cent as per DOLLOR currency

View 6 Replies View Related

SQL & PL/SQL :: How To Get 2nd Highest Salary From Employee Table

Apr 29, 2011

i was given a task to find the second highest employee sal from emp table.

View 5 Replies View Related

SQL & PL/SQL :: Calling Function Which Calculate Sum Of Salary

May 8, 2010

I created a PROCEDURE in that i am calling function which calculate sum of salary...I just want Output in format for that which function i need to use...?

Actual Output:::

DEPt_Name SALARY
ACCOUNTING 8750
RESEARCH 10875
SALES 11600

I want Output in well alignment column...i WANT Output IN column format but my output in not geting in that format...Is there any function to align output...I want Output in well alignment column

View 4 Replies View Related

Forms :: How To Validate Salary Regarding To Its MIN And MAX Value From Another Table

Jun 16, 2012

I have a created data block using employees table so am trying to validate item salary using max_salary and min_salary from table jobs how to do this kind of validation.

[How can i validate input number into a Field of type char in oracle form?]

View 1 Replies View Related

SQL & PL/SQL :: Function To View Maximum Salary

Dec 16, 2011

I have to write function that receives department name and an aggregation operation (average, maximum, minimum) and apply the operation on the salary of employees working on the given department and return the result.

here is my select statement:

select distinct d.deptno, d.deptname, max(e.salary)
from employee e join department d
on e.deptno=d.deptno
where d.deptname=upper('finance')
group by d.deptno, d.deptname;
[code]...

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved