SQL & PL/SQL :: How To Clip Data Starting From Certain Character String

Aug 23, 2010

I have created and formatted a mini test scenario. Execute the scripts I have below?

From the column adj_second_line, I am trying to clip everything from the characters '201' all the way to the end...or the NUMERIC start value after the word 'TYPE'. Whichever way is easier for you...

create table test_split
(adj_second_line varchar2(80))

insert into test_split
values ('ADJ#1-2G3AYL TYPE 20100501 20100524 0MO/23DY')

insert into test_split
values ('ADJ#2-656GYP TYPE AR 20100522 20100524 0MO/15DY')

insert into test_split
values ('ADJ# 265HKK TYPE X 20100428 20100524 0MO/30HT')

insert into test_split
values ('ADJ#13 43327DR TYPE AJ 20100413 20100524 0MO/30HT')

-- Need to have another column called split_second_half

SELECT adj_second_line,
substr(adj_second_line, 1, instr(adj_second_line, '201')-1) split_first_half,
instr(adj_second_line, '201') clip_from_position
FROM test_split

--Desired output for the split_second_half column

20100501 20100524 0MO/23DY
20100522 20100524 0MO/15DY
20100428 20100524 0MO/30HT

View 11 Replies


ADVERTISEMENT

PL/SQL :: How To Select String Between Nth Specific Character To Nth+1 Character

Aug 9, 2012

I have one string 'SWAP_20120224_MEGAMART_MAR_Sales.csv'

I want to get the string between second underscore and third underscore i.e. 'MEGAMART'

How to achieve this in sql statement?

View 4 Replies View Related

SQL & PL/SQL :: Extract Number And Previous Character From Where 5 Digit Number Starting

Oct 21, 2011

I have a text field and if the text field has 5 consecutive numbers then I have to extract the number and the previous character from where the 5digit number starting

For example i/p asdfasfsdS251432dasdasd o/p should be S251432

View 10 Replies View Related

SQL & PL/SQL :: How To Discard Last Character Of A String

May 14, 2011

how we can discard the last character of a string in sql?

eg: we have a string v_str := 'abcde/xyz/eerr/';

I need to discard the last character '/' from this variable.

Is there any function available to do that?

View 3 Replies View Related

SQL & PL/SQL :: Special Character In String

Feb 23, 2011

My requiremnt is that a field must contain on Alpha number value, no specail character allowed, how can we sure by query that string which is going to commit is alphanumeric.

View 2 Replies View Related

SQL & PL/SQL :: Character String Buffer Too Small?

Dec 14, 2012

I'm trying to create a table with a select statement. I want to populate this new table with the aggregated value from a VIEW. Following is the code used for creating the VIEW,

create or replace view FINAL_WEB_LOG
as
select SESSION_ID,
SESSION_DT,
C_IP,
CS_USER_AGENT,
tab_to_string(CAST(COLLECT(web_link) AS t_varchar2_tab)) WEBLINKS
from web_views_tab
group by C_IP, CS_USER_AGENT, SESSION_DT;

I want to create a table with WEBLINKS and SESSION_ID which is a sequence from another table.

CREATE TABLE FINAL_WEB
AS
SELECT weblinks
FROM final_web_log
UNION
SELECT session_id
FROM WEB_VIEWS_TAB;

This now gives me the following error,

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

This has to do with the field, Weblinks, it does have longer values.

View 5 Replies View Related

SQL & PL/SQL :: Last Numeric Character In Alphanumeric String

Feb 8, 2011

How can I find the last numeric value in the alphanumeric string?

Example:

LS14160220SPAD show me 0
MN23160224N show me 4
SP34524442 show me 2

View 9 Replies View Related

SQL & PL/SQL :: How To Validate Given String For Alphanumeric Character

Feb 14, 2011

To built the Pl/SQL function or SQL query to validate the given string for alphanumeric character.

I would pass the string of size 10 character, we need to validate first 5 character as alphabet, next 4 character as numeric and last 1 character as alphabet.

I will pass the each row value to the function, it need to return "T" or "F" based on the condition,first 5 character as alphabet, next 4 character as numeric and last 1 character as alphabet.

Here is the DML and DDL.

[code]

create table abc ( classid varchar(10));

insert into abc values ("abcde1234f");
insert into abc values ("abcde12345");
insert into abc values ("ab1de1234f");
insert into abc values ("abcde1234f");
insert into abc values ("abcd21234f");

[code]

Output:

Input : abcde1234f Output : T
Input : abcde12345 Output : F
Input : ab1de1234f Output : F
Input : abcde1234f Output : T
Input : abcd21234f Output : F

View 6 Replies View Related

SQL & PL/SQL :: How To Find Any Character Is Repeating In A String

Sep 8, 2011

