SQL & PL/SQL :: TRIM Function - Remove Symbol Only From LEFT Or RIGHT Side

Feb 22, 2011

Is there any possibilites to remove the symbol '*' only from LEFT or RIGHT side, instead BOTH the side.

select '>' || trim (both '*' from '***removing stars at both sides***') || '<' "Stars removed" from dual;

View 4 Replies


ADVERTISEMENT

Application Express :: How Vertical Left Side Menus Created

Jun 11, 2012

I was keen on creating left side vertical menus instead of using a tab for every page to organize my application better. An example is

[URL].......

I tried creating a list and attaching it to a page. Did not work. Still investigating this. Specifically, what list option that person is using. Not sure, if its really a list or maybe the vertical navigation bar?

View 3 Replies View Related

SQL & PL/SQL :: Remove / Trim More Than One Character?

Aug 15, 2010

How to write a pl/sql query to trim/remove more than one character from string.

Like the itemfield is 'Profit CY' I want it to show as 'Profit' but only for itemfields that say 'Profit CY' in the column for remaining items in column such as 'Loss CY' should stay as it is.

View 25 Replies View Related

Index With Trim Function?

Jun 26, 2012

I have the below SQL with cust_id is indexed

DELETE FROM ALERT WHERE TRIM(CUST_CD)=TRIM('100350378');

Output of the plan table is

OBJECT_NAME OPTION COST
ALERT 1866
ALERT BY INDEX ROWID 1866

DELETE FROM ALERT WHERE CUST_CD=TRIM('100350378');

Output of the plan table is

OBJECT_NAME OPTION COST
ALERT 3
ALERT BY INDEX ROWID 3

how to use trim on indexed column.

View 1 Replies View Related

Function Based Index With Trim

Jun 22, 2011

I have created a function based index(FBI) with trim(header_date), but when i query the table by passing the hardcoded date, it is not working and i have to manually apply trim to get the result?

my query after applying FBI is

select * from abc where header_date = '21-JUN-11', no results are returned and when i apply trim to header_date it works fine .

View 4 Replies View Related

PL/SQL :: Trim Extra Space In Function Parameter List

May 9, 2013

I am calling a function in front end, from front end i am passing space, now i want to trim space in parameter itself, it's psssible, assume the below is the function defination, i want to trim the p_region_name parameter like this trim(p_region_name), is this possible?

