SQL & PL/SQL :: Regexp_substr Single Step Possible?

Oct 27, 2011

I'm trying to get this down from two regexp to a single one, but I'm not sure it can be done and if so, its beyond me to work out how atm.

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Solaris: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
[code]...

The expression itself contains all the rules I'm aware of (nothing to say they won't move the goalposts again mind!) of and the desired output is as shown above. I should add that the actual data is more complex (many tags per record) so it rules out a regexp_replace

View 5 Replies


ADVERTISEMENT

Workflow :: How To Revert Back Or Rollback A Process To Its Initial Step

Oct 15, 2012

I have a standard workflow process which was started but got completed without performing all the process.The process had to generate two(Comments & Approval) notifications but generated only one & as the user responded to the notification it got completed without invoking the approval process.Now I need the workflow to be rolled back to the initial step to restart the whole process again.

View 1 Replies View Related

SQL & PL/SQL :: REGEXP_SUBSTR Used In HTML

Sep 28, 2010

I got a record in the column:

<li class='tbl-b'>Ram card: Yes, typ: MicroSD, max. 16 GB </li>

And I would like to extract just:

Ram card: Yes, typ: MicroSD, max. 16 GB

Without <li class='tbl-b'> and </li>.

I try to do this by REGEXP_SUBSTR but not successfully.

View 8 Replies View Related

SQL & PL/SQL :: Select NULL Using REGEXP_SUBSTR?

Feb 2, 2011

My Database is in Oracle 11g.

My query returns the 3rd field from a CSV string. If the third field in the string is empty I want the select to return a null but it returns the 4th field :

SELECT REGEXP_SUBSTR( 'A,B,,D,E','[^,]+',1,3) from dual;

.. this returns 'D'.

Can we somehow make the REGEX_SUBSTR return a NULL for the third field ?

View 10 Replies View Related

SQL & PL/SQL :: Separate String Using REGEXP_SUBSTR

Jan 25, 2011

I can separate numbers from string (info) and the first value of the string using REGEXP_SUBSTR (see below):

with dat as (select '35263304 Alcatel One Touch 806' info from dual)
select info, REGEXP_SUBSTR ( info, '[[:digit:]]+',1 ) tac,
REGEXP_SUBSTR ( info, '[[:alpha:]]+',1) brand
from dat

But how can I get rest of the values from that string (red color) ?

I just would like to get separately like:
35263304 Alcatel One Touch 806

View 5 Replies View Related

SQL & PL/SQL :: Regexp_SUBSTR On Array Returning No Value

Oct 1, 2013

I'm trying to query an array where we will have mathnames that have the follow format: variablemathname[00000]

have been digging around for a few days to try and simplify my existing query. As of right now i'm hacking it together to bring back the bracketed array value by using INSTR and SUBSTR. This works and gets me the correct results but I want to clean the code up by using regexp_SUBSTR.

