PL/SQL :: Send Regexp Back Reference To A Function?

Jul 23, 2013

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE     11.2.0.3.0     Production
TNS for HPUX: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Can I call a function from a regexp back reference? I would like to call SELECT UTL_ENCODE.TEXT_ENCODE (:mytext, 'WE8ISO8859P1', 1) FROM DUAL;
for all text after Password= and before line break WITH t AS (SELECT '[MyDB]
Army=@MyDBarmy
Navy=@MyDBnavy
Airforce=@MyDBairf

[code]...

View 3 Replies


ADVERTISEMENT

Forms :: How To Pass Table Name By Reference To Function

Oct 1, 2011

I have to create a function which count the max id in the provided table.

For example: I have two table customer and book

and

create or replace function Row_Count(tab_nam) return varchar2 is

CONTR_NO varchar2;
begin
select NVL(MAX(t.contract_num), 0) + 1
INTO CONTR_NO
FROM tab_nam t;
return(CONTR_NO);
end Row_Count;

when I call

Function( customer) ;
or
Function( book) ;

It should tell the max number with addition of one.

View 4 Replies View Related

SQL & PL/SQL :: Send Again Function Of Equivalent Of DATEADD

Apr 19, 2011

Is it possible to send again the function of equivalent of DATEADD in sql for plsql??

View 3 Replies View Related

SQL & PL/SQL :: Set / Send / Input CLOB Value To Function?

Jan 19, 2012

Trying to set/send/input CLOB value to function.

I tried:

Clob size is about 4-5KB of data.

DECLARE clobz CLOB:=EMPTY_CLOB();
BEGIN
SELECT '<claim>'+
'<lbo_id>34</lbo_id>'+
'<claim_type_id>11</claim_type_id>'+
'<claim_subtype_id>11</claim_subtype_id>'+

[code]...

Error:
Error report:
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete

[code]...

View 18 Replies View Related

PL/SQL :: Best Way To Send A Procedure / Function Output To The Application

Sep 8, 2013

What is the best way to send the output of a PL / SQL function / procedure to the application in Oracle 11g. I understand that it could be different for applications built in JAVA , .Net , SAP etc. 

View 49 Replies View Related

PL/SQL :: REGEXP-LIKE Condition?

Jul 26, 2012

I would like to use REGEXP_LIKE condition expression with '[:alnum:]' operator in Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 for to check the string is alphanumeric or not.Why do i need: I have xinput field [RAW(24)] which not sure to contains always an alphanumeric string. We should use only when xinput field is an alphanumeric string.

x varchar2(50);
x:=rtrim(ltrim(utl_raw.cast_to_varchar2(xinput)));

how should i use REGEXP_LIKE

select 'TRUE' from dual where REGEXP_LIKE( 'True', '[:alnum:]')
select 'TRUE' from dual where REGEXP_LIKE( 'False%*^tt123.***', '[:alnum:]')

View 4 Replies View Related

PL/SQL :: How To Use OR (|) With REGEXP-SUBSTR

Jan 10, 2013

i would like to know how can i used the OR "|" with REGEXP_SUBSTR

SELECT  REGEXP_SUBSTR('500.90 Oracle Parkway, 12-12-2000 78 Redwood Shores, CA','([0-9]{1,2})-([0-9]{1,2})-([0-9]{4})') "REGEXPR_SUBSTR"  FROM DUAL;this two condition
([0-9]{1,2})  ([0-9]{1,2}) ([0-9]{4})
or this i did it like this

[code]...

View 8 Replies View Related

SQL & PL/SQL :: Extract Data - REGEXP?

May 25, 2012

How to extract data from the below string.
END OF FILE. ROW COUNT: 23,9
Output should be divided into 3 parts

END OF FILE. ROW COUNT:
23
9

View 8 Replies View Related

SQL & PL/SQL :: REGEXP Digit Extraction?

Oct 25, 2011

I am trying to write a pattern that will return multiple numbers when it finds the string "My String" in a Text such as

Text String:
My String 5 Test Need a good regExp 34 to test multiple 12334 occurrences of the same 566 search string My String 3 hopefully this time My String 7 with three of these'

Expected Output:
5
3
7

I managed to write Regular expression that returns only for the first match as shown below.

select TO_NUMBER(REGEXP_SUBSTR(REGEXP_SUBSTR (vSearchText1, ('My String' d+')), 'd+'))

View 30 Replies View Related

PL/SQL :: Retrieving Not Full Result / Regexp Substr

Nov 5, 2012

when I try to run this code

SQL> select

2 regexp_substr('But , soft! What light through yonder window breaks?',
3 'l[[:alpha:]] {4}') AS result
4 from dual;

I get just this ( R
-)

where the result should be like the full sub string which is ( light) not just R.

View 2 Replies View Related

Loop Back Entry

Jun 1, 2013

How the loop back entry in /etc/hosts relates to listener?

View 1 Replies View Related

SQL & PL/SQL :: Data Should Be Updated Back To Table

Aug 16, 2011

The requirement is, the combination of col1,col2,col3 and col4 should always be unique, and wherever the col1, col2,col3 are same then col4 should be the sequence, starting from 1. Likewise the data should be updated back to the table.I'm able to do this using PL/SQL. Can I do the same using a single update statement?

create table tab1 (col1 number(5), col2 number(5), col3 number(5), col4 number(5));

Existing Data:

insert into tab1 values (101,521,3,1);
insert into tab1 values (101,521,3,1);
insert into tab1 values (101,522,3,2);
insert into tab1 values (101,522,3,2);
insert into tab1 values (101,523,3,1);
insert into tab1 values (101,523,3,2);
[code]....

View 3 Replies View Related

SQL & PL/SQL :: Deleted All Records From Table - How To Get Back

Nov 8, 2010

I have deleted all the records from the table.And I have committed.Now I want to get all the records back.

View 16 Replies View Related

SQL & PL/SQL :: Getting Back Whole Record Then Picking 1 Column?

Jul 22, 2010

I want a function that'll execute a query and return the whole table record then i need to somehow pick a column in that record and return the data in that column

So like

function something(p_param varchar2)
return table_record
something('blah).employee_number

where employee_number is a column in that table

View 3 Replies View Related

Forms :: How To Go Back To Previous Block

Feb 21, 2012

I'm making a menu in my form, wherein it has FILE, TRANSACTION and REPORT. Under FILE it has BACK, and LOGOUT. In my back menu item, i want to go back to the previous block or previous module. I used previous_block but it's not working in some of my blocks.

View 8 Replies View Related

SQL & PL/SQL :: Any Draw Back Caching Sequence?

Mar 13, 2011

cache sequence all my sequence scripts has a cache of 20...here is the script

CREATE SEQUENCE APP_TEMP_SEQ
START WITH 1000400
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;

is there any draw back caching sequence ? I noticed in my tables several sequences are skipped.

View 2 Replies View Related

SQL & PL/SQL :: Get Result Set Back From Stored Procedure?

Apr 9, 2012

I come from a world of MSSQL and have been thrown into doing some Oracle work. Great! Ok, moving on.. I work in an environment where I do not have direct access to the database tables that I need data from. As a workaround, I have been asked to create a stored procedure that will be loaded into our CRM system's production db once it goes through the internal "approval" process.Basically, I need to return a result set back to the client by calling a stored procedure.

Version 1 of this that was already in place was done with the following code.

procedure events_by_day (p_start_date IN OPERATION_LOG.DT%type,
p_end_date IN OPERATION_LOG.DT%type,
p_results OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_results FOR

[code]....

Then the code is executed from the client side like so:

events_by_day(p_start_date => to_date('2012-3-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
p_end_date => to_date('2012-3-29 23:59:59', 'YYYY-MM-DD HH24:MI:SS'),
p_results => r_cursor);
LOOP
FETCH r_cursor
INTO event_date, event_acct_no, event_type, event_count;

[code]....

As you can see, a SYS_REFCURSOR was used in this case to pass the data back.

View 3 Replies View Related

PL/SQL :: How SQL Queries Act In Back End To Give The Result

Jul 22, 2013

When we are running a query it is giving us the result based on the conditions .But to know what exactly is happening in the database when we are running a query against it and how finally it returns the result.

View 1 Replies View Related

Adding New Node Back To Cluster Via Documentation

Apr 10, 2012

I have a 3 node RAC server on Windows Server 2008. Last week the hard drive went out on one of the nodes and I have had to rebuild as I could not recover anything.

I went through and deleted the old node and now I have just finished adding the new node back to my cluster via documentation. Once I created the new instance on that server DBCA attempted to start and it failed gaving me a crs error. I found out later that the other 2 nodes went down and the new one that did not start correctly was the only one up!! I went and stopped the new instance and restarted the first 2. The associated services did not start with the instance so I had to start each manually. The trace files show an ORA-29702 error with cluster group service and the instance being stopped on both of the existing nodes. No other error messages stood out.

Now I cannot get any crs services to start on that 3rd node even if I attempt to start manually. I have also tried stopping all and restarting and that does not work. I found another post on this forum from you and followed it. The ASM service was fine the entire time through all the logs and I don't know how to verify LMON in Windows but I didn't see any LMON errors in the alert log. Also, the voting disks are online. Each node has their own and they are mirrored. Where else to look?

View 7 Replies View Related

How To Back Up Database Call Without Archived Logs

May 11, 2011

here i have an question with oracle database backup strategy.my question is

how to backup my oracle database call DB11G without archived logs while the database is open for user activity and also this should be the base for an incremental backup strategy?

View 5 Replies View Related

Generate Software That Used VB As Front End - Oracle 10G As Back End?

Mar 31, 2007

I am trying to generate a software that used VB as the front end and Oracle 10G as the back end. I have noticed that the date format is VB is : m/dd/yyyy whereas the date format in oracle is dd-mm-yyyy. Whenever i try to insert a tuple with either of the date formats, the program halts running and after a long time , i get a message saying a deadlock was encountered. I have never had any formal introduction to either of the two tools

View 1 Replies View Related

SQL & PL/SQL :: Revert Back Package Changes To Previous Version?

Mar 29, 2012

I overwritten the package and want to get the previous version.

Is there a way I can get it using FLASHBACK or any other feature?

My user_recyclebin is showing only tables.

SELECT object_name, original_name, TYPE
FROM user_recyclebin;

View 10 Replies View Related

PL/SQL :: How To Convert BLOB To BASE64 String And Back

Aug 15, 2012

We have a forms application in Forms 10g. We scan a document and gets it to a image item in the Form. Now, we need to pass this image to a 3rd party application (document mgt. system, ). They have given us a web-service for this. We have to convert the image to a BASE64 string and then invoke the web-service method and input it.

The, 3rd party application document management system will also send images as BASE64 strings through the web-service. We need to convert them back to a blob and put to an image item in the form so that it can be viewed by the end-user.

So, how can we convert a image item in form (as BLOB) to a base64 string and also do the reverse???

View 2 Replies View Related

RMAN :: Redo Log Sequence Gets Pushed Back

Jul 31, 2013

Today we reverted back our Virtual machine to the snapshot taken on 7/25. Sure enough it worked.When you revert back the snapshot, you are literally going back in time so that means the redo log sequence gets pushed back.  So, we start back old log sequence number which is where we were on 7/25.  The problem is that we took the RMAN today before the snapshot was taken, now that we reverted back to old date 7/25.  Now what happens to the backup pieces that we took after 25th July to till date in RMAN? do I need to delete those backups which were in there in RMAN taken after 7/25?

View 15 Replies View Related

SQL & PL/SQL :: Sum When Reference In Another Table?

Dec 19, 2011

Two tables: article i mutations

Article:

artno descr qty sales
1 beer 1 5
2 coke 1 7
3 wine 1 4
4 beer ct 12 2
5 coke ct 6 3
6 wine ct 12 2
7 beer pl 336 1
8 coke pl 336 0

and mutations:

artno mutation
1 4
1 7
2 5
2 8
3 6

I want to get the result like

article sales
beer 365 '5+2*12+1*336
coke 25 '7+3*6
wine 28 ' 4+2*12

How to do a query.

View 39 Replies View Related

Force Query To Always Bring Back Certain Amount Of Rows

Jun 24, 2010

Is there a way to force a query to ALWAYS bring back a certain amount of rows, even if some of the rows are blank?

I know there's a way in MSSQL but not sure how to in SQL+

View 7 Replies View Related

SQL & PL/SQL :: Convert Data Back To Special Character Values?

Nov 4, 2011

Enterprise Edition Release 10.2.0.5.0

SQL> SELECT * FROM NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------
NLS_CHARACTERSET AL32UTF8
SQL>

There is table (VIN_TEMP) in my company database containing following records. It seems like this table should contain some greek language special chracter values instead of this weird data.

SQL> select * from vin_temp;
ATTR
--------------------------------------------------------------------------------
���9999
���9998
���9997
���9997

[code]....

Client are reporting these records as invalid and requesting us to fix. As i investigated i found out, this table was created and loaded few year back. Client sent us one time files which we loaded into this table. I was able to find the code which was actually used to load this table, but unfortunately i was not able to find the raw files where we load this data from...

It seems like Previous Developer specified character set "UTF8" statement, in his sql loader script, to load this data. It seem those file contain some Greek language special character data which was not support by "UTF8" charater set and result in creating those invalid data. My Job is to fix these invalid records and convert them back to its original values which were present in the raw file. I tried to contact client and see if i can find out the raw files but no luck. I tried to use convert function as mention to convert this data from "UTF8" to our current character set format but no luck.

SQL> SELECT attr, dump(attr), convert(attr,'UTF8', 'AL32UTF8') from vin_temp;
ATTR DUMP(ATTR) CONVERT(ATTR,'UTF8','AL32UTF8'
------------------ ---------------------------------------------------------------- -----------------------------
���9999 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,57 ���9999
���9998 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,56 ���9998
���9997 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,55 ���9997
���9997 Typ=1 Len=13: 239,191,189,239,191,189,239,191,189,57,57,57,55 ���9997

[code]....

Here is the script to create table with those type of invalid records.

create table VIN_TEMP
(
attr VARCHAR2(50 CHAR)
);
insert into VIN_TEMP (attr) values ('���9999');
insert into VIN_TEMP (attr) values ('���9998');

[code]....

View 1 Replies View Related

Server Administration :: Flash Back Recovery Area

Mar 3, 2012

what is flash back recovery area? what is its main function and usage

View 1 Replies View Related

Server Administration :: Back Track OS Processes To RDBMS

May 5, 2013

I have two Oracle databases (test1- 10gr2,test2 - 11gr2) on rhel 5.5. is it possible to find which particular oracle process belongs to which home from OS level. was trying to find which db processes are consuming more resources on my host.

View 4 Replies View Related

Client Tools :: SQLPLUS For Oracle 11 Not Rolling Back

Sep 3, 2013

I have a shells script which invokes a SQL file. However even with AUTOCOMMIT OFF and on SQLERROR EXIT ROLLBACK. Sqlplus fails to rollback.

My sql file has 3 lines 3 are correct and 1 is incorrect. For example:
INSERT INTO TEST_ROUTING VALUES (24, 'ROUTING');
INSERT INTO TEST_ROUTING VALUES (25, 'ROUTING');
INSERT INTO TEST_ROUTING VALUES (26, 'ROUTING);

Lets say file is called 1.sql

My shell script invokes this SQL as follows: (Where $File1 = 1.sql)

$SQLPLUS_PATH/sqlplus -s /nolog <<-EOF>> ${LOGFILE}
connect $DB_USER/$Password1@$Database1
SET AUTOCOMMIT OFF
@$File1
WHENEVER SQLERROR EXIT ROLLBACK;
EOF
[code]......

So tried SET AUTOCOMMIT, tried SQLERROR ROLLBACK and tried few variations.

View 7 Replies View Related







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