FUNCTION add_country_region_column (
p_s_country_code_iso_2 IN varchar2_table_type,
p_type IN d_country.c1_type%TYPE,
p_name IN d_country.c1_name%TYPE,
p_desc IN d_country.c1_desc%TYPE,

[Code]....

View 3 Replies View Related

SQL & PL/SQL :: Left Function Equivalent In Oracle 9i

Dec 22, 2006

Is there is an equivalent to the 'LEFT' Function of SQLServer in Oracle9i?

View 10 Replies View Related

SQL & PL/SQL :: Left Outer Join Or Function

Oct 13, 2010

I have two tables employee_master and employee_time. There could be situation that record from employee_master does not exist in employee_time.

create table employee_master(employee_id number, employee_name varchar2(100));

insert into employee_master values(1, 'employee 1');
insert into employee_master values(2, 'employee 2');
insert into employee_master values (3, 'employee 3');

create table employee_time(employee_id number, project_id number, project_type varchar2(20), time_date date, hours number)
insert into employee_time values(1, 100, 'Billable', '01-Oct-2010', 10);
insert into employee_time values(1, 200, 'Billable', '02-Oct-2010', 9);
insert into employee_time values(1, 210, 'Non Billable', '03-Oct-2010', 10);
insert into employee_time values(2, 100, 'Billable', '01-Oct-2010', 10);
insert into employee_time values(2, 200, 'Billable', '02-Oct-2010', 9);

The requirement is to show all the employees from employee_master and with total billable hours and non billable hours, if not exist, show zero.The output will be:

Employee_ID Employee_Name Total_Billable_Hours Total_Non_Billable
1 Employee1 19 10
2 Employee2 19 0
3 Employee3 0 0

The question is to write a Left outer join query or to write a PL/SQL function which can return total rows if Employee_ID is supplied to it as a parameter

Query 1:
Select Employee_ID, Employee_name, sum(Billable), sum(Non_Billable)
From
(
Select a.Employee_ID, a.employee_name,
decode(b.project_type, 'Billable', hours, 0) as Billable,
decode(b.project_type, 'Non Billable', Hours, 0) as Non_Billable
from employee_master a
left outer join employee_time b on a.Employee_ID=b.Employee_ID
)
Group by Employee_ID, Employee_Name

Query 2:
Select Employee_ID, Employee_Name, func_billable(Employee_ID) as Billable, func_non_billable(Employee_ID) as Non_Billable
From Employee_Master

Which query is good from the performance perspective? In real situation the employee_time is a very huge table.

View 2 Replies View Related

SQL & PL/SQL :: Printing Tables Side By Side

Mar 8, 2012

Here i am displaying the multiplication tables by given number...the problem is the tables are displaying one after another...i need to print it by side by side like..

1*1=1 2*1=2 3*1=3
` ` `
` ` `
` ` `
1*10=10 2*10=20 3*10=30
....

the written code is

declare
a number:=1;

[Code]....

View 9 Replies View Related

PL/SQL :: Updating 2 Tables Side By Side

Apr 4, 2013

sqlplus experts,

How do I update Table2(idno) with values of Table1(idno)? By the way there are other columns which i do not want to be touched. This is just to create a match parent-child table test data.

Example:

Table1

idno
===
100
101
102
103

Table2

idno
===
any no
any no
any no
any no

I want all idno values in Table2 to be identical with Table1. It can be any order as long as the value is 1 to 1. I just want it populated for test data purposes.

View 28 Replies View Related

How To Change Decimal Symbol And Digit Grouping Symbol

Jun 19, 2011

Currently , my system using , as digit grouping symbol and . as decimal symbol in Oracle forms and report. Is there any way to reverse that to the whole system?

View 3 Replies View Related

PL/SQL :: Translate Function Remove Alpha Characters From String

Feb 7, 2013

I am on 11g.

I need to remove the alpha characters from a string, leaving only numbers, but I am getting unexpected results:

SQL> SELECT TRANSLATE('3N', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', NULL) a FROM DUAL;
A
-

I thought this would leave the 3 from the 3N, but it is returning an empty string. For my application, the string '3N' could be any length, will only contain letters and numbers, and the letters will always come at the end, but there could be more than one letter

VALID INPUT samples:
4
25
11F
361NG
8ABC

View 6 Replies View Related

SQL & PL/SQL :: TRIM To A Specified Length?

Aug 9, 2013

I have a field "Email". The length of it is restricted to 30. But i mayget more than 30 characters. So how to trim the email address so that its max length is 30 characters.

View 5 Replies View Related

SQL & PL/SQL :: How To Trim A Textfile

Aug 20, 2013

read the data and write into the textfile. I have a target text file with one column in varchar2(8000) length size. Whenever i write into this textfile, after the first row entered, for example, the first row string is 'H2001', then it will automatically spacing 7995 spaces. What I want is, when i enter the second row, it will auto write into the file in next row without all the spaces in row 1. I had been tried using trim, rpad, substr. and still don't get the thing that i want.

View 14 Replies View Related

SQL & PL/SQL :: Trim Column Data

Mar 11, 2010

Need to do this

Column1
--------
5648_6844_20020201
6878_6845_20051201
9845_6548_20080307

Need to change it to this

Column1
--------
20020201
20051201
20080307

So I basically need to remove the leading part of the string using the "_" underscore as the delimiter.

So I thought this would work, but no luck.

SELECT LTRIM('_', Column1) "NewCOL" From table;

View 9 Replies View Related

Fine Tune - Trim Down SQL To Within 255 Characters?

Feb 1, 2013

trim down the following sql to within 255 characters help:
select indate
from (
select case count(inputDate)
when 1 then inputdate
end as indate
from commLeaseBut5

[code]...

This sql is check a date field in the database for record which, if the date field is blank it should be a new record. Then the sql will assigned the current timestamp and stored to the new record. Otherwise, the sql will return the record timestamp for display.

View 7 Replies View Related

SQL & PL/SQL :: Comparing 2 Char Columns Using Trim?

Aug 18, 2011

I am trying to compare 2 char columns using trim to avoid space padding

CREATE TABLE TRIAL_A
(ABC CHAR(6));
INSERT ALL

[Code]....

View 11 Replies View Related

Server Utilities :: SQL Loader TRIM In WHEN Clause?

Apr 9, 2012

My control file is :

LOAD DATA
APPEND
INTO TABLE IPGITLREDATA WHEN ITL_REC_TYPE = 'D'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(

[code].....

The data file might have a value of " D " instead of "D" for ITL_REC_TYPE and ITL_REC_TYPE is in the WHEN clause. How can I check for the trimmed value of ITL_REC_TYPE in the WHEN clause ?

View 5 Replies View Related

SQL & PL/SQL :: Use A Trim (col_name) To Check NULL Value In A Table?

Aug 8, 2013

Can i use a trim(col_name) to check a NULL value in a table? as IS NULL function is taking a long time.

View 8 Replies View Related

Server Utilities :: Trim Date Characters Within SQL Loader

Sep 9, 2010

I am attempting to insert date data into a column using sqlldr...Here's the current format:

2010-03-01 00:20:19.277

So far, I haven't gotten anything to work. I would like to trim the .277 from the existing date. Here's my latest attempt:

birthdate DATE "to_date(substr(birthdate,1,19),'YYYY-MM-DD HH24:MI:SS')"

View 6 Replies View Related

Where Server Side Procedures Are Stored

Jun 2, 2009

Where are the server side procedures are stored? Where are the server side procedures are visible??

View 5 Replies View Related

Upgrade Server-Side - Oracle DB JVM?

Sep 28, 2012

how to upgrade Oracle SERVER jvm version

View 2 Replies View Related

RAC & Failsafe :: Load Balancing At Server Side

Oct 28, 2010

I've configure TAF and Load Banalcing, both of server side some days ago. And now, I find Node 2 is accessed more than node 1. I upload some images here, anything you need to clarify information more, I can give.

Node 2 is often at 40%-55%, some time to 77%.

Node 1 is often 5-12%

Service TAF was configured

Show TAF service by crs

The remote listener

Were I wrong?

View 10 Replies View Related

Forms :: Run Adobe Acrobat In Client Side Using 10g

May 10, 2010

How i can run adobe acrobat in the client side and maximize it ?

Note: i am using oracle 10g.

View 8 Replies View Related

Security :: Query Regarding Client Side Wallet

Jul 11, 2012

I need few clarifications regarding oracle wallet.

db version: 11.2.0.3.2 (Enterprise Edition)

We have a requirement to run shell scripts calling stored procedures for specific activities, which are run on database server. We do not want to store passwords in shell scripts and decided to use Secure External Password Store for hiding passwords instead of os authentication method. need few clarifications on the below.

1) Currently, we are creating oracle wallet entry on db server and making modifications in sqlnet.ora file accordingly. Is it good to use like this or we should do this only on a client machine?
2) Do we need any licensing to use this option?
3) Any knows issues with using oracle wallet?
4) Can we use orapki for creating oracle wallet instead of mkstore?
5) Any knows issues we face during startup and shutdown of db activities?

View 1 Replies View Related

Monitoring Script From Client Side (no Access To Server)

Sep 12, 2012

We are on oracle 11.2.0.2 on Solaris 10. I dont have acces to the db server but connect to the db from the client side using sqlplus. though I have dba privilege (at oracle level) but no access to db server at os level. I also dont have access to enterprise manager console where such information is available.

I want to set up monitoring so that I can get a mail when the space falls below some threshold like say tablespace is 90% full. Is it possible to get mail from pl/sql script for example?

View 1 Replies View Related

Oracle 10g On Linux System - ORA 12560 Error On Client Side?

Jan 2, 2011

i have installed oracle 10g on linux system. and i am using xp as a client when i use this

cmd>conn scott/tiger@database at client side i receive ora-12560 error

while my database is up and listener and tnsping also working at server side.

View 3 Replies View Related

Networking And Gateways :: TNS 12537 - Connection Closed On Client Side

Jan 12, 2011

I am getting below error while tnsping from client

C:Program FilesMicrosoft Visual Studio 9.0VC>tnsping xpcdev

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-JAN-2011 21:35:03

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.102)(PORT = 1524))) (C
xpcdev)))
TNS-12537: TNS:connection closed

on server side listener is up also I am able to tnsping without any error.

contents from sqlnet.ora

Quote:NAMES.DIRECTORY_PATH=(TNSNAMES, ONAMES, HOSTNAME)
SQLNET.EXPIRE_TIME= 10i

I am getting below error on listener log file

Quote:12-JAN-2011 10:04:56 * 12546
TNS-12546: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00516: Permission denied

View 9 Replies View Related

Server Administration :: Ora / 1043 User Side Memory Corruption

Nov 20, 2002

I'm using Oracle8i, with VB6 as front end When i try to connect Oracle using RDO in VB i get an error message from Oracle It is S1000:Oracle ODBC.ora Ora:1043 User Side Memory Corruption.

View 11 Replies View Related

Application Express :: Shuttle / How To Reference Selected Items On The Right Side

Sep 13, 2012

I have a simple shuttle:

1 Adam
2 Baker
3 Charlie
4 Delta
5 Echo
6 Foxtrot

Select Name D, UserID R from UserTable

The values are stored in a VARCHAR2 field MULTI_USER (for example 1:2:4:6) When I want to get the names back from the selected item, how do I reference the colon-separated items?

For example:

Select Name from UserTable
where UserID in (select MULTI_USER from MULTI_USER where MULTI_USER_ID = 1)

should return:

Adam
Baker
Delta
Foxtrot

View 2 Replies View Related







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