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 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 RelatedI'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 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 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?
creating 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 this function
instr(','||replace(r_code,' ') ' ' || ', ' , ',' ||r_code || ' , ' )=0
i want to hardcode 'a1', 'a2' from r_code i dont want records from a1, a2
I have a table called email that contains a field called email
I have a few records in the table that contain example@hotmial.com and want to change the hotmial to hotmail
I also have some that are line example@hotmail.co and I want to change those to hotmail.com
so - I want to do a substring search and replace.
can the replace do this for me in one select statement - how do I structure the sql to do this?
Years ago, someone created a database in Oracle that was carried over and now sits in Oracle 10g. I am developing an application that queries this database and returns the result as XML.
Many (thousands) of rows have an item description that contains an ampersand. I want each of these pieces of data to have it written as & amp; (had to add a space so it would show up here, but you know what I mean) instead of &, but I don't feel like doing thousands of UPDATEs to change this.
Does Oracle have any global find/replace functions that I can call? I'd rather do one update statement that replaces all occurances of & with & amp; but I can't seem to find a function that will do this.
I'm thinking something that would work like:
UPDATE table SET column1=REPLACE(column1,oldstr,newstr);
Explain with the best example for difference between translate and replace function.
View 2 Replies View Relatedwhy the REPLACE function is not replacing. I assume it has something to do with the ASCII value being zero.
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as aggs@AGGSTEST
SQL>
SQL> SELECT str,
2 e9,
3 REPLACE(str, '%E9', e9) replace,
4 regexp_replace(str, '%E9', e9) regexp_replace,
5 utl_url.unescape(str, 'UTF8') utl_url,
6 ascii(e9) ascii
7 FROM (SELECT 'Soir%E9e' str,
8 chr(to_number('E9', 'xx')) e9
9 FROM dual);
STR E9 REPLACE REGEXP_REPLACE UTL_URL ASCII
-------- --- ------- -------------- ------- -----
Soir%E9e é Soire Soirée Soirée 0
I have a table with:
1 million rows
average row length 200 bytes
50 columns
and this update statement
UPDATE mytable SET varchar2_4000_column = replace(replace(replace...300 times)
It looks at every row in the table (no WHERE clause) and does these 300 replace operations on this column for each row. Each replace replaces with a null so effectively it is removing strings. Much of the time these strings are not in the column.
This update statement takes 25 minutes and it is 98% CPU and 2% USER_IO time.
I figure that is what is taking all the time since it is a CPU bound statement. if rows in this table are persistent over time then tag rows with a flag to show which ones have already been processed and skip these next time around.
I have the next SQL Loader file, and it is giving me some headches because the Replace instruction is not working. It does not replace the ' ' by NULL.
load data
CHARACTERSET UTF8
infile TABLE1.unl
BADFILE 'TABLE1.bad'
DISCARDFILE 'TABLE1.dsc'
insert into table GSCIS.table1
fields terminated by "|"
TRAILING NULLCOLS(
codl1 CHAR "REPLACE(:codl1, ' ', NULL)"
,col2 CHAR "REPLACE(:col2, ' ', NULL)"
,col3 INTEGER EXTERNAL
,col3 INTEGER EXTERNAL "DECODE(:col3, NULL, 0, :col3)"
)
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?
In my project, the below function retrieve output very slowly. Is there any alternative code to replace this function with join
CREATE OR REPLACE FUNCTION f_johnson (i_case_id number,i_seq_num argus_app.case_reference.seq_num%type) RETURN VARCHAR2 AS
c_ref VARCHAR2(32500) := null ;
CURSOR c_refer IS
[code]...
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 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 ?
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 have converted one function from sql to oracle through sql developer. so it's created with package name. I execute package name and function also
here is my package name and function:
CREATE OR REPLACE PACKAGE FnFetchEmployees_pkg
AS
TYPE tt_v_employees_type IS TABLE OF tt_v_employees%ROWTYPE;
END;
create or replace
FUNCTION FnFetchEmployees
(
v_user_id IN NUMBER
)
RETURN FnFetchEmployees_pkg.tt_v_employees_type PIPELINED
AS
[code]........
it's executed successfully. when i am executing this function like this:
select emp_id from fnfetchemployees_pkg.fnfetchemployees(1) from dual;
getting error: sql command is not properly ended;
CREATE 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>
Is anyway to create function based index for group function columns.
For example
select max(timestamp),min(age),averge(sal).... ... .. from tab;
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 RelatedI have a requirement where i need to execute a unix shell script "a.sh" having permissions user1,group1 and no access to public. This should be executed from Oracle function.
So i wrote a routine in Java and it works fine when i execute a command like Date etc.
But when i execute "a.sh" from oracle client, its returning error that "could not be executed-permission denied". This is because once login happened through the "sqlplus" the shell takes user as "oracle" and group as "dba" but the script "a.sh" having only permissions user1,group1. Unfortunately we are not allowed to change the permission for "a.sh" to give execute permission to all(public).
After searching in internet, i understand that one way is to make the password (/etc/passwd) for the user "user1" as no password and can use "su" command so that it wont ask password while invoking it.
Is there any other way apart from this doing changes in password file at UNIX level when executing a script from oracle client?
if further information is required.
Oracle: version: 11.2.0.1
UNIX : AIX -5.9
I want to provide execute privilege to a function in a package to other schema.
Owner wedb
Package MASTER
function in a a package is convert_function
GRANT EXECUTE ON wedb.MASTER.convert_function to HRDB;
I got the blow error.
ORA-00905: missing keyword
I've used a date in execute immediate query in function, but at the time passing the date as input parameter and getting the result i'm getting following error.
CREATE TABLE MIS.TEMP
(
ID NUMBER(8),
STOCKDATE DATE,
STOCKQTY NUMBER(10,2)
);
[code]....
SQL> select getstockqty(1,to_date('31/03/2012','dd/mm/yyyy')) from dual;
select getstockqty(1,to_date('31/03/2012','dd/mm/yyyy')) from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "MIS.GETSTOCKQTY", line 11
I want to provide execute privilege to a function in a package to other schema.
Owner wedb
Package MASTER
function in a a package is convert_function
GRANT EXECUTE ON wedb.MASTER.convert_function to HRDB;I got the blow error.
ORA-00905: missing keyword
I am trying to execute the PL/SQL block below:
DECLARE
var VARCHAR2(4000);
BEGIN
SELECT DBMS_LOB.SUBSTR(v_clob,4000,1) INTO var FROM test_clob;
END;
** v_clob is a CLOB column in test_clob table.
I get the below error:
wrong number or types of arguments in call to 'SUBSTR'"SYS"."DBMS_LOB"."SUBSTR": invalid identifier...I have execute privileges to DBMS_LOB.SUBSTR function.
My purpose is to audit the execution of a specified procedure, function in a package. So I try this audit option audit execute on dbms_java.longname Althought I'm using SYS, it leads to this error:
SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" But when I try audit execute on dbms_java It's ok and it audit every statement that using that package dbms_java. But thing I want is audit the specified procedure on this package, not all of this package.
why DBA_OBJ_AUDIT_OPTS show DBMS_JAVA package object type is procedure ???
I 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