Change Code To Make Exception Print When No Data Is Found?
Feb 28, 2011
I have the following chunk of code, which could return no rows, one row or many rows. When it returns one or many rows the DBMS_OUTPUT.PUT_LINE prints out my values. When no data is found 'Hello' is not printed?
Suggest a way I can change the code to make the exception print when no data is found?
SET SERVEROUTPUT ON;
BEGIN
FOR prec IN ( select * from xxx.part_maint where drop_partition = 'Y' )
LOOP
DBMS_OUTPUT.PUT_LINE (prec.SCHEMA_NAME || ' ' || prec.OBJECT_NAME);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('hello');
END;
View 1 Replies
ADVERTISEMENT
Jul 7, 2012
My cursor went to the exception when its got the error " ORA-01403: no data found"
But my cursor need to run even though it got the error " ORA-01403: no data found"...This is my exception part
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (c1rec);
DBMS_OUTPUT.put_line (SQLCODE);
DBMS_OUTPUT.put_line (SQLERRM);
-- RETURN NULL;
-- code change added as part of GCA642 -- END
END get_geo_test1;
View 14 Replies
View Related
Feb 13, 2012
I downloaded oracle sql developer, i type my code into a worksheet but if i use the run statement option, it asks me to make a connection. I dont want to make a connection, just test the data locally.However, even if I do try and make a connection, i get ora-12560 error (local connection).
I just want to type up some data to make some table and test to retrieve or manipulate the data. I'll use any program, command line or gui.
View 7 Replies
View Related
Dec 21, 2010
would there be any code change for spatial data after migration from 10g to 11g? In case of upgradation from 10R1 to 10R2, there were lot of issues regarding the spatial data code.
know beforehand so that it should not affect the service.I have already done the migration from 10g to 11g by 2 ways succesfully.
View 1 Replies
View Related
May 27, 2011
how to print cursors? I mean like columnname : value. of couse curosr.columnname gives what I want. but I want genrally used way.
CREATE OR REPLACE PROCEDURE PROC_TEMP
IS
BEGIN
FOR C1 IN ( SELECT sbjct_id,sbjct_step
FROM RND.SBJCT_ORDR M) LOOP
DBMS_OUTPUT.PUT_LINE('c1 : '||c1);
END LOOP;
END;
View 3 Replies
View Related
Feb 16, 2011
I have a functional index.. which doesn't allow duplicate based on condition.I want to catch the exception and print the customized message but it always override my message.
create or replace function i_func
(tt_id in number,
d_date date,
stat in varchar2)
return varchar2
[code].....
output: ORA-00001: unique constraint (abb.TEST_IDX) violated
View 10 Replies
View Related
Oct 4, 2012
how can i put button in a form to print the information i displayed in this form.
View 1 Replies
View Related
Sep 5, 2011
format your code (Instant SQL Formatter is here) and - before submitting a message - enclose it into the [code] tags.Note the difference; see how the formatted code is easier to read?
View -1 Replies
View Related
May 22, 2013
I have an Image Type on a forum page. I want a default "not-found" image to display if the BLOB column value is null or if there is no data for that search value. The image is stored with the app: #APP_IMAGES#not-found.png
APEX 4.2 (with listener) on Oracle 11gR2
View 10 Replies
View Related
Jan 30, 2012
My environment is setup like this.
+ASM instance and 3 DB instance.
DB_US
DB_Aus
DB_Gulf
there is just single listener running from +ASM binary handoff connection to databases .i have individual tnsnames.ora for each DB $TNS_ADMIN. this query work from server ,since i have REMOTEDB tns in DB_US $TNS_ADMIN.
SCOTT:DB_US> select sysdate from dual@REMOTEDB;
-- same query fails from a remote client.eg laptop.
SCOTT:DB_US> select sysdate from dual@REMOTEDB;
select sysdate from dual@REMOTEDB
*
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified
-- issue is resolved when i add REMOTED tns entry to +ASM tnsnames.ora
now question is , Can i make any configure change in +ASM side so that if it dont find a tns in its TNS_ADMIN, it should look for it in DB_US $TNS_ADMIN . eg : multiple TNS_ADMIN path, or ifile etc..
View 1 Replies
View Related
Sep 14, 2013
We are converting WE8ISO8859P1 oracle db character set to AL32UTF8. Before conversion, i want to check implication on PL/SQL code for byte based SQL functions.
What all points to consider while checking implications on PL / SQL code? I could find 3 methods on Google surfing, SUBSTRB, LENGTHB, INSTRB. What do I check if these methods are used in PL/SQL code?
What all other methods should I check? What do I check in PL/SQL if varchar and char type declarations exist in code? How do i check implication of database character set change to AL32UTF8 for byte bases SQL function.
View 1 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
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
Oct 8, 2012
How to print data between two dates... startdate and enddate inclusive
View 2 Replies
View Related
Mar 24, 2011
I have this SQL statement:
WITH data
AS (SELECT user_id,
jc_name,
[Code]....
I wish to do something like
if results > 10 print an error message (and no results)
if results < 10 print the results/output
View 1 Replies
View Related
Sep 11, 2013
I want to print data from a clob in XML format and use the following
PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is
xmlstr varchar2(32767);
line varchar2(32767);
cnt NUMBER;
[Code]...
However the length of the clob is 13832630 which is too large for a VARCHAR2. Thus my output of line is cut off at 4000 characters. How can i increase this... Do i overlook something here in my code?
View 13 Replies
View Related
Sep 24, 2010
I have a table emp having data as
empno ename sal deptno
1001 manoj 1000 10
1001 sachin 2000 20
1003 manoj 30000 30
Now I wanted to write a query which will display the results like below. Row data need to be display in the columns
empno ename sal deptno 1001 manoj 10 20 1003 30
1001 manoj 1000 10 1001 manoj 10
1001 sachin 2000 20 1001 sachin 20
1003 manoj 30000 30 1003 manoj 1003 30
How to print the rows into column data?
View 1 Replies
View Related
Feb 3, 2012
What I am trying to do is print out page that displays all of the column titles and the data under them for a query given by the user. It is then going to be put into an excel spreadsheet.
I've done this before with Java, simply by using the getMetaData function, but I can't seem to find an alternative for PL/SQL. It seems at the very least I need to know the number of columns in a query, but that would defeat the purpose of this.
Is what I am trying to do even possible or is knowing the column names an absolute necessity when printing table data?
View 9 Replies
View Related
Nov 15, 2010
How similar is PL/SQL to SQL? I'm trying to retrieve all data from a customer table and print out each customer information one by one:
DECLARE
CURSOR all_customer IS
SELECT *
FROM customer;
BEGIN
DBMS_OUTPUT.PUT_LINE(all_customer);
END;
but I really don't know the correct syntax. The Powerpoint slides the professor gave is not useful at all.
View 13 Replies
View Related
Aug 10, 2010
I have a problem with a package that generate reports.
ORA-01403: no data found
This is the first error rule.
p_pte_tab( r_sales.product_type ).shipped_units := p_pte_tab( r_sales.product_type ).shipped_units + r_sales.shipped_units;
From the next procedure (the whole package is a bit too large to post) :
procedure sales_report_2hnd( p_running_date date )
is
cn_procname constant varchar2(61) := 'sales_report_2hnd';
t_error_rec bol_exceptions.error_rec_type;
cursor c_rpt
is
[code]....
I thought there was a NULL record for shipping units but thats not the case.
View 5 Replies
View Related
Aug 3, 2012
I'm using Oracle database10g.
I have a table called T1 with column C1
ORA-01403: no data found.
I know when I create a trigger I reference the new and old values using :NEW.C1 & :OLD.C1.
What about of the value is not changed and I still want access it what is the syntax.
View 2 Replies
View Related
Dec 8, 2011
I'm making PL/SQL function. Slice of
dbms_lob.append(temp_clob,to_clob(' <property>'||crlf));
fol:=lb_folios.get_chap1_sec1(folioid);
fetch fol into recid,rec_nr,text,shares,obj_size,monetary_sum,rec_status;
if fol%found then
dbms_lob.append(temp_clob,to_clob(' <chap1_sec1>'||crlf));
[Code]....
And I have error on "select jnl_id into jid" this line. And the error text is "no data found". And I know that there is no data in the table column jnl_id.
View 7 Replies
View Related
Aug 19, 2013
create or replace procedure set_update(p_name in varchar2,p_email in varchar2)is l_data user_details%rowtype; beginselect * into l_data from user_details ud where ud.name = p_name and ud.email = p_email; if l_data.name = p_name and l_data.email = p_email then update user_details set last_login_date = sysdate , date_updated = sysdate where name = p_name and email=p_email; end if; exception when no_data_found then insert into user_details(user_id,name,last_login_date,date_updated,date_created,email) values (l_data.user_id,p_name,l_data.last_login_date,l_data.date_updated,sysdate,p_email);end set_update;
This is my procedure will pass two parameters if it is available in table it will update other wise insert that data in table. My problem is while data is not available that data is inserted into table but that not inserted into table.
View 7 Replies
View Related
Apr 25, 2013
how can i print the stored images in reports 6i from database, based on the condition,that suppose if i have one field approval status whose flag is either 3 or 1 , based on this flag ,if the status is 3 then image should be displayed otherwise no. the rest of the process.
CREATE TABLE FT_GALVA (GALV_NO VARCHAR2(2),GALV_APPR_ST NUMBER,img_name varchar2(30),PIC_GALV BLOB)
Create or Replace directory image_dir as 'e:image_dir';
Grant all on directory image_dir to public
/* Formatted on 2013/04/25 23:50 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE insert_image_file (
p_id NUMBER,
p_status NUMBER,
[Code]....
View 2 Replies
View Related
Nov 27, 2008
In multimaster replication, I am getting the following error in the master sites.
ORA-01403: no data found
ORA-01403: no data found
But when i checked for the data, I found that the records are present.
View 6 Replies
View Related
May 4, 2010
In a procedure I try to make the following select :
select param_value
into vc_param
from cont_rp_serv where rp_serv_code = 36
and contract_id = 134
and rownum < 2;
But I receive ORA-01403: no data found.
If i run the select normally in a separate window in the same time I got the expected result : Enterprise.
I have checked that table is not locked by another user. Also i do not use triggers at all to get here.
View 4 Replies
View Related
May 10, 2013
We have the following trigger in database, whenever we try to insert the record in WIP_OPERATIONS , NO DATA FOUND exception has been thrown, when we debugged, we did not find any issue. The first select statement is getting failed even though there is the value coming for :NEW.wip_entity_id and when we execute the query separately in database with the :NEW.WIP_ENTITY_ID, its getting the value. What could be the reason? Can't we use SELECT Statement in AFTER or BEFORE INSERT trigger?why its throwing NO_DATA_FOUND Exception?
CREATE OR REPLACE TRIGGER sdm_brasil_wj_ops_iface_trg
BEFORE INSERT OR DELETE
ON WIP_OPERATIONS
REFERENCING
[code...
View 15 Replies
View Related
Dec 28, 2012
find the sample of code which is giving me the same error. whts wrong with below piece of code.
Programe
declare
cursor c1 is select * from x1;
l_x1 type1;
[Code]....
View 3 Replies
View Related
Dec 13, 2011
I have a stored procedure that is returning no data. I have read this is most common in stored procedures that use a SELECT INTO that tries to put a null in the variable.First, the stored procedure (from SQL Developer) then th execute and error.
PROCEDURE prc_add_address (addr_id OUT integer, addr_type_id IN integer, addr_line_1 IN varchar2,
addr_line_2 IN varchar2 := null, addr_line_3 IN varchar2 := null,
prov IN varchar2 := null, zip_id IN number,
country_cd IN varchar2 := 'USA', addr_start_date IN date,
addr_end_date IN date := null, changed_by IN varchar2,
changed_date IN date, channel_txt IN varchar2 := null)
[code]....
The sad conclusion:
Error starting at line 1 in command:
declare addrid integer := 0;
BEGIN
pkg_vic_person.PRC_ADD_ADDRESS (addrid, addr_type_id => 1, addr_line_1 => '351437 Tall Blvd', zip_id => 14906, addr_start_date => '01-FEB-2011', changed_by => 'RS', changed_date => sysdate);
[code]....
View 6 Replies
View Related
Mar 11, 2011
i got a prob in executing a query in my oracle.TRANSACTIONLIST is the table and my query is..
select * from TRANSACTIONLIST where USER_ID = '07751A1247'the table has 2 records with that user id.
But it is given result as "no data found".
View 4 Replies
View Related