i try to make trigger to execute immediate create or replace view
(
CREATE OR REPLACE TRIGGER xxxx
BEFORE INSERT ON table1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
l number ;
v_ddl varchar2(4000);
v_job number;
[code]....
it give me error for insuffition privilage in execute immediate and after i make GRANT CREATE ANY TRIGGER TO user give me error can't commit in trigger.
I cant compile & execute this function.
create or replace
FUNCTION get_branding_testing
RETURN r_brand
IS
BEGIN
CURSOR c1
IS
SELECT b.branding_code, c.name_desc
[code]....
I am about to pass 1Z0-051 and have been cramming. Ran across this scenario: Create or replace view. Correct answer: allows insert from view in multitable insert statement. Can I actually insert data from a view? My understanding is no data actually exists in a view. This has created a cavernous feeling of inadequacy.
View 3 Replies View RelatedI have make a new trigger.Create a trigger that inserting a new job_id MAX_SALARY assigned as the employee's salary more than 80 departmental charges
I have that code, is that correct?
CREATE OR REPLACE
TRIGGER TR27
AFTER INSERT ON JOBS FOR EACH ROW
BEGIN
(SELECT MAX(SALARY) FROM EMPLOYEES WHERE DEPARMENT_ID=80);
:NEW.MAX_SALARY := :OLD.MAX_SALARY;
END;
What I need to complete it?
I create this trigger:
CREATE OR REPLACE TRIGGER T_HST AFTER INSERT
On HST
FOR EACH ROW
DECLARE
Begin
INSERT INTO HST (EMP_NO, START_DATE )
SELECT EMP_NO, SYSDATE
FROM HST
WHERE EMP_NO = :NEW.EMP_NO;
End T_HRS_SAL_PERIOD_HSTY_UPD_PRI;
/
when I insert a new record in table HST I get this error:
ORA-04091: table HST is mutating, trigger/function may not see it
ORA-06512: at "T_HST", line 3
ORA-04088: error during execution of trigger 'T_HST'
What I wrong in this trigger?How can I modify it?
create or replace trigger aifer_transfer after insert on transfer for each new row begin
UPDATE Account SET balance = balance-:new.amount WHERE acc_id = from_acc_id; UPDATE Account SET balance = balance+:new.amount WHERE acc_id = to_acc_id; end if; end; create or replace trigger bifer_transfer before insert on transfer for each new row begin if get_balance(:new.from_acc_id) < :new.amount then raise_application_error(-20001, 'Not enough money in account!'); end if; end; create or replace function get_balance(p_acc_id in number) return number as v_balance account.balance%type; begin select balance into v_balance from account where acc_id = p_acc_id; return v_balance; end; select get_balance(123) from dual..................................................i am geting this error when executing the trigger..................................................Error report:ORA-01912: ROW keyword expected01912. 00000 - "ROW keyword expected"*Cause: keyword missing*Action:
I would like to create a trigger that will execute a stored procedure when a package/function/procedure is compiled. I tried creating an update trigger on user_objects, but it statues aI cannot create that trigger tyoe on views.
View 1 Replies View Relatedcreating package and i need not to use execute immediate. There are some dynamic build plsql's and sql's stored in global lists. Any other possibility to run them without execute immediate?
View 3 Replies View RelatedI am trying to use execute immediate in a trigger. See the below example
create table test_tbl(col1 number,col2 varchar2(10), col3 date);
create table test_tbl_log as select * from test_tbl where 1 = 2;
create or replace trigger test_trig
after insert or update or delete on test_tbl
for each row
declare
v_str varchar2(4000);
[code].......
insert into test_tbl values(3,'test',sysdate);
I got the Error : -1008->ORA-01008: not all variables bound
In the execute immediate statement if I change the v_using_str variable to :new.col1,:new.col2,:new.col3 trigger is running fine. But here I have to create trigger on all the tables in my schema and I dont want to list all the column names for each table manually and I am using a for loop to build the v_using_str string in my original code.
I'm using an 'On-Update trigger' to replace the default Form Builder processing for updated records. When I try to change a column in the form I get: ORA- 01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.
The on-update doesn't fire. How do I get around this. The block is based on a view.
I've been asked to look into ways I an execute an application with a trigger. I know I could set some database flag and have an application look at the flag and execute an application based on this, but my boss really doesn't wanna go down this route.
I was told I can use either C or java within the trigger somehow to execute an application (for example notepad.exe)
create or replace procedure my_proc(p_user in varchar2) is
l_cursor sys_refcursor;
l_query constant varchar2(1000) :=
'select a'
|| 'from ' || p_user || '.user_table'
|| 'where param0 = :x'
|| 'and param1 = :x'
|| 'and param2 = :x'
[Code]...
Suppose I execute my_proc many times and for multiple values of p_user. For performance reasons, will l_query be stored in the cache as I am using bind variables or it will not since I have the concatenation with p_user value ?
We have developed a code which builds a trigger on a atble dynamically at run time usinf Dynamic SQL command Execute immediate.
create or replace
PROCEDURE GENERATE_TRIGGER
(
P_TNAME IN VARCHAR2
[Code].....
I am not usre if the error is due to insufficient privileges as we are able to build tabl;e in the system dynamically using the above mentioned command.
I want to execute a db trigger once for updation of more than one record on a single primary key. The updation would be made by a 10g form by using the common tabular data block.
View 1 Replies View Relatedis it possible to create primary key on view and use this view for creating foreign key .
View 3 Replies View RelatedCREATE OR REPLACE PACKAGE pkg_mkt_hub_load_collection
AS
PROCEDURE sp_final_load_mkt_hub;
END pkg_mkt_hub_load_collection;
/
CREATE OR REPLACE PACKAGE BODY pkg_mkt_hub_load_collection
AS
c_default_limit CONSTANT PLS_INTEGER:=5000;
[code]....
show error
error code
SQL> @pkg_mkt_hub_load_collection.sql
Package created.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY PKG_MKT_HUB_LOAD_COLLECTION:
LINE/COL ERROR
-------- -----------------------------------------------------------------
57/4 PL/SQL: Statement ignored
PLS-00306: wrong number or types of arguments in call to 'MULTISET_INTERSECT_ALL'
SQL>
I have a problem in my form. In my form there are 4 blocks and in one of the block i have a button. In which i put a code same as below:
BEGIN
IF NAME_IN('SYSTEM.FORM_STATUS') = 'CHANGED'
THEN
CLEAR_FORM(NO_VALIDATE);
END IF;
PROCEDURE ABC;
END;
So as per code i want to clear all the changes in form for current session and want to call the procedure ABC after that. But it is not calling the procedure ABC. And what would i do to perform the same?
Is there any difference between include program header before CREATE OR REPLACE PACKAGE statement and program header after CREATE OR REPLACE PACKAGE statement
View 4 Replies View RelatedIs there a way to grant rights to view a stored procedure without granting execute permissions?
View 2 Replies View RelatedI am trying to create a procedure using the EXECUTE IMMEDIATE. I have been having problems calling it from an anonymous block
My code
CREATE OR REPLACE PROCEDURE homework
(p_table_name VARCHAR2)
IS
v_department_id departments.department_id%TYPE;
BEGIN
EXECUTE IMMEDIATE
[code]....
I called the procedure from an anonymous block
BEGIN
homework('Employees');
END;
It gives me an error
ORA-00905: missing keyword
Is it possible to create sequence using EXECUTE IMMEDIATE statement. The sequence name will be the bind variable.
DECLARE
TEMP VARCHAR2(20);
BEGIN
TEMP := :P2_INFO;
EXECUTE IMMEDIATE 'CREATE SEQUENCE' TEMP;
END;
This creates a sequence named TEMP, but i want the name :P2_INFO entered by user.
How can i create a temporary table using EXECUTE IMMEDIATE ??
Like:
CREATE GLOBAL TEMPORARY table new_table as (Select * from old_table);
I am trying to create table using bind variable in EXECUTE IMMEDIATE.. I want to know whether oracle allows to create table using bind variable in EXECUTE IMMEDIATE..
Following is the example :
Declare
test_tab varchar2(10) := 'tab_test';
sql_stm varchar2(100);
Begin
sql_stm := 'create table ' || :a || ' (col1 NUMBER)';
dbms_output.put_line(sql_stm);
EXECUTE IMMEDIATE sql_stm
using test_tab;
Exception
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm || ' ' || sqlcode);
End;
After running above block it is giving error : ORA-01008: not all variables bound.
I have a series of SQL scripts which contain SQL statements to create tables, populate them, create functions and stored procedures. Now I would like to execute each sql file against SQLPlus using a batch file so that I can just run this one file and all the configuration work I need to do can get done.
Problem is, when I try to execute the SQL file against SQLPlus, it gets upset with the Create Procedure/Function scripts...
I am using the following command:
sqlplus u/p@<someserver> @<path_to_sqlfile>.sql
this sql file contains create procedure pl/sql code
Is it possible create/compile SP/Functions that are contained within SQL Files using SQLPLus? Or do I have to physically write them out in SQLPLus (or load them in SQLDeveloper) to accomplish this?
I need to create an oracle job that will execute a shell script. i need to run it only when I call it(so not to be scheduled).
View 5 Replies View RelatedI want to create a meta data table which has all the views users can access with a description of what the view was supposed to do.
Is there a trigger that can be created that fires when a view is created? To make it easy only one user in this system (oracle 9.2.0.8) has the create view privilege.
I know I could write a procedure to create views with dynamic sql that would do this but that seems a bit of overkill.
the sequence of events should go like this:
view created
trigger fires
record with view name and creation date inserted in a table
later, manually go to the table and add explanatory comment
or can I add a comment to a view like you can to tables?
I have two different tables having similar structure but data is coming from different source.finally i want to update the view so that the it should affect the base table.
create table emp1 as
select empno,ename,job,deptno,sal from emp where deptno=10;
create table emp2 as
select empno,ename,job,deptno,sal from emp where deptno=20;
create view emp_view as select * from emp1 union all select * from emp2;
[code].......
I'm trying to insert data in my_second_table using a trigger and a view when I insert the data in my_first_table but there's something wrong in the code.
CREATE OR REPLACE TRIGGER my_trigger
AFTER INSERT ON my_first_table
FOR EACH ROW
BEGIN
[Code]...
I'm suspecting that the problem is in the :new.cod_emp
P.S.: I'm using: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production on Enterprise Linux Server release 5.5 (Carthage)
I've this java source create or replace and compile java source named "Decodificador" as
public class Decodificador {
public static String decodifica(String codigo)
return codigo;
and this function
create or replace function F_Decodificador(codigo varchar2) RETURN VARCHAR2 IS
LANGUAGE java NAME 'Decodificador.decodifica(String) return String';
when I execute the function the result is:
ORA-29531: no method decodifica in class Decodificador
I have created a Table and a log of materialized view in an Schema SchemaAA of Server A
CREATE TABLE TABLA_TEST
(
TEST_PK NUMBER,
TEST_TEXTO VARCHAR2(50),
CONSTRAINT PK_TEST PRIMARY KEY (TEST_PK)
);
CREATE MATERIALIZED VIEW LOG ON TABLA_TEST;
I have created a DB Link from server B with user REPLIC
I have granted SELECT to REPLIC to the table and the log materialized View.
I have created a table in instanceBA of Server B
CREATE TABLA_TEST
(
HOSPCODE NUMBER(3),
TEST_PK NUMBER,
TEST_TEXTO VARCHAR2(50),
FECHA_INSERT DATE,
FECHA_UPDATE DATE,
FECHA_DELETE DATE,
CONSTRAINT TTEST_PK PRIMARY KEY (HOSPCODE, TEST_PK);
I have created a materialized view in schema SchemaBB of Server B with a trigger
CREATE MATERIALIZED VIEW WORKAREA.TABLA_TEST70
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS
SELECT TEST_PK,
TEST_TEXTO
[code].........
All schemas have the appropriate grants.
When inserting in TABLA_TEST (SchemaAA) and refreshing MView TABLA_TEST70 things go nicely.
But, when updating a record in the original TABLA_TEST and refreshing the MView the results in TABLA_TEST in SchemaBA are as if I have deleted the record (FECHA_DELETE is set to SYSDATE).