PL/SQL :: Trapping Errors In Stored Procedures?

Feb 27, 2013

I have to run queries on a gov't computer. There are errors in some of the stored procedures that will NEVER go away. Fact of life, and I have to live with it (I'm just a data analyst--not the developer of the procedures)So I do this, in the hope of trapping the error

BEGIN
run_stored_proc (my_cursor, my_id, param3);
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('ERROR IN ID (' || my_id || '). The error was ' || SQLCODE || ' -ERROR- ' ||SQLERRM);
END;Well, it can still crash.
SQL> @c:mysql
un_test
ERROR IN ID (2692). The error was -20100 -ERROR- ORA-20100: Exception occurred in some_other_stored_procedure

ORA-01422: exact fetch returns more than requested number of rowsSo it looks like run_stored_proc calls some_other_stored_procedure which is having issues. I was hoping that by trying to capture the error in the top level procedure, it would also capture the error in a lower-level "sub-procedure". This is the way a TRY-CATCH in java works. If an error happens anywhere along the way inside the TRY-CATCH, it is caught. Sure, PL/SQL ins't java, but is there a way for me to catch a lower level error and not have this code crash?

Fortunately for me, it actually doesn't crash that often. But I have to put this code in a loop, and run it for many different my_id's. And when it does crash, it isn't significant from a data analysis standpoint. I can't do without a small percentage of the data. But I'd love to be able to just run my script and not have to watch it for unexpected crashes.

View 11 Replies


ADVERTISEMENT

SQL & PL/SQL :: Saving Procedures With Compilation Errors

Feb 21, 2010

When creating objects, is there a way telling Oracle not to store(overwrite) procedures or other DDL in the database until the statements have no compilation errors?

View 4 Replies View Related

SQL & PL/SQL :: IF ELSE ENDIF On Stored Procedures?

Apr 8, 2011

I come from a Microsoft SQL background and am having trouble adjusting to the PL/SQL syntax. I'd like to build this up to pass back a certain value back into a web form.

IF (SELECT COUNT(*) FROM EXCURSION WHERE EXCURSION_ID = 75)= 1 THEN
DBMS_OUTPUT.PUT_LINE 'True';
ELSE
DBMS_OUTPUT.PUT_LINE 'False';
END IF;

I'm having trouble writing values out at the moment

View 7 Replies View Related

Where Server Side Procedures Are Stored

Jun 2, 2009

Where are the server side procedures are stored? Where are the server side procedures are visible??

View 5 Replies View Related

Stored Procedures Using Oracle Tables

Jul 22, 2010

Let us say there is an oracle table A. How do i find out what stored procedures/packages/views are using this table A.

Reason to know: When some DDL changes happen on table A, how do i know what are the impacted sp/pkg/views which should also be modified. Is there any query with which i can find this?

View 5 Replies View Related

PL/SQL :: Java Stored Procedures And Triggers

Oct 31, 2012

I want to to use Java stored procedures and triggers within the Oracle 11g XE ?

View 3 Replies View Related

SQL & PL/SQL :: Why Do Stored Procedures And Functions Improve Performance

Feb 10, 2012

Why do stored procedures and functions improve performance?

A. They reduce network round trips.
B. They reduce the number of calls to the database and decrease network traffic by bundling commands.
C. They reduce the number of calls to the database and decrease network traffic by using the local PL/SQL engine.
D. They allow the application to perform high-speed processing locally.
E. They postpone PL/SQL parsing until run time.

I think the answer should be A and B but i came across answers as B and E Can u explain me what is the difference between option A and B and does it postpone parsing till run time?

View 1 Replies View Related

SQL & PL/SQL :: Bind Variables / Developing Stored Procedures In Developer

May 16, 2010

Am using Oracle 9i and developing stored procedures in SQL Developer. Have a situation where one stored procedure A is calling another B. Stored proc B builds the SQL based on parameters passed in. For one of the parameters i would like to use a bind variable in the SQL that proc B builds. The bind var is passed back to proc A as a part of the SQL string. In proc A, i then try to bind that variable to a parameter(value), however, the bind does not seeem to work.

The SQL string contained in v_SQLStatement defined as VARCHAR(4000) that is passed back to proc A looks like:

SELECT em.event_title AS event_name,
e.start_date AS start_date,
e.end_date AS end_date
FROM d_event_ml em
inner join d_event e
ON em.event_id = e.event_id
WHERE em.language_id = 46
AND e.end_date >= SYSDATE
AND e.stream_id IN ( :v_x1 )
AND e.event_id IN (SELECT event_id
FROM d_events_seas
[code]....

and o_EventList is defined as REF CURSOR. i'm experiencing is that :v_x1 stays as :v_x1 and does not change.This is my first attempt at using bind vars. URL....

View 7 Replies View Related

Client Tools :: How To Convert SQL Server Stored Procedures

Sep 3, 2013

In SQL server 30 stored procedures are there how to convert all the stored procedure from SQL server to Oracle 11g..

View 5 Replies View Related

SQL & PL/SQL :: How To Call Stored Procedures Or Functions From A Database Trigger

Oct 14, 2011

How to call stored procedures or functions from a database trigger?

View 1 Replies View Related

SQL & PL/SQL :: Pass Table As Input Parameter In Stored Procedures?

Feb 22, 2011

i need to pass table as input parameter in stored procedures. during the run time, i am getting error

CREATE OR replace TYPE emp_type IS OBJECT (
id NUMBER(4),
ename VARCHAR2(10));
CREATE OR replace TYPE emp_type_tab
IS TABLE OF EMP_TYPE;
CREATE OR replace PROCEDURE Test_proc (in_emp_type IN EMP_TYPE_TAB)
AS
BEGIN
FOR i IN 1.. in_emp_type.COUNT LOOP
dbms_output.Put_line(in_emp_type.Id(i));
END LOOP;
END;
/

View 10 Replies View Related

Advantages / Differences Of Methods Defined Types Over Stored Procedures

Aug 31, 2011

We are trying to use the methods/constructors in the object types and find it more similar to the procedures and functions in the packages. I am wondering how they are different from stored procs and functions and what are the advantages?

View 2 Replies View Related

.NET Stored Procedures :: How To Send Execute String To Oracle Database

Oct 31, 2013

I would like to send a raw command to the Oracle server. from .NET (System.Data.OracleClien), but I got invalid statement (ORA-00900) exception

.Excecute string:string SQL = "EXECUTE SP_THREADFILE_INSERT('" + Guid.NewGuid() + "','" + filename + "',utl_raw.cast_to_raw('" + content + "'))";

View 0 Replies View Related

Reports & Discoverer :: Calling Reports From Stored Procedures?

Feb 23, 2011

Is there a way to call an oracle report (version 9i) from a stored procedure?

View 2 Replies View Related

Server Utilities :: Trapping SQL Loader Summary Counts

Jun 30, 2011

I have got another curly one with regard to Sql Loader. My original issue via [message #513696] was resolved. In summary, SQL Loader opens a comma delimited csv file and it is able to successfully populate the appropriate Oracle table. However, during the run to process each unique csv file, SQL Loader produces a both a .log file (indicating what transpired during the load) and a .bad file of the records from the csv file that where rejected by SQL Loader.

the users have requested that "is it possible to update a separate table of the summary results of what went wrong and what was right?" It just so happens that the csv file contains some summary records (rows) with processing audit infor...as the following .bad file wrote out....
___________________________
ModuleID,ModuleSN,Lat,Lon,GMT Time,Tag Count,GMT Date,Client,Farm,Field,Variety,Machine
No Module ID ,No SN ,-30.178958,149.63844,9:55:58,0,21/04/2011,101,Widgen ,9,71BRF ,5987
3500B9880611170260179BFB,10202094587,-30.199379,149.661333,7:10:35,2,22/04/2011,101,Widgen ,30,71BRF ,5987
,,,,,,,,,,,
Total Modules,1602,,,,,,,,,,
Mis-reads,1,,,,,,,,,,
Mis-read %,0.06,,,,,,,,,,

1. The first record (starting in the 1st postion is "ModuleID") is a heading record in the csv file and can be disregarded.
2. The secord record (starting in the 1st postion is "No Module ID") is an error record in the csv file and can be disregarded.
3. The third record (starting in the 1st postion 3500B9880611170260179BFB, ) is a valid record, however, is was rejected as it is a duplicate of a preceeding record ...and the log indicates ...

Record 124: Rejected - Error on table PTLIVE.MODULE_CSV_LOADS.
ORA-00001: unique constraint (PTLIVE.MODULE_SERIAL_NUMBER_UK) violated

4. The fourth record (starting in the 1st postion is ,,,,,,,,,,,
) is a blank record in the csv file and can be disregarded.
5. The fifth record (starting in the 1st postion is "Total Modules" has a value of 1602 which I need to capture to write into a separate table.
6. The sixth record (starting in the 1st postion is "Mis-reads" ) has a value of 1 which I need to capture to write into a separate table.
7. The last record in the csv file and can be disregarded.

The summary Oracle table is
CREATE TABLE PTLIVE.SUBMODULES_SUMMARY
( IMPORT_CSV_FILE_NAME VARCHAR2(256 ),
TOTAL_SUBMODULES_LOADED NUMBER (5),
TOTAL_SUBMODULES_MIS_READ NUMVER (5),
DATEIMPORTED DATE default SYSDATE
)

Now I would like to trap the value 1602 and load that into the Oracle column TOTAL_SUBMODULES_LOADED and the value 1 and load this into TOTAL_SUBMODULES_MIS_READ.

The name of the csv file is known and I can (as the previous [message #513696] resolved) simply use a SQL Loader control file command > IMPORT_ CSV_FILE_NAME CONSTANT "Batch_2011Jun29_TEST.csv".

View 2 Replies View Related

How To Use Member Procedures

Dec 13, 2009

create type employee is object(
name varchar2(30),
member procedure change_name(new_name varchar2)
)

[Code]....

Now how to use the change_name procedure to change the name of the employees in the table?

View 1 Replies View Related

PL/SQL :: Drop All Procedures At One Go?

Aug 29, 2012

I have a schema sys_eg .I dont have any DBA privileges .How can i delete all the procedure with one sql or pl/sql.

ORACLE 11G
I am using sql developer 3.2 version tool

View 6 Replies View Related

Monitoring Runtime Of Procedures

May 23, 2013

runtime monitoring procedures....need a sample script

View 1 Replies View Related

Executing WHOLE Package (of Procedures) At Once?

Aug 30, 2007

I could execute a package for eg if i had a package with procedures related to statistics and i run them each night, could i just do an exec on the package and it would run all those procedures??

Its not possible but i could call each procedure from ONE procedure

View 2 Replies View Related

Procedures Are Not Running Paralleled?

Mar 17, 2011

I have a job that runs two procedure parallely . The code is as follows:

DECLARE
jobno1 NUMBER;
jobno2 NUMBER;
flag NUMBER;
BEGIN
dbms_job.submit(jobno1,'pc;', next_date=>SYSDATE);
dbms_job.submit(jobno2, 'pc2;', next_date=>SYSDATE);

[code]....

And the stored procedure (in SQL File) is as follows:

procedure pc:

create or replace procedure pc
as
begin
for idx in 1..100 loop
dbms_output.put_line(idx);
end loop;
end;
/

procedure pc2:

create or replace procedure pc2
as
begin
for idx in 1..1000 loop
dbms_output.put_line(idx);
end loop;
end;
/

However, when I execute the job , I get the following output:

1
2
3
.
.
.
.
100

[code]....

Which indicates the job 1 got executed 1st and then job2. But this is not what I want.

View 4 Replies View Related

Modifying Table Through A Procedures?

Aug 28, 2007

whats the best way to modify a table via a procedure for example if i have a table with records: id, fn, ln, address, tel, dob

how would i go about creating a procedure that modifies a record. The problem is what param needs to go in? i could certainly have different procedures that modify different things like one for modifying tel, one for modifying address, but is there a way to combine it all into ONE procedure so that if user wanted he could put in exec modifyTable(2, 099898787) to modify tel and then modifyTable(2, 'a new address') to modify address... ?

View 6 Replies View Related

SQL & PL/SQL :: List All Procedures Running On DB?

May 4, 2010

we are doing a performance tuning on a large database. Our main aim is to capture all the packages/procedures/view that are running on database. Below given is the format we have prepared

Eg:
SPIdSP_Name CPU TimeI/OLine Executed
1Emp_AllDetails221
1Emp_AllDetails582
1Emp_AllDetails25103

[code]...

Requirement:Write an Procedure that captures the above given information in the background of database and stores the information in a table.write the procedure to capture the above given information.

View 17 Replies View Related

SQL & PL/SQL :: Call Procedures In Cycle?

Jul 21, 2011

I've got some quantity of procedures in package

procedure1
procedure2
...
procedureN

I should call them in cycle instead of typing each of them manually. I know it's a bad idea to do something like this "dynamically".

View 13 Replies View Related

PL/SQL :: Exception Handling In Procedures

Oct 8, 2012

I am using for writing text files data to database. The problem here is let us assume there are 6 records in text file and if there is a problem at 2nd record, the later records are not getting inserted.

CREATE OR REPLACE PROCEDURE PROC1
IS
temp varchar2(500);
tmp_name varchar2(5);
tmp_no varchar2(4);
.
BEGIN
WHILE NOT end_of_file
LOOP

IF i = 18 THEN
tmp_no := temp;
END IF;

IF i = 21 THEN
tmp_name := temp;
END IF;

END LOOP;
END;
/If i=18 and temp = '12345' here, then tmp_no := temp; won't work (tmp_no varchar2(4);)
Similarly, If i=21 and temp = 'ABCDEFG' here, then tmp_name := temp; won't work (tmp_name varchar2(5));

how to handle this through EXCEPTIONS so that even if there is a problem with 1 record, while loop remain working for further records..

View 4 Replies View Related

PL/SQL :: How To Create A Package With Procedures

Aug 15, 2013

By referring through net I have written the following PCK file. I have installed my Oracle 11g database in VirtualBox(Win XP). I can able to select the tables using query from the host(Windows 7) 

CREATE OR REPLACE PACKAGE APEX_SYS_PCK  AS    TYPE refcursortype IS REF cursor  PROCEDURE GET_USER_DETAILS(  D1 OUT refcursortype,  P_USER IN VARCHAR2,  P_PASS IN VARCHAR2);END APEX_SYS_PCK; / CREATE OR REPLACE PACKAGE BODY APEX_SYS_PCKAS PROCEDURE GET_USER_DETAILS(  D1 OUT refcursortype,  P_USER IN VARCHAR2,  P_PASS IN VARCHAR2) IS BEGIN   OPEN D1 FOR SELECT * FROM APEX.PERSONS; END GET_USER_DETAILS;  END APEX_SYS_PCK;/

 When I try to run the script I am getting the following error. How to solve this problem?

Project: sqldev.temp:/IdeConnections%23vAPEX.jprF:PACKAGESAPEX_SYS_PCK.sqlError: PL/SQL: Compilation unit analysis terminatedError(1,14): PLS-00905: object APEX.APEX_SYS_PCK is invalidError(1,14): PLS-00304: cannot compile body of 'APEX_SYS_PCK' without its specification

View 9 Replies View Related

Privileges Of Create Trigger - Procedures?

Aug 16, 2013

i want to give privilege of create trigger,procedures and functions privileges to a user "A"on the schema "B". how can i do it. i've already given select,insert,update,delete privilege to user "A"

how can user "A" create trigger(etc.) on tables of user "B".

View 1 Replies View Related

SQL & PL/SQL :: How Many Procedures And Functions Can Be Written In Package

May 4, 2010

I think there is no limit for number of procedures and functions can be written in a package, but little much confusion.

What is the maximum number of Procedures and Functions can be written in a package? is such type of limit is there or not ?

View 4 Replies View Related

SQL & PL/SQL :: Renaming Column In Multiple Procedures?

Jun 2, 2010

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

Is there any way to rename a column in a table A, so that it is changed wherever it is used, like all the procedures that uses this column, or other tables that has reference to this table A?

View 5 Replies View Related

SQL & PL/SQL :: Calling Other Procedures From Autonomous Procedure?

Feb 26, 2013

It's somewhat related to my previous post DBMS_SQL Usage But here I tried to capture whole scenario.

Scenario is like below:

create user a identified by a;
grant connect,resource to a;
create user b identified by b;
grant connect,resource to b;
create user c identified by c;
grant connect,resource to c;

[code]....

Not getting why the schema is not changing... and how to resolve the error.

View 8 Replies View Related

PL/SQL :: List Of Unused Tables And Procedures

Aug 28, 2012

I am in the task of clean up of tables. I need to find the list unused tables and procedures. Is there any way where i can find when was the last time the table queried?

Give sql query to find the list of unused tables and procedures.

View 6 Replies View Related







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