How to find whether any character in a string is repeating or not

Eg: '123LH563' should return 'YES' , as 3 is repating there
'1234567' sould return 'NO' as there is no character which is repeating

View 8 Replies View Related

SQL & PL/SQL :: Query To Split Character String Using XQUERY

Aug 5, 2010

I am using this query to split numeric values i.e ('1,2,3,4,5,6')but when i am trying with char value i.e ('a,b,c,d'), its not working.

select id
FROM employee e
WHERE e.id IN
( SELECT TO_NUMBER(xt.column_value)
FROM XMLTABLE('1,2,3,4,5,6') xt );

View 6 Replies View Related

SQL & PL/SQL :: Space In Every Character Of A String With Single Query

Mar 19, 2012

How can we get 'space in every character of a string with Single select query'

for example:-
string 'INDIA'
result should be 'I N D I A'

View 1 Replies View Related

PL/SQL :: LENGTH (String) Ignores Special Character %?

Oct 10, 2012

why the below is not the same?

select * from textsearch where
LENGTH(TRIM(BOTH ' ' FROM Text)) < 1
--0 Rows

select * from textsearch where Text='%';
--12 Rows

View 1 Replies View Related

ORA-06502 Character String Buffer Too Small When Called From C# App

Apr 26, 2007

I have researched this problem and checked my variable sizes over and over again. I have tested the procedure within the Oracle Express environment and it works fine; HOWEVER, when the procedure is called from my C# app it produces the ORA-06502 error.

The stored procedure signature looks like this...

Original - SQL Code