In my reading up on regular expression I've tried to create my pattern by using [.] which I believe to be [any character]. I want it to start at the beginning of the string so I've used [^ and I only want the one occurrence so I've ended my expression with ]

I tried using the escape before my pattern as I know that [ is a metacharacter but I receive the same results.i'm trying to use to get the expression correct.

SELECT REGEXP_SUBSTR('variablemathname[00000]', '[.],[^,],') RESULT
FROM DUAL;

My expectation is it will bring back the following [00000] but the way it is written now is bringing back nothing.

View 10 Replies View Related

SQL & PL/SQL :: REGEXP_SUBSTR / Executing Prepare Statement

Jan 26, 2011

I am using embedded sql in a Fortran program on IBM AIX. Whenever I execute a SQL statement other than connect I get a coredump. So I wrote a small test program, but same thing happens. This is the test program -

PROGRAM TEST
EXEC BEGIN DECLARE SECTION
CHARACTER*20 UDPWD
EXEC END DECLARE SECTION
[code]....

And then when I run test, I see the 'Connected' print & then coredump. Same thing happens for EXEC SQL Prepare statement. Am I not linking some library ?

View 22 Replies View Related

PL/SQL :: How To Search One String With Various Occurrences Using Regexp_substr

Aug 14, 2012

My Scenario is'....456re0,50kg400,500rfabs43qre30,25kg150,354rf658....'

there is possible,using regexp_substr or other way to get the values, 0,50 and 400,500 and 30,25 and 150,354? I'm using [^re]+[$kg] and the string comes, but only the first occurence..

View 5 Replies View Related

SQL & PL/SQL :: How To Pass Output Of Regexp_substr As Number Input To A Cursor

Sep 13, 2012

I am using regexp_substr to break the pipe delimited string. Want to use the output as the NUMBER input to a cursor.Its not working and not getting any error also.

Here is an example.

initial input : '5545|4124|12456'
using the sql below to break the string into columns.
select regexp_substr('5545|4124|12456','[^|]+', 1, level)) from dual
connect by regexp_substr(, '[^|]+', 1, level) is not null;

But when i try to pass the output of the above query to a cursor with input defined as number, its not accepting. I tried using to_number for the output of above query.

View 12 Replies View Related

PL/SQL :: How To Replace 2 Single Quotes To Single Quote

Nov 21, 2012

I have an script.sql that receives as a parameter an string.

example:

@C:/myscript.sql "o'connor"

user_account_value varchar2(120) := '&1';

EXECUTE IMMEDIATE "Select * from Table where column = :1 "  USING user_account_value I am not sure how to deal with string that contains single quotes.

If the parameter were passed as : "o''connor" this will work
If the parameter is pass as: "o'connor" this will not work.

so my question is what options do I have to deal with dynamic queries and single quotes.

I tried replacing replace(myParameter,'''',''''''); but not working well.

View 11 Replies View Related

Single Instance Non-Grid To Single Instance Grid Database

Aug 27, 2012

I'm trying to test moving a single instance 11202 database to single instance w/ grid infra.

Here is what I've done:

1. Install a database (11202), single instance and create a database
2. Install Grid Software only (user: grid)
3. start the cluster
4. "srvctl add database -d orclsidb -o $ORACLE_HOME" to register the database with grid.
4.1> I was able to start/stop the database with srvctl command here onwards
5. configure disks using asmlib and start the asm
6. "srvctl add asm" to register asm with grid.
7. PROBLEM ... when I try to "backup as copy database format '+ASM_DATA_DG';" from oracle user it errors out as below:

RMAN> backup as copy database format '+ASM_DATA_DG';

Starting backup at 24-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=/fs0/oracle/oradata/orclsidb/sjc883p_indx_large_01.dbf
RMAN-00571:

===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/24/2012 08:53:29
ORA-19504: failed to create file "+ASM_DATA_DG"
ORA-12547: TNS:lost contact
ORA-15001: diskgroup "ASM_DATA_DG" does not exist or is not mounted
ORA-15055: unable to connect to ASM instance
ORA-12547: TNS:lost contactConsidering "TNS: lost contact" I tried to see if grid listener is aware of ASM instance:

[Code]....

All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=usracdb03.rwcats.com)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                22-AUG-2012 06:08:53
Uptime                    2 days 2 hr. 50 min. 48 sec
Trace Level               off

[Code]....

View 10 Replies View Related

SQL & PL/SQL :: All Rows Value To Single Row

Jul 15, 2011

i want to all rows value into single rows.

for example;

Input data:
Sec_SSC_ID Column_nameAs of dateOld valu New Value
IBM Mat_dt 10/10/20101/1/2001 1/1/2002
IBM Bid Market 10/10/201075 85
IBM asset_nm 1/1/2011International IBM
MSFT asset_nm 1/2/2011Microsoft Intel
MSFT Bid Market price 1/1/201189 90

Output data

I have attached sample output file with it.

how to get this via sql?

View 8 Replies View Related

SQL & PL/SQL :: Getting All Data In A Single Row?

Jun 16, 2013

The query I do paste executes and gives the data needed. However, I do need all of these data in a one row.

SELECT
DECODE(BREINV1.NAMEKEY, NULL,'0','1') "BRE_INV1",
DECODE(BREINV1.NAMEKEY, NULL,' ',BREINV1.SEQUENCE) "BRE_NUMINV1",
DECODE(BREINV1.NAMEKEY, NULL, ' ', DECODE(BREINV1.SEQUENCE,NULL,NULL,RTRIM(ADDINV1.FORMATTEDADDRESS,CHR(0))||CHR(13) || CHR(10) ||'Citizen of ' ||SUBSTR(PAY1.COUNTRYDESCRIPTION,1,30))) "BRE_NOMINV1",

[code]....

What I get after executing it is a data with 4 columns and a number of rows unknown.

What I do want is to get all of these data in a one row with columns name sequence increasing 1 by 1.

Example(BRE_INV2, BRE_NUMINV2, BRE_PRENOMINV2 ...)

View 8 Replies View Related

SQL & PL/SQL :: From Columns To A Single Row

Sep 22, 2011

I need to make the following transposition:

from this:

CUST_IDPLAN_IDNOREM_M4M ADD_M4M

6871 1231Yes Yes
68711232Yes NULL

into this:

CUST_ID PLAN_ID REM_M4M1 REM_M4M2 ADD_M4M1 ADD_M4M2

6871 123 Yes Yes Yes NULL

View 18 Replies View Related

Single Job For Whole Assembly

Aug 14, 2012

Business should be able to issue a job whereby the sub assemblies do not have a different job number, however the job itself maintains the indentation (BOM levels). What we are looking to do is to issue one job for a full build of a product, which would be built in one go anyway.

View 4 Replies View Related

PL/SQL :: Returning A Single Row

Jun 5, 2013

I have a table which lists previous jobs, whcih has monthto,yearto, monthfrom and year from fields. I am trying to find the most recent job (of which they might be multiple ending on the same month) Initially, i did the following:

ran a sub query to find the most recent (employee id, max (yearto * 12 + monthto), then another query where I got a list of the most recent jobs, and returned the first one using rownum = 1 (note this may be inconsistent between runs as thee rownum is calculcated before nay ordering clause. This worked, but i figured using the first function might be better as it gives an order

select employee_id from
(select employee_id
,max(NVL(yearto,0) *12 + NVL(monthto,1)) KEEP (DENSE_RANK FIRST ORDER BY emloyer) as latest
from employees
group by employee_id;

but this seems to return more than one entrty:

table date below:
drop table employee_list;
drop table employee_historic;
[code]...

What is the best way to return a single row, if the primary key is not available / applicable.

View 9 Replies View Related

Single Row Subquery Returns More Than One Row

Dec 6, 2012

I am returning the ORA-01427 after running the query below. why I am returning the error and how to address it.

select b.value , b.name, p.value ......
(case when p.value <> 'G2' then null else (select c.oldvalue from ad_changelog c
where c.record_id = b.c_bpartner_id and c.ad_table_id = 291
and c.ad_column_id = 4216 ) end) as oldtradeName
from c_bpartner b, zz_receipt r, zz_recp_alloc a, m_product p, ad_user us
where a.c_bpartner_id = b.c_bpartner_id
and a.zz_receipt_id = r.zz_receipt_id
and us.ad_user_id = r.createdby
and p.m_product_id = a.m_product_id

View 2 Replies View Related

Single-row Subquery Returns More Than One Row?

Dec 7, 2009

Just trying to update a table in which the sales amount is inserted when the sales amount is null.

I have

UPDATE ph2_customer_temp
SET sales_amount = (
SELECT sl.sales_amount
FROM PH2_CUSTOMER_TEMP pct
join
sales_limit sl
on substr(pct.site_code,1,2) = sl.state
where pct.credit_limit is null )

View 2 Replies View Related

Single-row Subquery Returns More Than One Row

Jan 10, 2012

I can't seem to wrap my head around this problem I'm having with a query. I need to update all rows in my ps_ntsr_gf_stufile tables with the concatenated values from the ps_classes_tbl table where a.CLASS_NBR = b.CLASS_NBR. I tried to limit it to emplid from the ps_stdnt_enrl table but no luck.

 UPDATE ps_ntsr_gf_stufile a
    SET a.CLASS_NAME = (SELECT CONCAT('SUBJECT', 'CATALOG_NBR')
    FROM PS_CLASS_TBL b
      WHERE a.CLASS_NBR = b.CLASS_NBR
      AND a.STRM = '1118'
      AND a.INSTITUTION = 'NT752')
      WHERE a.EMPLID IN (SELECT distinct EMPLID FROM PS_STDNT_ENRL); 

I'm still getting the ORA-01427 error.

View 1 Replies View Related

11gR2 SE And EE On Single Server?

Mar 24, 2011

I have one Linux machine on which first I did with Oracle 11gR2 Enterprise edition (EE) , it was working fine.

now today I installed oracle11gR2 Standard edition in different partition which is having separate oracle_home from EE.

when I checked the listener services ,port and all , I came to know that the new installation(SE) it will automatically pickup different listener name(listener_SE SID name) and non-standard port(1523).

I did with oracle10gR2 SE and EE both on the same machine with different oracle_home but all together using only one listener (LISTENER) and one port - 1521.

View 3 Replies View Related

Single To Multiple Records

Sep 18, 2013

I have a table as col_1 number,col_2 varchar2(10)

col_1 col_2
1 abcdefghijkl

what i want is i've to split

col_1 col_2
1 abcdefghij
1 kl

View 4 Replies View Related

SQL & PL/SQL :: Display A Value In Between Single Quotes?

Nov 18, 2011

how to write a select query to display a number column value in between single quotes. Ex:i have a emp table in that sal column is a number type.

Ex:
select sal from emp;
sal
----
1234
231
3456
3211
23445I want to display the same above result in b/w single quotes.

ex:
sal
----
'1234'
'231'
'3456'
'3211'
'23445'
for this i need to write a query,

View 5 Replies View Related

SQL & PL/SQL :: Single Row Subquery Returns More Than One Row

Mar 20, 2012

/* (SELECT A.TOTAL_ALLOCATED_AMT FROM CLS_ALLOCATION_HDR@LAMS_PROD A WHERE A.APPROVAL_DATE BETWEEN BCTH.TRAN_FROM_DATE AND BCTH.TRAN_TO_DATE
AND A.CUSTOMER_ID=BCTH.CUSTOMER_ID AND ALLOCATION_ID IN (SELECT ALLOCATION_ID FROM CLS_ALLOCATION_DTL@LAMS_PROD B WHERE ENTITY_TYPE='REC' AND
B.ALLOCATION_ID=A.ALLOCATION_ID AND ENTITY_ID IN (SELECT RECEIPT_NO FROM CLS_RECEIPT_DTL@LAMS_PROD C WHERE B.ENTITY_ID=C.RECEIPT_NO AND
RECEIPT_STATUS='A'))AND ALLOCATION_STATUS='A')*/

when i am trying to run this query i am getting an error'single row subquery returns more than one row'.

View 2 Replies View Related

SQL & PL/SQL :: Single-row Subquery Returns More Than One Row

Jul 30, 2010

My problem : I Wrote a package(a function), this function just return a varchar2 , but in this function I wrote a cursor , when I call this function in this manner :

SELECT secu.u_menu_id,
secu.menu_id,
menu.prompt,
secu.u_type,
secu.u_id,
[code]....

SQL return : Ora-01427 Single-row subquery returns more than one row

Yes , in this function it has cursor that more than one row. But I let this function return varchar2 ,

View 13 Replies View Related

SQL & PL/SQL :: Single Row Query Returns More Than One Row?

Oct 3, 2010

I am getting an error "Single row query returns more than one row" in an Exception block.

But in subqueries I am using IN operator not using =.

And I don't have duplicates rows in tables.

View 16 Replies View Related

SQL & PL/SQL :: Single Row Subquery Returns More Than One Row?

Apr 28, 2012

I have written the below sql select loc,(select ename from emp where emp.deptno = dept.deptno) from dept

It results in the below error.

[Error] Execution (1: 13): ORA-01427: single-row subquery returns more than one row

I have modified the SQL and got the required output
select em.ename,dep.loc
from
(select loc,deptno from dept) dep ,
(select ename,deptno from emp) em
where dep.deptno=em.deptno(+)

I have written the below sql to fetch all loc for emp which got executed

SELECT ENAME, (SELECT LOC FROM DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO) LOC
FROM EMP

But as i need all locations irrespective of any emp in the locaton so i tried to put emp side (+) which resulted in error.

View 3 Replies View Related

SQL & PL/SQL :: Getting Total And Single Name In One Query?

Oct 29, 2013

I want to get any employee name of deptno 10 but total count of number of employees under dept 10.

DECLARE
l_deptno NUMBER:=10;
l_count NUMBER;
l_ename varchar2(20);
BEGIN
SELECT count(*) OVER(order by empno) ,ename INTO l_count,l_ename FROM emp WHERE ROWNUM=1 and deptno=l_deptno;
dbms_output.put_line(l_count||' '||l_ename);
end;

View 7 Replies View Related

JDeveloper, Java & XML :: How To Get Single Tag Out Of XML

Jul 14, 2011

We have the following XML info.

<connection value="0">
<GroupDetails>
<info groupId="100" classType="0"/>
<info groupId="128" classType="1"/>
<info groupId="179" classType="0"/>
<info groupId="198" classType="0"/>
</GroupDetails>
</connection>

We need to get the single node with class type ="1" from this XML as given below.At any point of time, there will be only one such tag with classType="1". Its position could be anywhere inside the "GroupDetails" Tag.ie, The query should get the tag irrespective of its position inside "GroupDetails".

<info groupId="128" classType="1"/>

Is there any way using EXTRACT function with 3 parameter? I tried but with failure.

with rec AS (
select XMLTYPE(
'<connection value="0">
<GroupDetails>
<info groupId="100" classType="0"/>
<info groupId="128" classType="1"/>

[code].......

View 10 Replies View Related

SQL & PL/SQL :: Displaying A Single Row Into Column?

Mar 26, 2013

I just want to see single rows data as column.

select * from emp where rownum=1;
EMPNOENAMEJOBMGRHIREDATE SAL COMMDEPTNO
7369SMITHCLERK790217-DEC-80 00-00-00 800 20

How can it show as

column_name column_value
EMPNO 7369
ENAME SMITH
JOB CLERK and so on...

View 13 Replies View Related

SQL & PL/SQL :: New Values To Come In A Single Row Of Table

Oct 12, 2010

Below is my trigger:

CREATE OR REPLACE
TRIGGER AR_BANDS_TEST
BEFORE INSERT OR DELETE OR UPDATE ON FEE_BANDS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
[code]...

I want the old and new values to come in a single row of the table while inserting in trigger like below:

OLD_FEE_RU OLD_FEE_BANDS_ID OLD_RATE_NUMBER OLD_RATE_PERCENT NEW_FEE_RU NEW_FEE_BANDS_ID NEW_RATE_NU
---------- ---------------- --------------- ---------------- ---------- ---------------- -----------
TESTALL 1 0 1 TESTALL 1 0

View 7 Replies View Related







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