PL/SQL :: Characters Is Greater Than 4 / Actual Data Without Lpad Should Be Returned

Oct 15, 2012

For one of my row its returning as below lpad('abcdef', 4 , 'Z') returning abcd

but instead of this if no of characters is greater than 4 i want the actual data without lpad should be returned.

View 7 Replies


ADVERTISEMENT

SQL & PL/SQL :: Precision Greater Than Actual Value

Apr 6, 2012

When we are trying to create number data type column of a table with precision greater than actual value,it's accepting the definition of the table . But we are unable to insert any values into the table.how internally it stores the value

SQL> drop table precision_test;
Table dropped
SQL> create table precision_test(name number(2,5));
Table created
SQL> insert into precision_test values (1);
insert into precision_test values (1)
[code]....

View 5 Replies View Related

SQL & PL/SQL :: Order By Field Containing Characters And Numbers (split From Convert No Rows Returned To Zero)

Jan 13, 2012

I Want to make a query to select finished goods product in sales having product code greater than 280 but i have face a problem that order by is not working because products column have character code as well as number. how to sort that column.

View 2 Replies View Related

SQL & PL/SQL :: Selecting Data Greater Than Some Value Into One Column

Jul 15, 2013

I have table TEST_REP with below data

DA SUMA
---------------------- ----------------------
2011 2
2011 3
2011 5
2012 2
2012 7
2014 2
2014 10
2015 2
2016 33
2015 26
2017 21
2017 2
2018 23

13 rows selected

I have used following query to get the below output:

