SQL & PL/SQL :: Exception Query - Alphanumeric Input?
Dec 27, 2012
I'm a newbie to PL/SQL. I had a quick query about trapping exceptions.
I have a sample table called my_emp, which contains last name, salary, etc. I have written the following code that takes in an employee salary and if the salary exists it displays the last name and corresponding salary. If two or more rows are returned, the exception handles it. Likewise if there are no records with that salary, the exception takes care of it.
I was trying to input an alphanumeric input, such as 1bbb as the salary and of course ORA-06502 error pops up in the sql command line. I now want to trap this using an exception but whatever I try I still get the ORA-06502 in the calling environment rather than getting the 'Not a number' or 'Some other error occured' message. why the WHEN VALUE_ERROR or the WHEN OTHERS exceptions are not trapping the error?
DECLARE
v_sal NUMBER (12) := '&Enter_salary';
v_last_name VARCHAR2(10);
BEGIN
SELECT last_name
[code]...
View 3 Replies
ADVERTISEMENT
Nov 3, 2011
when no data found this query generate error ora-01403 but if i count these transaction and then apply this trigger it works very well i am not interested to count because it takes a time i want that when no data found exception clause took control but i am not understand how to add exception clause in this trigger
select sum(nvl(gl_quantity,0)),sum(nvl(gl_amount,0)) into cr_qty,cr_amt from account.glhis where gl_drcr='CR' and gl_account=:prod_consume_auto.store_code and gl_date<=:prod_master_auto.voc_date and gl_voc_no<>:prod_master_auto.voc_no group by gl_account;
View 3 Replies
View Related
Jan 31, 2013
SELECT COUNT(*)
INTO v_ctr
FROM leave_type_govt_service
WHERE (
:block_name.min BETWEEN minimum AND maximum
[code]......
This code in my forms really works for inserting a new record. But if I will update the record itself for example, in the table 'Type 1'
Minimum Maximum
1 5
6 10
11 15
and I will update the 6 - 10 to e.g. 7, It must update but my query includes 6 - 10 data so it raise the trigger failure.
Note: The AND condition in the last part is for unique types. Because even if e.g. 'Type 2' has a Min and Max data same as the 'Type 1' the trigger will not prompt.
View 1 Replies
View Related
Dec 26, 2012
Getting problem with below query.
select to_date('30-DEC-00','DD-MON-YYYY') from dual
Error starting at line 1 in command:
select to_date('30-DEC-00','DD-MON-YYYY') from dual
Error report:
SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"
*Cause: Illegal year entered
*Action: Input year in the specified range
View 1 Replies
View Related
Dec 21, 2012
Example:
select * from emp
where empno in (123 234 345 124)
View 6 Replies
View Related
Oct 7, 2013
APEX 4.2Oracle 11g Database We are using the standard exception handler that was introduced in APEX 4.1, and we have code in packages & procedures in the database (following proper processes of keeping code in the database where possible). When an exception is found in the procedures/ packages/functions, should the APEX application level exception handler catch any errors that occur or should they be handled in the package/procedure/function they occurred in? Why I ask if, we right now have exception handling code in the pl/code bodies BUT they write their errors to the same table that Apex's Exception handler does, but the errors are NOT presented to the user using the APEX exception handling mechanism.
View 2 Replies
View Related
Jun 17, 2013
When i select Filnumber(DEC99) and press QUERY button, i get error"frm-40735 pre-query trigger raised unhandled exception ora-06502".
View 1 Replies
View Related
Oct 10, 2011
I have to implement exception handling in the exception block of a trigger, Quote:exception
when ora_java.java_error then
message( 'Unable to call out to java, ' || ora_java.last_error );
ORA_JAVA.CLEAR_EXCEPTION;
when ORA_JAVA.EXCEPTION_THROWN then
ex := ORA_JAVA.LAST_EXCEPTION;
message( Exception_.toString(ex));
-- lv_exception := Exception_.getMessage(ex);
I get an error for the line: 'message( Exception_.toString(ex));'I have imported the java classes FException et IObject with their methods.
I have to create a Web Service Client, so I wonder if the paragraph Quote:when ORA_JAVA.EXCEPTION_THROWN then ex := ORA_ JAVA. LAST_ EXCEPTION; is mandatory.
View 2 Replies
View Related
Aug 1, 2012
create table x(
sno varchar2(5)
);
insert into x values('A-1');
insert into x values('B-1');
insert into x values('B-2');
insert into x values('B-3');
insert into x values('1');
insert into x values('A-2');
insert into x values('2');
insert into x values('3');
insert into x values('A-4');
insert into x values('B-4');
insert into x values('C-4');
insert into x values('D-4');
SQL>select * from x;
SNO
-----
A-1
B-1
1
A-2
2
3
A-4
B-4
C-4
D-4
B-2
B-3
How can I select it ike this
1
A-1
A-2
A-4
B-1
2
B-2
3
B-3
B-4
C-4
D-4
View 15 Replies
View Related
Sep 8, 2011
i need a function which checks if v_rand carrying a value is alphanumeric if nt this value of 6 alpha numeric characters must be generated again... here is the actual fn.
Function alphanumeric
Return varchar2
is
v_rand varchar2(10) := 0;
Begin
[code]......
View 4 Replies
View Related
Nov 20, 2008
I have an urgent request which is pending with the following problem.
Problem :
I have a table which contains data of various datatypes like alphanumeric,varchar and number.
Now my query is " how to sort the data of the table using alphanumeric field"
How to select the data in a required(MyRequirement) sort order.
Data
============
12.4PI1
12.4
12.2
12.4T
12.3PI1
[Code]....
afterSorting(which I am getting Now)
============
12.2
12.3PI1
12.4
12.4PI1
12.4PI10
12.4PI11
[Code]...
MyRequirement
===============
12.2
12.3PI1
12.4
12.4PI1
12.4PI2
12.4PI3
12.4PI10
[Code]..
Means it has to sort the data order by lefthand side of PI and also righthand side of PI.
Pls check the attachment if you are not getting the above data in correct order.
View 22 Replies
View Related
Apr 12, 2010
Is there a function or a process to generate and maintain an alphanumeric sequence?
I need to be able to sequentially generate both a 3 and 5 byte case sensitive alphanumeric primary key that uses A-Za-z0-9 characters intermittently of which the 5 byte MAY be a subset of the 3 byte. e.g. the key can be 00000 or a3BD7.
so I need to be able to generate a new 5 char using 3 char base and if that value is not available, generate a unique 5 char value and allocate the substr(1,3) of that value backwards.
Ex1: unique customer location comes in - not a preexisting customer name (same name,different location address doesn't exist) --> select nextval.[5char] into v_long_c to insert into customer_loc table, and substr (v_long_c, 1, 3) for customer name table.
EX 2: unique customer location comes in. customer name exists in customer table with 3char val A39, but that location doesn't exist in customer_location table (5chars are location specific).
I need to take the A39 and generate new 5char for that unique location using that prefix (A39) if possible.
However, a number of a39-- already exist, though not all assigned to the same customer name (we're trying to keep them grouped together but that might not be logically feasible)
How do I select next a39||[2char] for that unique location - and if that value is not available (all 62*62 possibilities have been used for A39--), select nextval.[5charseq] into long_key.
I suspect someone out there knows the functions I can use to create this or has written a package to do just this; I suspect with enough time I could do it, but I don't currently have the time or knowledge to develop it within these deadlines. I thought it would be easier.
My short-sighted solution was to create a static table of all iterations of A-z0-9 5 char values, select one, mark it used and move on. Unfortunately 62^5 is a substantial number (913+million records) and that table took a LOT of space, causing my development server to groan and crack miserably. indexing on it takes a lot of space too (and trying to build multiple indexes exceeded database size). But without an index on the 3char field, selecting an available 5 code from it based on the customer_3char prefix took five minutes - much longer than the fraction of a second I need.
View 5 Replies
View Related
Jun 18, 2010
I am trying to sort columns base on cluster and alpha numeric field.
Column1 Columns2 column3
1 2-CA6R-234 9
1 2-CA6R-231 8
1 ARCT-0037000000ewegZ 10
2 2-QIZFF7 1
3 2-PIZFF6 6
3 ARCT-0037000000ewipk 9
3 2-QIZTF7 1
Wanted to sort in a way that column1 will be same order and the second column will order first with ARCT-XXXXX and then reset of the column2. It should look like this
Column1 Columns2 column3
1 ARCT-0037000000ewegZ 10
1 2-CA6R-231 8
1 2-CA6R-234 9
2 2-QIZFF7 1
3 ARCT-0037000000ewipk 9
3 2-PIZFF6 6
3 2-QIZTF7 1
View 9 Replies
View Related
Oct 16, 2012
I have a column COL1 in table TAB1 which is varchar2. I want select only rows which has number and not alphanumeric value? I don't want to use regexp for this since
View 11 Replies
View Related
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
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
Nov 12, 2012
I have created a form that have a button to check the character in the textbox is alphanumeric and symbol. Here attached the code, that i tried from other post.
View 5 Replies
View Related
Jul 17, 2011
I am using Exception when others then body end;
inside the body i can get SQLCODE and SQLERRM but I also need to get the SQL statement that caused the error and I dont know how.
View 39 Replies
View Related
May 19, 2011
The following code is working fine,But the thing is if column already exists in the table,then also the other statements should be executed instead of coming out of procedure.SO how can I handle that exception??
SQL> CREATE OR REPLACE PROCEDURE sp_execparameters(tname IN VARCHAR2,
colname IN VARCHAR2,datatype IN VARCHAR2)
2 AS
3 v_sqlstr1 VARCHAR2(1000);
4 BEGIN
5 v_sqlstr1 := 'alter table '||tname||' add '||colname ||' '|| datatype ;
[code].........
View 3 Replies
View Related
Sep 16, 2010
1 declare
2 cursor c1 is select * from emp where deptno=&no;
3 begin
4 for i in c1 loop
5 dbms_output.put_line(i.empno||' ' || i.sal);
[code]...
PL/SQL procedure successfully completed.
SQL> /
Enter value for no: 120
old 2: cursor c1 is select * from emp where deptno=&no;
new 2: cursor c1 is select * from emp where deptno=120;
PL/SQL procedure successfully completed.
Even though deptno 120 is not there in emp table, the exception is not raising?
View 4 Replies
View Related
Jul 16, 2012
Is it possible to take execution control back from exception handling section to Execution statement?.. If Yes then How?..
View 25 Replies
View Related
Nov 4, 2010
I have a script that I want to add some sort of code that will allow for a Y or N input from the user.
Example: ** This script is going to drop your database, Do you want to continue? **
If the user puts Y, the database will begin to drop. If the user puts N, the script will exit.
Would Accept work for this or is their something better?
View 3 Replies
View Related
Jan 27, 2011
I want to get input of employee name from user, then select salary of that user & display. I have written following code but system shows error.
****************************************
create or replace procedure test is
number1 NUMBER(10);
number2 NUMBER(2) := 17;
text1 VARCHAR(12) := 'HELO WORLD';
text2 DATE := SYSDATE;
text3 VARCHAR(5);
BEGIN
text3:='&text3';
select sal
into number1
from emp
where ename=text3;
dbms_output.put_line(text1||' today on '||text2||' salary of Mr.'||text3||' is='||number1);
end test;
**************************************
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SCOTT.TEST", line 10
ORA-06512: at line 1
View 3 Replies
View Related
Jan 22, 2007
How to write an exception handler for the error "PL/SQL: ORA-01031: insufficient privilege"
View 2 Replies
View Related
May 9, 2011
Am getting the error ORA-03113: end-of-file on communication channel when am trying to run a query and when i checked the trace file the infromation i got is given below.....
CODEksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [0x363376E] [] [] [] []
Current SQL statement for this session:
select
count(av_sal)
[code]...
View 1 Replies
View Related
Jan 14, 2008
I am using Oracle 10g Rel 2 and currenly working on a project which creates a repository and bulk inserts data into it using FORALL statment. I am using SAVE Exceptions to save the errors in a table and then report to the user about these errors.
My question is ,can i somehow know the column which caused the exception ? Currently ,we can save only SQLCODE and SQLERRM . Is there any possibility that I get to know the column also which raised the exception ? For example ,during a insert ,if column raises exception
ORA-01438: value larger than specified precision allows for this column
Is it possible for me know using some programming technique that which column raised this exception ?
View 12 Replies
View Related
Jan 11, 2005
I'm trying to connect to multiple Oracle 10g databases (failover) over the JDBC thin client, and when I run the following, I get: java.sql. SQLException: Io exception: NL Exception was generated
connection = DriverManager.getConnection("jdbc:oracle:thin:@DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = dbdev) (PORT = 1521))) (CONNECT_DATA = (INSTANCE_NAME = orcldev)))", "scott", "tiger");
I eventually want to put multiple ADDRESSes in that string, but right now I'm trying to get it to work with just one! I've tried multiple variations of this, like changing INSTANCE_NAME to SID or SERVICE_NAME, and using the internal IP address for the HOST dbdev (see below). Here's the tnsnames.ora file used by SQL*Plus on the same client machine:
ORCLDEV.world =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.132)(PORT = 1521))
(CONNECT_DATA =
(SID = orcldev)
)
)
View 7 Replies
View Related
Sep 22, 2010
i cant trace out why this exception is for Below given is my scripts create or replace package body pkg_rwc_migration as
procedure pro_rwc_mig as
cursor cur_mig_job is select substr(job_id,1,4) "JOB_ID",5 "JOB_STAGE_ID",gii_app_id "GII_APP_ID",
gai_app_id "GAI_APP_ID",status_id "STATUS_ID",job_type "JOB_TYPE",
'jobdesc' "JOB_DESC",current_assignee "CURRENT_ASSIGNEE",CREATED_DATE "CREATED_DATE",
CREATED_BY "CREATED_BY",MODIFIED_DATE "MODIFIED_DATE",MODIFIED_BY "MODIFIED_BY",
IS_COMPLETE "IS_COMPLETE",OVERWRITTEN_BY "OVERWRITTEN_BY",OVERWRITTEN_DATE
[code]...
when others then
v_error_code := SUBSTR(v_error_code||':'||SQLCODE,1,30);
For i in 1 ..sql%bulk_exceptions.count LOOP
v_error_desc := SUBSTR( SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE),1,400);
dbms_output.put_line('The value of Error is '|| v_error_code ||' '||v_error_desc);
End loop;
[code]...
By executing the above i am getting Error
SQL> @pkg_rwc_migration_bdy.sql
130 /
Package body created.
SQL> Begin
2 pkg_rwc_migration.pro_rwc_mig;
3 End;
4 /
The value of Error is :-1843 -1843: non-ORACLE exception
View 2 Replies
View Related
Feb 20, 2012
Can we have any exception in DECLARE block (shown are just for example)?
Declare
exception occured
begin
code
end;
declare
variables;
begin
declare
variables;
begin
code;
end;
end;
View 6 Replies
View Related
Nov 17, 2011
I trying to Assign XML content to the clob variable inside the pl/sql block, But i am getting the Below Error:
declare
t clob;
begin
t := 'xml content exceeds 32000 characters'
update test
clob_cloumn = t;
where id =2;
exception
when others then
null;
End;
ORA-06550: line 5, column 4:
PLS-00172: string literal too long
I need to handle this exception, i know it length exceeds 32000 characters, but even though i need to handle the exception and to perform other operation after handling the exception.
View 5 Replies
View Related
