PL/SQL :: Extract Values From String To Variables?

Jul 16, 2013

I need to extract values from string to variables as below.

declare
str varchar2(100):='Acknowledgment=1234,Order Requester=5678,Site Contact=9999,Other Contact=1456,Pre=1234,23445,56767';
l_a varchar2(100);
l_or  varchar2(100);
l_s  varchar2(100);
l_ot  varchar2(100);
l_pre  varchar2(100);
Begin
l_a:='1234';
l_or:='5678';
l_s:='9999';
l_ot:='1456';
l_pre:='1234,23445,56767';
end;
/

But here challenge is order of alignment change dynamically. ex as below. 

str varchar2(100):='Order Requester=5678,Acknowledgment=1234,Site Contact=9999,Other Contact=1456,Pre=1234,23445,56767';
str varchar2(100):='Pre=1234,23445,56767,Order Requester=5678,Acknowledgment=1234,Site Contact=9999,Other Contact=1456';

 So how to extract Acknowledgement to l_a,Order requester to l_or ...

View 9 Replies


ADVERTISEMENT

SQL & PL/SQL :: Extract Date From String

Jan 8, 2013

I need to extract the First portion of date entered in remarks column , i have prepared a test as below.

CREATE TABLE LEAVE_DYS (LV_EMP_CODE VARCHAR2(12), LV_DESC VARCHAR2(2000));
INSERT INTO LEAVE_DYS VALUES ('R0754','LEAVE SALARY FOR APRIL 16, 2010 - APRIL 15, 2012 (33 ABSENTS)');
INSERT INTO LEAVE_DYS VALUES ('R0667','LEAVE SALARY FOR JULY 14, 2009 - JULY 13, 2011 (44 ABSENTS)');
INSERT INTO LEAVE_DYS VALUES ('R0841','LEAVE SALARY FOR MAY 29, 2010 - MAY 28, 2012 (NO ABSENT)');
INSERT INTO LEAVE_DYS VALUES ('R0854','LEAVE SALARY FOR MAY 29, 2010 - MAY- 28, 2012 (1 ABSENT)');
[code].....

--Required output is

LV_EMP_CODE LV_DESC
R0754 16/04/2012
R0667 14/07/2012
R0841 29/05/2012
R0854 29/05/2012
R0313 09/09/2012

View 15 Replies View Related

SQL & PL/SQL :: Extract String Between The Braces?

Sep 13, 2011

I have following column data