select
br_mat MAT_YEAR,
sum(br_par) TOTAL
from (
(select to_char(da) br_mat,suma br_par from test_rep)
UNION ALL
[code].......

Output :

MAT_YEAR TOTAL
---------------------------------------- ----------------------
2011 10
2012 9
2013 0
2014 12
2015 28
2016 33
2017 23
2018 23
2019 0
2020 0

10 rows selected

Expected Output :

MAT_YEAR TOTAL
---------------------------------------- ----------------------
2011 10
2012 9
2013 0
2014 12
2015 28
2016 33
2017 and Greater 46

View 6 Replies View Related

How To Find Time Taken For Parsing / Creating Plan And Actual Data Retrieval

Jul 13, 2010

Is there any way to find out the division between the time taken for query parsing, creating execution plan and actual data retrieval seperately? If I enable 'set timing on' I see the elapsed time which is the total time taken for all these 3. Some of my queries are taking long time when I run it first time and so want to know what is it taking long? is it the parsing or creating the execution plan, if so what can I optimize.

View 3 Replies View Related

Server Administration :: Any Relation Of Tablespace Disk Space And Actual Data Present In Tables

Aug 16, 2012

We are using Oracle 10g and have 10 tablespaces defined for our Database which have 108 tables. Size of 108 tables is around 251 MB as seen during importing the dump. While creating these 10 tablespaces I used below parameters for allocation of space

SIZE 1M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 1M;

which set the initial space for 10 tablespaces to around 1032Kb each. Now my Question is after importing the dump , how the disk space for 10 tablespaces increases to 398 MB in total ?

Is there any relation of Tablespace disk space and Actual Data present in the tables ?

View 18 Replies View Related

SQL & PL/SQL :: How To Obtain Data Returned By Function When Executed Using Dynamic Query

May 25, 2011

I'm attempting to use dynamic SQL to execute a function that returns a user-defined collection. The problem is that I don't know how to use dynamic SQL to handle user-defined types...or if this is even possible?

The code I have is as follows:

CREATE OR REPLACE PACKAGE qi_test IS

TYPE typ_qi_data IS RECORD(
iQIFlag NUMBER(1),
iIPFlag NUMBER(1),
iRiskIndicator NUMBER(1),
iDenominator NUMBER(8),
iNumerator NUMBER(8)
[code]........

I want to be able to execute the above function using dynamic SQL. Initially tried:

DECLARE
f2_data qi_test.typ_qi_data_tab;
BEGIN
EXECUTE IMMEDIATE 'begin :1 := qi_test.get_f2_data; end;'
USING OUT f2_data;
[code]......

...but this just produces "PLS-00457: expressions have to be of SQL types". So it looks like I can't do it this way if the returned data type is user defined. I know it would be easier in this instance to just use something like:

f2_data := qi_test.get_f2_data;

...rather than EXECUTE IMMEDIATE, but it's the principle that I need to get right as it forms part of a much bigger piece of work.

View 10 Replies View Related

PL/SQL :: Apply LPAD In NUMBER Datatype?

Jun 27, 2013

Can we apply LPAD in NUMBER datatype?

View 11 Replies View Related

LPAD - Reading Values From One Table And Inserting Into Another

Apr 20, 2012

LPAD not behaving as expected. the main thing I'm trying to accomplish here is reading values from one table, and inserting them into another... but in the "other" table, they need to be inserted as 11 characters long, with leading zeros. in it's most basic form, this is the cursor I'm using:

CODEDECLARE
CURSOR update_mpi_cur IS
    select distinct A.epn_nbr, A.mrn_nbr, B.mpi_nbr
      from table1 A, table2 B
     where B.external_id = A.epn_nbr and B.identifier_type = 'EPN';
[code]....

should have mentioned that this is Oracle 10.2.0.3, on HPUX. not sure if that matters for this issue or not, but wanted to throw that out there.

View 2 Replies View Related

SQL & PL/SQL :: Removing Special Characters And Get Desired Characters From Column Values

Jul 23, 2013

create table test
(
name varchar2(50),
descd varchar2(50)
)
insert into test values ('kethlin','da,dad!tyerx');
insert into test values ('tauwatson','#$dfegr');
insert into test values ('jennybrown','fsa!!trtw$ fda');
insert into test values ('tauwatson','#$dfegr ,try');

how do I get the first three characters and last three characters from name field and remove all the junk characters from descd field?

so my o/p be like;

Quote:('ketlin','dadadtyerx')
('tauson','dfegr')
('jenown','fsatrtw fda')
('tauson','dfegr try')

View 6 Replies View Related

SQL & PL/SQL :: Select Data With Special Characters

Feb 16, 2012

I have a table in which a column has some data like this:

prod¿ct_liste¿_25175892
prod¿ct_liste¿_76815456

Is there any way to select this data?

View 3 Replies View Related

SQL & PL/SQL :: Data Value Size Exceeding 4000 Characters?

Oct 11, 2012

I have a insert statement where i need to insert data to a column where i need to insert value more than 4000 char into column.

Different approaches and condition:
1. CLOB should not be used
2. Need full value to be stored

Approach:
1.I created few more dummy columns to insert the data , was inserting 4000 char and if exceeded i was inserting to next column, but this will be tedious if we have 35000 char
2.Insert in the same column as different rows

View 5 Replies View Related

SQL & PL/SQL :: German Data Upload Special Characters

Sep 20, 2010

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I am uploading the data file which is a .csv, into one of the tables.. Few columns of the data file, contains sentences in German - like- Lötstelle Zinnspritzer Zinnschluß .. and while loading the problem that i encounter is - it doesnt recognize the commas, and doesnt know where the field ends..
e.g., in this line 1540,Lötstelle Zinnspritzer Zinnschluß,solder short,45230,94885,0,0,0,0,0,0,0,0,0,0,0,0

sometimes it assumes comma after 1540,Lö,tstelle and then no commas till Lö, tstelle Zinnspritzer Zinnschlußsolder short,

and sometimes, the whole as one line Lötstelle Zinnspritzer Zinnschlußsolder short4523094885,0,0

View 2 Replies View Related

Precompilers, OCI & OCCI :: Getting Trailing Characters Inserted For Blob Data?

Jul 15, 2010

I am seeing some trailing characters in the coloumn when we are inserting Blob. I am doing SQLBindParameter with SQL_C_BINARY and SQL_LONGVARBINARY as InputOutputType and ValueType respectively.Do you see any problem in this. I get this problem when I am running Oracle 11 g client on an Windows 2008 Server 64bit.When the same set of query is fired from Windows 2003 Server 32bit with Oracle 10g it works fine and no trailing character gets inserted.

View 6 Replies View Related

Server Utilities :: Loader Loading Some Garbage Characters At The End Of The Data Field

Mar 25, 2011

After exporting some data to excel, I noticed that on one row all the columns shifted over some. So I queried this record in the database and noticed that the ADDRESS field has some unknown characters at the end of it. They are little squares. I think they are TABS.

2630 LINDEN BLVD, APT. #8G(2 squares are in here)

ADDRESS_1 "TRIM(:ADDRESS_1)",

Besides trimming the data, is there some other function I can use to clean up the address further?

View 9 Replies View Related

JDeveloper, Java & XML :: Reading Data From Database Table That Contains Control Characters

May 1, 2011

I have the following bit of code that reads data from the an Oracle table (Note: This is running on Jdk 1.4.2)

ResultSet message = messageStatement.executeQuery(getMsgSql);
String messageData = message.getString("MESSAGE_DATA");

The data in the MESSAGE_DATA column contains text but also control characters that separate data elements in the message (i.e (char)31, (char)29) and (char)28) . What i am finding is that for some reason message.getString() is sometimes truncating the message. I can read the majority of messages but some of them are truncated. Am i supposed to be reading the data using a different method? If so how?

I have tried to use sqlplus to look at the data in the database and it is all there it is just truncated by the message.getString() method. I saw this when i tried to output the result i.e. System.out.println(message.getString()).

I ran an sql query using the length() function in Oracle and length("MESSAGE_DATA") returns 2032 whereas in java message.getString(1).length() returns 2000.

View 5 Replies View Related

Can Execute Procedure On Some Other Day Without Changing Actual

Aug 21, 2012

I have a procedure which will execute on every Monday. Same is not executed last Monday. Can I execute the Procedure on some other day with out changing the actual procedure?

View 1 Replies View Related

SQL & PL/SQL :: Use Greater And Less Than In Left Join?

Apr 9, 2013

I am creating a query where I am trying to take phone call lengths and put them into buckets of length ranges 0:00 - 0:59, 1:00 - 1:59 etc. Even if there are no calls in the call table I need to return the range with a zero (hence the left join and nvl). When I do this the left join acts like an equal join, I suspect there is some reason left joins only work if there is an equal condition in the join (instead of >= and < that I use, or similarly I could use BETWEEN). I also have a question about performance (below).

The create table script for the lookup is like this:

CREATE TABLE DURATION_RANGES
(
RANGE_TEXT varchar2(20),
RANGE_LBOUND decimal(22),
RANGE_UBOUND decimal(22)
)

Sample inserts are:

INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',0,59);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('01:00 - 01:59',60,119);
etc.

The query is:
select
r.range_text as duration_range,
nvl(count(*),0) as calls,
nvl(SUM(call_duration),0) as total_duration
from

[code]...

As I say, it is not returning all ranges in the duration_ranges table, so acting like an inner join. I realize one solution would be to populate duration ranges with every value possible (instead of ranges) so join is an equal join, but that would make the duration_range table larger.

My questions:
1. Is it possible to get the left join to work with the duration range values as they currently are?
2. Even if 1 is possible, would it be better performance to have exact values (but a larger lookup table) and do an equals join instead of >=, < or BETWEEN? Performance now is not bad.

What I mean is (with only one time value and not lbound and ubound:

INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',0);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',1);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',2);

View 4 Replies View Related

SQL & PL/SQL :: Records Greater Than Row Number?

Mar 9, 2011

I have two sql queries. They run the one after another.

Query 1:
select * from capital
where member_status = 'MEMBER' AND rownum <= 25
order by price desc

Query 2:
select * from capital
where member_status = 'MEMBER' AND rownum > 26
order by price desc

Question is, in the query 2 I want records greater than row number 25. In query 2, I don't want the records that were fetched in Query 1. Is there any way to do this without using rownum?

View 7 Replies View Related

SQL & PL/SQL :: Time Comparison Alone Like Less / Greater Than?

Dec 15, 2010

Select
to_char(to_date('10-02-2006 10:30:00 AM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') as a1,
to_char(to_date('10-02-2006 01:30:00 PM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') as a2,
Case
when to_char(to_date('10-02-2006 10:30:00 AM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') >

[code]...

from the above query i was expecting value '2' but its returning '1'. As I am using TO_CHAR its trying to compare characters. Is there a way, to compare times alone like less than, greater than?

View 3 Replies View Related

Reclaiming Actual Physical Disk Space

Sep 2, 2011

I look after a team of DBAs and I have a request to free up space on our very expensive storage system. However the answers on how to do this differ and i'd like to ask for external input...So not being a techincal person I see the world as quite black and white. Meaning that you delete data and you free space but after doing much reading I understand this is not the case, as you essentially create data fragmentation within the datafile resulting in the db having lots more space to write into but not actually freeing space, even if you shrink the file it doesnt free space or do a reorg?

We have as an example a DB with 2 billion rows of data in 1 table, no partioning just one large table. We have worked out that we can probably delete 1 billion rows or even better only keep a rolling 3 month window of data. What would be the suggestion on deleting this data and reclaiming the disk space to actually see additional disk space made available at the os level.

How about deleting the data and reclaiming the space. Through reading it looks like it might be something like, delete, creating new table space partitions from this data. This in theory would create new a tablespace in newly created data files which would result in the data being reorganised and taking up less physical space and when completed you point to the newly created partitions and drop the old tables.

how they have done this as it must be a common problem that people have created some different solutions. What commands, procedures have been used?

View 2 Replies View Related

SQL & PL/SQL :: ORA-12899 / Value Too Large For Column (actual / 2 - Maximum / 1)

Feb 14, 2013

While creating the external Table, i am getting error.

ORA-12899: value too large for column PRC_REC_TYPE (actual: 2, maximum: 1)

But while checking the CSV file, i found that prc_rec_type is having one 1 length value. I am uploading the csv file also.

-- Create table

create table ET_PGIT_POL_RISK_COVER
(
prc_pol_no VARCHAR2(60),
prc_end_no_idx VARCHAR2(22),
prc_sec_code VARCHAR2(12),
prc_risk_id VARCHAR2(12),
prc_smi_code VARCHAR2(12),

[code].....

View 23 Replies View Related

PL/SQL :: How To Pass Changeable Actual Parameters Into Procedure

Mar 29, 2013

I am writing a procedure for the front-end. The end-users need to insert multiple rows of data into history tables in the database (11G). My problem is: the multiple actually parameters is not a fix amount, this time, the amount could be 5, next time, it could be 12. I currently used one string and pass the actual parameter (P_id, number) as '2, 4, 5, 7, 8', the procedure was executed successfully, but cannot insert any data into history table.

See my procedure below (the base table has clob data, I have to consider insert ... select *), I tried to use to_number (CONTACT_MSG_ID), it doesn't work well:

PROCEDURE ARCHIVE_XREF_CONT_EMAIL(P_ID IN VARCHAR2) IS
BEGIN
INSERT INTO TRC_XREF_CONT_EMAIL_MSGS_HIST
SELECT *
FROM TRC_XREF_CONT_EMAIL_MSGS
[code].......

View 10 Replies View Related

Conversion Hours Incorrect When Revert To Actual Date

May 18, 2011

I can't seem to understand why the hour is incorrect. Below query "dte_computation_on_data" is the old function they use to convert date and insert it to the table. Problem is when I revert it to the actual date the hour
is incorrect.

CODE
SELECT -- THIS HERE IS MY TEST TO REVERT TIME AND DATE ON THE FORMULA OF WITH RESPECT TO THEIR FUNCTION
to_char(TO_DATE('19700101', 'YYYYMMDD')+(tb1.dte_computation_on_data/86400),'MM/DD/YYYY') || ' ' ||
to_char(to_date(mod  (tb1.dte_computation_on_data,86400) ,'sssss'),'hh24:mi:ss ') revert_test,
systimestamp,tb1.dte_computation_on_data
from
( SELECT -- THIS IS THE FORMULA OF THE OLD FUNCTION THEY USE TO CONVERT DATE TO NUMBER AND INSERTED ON THE ROW
    floor((CAST(SYS_EXTRACT_UTC(systimestamp) AS DATE) - TO_DATE('19700101', 'YYYYMMDD')) * 86400) dte_computation_on_data
  FROM dual)tb1;

results
---------------------------------------------------------------------------------------
REVERT_TEST             SYSTIMESTAMP                            DTE_COMPUTATION_ON_DATA
05/19/2011 03:46:18     5/19/2011 11:46:18.005171 AM +08:00     1305776778

View 1 Replies View Related

Automatic Storage Management :: How To Identify Actual / Dev Nodes

Aug 1, 2012

I'm using ASM on LUNs from an EMC SAN, fronted by PowerPath. Right now I have only one fiber path to the SAN, so /dev/emcpowera3 maps directly to /dev/sda3, for example. Oracle had a typo in what they told me to do in /etc/sysconfig/oracleasm*, so the scan picks up both devices.

#/etc/init.d/oracleasm querydisk -p ASMVOL_01

Disk "ASMVOL_01" is a valid ASM disk
/dev/emcpowera3: LABEL="ASMVOL_01" TYPE="oracleasm"
/dev/sda3: LABEL="ASMVOL_01" TYPE="oracleasm"

But I don't think it can be using both. How do I see which one it's actually using?

*They said:
ORACLEASM_SCANORDER="emcpower*"
ORACLEASM_SCANEXCLUDE="sd"

But I think that should be "sd*".

View 3 Replies View Related

Workflow :: How To Find Actual Responder Name Displayed In Notification

Feb 13, 2013

Where are the details of the Actual Responder Details stored in the case of the below scenario

If a notification for one user was closed by another user through access to the first user's worklist, the name of the second user, who actually took the action, is displayed as the responder.

I am only able to find the Original Recipeint username in the wf_notifications table.But where will the details of the Actual Responder name displayed in the notification be stored.

When I use the "wf_notification.Responder" function, I get the Original Recipeint username of the user but not the one who acted on the notification. Is there ANY way by which I can get the Actual responder user name who acted on the notification

select responder from wf_notifications where notification_id = <nid> also gives me the Original Recipeint username of the user but not the one who acted on the notification

View 5 Replies View Related

SQL & PL/SQL :: How To Manipulate Greater Than 24 Hour Clock In Oracle

Jul 14, 2011

I have an Oracle application that deals with a 29 hour clock. so the days begin at 5am instead of midnight. I am trying to get data from a table where time is up to certain number of hours, but when it reached the 00 clock my whole query returns no records, even though there is plenty of records before the midnight hour, because the data is saved as lets say 2530 instead of 0130

I am using this

SELECT *
FROM WORK.WORK_UNIT
WHERE (to_char (current_garage) = :entry_blk.curr_garage
OR 'all' = :entry_blk.curr_garage)
AND route >= 0 AND run >= 0
AND ((package_id > 0 and work_code = 99) OR
(package_id is null and work_code = 1))
and Nvl (Emp_No, 0) = 0
AND work_date = :entry_blk.p_work_date
AND on_time <= TO_CHAR(SYSDATE + :Entry_Blk.up_to_hour/24, 'HH24MI')

since oracle deals with a 24hour clock, my code doesn't seem to work if there is data for after midnight (00 hour). I am using :Entry_Blk.up_to_hour/24 to determine up to how many hours I want to see data, i.e.

on_time <= TO_CHAR(SYSDATE + 2/24, 'HH24MI')
will give me data up to 2 hours

View 9 Replies View Related

PL/SQL :: Date Is Greater Than Current Time Plus 24 Hours?

May 26, 2013

how to pull data from a table where date is greater than current time (+24 hours)... my date field is in the following format 15-MAR-2013 20:07:00

I want to do something like this

select * from table_A where date_field > (sys_date_time) +24h

as an example, when I run a query @ 4 PM on March 26, I want to pull data that has date > 4 PM March 27

View 6 Replies View Related

PL/SQL :: Tuning Greater Than Predicate Query On Dates

Aug 2, 2012

I have the below query which is doing FTS and is very expensive causing load to timeout.

I did my analysis and found that table is having large number of records and hence FTS is taking long time causing timeout from app side.

I proposed to have this table partitioned but this is still pending with business and they in meantime want some solution other solution to fix this issue.

below is the query and plan

SELECT TRANSACTION_LOG.ID, TRANSACTION_LOG.USER_IDENTIFIER, TRANSACTION_LOG.START_TIME, TRANSACTION_LOG.END_TIME, TRANSACTION_LOG.REQUEST, TRANSACTION_LOG.RESPONSE ....

View 5 Replies View Related

Server Administration :: Reclaiming Actual Physical Disk Space?

Sep 3, 2011

essentially create data fragmentation within the datafile resulting in the db having lots more space to write into but not actually freeing space, even if you shrink the file it doesnt free space or do a reorg?

We have as an example a DB with 2 billion rows of data in 1 table, no partioning just one large table.

We have worked out that we can probably delete 1 billion rows or even better only keep a rolling 3 month window of data.

What would be the suggestion on deleting this data and reclaiming the disk space to actually see additional disk space made available at the os level.

deleting the data and reclaiming the space.

Through reading it looks like it might be something like, delete, creating new table space partitions from this data. This in theory would create new a tablespace in newly created data files which would result in the data being reorganised and taking up less physical space and when completed you point to the newly created partitions and drop the old tables.

View 6 Replies View Related







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