SQL & PL/SQL :: REGEXP_INSTR Using OCCURANCE Parameter

Sep 19, 2012

I'm not able to understand OCCURANCE parameter for REGEXP_INSTR.As per my understanding it should see the occurance of given pattern in source string.Eg.

'[o][[:alpha:]]{3}'Above pattern matches character 'o' followed by any 3 alphabetic characters: case insensitive.
SELECT REGEXP_INSTR('500 Oracle Pkwy, pneumococcal Shores, CA herohonda lionab', '[o][[:alpha:]]{3}', 1, 1,0,'i') RESULT
FROM DUAL ;

It is giving me 5 as result. Which is correct as search is started from first character of the source string and found first occurance "500 O".

SELECT REGEXP_INSTR('500 Oracle Pkwy, pneumococcal Shores, CA herohonda lionab', '[o][[:alpha:]]{3}', 1, 2,0,'i') RESULT
FROM DUAL ;

It is giving me 23 as result. Which is correct as search is started from first character of the source string and found second occurance "500 Oracle Pkwy, pneumo"

SELECT REGEXP_INSTR('500 Oracle Pkwy, pneumococcal Shores, CA herohonda lionab', '[o][[:alpha:]]{3}', 1, 3,0,'i') RESULT
FROM DUAL ;

It is giving me 33 as result although I'm expecting 25. Because the next occurance is "'500 Oracle Pkwy, pneumoco", and there are alteast 3 alphabets after it. understand "How OCCURANCE parameter works?".

View 11 Replies


ADVERTISEMENT

SQL & PL/SQL :: Using A Cursor Record In REGEXP_INSTR

Oct 18, 2012

I've got a task to star out account numbers in a free text field. The account numbers can be in a few different formats and there are a number of tables. The formats could increase or change as could the tables. For that purpose I want to use a couple of tables; one to store the formats and one to store the tablenames. The tablenames work fine, but I'm having problem with the formats.

The table definition for the formats is:-

CREATE TABLE account_format (acc_id number(2),acc_format VARCHAR2 (200), acc_replace VARCHAR2 (200),acc_process varchar2(1))
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

and one insert would be

INSERT INTO account_format
VALUES (1,
'''[0-9]{5}[A-Z]{1}-[0-9]{5}''',
'''[0-9]{5}[A-Z]{1}-[0-9]{5}'',''****-****-****-****''',
'Y');

The idea is to bring back the format and use it in REGEXP_INSTR (or a similar REGEXP) to ascertain if the field contains the account number. The code I have been using looks a bit like this:-

CURSOR m_format_cur IS
SELECT acc.acc_format
FROM account_format acc;
m_format_rec m_format_cur%ROWTYPE;
IF REGEXP_INSTR (m_narrative, m_format_rec.acc_format) > 0 THEN
....
END IF;

I have tried several different ways to get this to work e.g. hardcoding the format and declaring a variable with the format, which both work. Whenever I try to use the value retrieved from the database field it never works. Why should a variable from a cursor not work?

View 8 Replies View Related

PL/SQL :: Using Regexp_instr In A Where Clause - Invalid Relational Operator

Oct 23, 2013

Whey I try to run this query in TOAD I get an ORA-00920: invalid relational operator error. It's part of a 10g stored procedure. When I highlight it and run it it prompts me for the missing values and then the error pops up. The AND in line 4 is highlighted. 

select CRIME_CLASSIFICATION_ID, crime_type, nvl(count(CRIME_CLASSIFICATION_ID),0) as CRIMECNTFrom vaps.vw_offenses where  regexp_instr(valoc,to_char(location_id)) AND ( fromdate is null or offense_date between to_date(fromdate, 'mm/dd/yyyy')  AND to_date(todate,'mm/dd/yyyy'))group by crime_classification_id, crime_type

View 3 Replies View Related

The Value (167) Of MAXTRANS Parameter Ignored?

Jun 7, 2012

I am gettting the below mentioned error in my alert log file

The value (167) of MAXTRANS parameter ignored.

I have search this error on google, but i didnt find anything every post is telling that this is the impdp's or expdp's error.

But i havent use impdp and expdp here.

View 4 Replies View Related

Parameter Before It Has Been Changed?

May 17, 2010

let's say a parameter changed in the database ex: alter system set retention_target= 1500; and i want to know what was the old value before it has been changed ,

