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


ADVERTISEMENT

.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

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

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 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

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

ODP.NET :: Connection Open But No Tables Views Or Procedures?

Oct 5, 2012

Going through my first attempt ever to connect to a Oracle database with a .Net application. Using Visual Studio 2012, I have downloaded and installed the latest version of odp.net, actually both the 32 and 64bit versions.

Application is targeting the 4.5 Framework, Latest version of Entity Framework installed (but not important I think at this point) Added a reference in my project to the Oracle Data Provider ( tried with both 2.0 and 4.0 )

Created a connection string with EZ Connect, stored in web.config file. I have tried 2 different projects to use EF and one without. Trying to create a edmx or using a datasource adapter brings up the connection wizard or prompts to use the existing one, but no matter what I try, there are no tables, views or procedures listed.

From Server Explorer, I can open the Data Connections and verify that the connection to the Oracle database is Open. During the Connection Wizard, there were no errors, and all the databases available were listed. Yet, connecting to and opening the connection to this specific database or anyone for that matter no tables, views or procedures are listed.

View 14 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

SQL & PL/SQL :: Create Stored Procedure Where Tables Of Two Different Databases Are To Be Used?

Feb 22, 2012

I want to create a stored procedure where tables of two different databases are to be used.

View 4 Replies View Related

API For Deleting Bank Statements Stored In Tables

Nov 20, 2012

if an API is available for deleting Bank statements stored in tables CE_STATEMENT_HEADERS and CE_STATEMENT_LINES.

View 3 Replies View Related

SQL & PL/SQL :: Which Projects Stored In Tables Not Updated On Consecutive Days

Aug 7, 2013

I have a table (tblProject) with the following fields:

Project_ID, Project_Name, Update_Date

I need to look back over the last week to find out which of the projects stored in the above tables haven't been updated on consecutive days day.

For example i would expect to see:

14, Streamline, 01/08/2013
14, Streamline, 02/08/2013
14, Streamline, 03/08/2013
14, Streamline, 04/08/2013
14, Streamline, 05/08/2013
14, Streamline, 06/08/2013
14, Streamline, 07/08/2013
15, Bonus, 01/08/2013
15, Bonus, 03/08/2013
15, Bonus, 04/08/2013
15, Bonus, 05/08/2013
15, Bonus, 07/08/2013

The code should identify that the 'Bonus' project missed updates on 02/08/2013 and 06/08/2013. Should be a simple enough piece of code for an experienced developer i'm sure

View 5 Replies View Related

SQL & PL/SQL :: Stored Procedure / Extract Data From Excel To Multiple Tables

Oct 14, 2010

This is my first time running a stored procedure. The procedure is already written.

We have various related table. I need to use this stored procedure and extract information from an excel sheet into the multiple tables.

View 8 Replies View Related

SQL & PL/SQL :: Build A Stored Procedure Which Compares Specific Fields In 2 Tables

Jul 24, 2013

I have table A.Security:

CouponIssue_nameQuantity_typeCurrencySec_TypeSec_alias
1 a 56 USD AB 676
2 b 45 INR AB 143
3 c 32 JPY BA 200

and Table B.Security:
CouponIssue_nameQuantity_typeCurrencySec_TypeSec_alias
1 a 33 USD OB 676
9 b 21 NZD AC 143
8 c 19 GBP CA 200

build a Stored Proc which compares sepecific fields in the 2 tables and reports into a new table as:

A.Security.sec_aliasB.Security.sec_aliastable fieldA-valueB-value Compare
676 676 Security Currency USD USD Match
143 143 Security Currency INR NZD Not-Match

Fields to be compared are:
Quantity_type
Currency
Sec_type

The join between the tables A and B can be made on Sec_alias.Also, this is just a single pair of tables. Further I need to compare 5 more pairs of tables after this and report out of the new table.

View 23 Replies View Related

Print Out All Triggers / Procedures From Oracle 9iForms

Dec 15, 2011

I need to print out all the triggers/procedures from an Oracle 9iForms. When I open an Oracle 9iform in the Oracle form builder, I click on FILE/CONVERT but cannot see the triggers/procedures.

View 2 Replies View Related

SQL & PL/SQL :: Schedule Multiple Procedures At Morning On Oracle 10g

Nov 14, 2008

I have 4 stored procedures where i need to schedule these four procedures at morning 6:00am in my Oracle Database. Whether all the four procedures can be executed at same time.

View 39 Replies View Related

Implementation Where Data From DB2 Tables Are Moved To Oracle Tables

Sep 3, 2012

I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?

View 1 Replies View Related

How To Execute A Stored Procedure In Oracle

May 22, 2012

how to you execute a stored procedure in ORACLE..For example in SQL SERVER its just

EXEC Proc_Name ParameterValues

How the hell do you do this in oracle i just want to test if my stored procedure works.

View 1 Replies View Related

Oracle 8.5.1 - Stored Procedure Not Added To DB

Mar 21, 2007

After compiling a simple valid SP in Toad for Oracle 8.5.1:

CREATE OR REPLACE PROCEDURE proc_test
AS
DECLARE v_name VARCHAR2(20) := 'Ed Edson';
BEGIN
DBMS_OUTPUT.PUT_LINE('Hi, my name is ' || v_name);
END;

The SP is not added to the schema. Also the three status fields (status, created and Last Update in the Procedure Editor remain <Unknown>. It is not a rights or privilege issue, because the creation works perfectly in SQL*Plus!

View 1 Replies View Related

SQL & PL/SQL :: XML Data Stored In Oracle Table

Nov 8, 2010

I have a XML data that needs to be stored in oracle table and it is read from a java application. what datatype will suit for XML storage varchar2 or CLOB. The length of XML will be less than 4000.

View 4 Replies View Related

Create A List In Oracle Stored Procedure?

May 14, 2013

How to create a list in oracle stored procedure and that list contains two columns.

View 1 Replies View Related

Oracle 10g - Create A Stored Procedure Without Parameters

Oct 11, 2010

Iam using oracle10g . when i created a simple stored procedure,got an error

PLS-00428: an INTO clause is expected in this SELECT statement

here is my code

create or replace procedure sp_TrialLiswt
as begin
select * from mivrs_studyinfo;
end;

View 1 Replies View Related

Pass Array To Oracle Stored Procedure?

Oct 3, 2010

I am passing a string from vb.net through oracle store procedure. String consists of varchar values for eg '123456;I|N,2424424;O|A'. But how can i call these string values into array using oracle stored procedure? After getting these values into array , i wanted to loop through these values.

View 1 Replies View Related

SQL & PL/SQL :: Execute File In Oracle Stored Procedure

May 16, 2012

I need to execute a .sql file in an oracle stored procedure.

View 9 Replies View Related







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