create or replace save_new_project (p_custorgid in number,
p_title in varchar2,
p_AOIName in varchar2,
p_description in varchar2,
p_receiveddate in date,
p_deadlinedate in date,
p_startdate in date,

[code]....

The OracleParameter in my C# app for the last out param is defined as such...

cmd.Parameters.Add(new OracleParameter("p_statustypedescrip", OracleDbType.Varchar2, 30, ParameterDirection.Output));

As I said at the beginning of this post, the procedure works fine in the Oracle environment. So why is it not working by simply calling it from C#? I've tried changing the OracleDbType to CLOB which eliminates the error but it returns a bizarre result. It returns this string, "Oracle.DataAccess.Types.OracleClob".

Since CLOB doesn't really work either I switch back to Varchar2 and specify a size of 5000 (in the database the field I am querying is defined as Varchar(30)). I still get the ORA-06502 error.

I am clueless as to what the problem is. It should work and it does if I run a series of SQL statements in an Oracle SQL Command window. The test that works fine looks like this...

Original - SQL Code

declare
v_projid projects.projectid%type;
v_statustypedescrip projectstatustypes.type%type; /* this is a varchar(30) */
begin
save_new_project(2, 'Some input text goes here', 'More input text', 'And more again','26-APR-2007','26-APR-2007','26-APR-2007','26-APR-2007','users name as inpujt text

[code]....

But calling save_new_project from C# throws ORA-06502. It identifies line 40 of my stored procedure. This is line 40...

Original - SQL Code

p_statustypedescrip := v_statustypedescrip;

p_statustypedescrip := v_statustypedescrip;

View 4 Replies View Related

SQL & PL/SQL :: String Split / Replace Based On Character Length

Jun 19, 2013

notes column having 2000 characters max, i want my string output based on 35 characters, ya i need to replace tag after 30 characters in the string.. I need out put as "hi hello how are you doing out there, similar i need to calculate the sting length and have to split it 35+35+35..

This i tried

select substr(note,1,(instr(note, ' ',35)))||'
'||substr(note,instr(note, ' ',35),(instr(note, ' ',35)))notes from test

View 7 Replies View Related

PL/SQL - Numeric Or Value Error / Character String Buffer Too Small

May 24, 2011

when I want to create a table.When I run my procedure I received :
PL/SQL: numeric or value error: character string buffer too small

create or replace PROCEDURE p_create_tmp_tables (p_result OUT NUMBER ) IS
string_sql varchar2(1000);
result NUMBER;
BEGIN
string_sql := 'CREATE TABLE TMP_CATEGORIES (CODE_CATEGORY NUMBER(6,0), NAME_CATEGORY VARCHAR2(25 BYTE))';
execute immediate string_sql;
[code]...

View 10 Replies View Related

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

May 6, 2008

I am trying to execute this

begin
-- Initialize owa with an empty array, just so we can use htp.
owa.init_cgi_env( param_val=>owa.cgi_var_name );
-- Add a header to avoid dealing with header-related code in htp.
htp.prn('Content-Type:text/plain');
htp.print('');

[code]....

The error I am getting is

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.HTP", line 1550
ORA-06512: at line 32

what i feel is that oracle Database UTF8 or AL32UTF8 characterset does not support htp package properly. When i run the code on another characterset, it runs errorfree except in UTF8 or AL32UTF8.

View 7 Replies View Related

ORA-01458 - Invalid Length Inside Variable Character String

Sep 30, 2010

Where I am copying the data from one database to another I am getting this error.

ORA-01458: invalid length inside variable character string

This is applicable for only character string.

The source Database is
Oracle9i Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for HPUX: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

The destination database is
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
PL/SQL Release 9.0.1.1.1 - Production
CORE 9.0.1.1.1 Production
TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
NLSRTL Version 9.0.1.1.1 - Production

View 4 Replies View Related

SQL & PL/SQL :: Error / Character String Buffer Too Small ORA-06512 / At Line 9

Nov 17, 2011

Actually I am getting the following error while running my script.

Error:(Error): ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 9

Seems to be getting eror in the following line but not sure why its coming."select adary.dilution_strategy_seq.NEXTVAL into :dil_strat_seq_new from dual;"

Code snippet:
---------------------------------------------------------------
declare
dil_strat_seq_new NUMBER :=0;
disc_conc_seq_new NUMBER :=0;
disc_fold_seq_new NUMBER :=0;
begin

[code]...

View 4 Replies View Related

SQL & PL/SQL :: ORA-06502 Numeric Or Value Error / Character String Buffer Too Small

Jul 14, 2011

i am getting above error while doing

ORA-06502 numeric or value error: character string buffer too small
ORA-06512 itw_item_add_ch at line 17

CREATE OR REPLACE procedure itw_item_add_ch (header_id1 number, folio out varchar2,tariff out varchar2) as
cursor item_add(header_id1 number) is
SELECT
DISTINCT c.attribute4 item_tariff_no,
c.attribute5 item_folio
FROM OE_ORDER_LINES_ALL b,

[code].....

View 11 Replies View Related

SQL & PL/SQL :: ORA-06502 - Numeric Or Value Error - Character String Buffer Too Small?

Jul 10, 2012

Following are my declarations and query inside my procedure.i am getting "ORA-06502: PL/SQL: numeric or value error: character string buffer too smal" error when i try to execute the procedure

code :

request_owner request.SCC_USER_ID%TYPE := NULL;
receipt_location request.RECEIPT_LOCATION_LKP_ID%TYPE := NULL;
receipt_type request.RECEIPT_TYPE_LKP_ID%TYPE := NULL;
item_id request.ITEM_ID%TYPE := NULL;

[code]...

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

ORA-06502 - PL/SQL / Numeric Or Value Error / Character String Buffer Too Small

Oct 8, 2012

Actually I have an existing table in LOG RAW instead of BLOB. Which stores some transaction XML data. The data is very huge. When i trying with the following, gives me following error

SQL> declare
2 a varchar(255);
3 B LONG RAW;
4 cursor c1 is select xml FROM LOG_tab WHERE ID='13148' ;
5 begin
6 open c1;
7 loop
8 fetch c1 into b ;

[code]....

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

Since dbms_output.put_line has buffer limitation.Can we write the result of a select statement into client LOCAL disk files using PL/SQL.

View 1 Replies View Related

SQL & PL/SQL :: Getting ORA-06502 - Numeric Or Value Error - Character String Buffer Too Small?

Jun 7, 2012

I am getting an error as below
ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

Here is complete code

CREATE OR REPLACE
TYPE MDI.ACCUM_STRING_TYPE2 AS OBJECT (
rvalue VARCHAR2 (32767
STATIC FUNCTION odciaggregateinitialize (sctx IN OUT accum_string_type2)
RETURN NUMBER

[code]...

View 4 Replies View Related

SQL & PL/SQL :: ORA-06502 - Numeric Or Value Error / Character String Buffer Too Small

Feb 17, 2012

ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8

I am reexecuting this view:

set serveroutput on buffer 2560000
;
CREATE OR REPLACE VIEW VIEW_TRIP_ASSIGNMENTS
(COMPANY_ID, TRIP_ID, TRIP_STOP_ID, TRIP_CODE, TRIP_NAME,
TRIP_DESC, TRIP_NUMBER, TRIP_START_TIME, TRIP_END_TIME, TRIP_LOADING_TIME,
TRIP_UNLOADING_TIME, FREQUENCY_ID, FREQUENCY_CODE, FREQUENCY_NAME, FREQUENCY_DESC,
FREQUENCY_CODE_NUMBER, CUSTOMER_ID, CUSTOMER_NUMBER, CUSTOMER_NAME, CUSTOMER_DESCRIPTION,
CUSTOMER_TYPE_ID, CUSTOMER_TYPE_NAME, CUSTOMER_TYPE_CODE, DAY_PHONE_NUMBER, EMAIL_ADDRESS,
CONTRACT_ID, CONTRACT_NUMBER, CONTRACT_DESCRIPTION, WORK_LOCATION_ID, ROUTE_ID,

[code]....

before reexecuting above view I was counting the column and was getting below error:

ora-00600 internal error code,arguments:[qctopn1], [],[],[]...

View 14 Replies View Related

Forms :: ORA-06502 - PL/SQL - Numeric Or Value Error - Character String Buffer Too Small

Oct 2, 2010

i am generating html format mail from oracle 10g database.

For displaying data into html format, message body data is exceeding more than 32k.

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

i am using long data type for message body data.

View 4 Replies View Related

Precompilers, OCI & OCCI :: ORA-01458 / Invalid Length Inside Variable Character String

Apr 16, 2009

I want to insert data into a RAW column in a table. The below code snippet is used for the same

OCIRaw **l_rawPtr = new OCIRaw*[10];
for(int i=0;i<10;i++)
{l_rawPtr[i] = NULL; }
int max = 0;
for(int i=0;i<10;i++)
{
OCIRawAssignBytes(l_environment, l_error, l_char, strlen((const char*)l_char), &l_rawPtr[i])

[code].....

When the statement is getting executed, i am getting the following error. Error - ORA-01458: invalid length inside variable character string

View 3 Replies View Related

Client Tools :: ORA-06502 - Numeric Or Value Error / Character String Buffer Too Small

Mar 28, 2013

Trying to learn and understand Triggers, PL/SQL code, etc. When I execute a simple insert using Toad for Oracle, all is fine. When I try the identical insert in SQL*Plus, it throws error ORA-06502 during execution of the trigger.

Here is the error info:

insert into AAAJOB(PROCEDURENAME,DESCRIPTION) VALUES('OOO','PPP')
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "PMSC0.AAAJOB_TIMING", line 10
ORA-04088: error during execution of trigger 'PMSC0.AAAJOB_TIMING'
===

Here is the table and the trigger:

CREATE TABLE PMSC0.AAAJOB
(
CREATETS TIMESTAMP(0) DEFAULT current_timestamp,
PROCEDURENAME VARCHAR2(100 CHAR),
DESCRIPTION VARCHAR2(100 CHAR),
LASTUPDATEDBY VARCHAR2(9 CHAR)
)

[code]....

The error seems to be on this:

SELECT current_timestamp
INTO :new_row.createTS

View 5 Replies View Related

Server Administration :: How To Know Character Set As Single Character Or Multi Character

Sep 26, 2013

how do we know database character set is either single character set or multi character set?

While changing character-set from AL32UTF8 to WE8MSWIN1252 got "ORA-12712: new character set must be a superset of old character set".

Below are steps taken to resolve the issue -

ALTER DATABASE CHARACTER SET WE8MSWIN1252;

i got this error: ORA-12712: new character set must be a superset of old character set

below are the commands executed by me:

SQL> SHUTDOWN IMMEDIATE;
SQL> CONNECT SYS/password AS SYSDBA;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;
SQL> SHUTDOWN;
SQL> STARTUP;
SQL> QUIT;

And its working...

I have not done it in proper order. Neither have done ccsscan. Still, no user reported any issues. Do my changes truncated the data?

View 11 Replies View Related

Export Data Using Escape Character

Jul 2, 2012

I have got a requirement where i need to export data from oracle with escape character.

eg. I am using a delimiter 237(í) and if the same character is present in data it should be escaped by escape character eg. /.

Once this file will get created i need to load this file in Netezza database which supports escape character.

Data in oracle table
FirstName     Lastname     Designation
abc     xyz     mnz
def     ghío     pqr

Data should be exported like below
FirstnameíLastnameíDesignation
abcíxyzímnz
defígh/íoípqr

View 3 Replies View Related

SQL & PL/SQL :: Importing To New Character Set And Its Affect On Encrypted Data

Nov 9, 2010

We are exporting from a 9i db to an 11g. During the migration, we are changing our character set from USASCII7 to AL32UTF8 so that the "extended" characters that our users like to put in text fields are stored and retrieved properly.

However, we've found a problem, and i'm not sure if Oracle has a method of dealing with. Searching this site and Oracle docs got me nowhere.

We store account #'s and credit card info in the DB encrypted with the dbms_obsfucation_tookit. We have an encryption key cross reference table that we use to store the key to un encrypt the data.

However, what we've found is that by importing these keys into our new character set database, the keys are no longer valid and can't be used with the DES3DECRYPT function to get the correct numbers out.

Is there a conversion utility or any tool that Oracle provides to maintain the encrypted datas "decrypt ability"? Worse comes to worse, we will have to write a script/procedure to decrypt everything on the 9i, import it to 11g, and then re-encrypt it.

View 8 Replies View Related







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