SQL & PL/SQL :: Check Wether Digit Is Numeric Or Character
Dec 4, 2004is there in pl/sql a function to check wether a digit is numeric or character like we have in c 'is_numeric() ' or is_digit() functions�
View 6 Replies
ADVERTISEMENT
PL/SQL :: Function That Returns The Check Digit Of 9-digit Number
Jan 17, 2013I would like a pl/sql function that returns the check digit of a 9-digit number?
Look at
[UR]......
For example number 109409714 (9 digits) should do check digit. I want validation of check digit, & want to return the check digit.
SQL & PL/SQL :: Trigger - Non-numeric Character Was Found Where Numeric Was Expected
Oct 10, 2011I have an sqlldr process running loading data into my database. I have created a trigger to run before inserts on each row to start gathering summary data from the basic underlying data. The trigger compiles ok and the procedures the trigger is calling compile ok, but when the sqlldr process runs I get errors in the log files.
Here is the sqlldr control file:
LOAD data
APPEND INTO TABLE cdr.day_tables
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
RecordCode
,CdrStart DATE 'YYYY DDD SSSSS'
[code].......,
Next is my trigger
create or replace TRIGGER BNUMBER_SUMMARY_INS
BEFORE INSERT ON DAY_TABLES
FOR EACH ROW
DECLARE
[code]......
Next are the procedures that are called by the trigger:
create or replace PROCEDURE BNUMBER_SUMMARY
( BNUMBER IN VARCHAR2
, CALLDATE IN DATE
, CALLDURATION IN NUMBER
) AS
record_found NUMBER;
BEGIN
[code].......
The error messages I am getting are:
Record 1: Rejected - Error on table CDR.DAY_TABLES, column CDREND.
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "CDR.BNUMBER_SUMMARY_INS", line 6
ORA-04088: error during execution of trigger 'CDR.BNUMBER_SUMMARY_INS'
I need to find out what field it is complaining about, especially since I am not even using the cdrend field from the input record?
SQL & PL/SQL :: ORA-01858 / Non-numeric Character Was Found Where Numeric Was Expected
Apr 22, 2013select ORDER_NUMBER from OE_ORDER_HEADERS_ALL
WHERE ordered_date=to_char(to_date(substr(ORDERED_DATE,1,10),'YYYY/MM/DD'),'DD-MON-YYYY');
Error:-ORA-01858: a non-numeric character was found where a numeric was expected
Non-numeric Character Found Where Numeric Was Expected?
Jun 1, 2007I get the error message mentioned in the subject with this SELECT-statement
....where (t.cfonte=14 and t.data_ultima_modifica between sysdate -4000/(24*60*60) and sysdate ) or (t.data_ultima_modifica > to_date('%TIMESTAMP%','ddmmyyhh24miss'))]]>
If I substitute %TIMESTAMP% with 310507143709 then it works
SQL & PL/SQL :: Extract Number And Previous Character From Where 5 Digit Number Starting
Oct 21, 2011I 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
SQL & PL/SQL :: Last Numeric Character In Alphanumeric String
Feb 8, 2011How can I find the last numeric value in the alphanumeric string?
Example:
LS14160220SPAD show me 0
MN23160224N show me 4
SP34524442 show me 2
PL/SQL - Numeric Or Value Error / Character String Buffer Too Small
May 24, 2011when 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]...
SQL & PL/SQL :: ORA-06502 - Numeric Or Value Error - Character To Number Conversion?
Aug 4, 2011I got a string in the form 1+2+4.If we write select 1+2+4 from dual;then we get o/p as 7.but the same thing iam trying to do in a bit of pl/sql program by passing the string 1+2+4 value to a number variable as below.
COUNT_TASK := TO_NUMBER(TASK6_STATUS);
TASK6_STATUS value is 1+2+4 (this thing i got by replacing the string and lots of stuff) but i need the result after adding these 3 numbers in the string. and i declare COUNT_TASK as NUMBER;and i am very well aware that it gives me the error ORA-06502: PL/SQL: numeric or value error: character to number conversion error
how to add these numbers in my program to get the result 7.
SQL & PL/SQL :: Numeric Or Value Error - Character String Buffer Too Small
May 6, 2008I 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.
PL/SQL :: ORA-06502 - Numeric Or Value Error - Character To Number Conversion?
Jun 20, 20131 error has occurred ORA-06502: PL/SQL: numeric or value error: character to number conversion error
I get the above error when I try to compare the below dates in a Pl/sql process in APEX environment.
Is there a work around for it? or (to_char(V_SERVFROM,'mm/dd/yyyy')) != (to_char(:P29_SERVFROM,'mm/dd/yyyy'))
SQL & PL/SQL :: ORA-06502 Numeric Or Value Error / Character String Buffer Too Small
Jul 14, 2011i 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].....
SQL & PL/SQL :: ORA-06502 - Numeric Or Value Error - Character String Buffer Too Small?
Jul 10, 2012Following 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]...
SQL & PL/SQL :: Two Variables - Numeric Or Value Error / Character String Buffer Too Small
Jan 24, 2011I 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]...
ORA-06502 - PL/SQL / Numeric Or Value Error / Character String Buffer Too Small
Oct 8, 2012Actually 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.
SQL & PL/SQL :: Getting ORA-06502 - Numeric Or Value Error - Character String Buffer Too Small?
Jun 7, 2012I 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]...
SQL & PL/SQL :: ORA-06502 - Numeric Or Value Error / Character String Buffer Too Small
Feb 17, 2012ORA-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], [],[],[]...
Forms :: ORA-06502 - PL/SQL - Numeric Or Value Error - Character String Buffer Too Small
Oct 2, 2010i 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.
Client Tools :: ORA-06502 - Numeric Or Value Error / Character String Buffer Too Small
Mar 28, 2013Trying 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
Server Administration :: How To Know Character Set As Single Character Or Multi Character
Sep 26, 2013how 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?
SQL & PL/SQL :: Non-numeric Found Where Numeric Was Expected
Dec 7, 2010We are getting an error : a non numeric was found where a numeric was expected sometimes when this statement is executed:
INSERT INTO training select * from temp_training where class_id='xyz';
all columns and their datatypes are the same in both the tables
however if i replace the * with the column names as shown below it seems to work fine without giving an error
insert into training (a,b,c) select a,b,c from temp_training where class_id='xyz'
wanted to understand the subtle difference between the 2 statements
SQL & PL/SQL :: REGEXP Digit Extraction?
Oct 25, 2011I 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+'))
Forms :: Put One Check Box To Check All The Check Boxes?
Jul 30, 2011I want to put one check box to check all the check boxes.how can i do this?
View 5 Replies View RelatedFix Number Of Digit After Decimal Point?
Mar 21, 2007I have a small prob. I want an amount to be always 2 decimal places. I've used the Round function - Round(amount,2). the problem is that if the amount is only to 1 dp like 1.4. the above function will return 1.4. I want it to appear like 1.40
View 2 Replies View RelatedSQL & PL/SQL :: Function To Accept 4 Digit Number Which Should Not Be Repeated?
Aug 15, 2012i nee a function which accepts 4 digit number and in four digit number the number should not be repeated. i want all the number in the output.
ex:1234
2367
1262(is not valid)
Generate Random And Unique 6 Digit Number In Oracle
Sep 29, 2008I need to generate random and unique 6 digit number in Oracle. I need to insert these numbers into a table. I tried using DBMS_RANDOM package, which generates random 6 digit numbers, but fails to generate UNIQUE numbers.
View 3 Replies View RelatedForms :: Show Increasing Digit 1 To 100 In Text Field After Pressing One By One
Aug 26, 2013the code for show increasing digit 1 to 100 in a text field after pressing a button 100 times?
View 26 Replies View RelatedPL/SQL :: Round Function Output Depend On 2nd Digit After Decimal Point
Oct 19, 2012I have a question on Round Function
Round(###.###,1)
Will the round function output depend on 2nd digit after decimal point also or not?
SQL & PL/SQL :: How To Insert Special Character / ORA-00911 / Invalid Character
May 25, 2010We have Oracle 10g and user trying to Insert following in one of our table field and getting error:
'REVOLUCIÃ"N Historical Corruptions Agenda'
Getting follwoing error:
ORA-00911: invalid character
create table Employee
(Emp_ID Varchar2 (10),
Company_Name Varchar2 (40)
)
Insert into Employee
values ('Emp1', 'REVOLUCIÃ"N Historical Corruptions Agenda');
Inserting Character From Extended ASCII Character Set?
Mar 8, 2007I'm trying to insert a character from the extended ascii character set. Specifically, there's a company that has an accented e (�) in the name. Right now, the company name doesn't have the e at all, accent or no accent. So I'm trying to do an update, something like
update table1 set company_name='blah�" where company='blah'
It runs, but doesn't do the update. Even when I try to forcefully do an insert (instead of an update) I get nowhere; the accented is simply dropped. So the basic question is, how do you insert extended ascii characters into oracle?