NZ(abc_mode)/ (NZ(bch_mode) + NZ(cdh_mode)

Now i need query to extract the string inside the braces NZ() with or without using regular expressions in SQL

the output should be

abc_mode
bch_mode
cdh_mode

View 5 Replies View Related

PL/SQL :: Extract Number From String

Oct 21, 2012

I am using this Regexp to extract numbers from a string, and I doubt that there is a more efficient way to get this done:

SELECT  regexp_replace (regexp_replace ( REGEXp_REPLACE ('  !@#$%^&*()_+= '' + 00 SDFKA 324 000 8702 234 |  " ' , '[[:punct:]]',''), '[[:space:]]',''), '[[:alpha:]]','')  FROM dual
{code}

Is there a more efficient way to get this done ?

View 4 Replies View Related

SQL & PL/SQL :: Extract Part Of String And Replace

Feb 13, 2012

I have written one program that inserts one field in table item_master based on existing field,for eg, its like old field is 'HEB240x240x10x17x13000mm, S 275 JR' - and i want to replace the 5 digits before mm i.e 13000 needs to be replaced by 6000 or 4000 based on generated values and the new item will be like 'HEB240x240x10x17x6000mm, S 275 JR' or 'HEB240x240x10x17x4000mm, S 275 JR'.

View 3 Replies View Related

SQL & PL/SQL :: Two Variables - Numeric Or Value Error / Character String Buffer Too Small

Jan 24, 2011

I am learning pl/sql and this is procedure i had created which got created successfully.then i created two variables name v_name, v_loc with command.

variable v_name varchar2; etc.
and executed procedure like
execute dept_proc2(10, :v_name, :v_loc);
and i am getting the error

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

1 create or replace procedure dept_proc2
2 (v_dno in number, v_name out varchar2, v_loc out varchar2)
3 is
4 cursor dept_cur is select dname, loc from dept
5 where deptno=v_dno;
[code]...

View 12 Replies View Related

SQL & PL/SQL :: String Function - Extract Domain From Email Address

Oct 12, 2011

I'm trying to extract the domain from email addresses like from clay500@msn.com I want to pull msn

I figured I would use the POSITION function, but it fails, even when I cut and pasted the example from the syntax guide

SELECT POSITION ('CAT' IN 'CATCH') FROM DUAL;

the guide says you get the answer 1

but I get ORA-00907: missing right parenthesis

What gives?

View 8 Replies View Related

SQL & PL/SQL :: How To Extract First 3 Characters From Each Word In A String / Sentence And Separate With Underscore

Oct 22, 2013

I have some strings like

'Net Amount Payable by an Individual',
'Net Amount Payable by an Individual+Tax',
'Total Amount Payable towards Service',
'Total Amount Payable towards Service.+Tax'

I need to extract the first three letters from each word and separate them using an underscore. The output should be as follows for the above strings -

Net_Amo_Pay_by_an_Ind
Net_Amo_Pay_by_an_Ind_Tax
Tot_Amo_Pay_tow_Ser
Tot_Amo_Pay_tow_Ser_Tax

I request to let me know the way to get the ouput as mentioned.

View 4 Replies View Related

SQL & PL/SQL :: Extract Email ID From A String / Text Using Oracle Regular Expressions

Oct 14, 2013

code to extract email id (full email id) from a string/text using Oracle Regular Expressions.

Example -

"This is my String with email abc.efg@hij.com to test" O/p - abc.efg@hij.com

View 9 Replies View Related

PL/SQL :: Extract Values From A Column

Aug 26, 2013

I need to extract the values and store it in seperate column. write the function to extract the data from column.I am having column DOC_NO from which i have to extract dob, gender, nationality Doc_no:  

9005070288080 DOB : 900507- yyddmm  Gender : 0288- if less than 5000 then female else male Nationality: 080-  0 means India 1 means foreigner. 

View 4 Replies View Related

PL/SQL :: Extract Values Of Strings?

Jun 20, 2012

extract the value of the strings for REQUEST_GROUP_CODE and REQUEST_GROUP_APPL_SHORT_NAME. As you can see I have to deal with whitespace differences, case differences etc. I need the values between the quotes for each one. get ACCOUNTING and AR for the first example. I am using db version 11g r1.

WITH TEST AS
(SELECT 'REQUEST_GROUP_CODE="ACCOUNTING" REQUEST_GROUP_APPL_SHORT_NAME="AR" TITLE="AR:AR_SRS_TITLE_ACC_RPTS"' str FROM dual
UNION ALL
SELECT 'REQUEST_GROUP_CODE = "PRINT_CONSINV"REQUEST_GROUP_APPL_SHORT_NAME = "AR"TITLE =

[code]...

View 5 Replies View Related

Forms :: Assign Multiple Values To Variables?

Mar 31, 2012

assigning values to a particular variable that i need for my button trigger. I Understand that you can assign multiple values to a variable that has a varchar or char data type....is there a way to assign multiple values to a variable that has a 'number' data type?? I need this for my 'where' clause

declare
usergrade varchar(4) := 'pass';
user_unitcode number(6) := ;--needs three unit codes to equal pass

View 1 Replies View Related

SQL & PL/SQL :: How To Replace Variables With Values In Tables And Get The Result

Nov 8, 2012

I have two tables A and B.

A
--
variable value
-------- ------
a 10
b 20

B
--
Exp
---
b-a
b*a
b/a

How can the variables be replaced with values(10,20) using a single query...?

View 2 Replies View Related

Forms :: 10g - Debug And Check Values Of Defined Variables

Sep 22, 2010

I am currently working in form 10g and i m newbie to that technology...I want to debug my form and also check step step by values of variables which i defined.

View 1 Replies View Related

PL/SQL :: Extract Values From Text In Reporting Environment

Jun 26, 2012

I need write a query based on a bunch of user supplied IDs. The IDs will be pasted as plain text, one per row, by end user in a memo field in the reporting environment, and I need to do something like this:

SELECT PHONE_NUMBER FROM TELEPHONE
WHERE
ID IN('MEMO_ID1', 'MEMO_ID2',.....)

Reporting environment does not provide any tools to automatically convert plain text into IDs.

View 9 Replies View Related

SQL & PL/SQL :: Retrieving Multiple Values In Stored Procedure Using Variables And Cursors

Oct 6, 2011

The goal is to create a stored procedure that will retrieve multiple values from a table.

GUI is in Java and they will trigger our procedure to show list of all employees and their roles , doj etc.

So I wrote the following procedure.
---------------------------------
create or replace
PROCEDURE emp_test(
c_cursor OUT SYS_REFCURSOR)
AS
BEGIN
OPEN c_cursor
FOR
SELECT emp_name, emp_doj, emp_role FROM emp_table ;
END;
---------------------------------

I'm using sql developer, stored procedure is compiled and I can manually run it by right clicking on the procedure and click 'Run'.

When I intend to run it by executing the script "Execute Procedure name ", I get errors.

In SQL Developer, I open new SQL file and key in

EXECUTE emp_test;

Highlight it and run the script, here is the list of errors that I get.

-------------------------------------------
Error starting at line 18 in command:
execute frm_lst
Error report:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'emp_test'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:
%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
--------------------------------------------

Issue 2:

Instead of using cursor, is there a way to use multiple parameters and insert the data retrieved from select column_name from table into these parameters and build it in a stored procedure. I tried something like below which did not work.
____________________________________________________

CREATE OR REPLACE PROCEDURE emp_test1
(e_name OUT emp_name%TYPE,
e_dob OUT Edob%TYPE)
IS
BEGIN
SELECT emp_nam, Edob
INTO e_name, e_dob
FROM emp_table
END emp_test1;
End;
______________________________________________________

Just so you know, there is no input parameter or input feed, when called the procedure should return all the values.

View 7 Replies View Related

Client Tools :: Providing Bind Variables As Values In Insert Statement?

Aug 23, 2011

I executed the following PL/SQL block in SqlDeveloper :

VARIABLE max_dept_no NUMBER
DECLARE
v_dept_name VARCHAR2(30) := '&p_dept_name';
v_max NUMBER(4,0);
BEGIN
SELECT MAX(department_id) INTO v_max FROM departments;
:max_dept_no := v_max + 20;
INSERT INTO departments VALUES (:max_dept_no,v_dept_name, NULL,NULL) ;
END;
/

And it gave the error : Quote:Error report:

ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_ID")
ORA-06512: at line 7
01400. 00000 - "cannot insert NULL into (%s)"

The same code when executed in iSqlPlus gave no error.

View 13 Replies View Related

PL/SQL :: Split String Values

Feb 28, 2013

I have a column in a table that contains a string seperated by .

e.g.

IT.HARDWARE
IT.APPS
IT.SOFTWARE

I would like to split the two values out on two columns e.g.

Column1 - IT
Column2 - Hardware
etc.

View 3 Replies View Related

PL/SQL :: Filter Values From String

Oct 13, 2013

I have data something like this:

Sample DateWITH DATA AS          (          SELECT 'AAAXXXX IO BLUEEXPRESS' LIST FROM dual          UNION ALL          SELECT 'BLUEEXPRESS AAAXXXX IO BLUEEXPRESS'  FROM dual          UNION ALL          SELECT 'DDDDD BLUEEXPRESS AAAXXXX'  FROM dual          UNION ALL          SELECT 'DDDDD DDDDD AAAXXXX'  FROM dual          UNION ALL          SELECT 'DDDDD BLUEEXPRESS AAAXXXX NO CARBON'  FROM dual          UNION ALL          SELECT 'NO CARBON
[code]....

The above result depends on the following rules:         

- Replace BLUEEXPRESS into BEXPRESS         
- Remove the term NO CARBON (See row no 6)         
- Reduce all multiple space into single space (see last record).

So far I create separated queries for replacing BLUEEXPRESS into BEXPRESS and replace NOCARBON term but I don't know how to do it in a single shot as well as stuck on scenario to remove multiple spaces  and put single space. 

View 5 Replies View Related

SQL & PL/SQL :: How To Retrieve Unique Values From A String

Feb 5, 2013

Objective: I need to compile a final string by concatinating the unique values from different strings.

Here is the script to create tables and data.

Create table temp_acronyms(id number, acronym varchar2(30);
insert into temp_acronyms values(1, 'ABC');
insert into temp_acronyms values(2, 'DEC//NOFO');
insert into temp_acronyms values(3, 'CBK//FO TO USA');
insert into temp_acronyms values(4, 'DEC//NO ENTRY');
insert into temp_acronyms values(5, 'ABC//NOFO');

COMMIT;

select * from temp_acronyms;

ID ACRONYM
--- --------
1 ABC
2 DEC//NOFO
3 CBK//FO TO USA
4 DEC//NO ENTRY
5 ABC//NOFO

I need to store all the unique strings from the acronyms for id's 1,2, 3, 4 and 5 into a variable. doesn't matter even if it is through database procedure.

my final string should have the values as below

ABC//DEC//NOFO//CBK//FO TO USA//NO ENTRY

View 6 Replies View Related

SQL & PL/SQL :: How To Find Greatest Values In A String

Jul 22, 2011

I am trying to update the greatest value in a column from a string of other column.

Ex: f the value is shown 10M+16M+25M-DG, then populate 25 only

so for that I had written query as follows:

update ANCHOR set IEL_STRAND_SIZE= greatest(
substr
(REPLACE(REPLACE(REGEXP_REPLACE( F_TYP, '[A-Z]', '' ),'+',','),'-',','),
0,
length(REPLACE(REPLACE(REGEXP_REPLACE( F_TYP, '[A-Z]', '' ),'+',','),'-',','))-1))

The output is given as 10,16,25,but not as 25.so how could i write it?Do I need to implement procedure or arrays for it.

View 7 Replies View Related

SQL & PL/SQL :: Getting Distinct Values String Using LISTAGG?

Jun 3, 2013

CREATE TABLE TEST_TAB
(
A NUMBER(5),
B VARCHAR2(20)
) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Manoj' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Arun' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Varun' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Suresh' ) ;

[code].....

Query Output :

1Arun,Arun,Manoj,Manoj,Manoj,Suresh,Varun
2Kamlesh,Manoj,Manoj,Manoj,Suresh,Suresh

Expected Output :

1Arun,Manoj,Suresh,Varun
2Kamlesh,Manoj,Suresh

Expectation here is duplicate values should not be repeated.

View 1 Replies View Related

SQL & PL/SQL :: Create A String With Distinct Values

Aug 11, 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 have a cursor, whose sql is returning seven rows with these values:
9
4
4
9
7
9
4

i open cursor and fetch these values into variable as shown

OPEN id_search FOR l_sql_stmt;
LOOP
FETCH id_search INTO l_eve_id;
if l_eve_id != l_eve_id_prev then
l_eve_id_str := l_eve_id_str || ' , ' ||l_eve_id ;
[code].......

but i want only the distinct values in l_eve_id_str , i.e.,

l_eve_id_str := 9,4,7
What this code is doing is creating a string with all the values
l_eve_id_str := 9,4,4,9,7,9,4

How to remove duplicates from this string?

View 8 Replies View Related

PL/SQL :: String Having Null Values Between Commas

Dec 29, 2012

My DB version is 10.1.0.5.0

I want extract the values from the string using below query but i am unable to bring the correct result.

WITH t  AS ( select '123,1,3,22' col FROM DUAL
        UNION ALL
        SELECT '123,,2,1' FROM DUAL
        UNION ALL
        SELECT '5,1,2,,' FROM DUAL
        )

[Code]...

My required result like below.

123     1      3      22
123            2       1
5       1      2       

get required result using regular expressions.

View 3 Replies View Related

JDeveloper / Java & XML :: Extracting Values From String

Aug 11, 2012

I would like to extract only the values from the XML string. The xml string looks like

<addressId>1</addressId><addressSource xsi:nil="true"/><addressInfoName xsi:nil="true"/><addressLine1>245 Murray Ln SW Bldg 14</addressLine1><addressLine2 xsi:nil="true"/><addressLine3 xsi:nil="true"/><addressLine4 xsi:nil="true"/><phoneNumber xsi:nil="true"/><phoneNumberExt xsi:nil="true"/><city>Washington</city><county xsi:nil="true"/><stateOrProvince>DC</stateOrProvince><ZIPCode>20528-1002</ZIPCode><country>USA</country><congressionalDistrict xsi:nil="true"/></address>

And the expected output is
245 Murray Ln SW Bldg 14
Washington
DC
20528-1002
USA

I need to extract only the values from the XMl string using sql.

View 4 Replies View Related

SQL & PL/SQL :: Pattern Matching And Updating Of Number Values In String?

Nov 5, 2011

I have a table(PSUSEROBJTYPE) with a long field(PTCUSTFORMAT) containing a row value value in the form:

#1|0|0|0|0|#2|1|0|0|1|#3|1|0|0|0|#4|0|0|0|0

Here, I want to update the above field value to a value in the form:

#2|0|0|0|0|#3|1|0|0|1|#4|1|0|0|0|#5|0|0|0|0

This is nothing but finding each occurrence of (#n) in the above string and replacing it by (#n+1). (i.e #1 is replaced by #2,#2 is replaced by #3).

View 4 Replies View Related

Reports & Discoverer :: Between Clause To Compare Two String Values

Jun 15, 2013

I have a problem with Between clause used in where statement to compare two string variable.

Query is like this,

select item_code, item_deacrption
from itm_master, invoce_det
where im_code = item_code
AND invd_item_number BETWEEN (:startNum) AND (:endNum)

Here invd_item_number is a DB field and is of type varchar2(41), and (:startNum),(:endNum) are of same type.

now invd_item_number has one value '001003002001'
if we give :startNum = '001003001002' and :endNum = '001003004006'

:startNum and :endNum is composed of separate field values (ie, 1st 3 character shows color code, next 3 for catagory, next 3 for size etc). These codes are entered separately and are combined at run time.

it is still fetching the invd_item_number with value '001003002001'. (the last set of character(type code) in the :startNum is greater than invd_item_number's type code value. But it is smaller than the previous code (size code), that's why it is fetching).

But how can i get around this as i don't need that value to be fetched.

View 7 Replies View Related

SQL & PL/SQL :: Process A String Of Values That Is Being Passed From Cold Fusion?

May 7, 2010

I'm trying to figure out how to process a string of values that is being passed from Cold Fusion.

procedure test (Names in varchar2) is The Names variable from the Cold Fusion page would have values like Joe1,Joe2,Joe3,Joe4.

I need to loop through the Names variable (Joe1 then Joe2 then Joe3 and so on) and insert each one into a table. how to do that within the procedure?

View 5 Replies View Related

PL/SQL :: Passing String Values To Partition Clause In A Merge Statement?

May 24, 2013

I am using the below code to update specific sub-partition data using oracle merge statements.

I am getting the sub-partition name and passing this as a string to the sub-partition clause.

The Merge statement is failing stating that the specified sub-partition does not exist. But the sub-partition do exists for the table.

We are using Oracle 11gr2 database.

Below is the code which I am using to populate the data.

declare
ln_min_batchkey PLS_INTEGER;
ln_max_batchkey PLS_INTEGER;
lv_partition_name VARCHAR2 (32767);
lv_subpartition_name VARCHAR2 (32767);
begin

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Local Variables Are Bind Variables

Apr 27, 2012

Which of the below is considered a bind variable. In example one proc. Test the parameter p1 is directly used in the query, so this can be considered as a bind variable.

Is that true about the second proc. where p1 is assigned to a local variable v1 , or this needs hard parsing because v1 is not a bind variable ?

Create or replace procedure test(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=p1;
END;
------------
Create or replace procedure test1(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
v1 varchar2(100):=p1;
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=v1;
END;

View 8 Replies View Related







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