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...
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
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.
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.
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
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...
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...
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
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]...
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.
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
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
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,
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
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]...
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.
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
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' ===
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?
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.