View 1 Replies View Related

SQL & PL/SQL :: Column Name As Parameter

Feb 9, 2012

From ETL tool i will be passing two parameters to a procedure. I want to know whether this parameter can be used in a select statement of a correlated query.

Query to be in the procedure :

begin
update table1 set table1.col1 = (select parameter2 from
table2 where table2.x = table1.x and table2.x = parameter1) ;
commit;
end;

parameter2 will be having the columnname to be passed. And everytime different columnname will be passed to this procedure.

View 16 Replies View Related

SQL & PL/SQL :: Procedure In Put Parameter

Sep 5, 2012

I am writing a procedure in which I have a input string parameter in the following way..('NYC,ATL,OKC,KAC,LA'). I need to use that string to search values in the table.

Example

create or replace procedure search_city(p_string varchar2)
/*
paramater will be like ('NYC,ATL,OKC,KAC,LA')
*/
is
v_city varchar2(40)
begin
for i in (select city_name from cities
where city_code in (p_string )
) loop

v_city := i.city_name;
end loop;
end ;

View 2 Replies View Related

SQL & PL/SQL :: Function Without Parameter?

Aug 5, 2010

if function having retrun statement, why we need to use out or inout parameters.

View 16 Replies View Related

SQL & PL/SQL :: Ref Cursor As OUT Parameter

Aug 8, 2012

CREATE OR REPLACE PACKAGE test_package IS
TYPE refcur IS REF CURSOR;
END test_package;
CREATE OR REPLACE PROCEDURE get_info(o_cursor OUT test_package.refcur)
AS
BEGIN
OPEN o_cursor FOR
SELECT * FROM emp;
END get_info;

What is the advantage of using refcursor variable as OUT parameter in procedure. Instead of that why cannot we use variables or TYPE variables. use ref cursor as OUT parameter in procedure.

View 1 Replies View Related

SQL & PL/SQL :: Test Parameter?

Jan 12, 2012

I need to test a parameter in a script that must adhere to what is currently in the database.For example, when a user enters the value when prompted from this script, ("Enter the schema copy desired: " ) the value must be of the current values in the db:, it would be something like this:

Values currently in DB: copyofschema201110 (the 2011 is the year, and 10 would be the month of oct).

copyofschema201109
copyofschema201108

So, if the user enters the value 'abc' (an incorrect value), then I would like the script to stop the user, and kick back a message, say from dbms_output.put_line, and force the user not to go forward, but re-enter a correct value.

View 13 Replies View Related

PL/SQL :: OUT Parameter Functionality

Mar 25, 2013

I thought that you are not supposed to be able to assign the value of an out parameter to a variable (Feuerstein, 5th ed) and yet I was just
able to do this without any compilation error messages. Is Feuerstein wrong or am I missing something? Has the functionality changed?

PROCEDURE Parse_HC
          (p_timestamp_string IN VARCHAR2,
          p_timestamp_date OUT DATE)
AS
v_date DATE;

[Code]....

Here is my banner:

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 Linux: Version 11.1.0.7.0 – Production
NLSRTL Version 11.1.0.7.0 - Production

View 8 Replies View Related

PL/SQL :: How To Assign Value To Parameter

Mar 20, 2013

I have written this code, its throwing error.

begin
declare tmdate DATE;
select TO_DATE(TO_CHAR(MAX(DATERANGE),'DD-MON-YYYY')) into tmdate from table_check_date_range;
dbms_output.put_line(tmdate);
end;

View 10 Replies View Related

OBJECT_CONSISTENT Parameter Is Set To Y During Export?

Aug 29, 2012

What will happen when the OBJECT_CONSISTENT parameter is set to "Y" during export? When i have to set it as "Y"?

View 6 Replies View Related

Passing Parameter To In Statement?

Oct 9, 2008

I have a scenario where I have to pass a paramter to "in statement". When I run the query from SQL plus its working fine.......but when I run the query at run time it doesnot come back with the results. I am sure its formating issue.

This is how I create a paramter

