SQL & PL/SQL :: How To Call Stored Procedures Or Functions From A Database Trigger
Oct 14, 2011How to call stored procedures or functions from a database trigger?
View 1 Replies
ADVERTISEMENT
SQL & PL/SQL :: Call Procedures And Functions In Database Triggers?
Oct 6, 2010Is it possible to call procedures and functions in Database Triggers?
View 1 Replies View RelatedSQL & PL/SQL :: Why Do Stored Procedures And Functions Improve Performance
Feb 10, 2012Why 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?
SQL & PL/SQL :: Triggers / Procedures / Functions Used As Prompts To Update And Modify Database
Apr 16, 2013I have my database structured to where i have my tables, constraints, and Rows populated in each table which is 4 tables in particular. for one I want to be able to List all four tables with its content and constraints all at once. What i want to with the PL/SQL is to be able to program a prompt to where the user can just enter the individual order information and the data goes to what ever appropriate column or Table it needs to go to. Now should I be mostly be using procedures, functions or triggers to make this possible.
View 9 Replies View Related.NET Stored Procedures :: How To Send Execute String To Oracle Database
Oct 31, 2013I 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 + "'))";
SQL & PL/SQL :: How Many Procedures And Functions Can Be Written In Package
May 4, 2010I 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 ?
SQL & PL/SQL :: How Many Functions And Procedures Exist In Package
Apr 6, 2011 how many functions and Procedures are exist in Package. Is it possible to identify through query.
Eg: I have one Package:- ABC. This ABC package having 2 functions and 3 procedures. I want, total no.of functions and total no.of procedures through query.
Determining What Procedures / Triggers / Functions Update A Table
Jul 2, 2010I'm trying to determine which procedures (in or out of packages), triggers or functions insert to or update particular tables in Oracle 10.2.0.4.I've got a far as looking at user_dependencies, and have written the following sql based on that:
CODEselect o.name, decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE',
8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY',
[code]....
This isn't how I'd write the code for production, it's just research code at the moment.
This tells me most of what I need to know, but it doesn't allow me to remove those procedures etc that just select from the table I'm interested in. The vast majority of the dependencies are just selects. In a previous role I used SQL Server 2000 and I could get this information from sysdepends, filtering on the resultobj column to remove the select only dependencies - so I'll be a little surprised if Oracle doesn't have this facility.
SQL & PL/SQL :: Call Procedures In Cycle?
Jul 21, 2011I'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".
Server Utilities :: Will Schema Export All Roles / Procedures / Packages / Synonyms / Functions And Triggers
Sep 21, 2010 i want to do a schema export from Database A. There are hundreds of users under this schema.I have to import this schema into other database say B. My question's are:
1) Do i need to pre-create only schema user or all the users under it.
2) Will the schema export all the roles,procedures,packages,synonyms,funsctions and triggers?
SQL & PL/SQL :: IF ELSE ENDIF On Stored Procedures?
Apr 8, 2011I 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
Where Server Side Procedures Are Stored
Jun 2, 2009Where are the server side procedures are stored? Where are the server side procedures are visible??
View 5 Replies View RelatedStored Procedures Using Oracle Tables
Jul 22, 2010Let 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?
PL/SQL :: Trapping Errors In Stored Procedures?
Feb 27, 2013I 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.
PL/SQL :: Java Stored Procedures And Triggers
Oct 31, 2012I want to to use Java stored procedures and triggers within the Oracle 11g XE ?
View 3 Replies View RelatedSQL & PL/SQL :: Bind Variables / Developing Stored Procedures In Developer
May 16, 2010Am 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....
Client Tools :: How To Convert SQL Server Stored Procedures
Sep 3, 2013In SQL server 30 stored procedures are there how to convert all the stored procedure from SQL server to Oracle 11g..
View 5 Replies View RelatedSQL & PL/SQL :: Pass Table As Input Parameter In Stored Procedures?
Feb 22, 2011i 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;
/
Advantages / Differences Of Methods Defined Types Over Stored Procedures
Aug 31, 2011We 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 RelatedForms :: Call One Trigger Of Item In Trigger Of Form?
Jul 1, 2011How can "call one trigger of item in trigger of form"
View 5 Replies View RelatedSQL & PL/SQL :: Stored Procedure With Multiple Table Join With Aggregate Functions
Jun 15, 2013I have 2 tables order_items and items.
Order_items Items
Item_id Item_id
Quantity Price
In normal sql statement: select sum(order_items.quantity*items.price) sales_price
from order_items,items
where order.item_id=items.item_id;
I have to put this logic in either a stored procedure or Function just to calculate sum(order_items.quantity*items.price) and store the aggregated value as Sales_price in DB. Then we have to call this from Informatica Stored procedure Transformation where we will have only one output port as Sales_price,this is to load into summary table. All the aggregate calculations and joining of 2 tables should be done on DB side and only one output should be populated when we execute this procedure.
Privileges Of Create Trigger - Procedures?
Aug 16, 2013i 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".
PL/SQL :: How To Know A Trigger Gets Called By Which All Packages / Procedures
Sep 11, 2012Wanted to know whether it is possible to know a trigger is getting fired due to which all packages/procedures. If it has got fired for a particular transaction say multiple times, then wanted to get details of the packages/procedures which caused an event so that trigger got fired?
View 2 Replies View RelatedPL/SQL :: Call Java From Stored Procedure
Aug 21, 2012We have a stored procedure named proc_java_test . From this stored proc, we need to call a java class named java_test.java
This class posts messages to a queue hosted on an app server running on a separate box.
create or replace
PROCEDURE PROC_JAVA_TEST
(
num IN NUMBER
)
AS LANGUAGE JAVA
NAME 'java_test.post(int) ';
Is it mandatory to place java files in oracle_home/bin ? I tried placing the file on some other location and then calling it from stored proc. It did not work. Can we point oracle to read java file from location other than oracle_home/bin?
Oracle version used: 11.2.0.2r3
Windows / .NET :: Call External Dll From Stored Procedure
Jun 6, 2013I have function in stored procedure which call function from another .net dll, is a first step. And this dll call function from unmanaged dll, is a second step.I do not have any problem in the first step, but when i do second step i have error:
ORA-20100: System.Security.SecurityException
System.Security.Permission.SecurityPermission
at ...
at ...
ORA-06512: at "SYS.DBMS_CLR", line 243
ORA-06512: ...
ORA-06512: ...
when i am deploying proceduse i set security levels "external".
PL/SQL :: Call VB Function From A Package Stored Procedure
Apr 30, 2013I need to call the VB function below from a Procedure's PL/SQL code and capture the returned variable into a varchar2 variable.I looked at the several means and nothing seems to work.
View 5 Replies View RelatedForms :: Call Oracle Form Using Stored Procedure?
Mar 14, 2011How to call Oracle Form using a Stored Procedure?
Under
create or replace procedure ........
Begin
.......
End
Inside such procedures , is it possible to call a form ?
Reports & Discoverer :: Calling Reports From Stored Procedures?
Feb 23, 2011Is there a way to call an oracle report (version 9i) from a stored procedure?
View 2 Replies View RelatedSQL & PL/SQL :: Procedure Call Inside Trigger?
Aug 8, 2011I am trying to call procedure inside trigger.. but i get error ora-04098 ..
create table emp_hstry
as
select * from emp
where
1= 2 ;
create or replace procedure emp_del_hstry(v_empno NUMBER ,
v_ename VARCHAR2,
v_job VARCHAR2)
is
insert into emp_hstry (empno,ename,job)
values (v_empno,v_ename,v_job);
COMMIT;
end;
create or replace trigger emp_del_hstry1
after insert or delete
on emp
for each row
begin
if deleting then
emp_del_hstry(:old.empno,:old.ename,:old.job);
end if;
end;
delete from emp
where
empno = '7369'
AFTER delete statement run i get ora-04098 message i also check show error command ,but still i am not getting solution of this error ..
Precompilers, OCI & OCCI :: PLS-00306 / Wrong Number Or Types Of Arguments In Call To Existing Stored Procedure
Feb 1, 2010I'm using an existing stored procedure in my C code. The stored procedure in question has been compiled and is proven to work without any errors. However, when I use the same in my C code, its failing with the above error.
The Store procedure definition looks like :
CREATE OR REPLACE FUNCTION SP(
srq_id integer ,
unid IN SPkg.arr_parmid,
parm_typ IN SPkg.arr_parm_typ,
[code].....
Type definitions
TYPE arr_parm_typ IS TABLE OF char INDEX BY BINARY_INTEGER;
TYPE arr_parmid IS TABLE OF tbl_parm.UNID%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_parm_lbl IS TABLE OF tbl_parm.PARM_LBL%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_parm_vlu IS TABLE OF tbl_parm.PARM_VLU%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_vlu_hint IS TABLE OF tbl_parm.VLU_HINT%TYPE INDEX BY BINARY_INTEGER;
My C code looks like :
typedef struct param
{
char lbl[30][81];
char vlu[30][256];
char typ[30];
ub8 seq_no[30];
[code].....
The way I invoke the stored procedure:
char command[250] = "begin
:retval := SSP_srq_parm_all(:srq_id,:unid,:parm_typ,:parm_lbl,:parm_vlu,:commit_flag,:vlu_hint,:create_flag);
end;";
OCIStmtPrepare2((OCISvcCtx *)svchp, (OCIStmt **)&(stmthp),
(OCIError *)errhp, (OraText *)command,
[code].....
OCIStmtExecute() fails with the above error.