_sbInStatement.Append("(");
foreach (ListItem item in _listBox.Items)
{

[Code]....

oCmd.Parameters.Add(":InStatement", OracleDbType.Varchar2).Value = InStatement;

View 2 Replies View Related

How To Output Parameter To App C# Procedure PL/SQL

Sep 17, 2013

this PROCEDURE for Paging.

PROCEDURE cursor_example
IS
p_id NUMBER;
p_status number;
p_rownum number;

[code]...

View 1 Replies View Related

RMAN Compression Parameter

Aug 22, 2011

We are seeing volume issue when taking Rman level 0 backup for a database , the database version is 11.2.0.2 and its on RHEL 2.1. As 11g supports compression for RMAN, we have implemented so as to reduce the backup space used.

" CONFIGURE COMPRESSION ALGORITHM 'LOW' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE "

However during full backup the volume size increases, meaning we have to increase /data volume (currently 500G) to more then a 1T for just rman to go through, else the backup hangs. Once backup is done we again bring down the volume size to less then 1T. The other compression parameters are HIGH and MEDIUM, hoever I am not very sure if changing to high or low will work as I couldn't find any right doc in meta link or may be I didn't searched correctly, I will continue to look for that.

View 2 Replies View Related

SQL & PL/SQL :: How To Pass Parameter In A View

Nov 16, 2012

How to pass parameter in a view.

create or replace view spic_bags as
select distinct(a.item_code) Material_Code,a.uom,
(case when a.card_code = '60' then
case when a.ccn <> '091' then
[code]........

in this above view i have hardcoded dates, well how to pass parameter for this view without hardcoding? here i need to pass the date as a parameter from a select query to view the data from the above view!

View 4 Replies View Related

SQL & PL/SQL :: ARRAY As Parameter In Procedure?

Nov 1, 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 this in one of the packages WCL_LIB:
TYPE vc2_255_arr IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;

and i use it in one of the procedures as
PROCEDURE "WCL_EVENTS"
(p_event_id IN NUMBER,
p_event_arr IN Wcl_Lib.vc2_255_arr,
p_model IN VARCHAR2 DEFAULT NULL,
p_model_code IN VARCHAR2 DEFAULT NULL

but the calling procedure doesnt have any array.... Can I declare something like in the procedure to be called
p_event_arr IN Wcl_Lib.vc2_255_arr DEFAULT NULL,

I tried, but doesnt seem to work? so how to call the procedure, which has a array as mandatory, but calling one doesnt have any?

View 5 Replies View Related

SQL & PL/SQL :: Validating Input Parameter

Aug 11, 2010

I'm using Oracle 9i. How can i check for a string if it contains only numbers or letters? Otherwise i should return false.

View 6 Replies View Related

SQL & PL/SQL :: Procedure Without Parameter Is Ref Cursor

Jan 12, 2012

I have the procedure with out parameter is ref cursor.

l_sql VARCHAR2(32767);
BEGIN
l_sql := 'select query with appending procedure IN aparameters';
OPEN rc_rpt FOR l_sql;

Here procedure IN parameter is a string with comma separated value which is appended in the dynamic query IN clause.So some time the size exceeded more then 32767 and getting error.If i am using normal parametrized cursor this issue is not there,but i want to return only ref cursor for some java purpose.My oracle version is 10g.

View 4 Replies View Related

SQL & PL/SQL :: Different Where Clause Based On In Parameter

Aug 30, 2011

how to use different where caluse based on different in parameter. say I pass 1,2 as parameter if it is 1 then the where condition > 10 else if it is 2 then the where condition <5 the where conditions are placed in cursor for loops.

View 2 Replies View Related

SQL & PL/SQL :: Sub Query Using Outer Parameter?

Apr 10, 2010

How to give table1 column to subquery with table2 :
(select t1.*, sq.*

from table1 t1,
(select a,b,c from table2 where col1= t1.col1) sq
where ...

View 4 Replies View Related

SQL & PL/SQL :: Using Collection Variable As OUT Parameter

Aug 8, 2012

The following procedure stores the retrieved data in o_ref_primary_dept collection variable, suppose I want to display the data from that variable while executing what code I have to write.

CREATE OR REPLACE PROCEDURE sp_ost(
o_ref_primary_dept OUT PRIMARY_DEPT)
IS
l_primary_dept LONG;
[code].....

how to use collection variables as OUT parameters in procedure.

View 1 Replies View Related

SQL & PL/SQL :: Can Have Input Parameter In Procedure

Oct 11, 2010

i am trying to create a procedure that will take an input and based on that input it will delete or update a row, if the input is wrong we will dbs_ouput a line telling the user to enter the input again. can i have a input parameter in a procedure and insert multiple rows with a single insert on a procedure?

View 6 Replies View Related

SQL & PL/SQL :: Passing Required Parameter

Jun 14, 2011

I have a procedure with 20 parameters, acutely it is to update a table and each param represents respective columns in a table. I want to update only few selected columns(random), as of now am passing Null as param values for remaining.

Is there any way to ignore the unnecessary parameters instead of passing NULL value.My Proc call looks like...

Exec MyProce(IN_ID, NULL, NULL, NULL,NULL,NULL,'SOME_VALUE', NULL,NULL,NULL,NULL,NULL,NULL,...);

Or

Exec MyProce(IN_ID, NULL, NULL, NULL,'SOME_VALUE' ,NULL,'SOME_VALUE', NULL,NULL,NULL,NULL,NULL,NULL,...);

View 4 Replies View Related

SQL & PL/SQL :: Pass Date Parameter

Jun 22, 2010

In a procedure i need to pass date parameter, to check how to do this. I create a simple procedure, but it giving

PLS-00049: Bad Bind Variable 'FD'
PLS-00049: Bad Bind Variable 'TD'

How to resolve .......

CREATE PROCEDURE pass_date_parameter
AS
fd VARCHAR2 (10) DEFAULT '01-01-2010';
td VARCHAR2 (10) DEFAULT '01-06-2010';
ffd VARCHAR2 (10) DEFAULT '01-01-2010';
ttd VARCHAR2 (10) DEFAULT '01-06-2010';

[Code]....

View 19 Replies View Related

SQL & PL/SQL :: Output After Passing Parameter

Mar 23, 2012

we have a table which name is empl have two column (comapnyname,jobdate) i want output after passing parameter which name is month which value is like(feb 2011,or mar 2011 or apr 2011 or jun 2011 etc) output should come the nameof company with jobdate whose jobdate is equal to 3 or greate than three

name of company,jobdate whose job date in particular month is greater than three or equal to three the purpose of this we want to find only the name of company where we visit morethan three times in particular month table structure is

create table empl(companyname varchar2(10),jobdate date)

insert into empl values('ABC','1-feb-2011')
insert into empl values('ABC','10-feb-2011')
insert into empl values('ABC','21-feb-2011')
insert into empl values('xyz','18-feb-2011')
insert into empl values('xyz','1-feb-2012')
insert into empl values('xyz','1-apr-2011')
insert into empl values('ABC','28-feb-2012')

output:

after passing parameter month=feb 2011

companyname jobdate
abc 1-feb-2011
abc 10-feb-2011
abc 21-feb-2011

View 3 Replies View Related

SQL & PL/SQL :: Passing Multiple Value In Parameter

Oct 3, 2011

How can I pass multiple value in one parameter

example

select * from table

where table_cd in ('01','02','03','04')

here i want to put multiple value like above query by select 1 value in list

like when user select 'A'
THE VALUE PASS IN WHERE CLAUSE ('01','02','03')
FOR 'B' ('03','045','07')
FOR 'C'('044','046','078')

View 6 Replies View Related

SQL & PL/SQL :: Parameter Passing In Oracle

Dec 13, 2011

i have a SQL query . In the where clause of the query , there is function called dimension_intersect which takes 2 parameters.Now , when the 2 dimensions passed intersect , the function returns "Y" and the query works as expected

Function in where clause is as below

Dimension_intersect(Dimension1,select dimension2 from product where product_sys_id=1)='Y'

The above function works fine till only 1 record is returned by the inner subquery used in above function. But when "select dimension2 from product where product_sys_id=1" return 2 dimensions then the function fails as it can accept only one dimension at a time . I am not allowed to edit this function. I need to find a way to pass both the dimensions one at a time.

Query
-----
SELECT DISTINCT PROD_LONG_NAME,
P.PROD_ID_USER,
MRS.RESTRICTION_SEVERITY,
MRT.RESTRICTION_TYPE,
RESTRICTION_COMMENT RESTRICTION_DETAIL,
[code].......

View 15 Replies View Related

SQL & PL/SQL :: First Tuesday Date Between Two Parameter

Nov 20, 2010

i need to get first Tuesday per each month between two parameter i will use cursor in forms.

for example

my parameter is '04122007' and '31122009'
i need the first tuesday for each month
like '02122008'
'01�12009'
'03�22009' ext....

View 8 Replies View Related







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