how can i execute "AUDIT SELECT ON EMPLOYES" from a procedure?
Ex:
create or replace procedure myfunc()
begin
AUDIT SELECT ON EMPLOYES;
end;
The above procedure will cause an error because AUDIT is no a DML statement.But... I still want to do that bebause i wanna call that function from PHP Application.With PHP, i can't execute AUDIT SELECT ON EMPLOYES.
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 ???
Execute Immediate on SELECT Statement
declare l_stmt VARCHAR2(1000);
begin
l_stmt := 'Select * from mytable' ;
EXECUTE IMMEDIATE l_stmt;
--- dbms_output.put_line( l_stmt);
end;
it is not returning any output. I have hardcoded the table name here where as in real time Mytable name will differ.
CREATE TABLE TEST1
(
OFFICE_PRODUCTS NUMBER,
OFFICE_ELECTRONICS NUMBER
)
Insert into TEST1 (OFFICE_PRODUCTS, OFFICE_ELECTRONICS) Values(1, 0);
COMMIT;
CREATE TABLE TEST2
(
EXPORT_FIELD_NAME VARCHAR2(100 BYTE),
EXPORT_COLUMN_EXPRESSION VARCHAR2(100 BYTE)
)
Insert into TEST2
(EXPORT_FIELD_NAME, EXPORT_COLUMN_EXPRESSION)
Values ('A1', 'least(OFFICE_PRODUCTS, OFFICE_ELECTRONICS)');
COMMIT;
I want to be execute the expression should run in select statement how to do? and tried as like below,it's not working.
select (select EXPORT_COLUMN_EXPRESSION from test2 where EXPORT_FIELD_NAME='A1') FROM TEST1;
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
I have one package, that included so maany ref. cursor package..Now , i want to execute of one procedure in this package, how can i do it ..
CREATE OR REPLACE package Pkg_HR As
Type Typ_Cur Is Ref cursor;
procedure getHR_initiate(pvFinYr Varchar2, Cur_HR_Init OUT TYP_CUR);
procedure getFin_Yr(Cur_Fin_Yr Out TYP_CUR);
procedure getCutOFfStatus(pvAppsee1_Appsr2_Review3 Varchar2, pvFinYr Varchar2, Cur_HR_Init OUT TYP_CUR);
procedure SetEmp_For_FinYr(pvFinYr Varchar2, Cur_Emp OUT TYP_CUR);
End Pkg_HR ;
My Package Body is :
CREATE OR REPLACE package body Pkg_HR As
procedure getHR_initiate(pvFinYr Varchar2, Cur_HR_Init OUT TYP_CUR)
IS
Begin
Open Cur_HR_Init For
Select HR_FINYR HR_FinYr, To_Char(HR_PERIOD_FROM,'DD/MM/RRRR') HR_PERIOD_FROM
[code].....
My need is to execute procedure in cycle. I can run
EXEC zoo.pkg_z184.rep184_fill( date '2011-09-28' );
EXEC zoo.pkg_z184.rep184_fill( date '2011-09-29' );
But I'd like to do it in cycle when the period is much bigger. Here is my try to do it:
begin
for l_row in (
select date '2011-09-27' as arcdate from dual union all
select date '2011-09-28' as arcdate from dual union all
select date '2011-09-29' as arcdate from dual union all
select date '2011-09-30' as arcdate from dual
)
loop
EXEC zoo.pkg_z184.rep184_fill( l_row.arcdate );
end loop;
end;
It returns error:
ORA-06550: line 9, column 6:
PLS-00103: Encountered the symbol "ZOO" when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "ZOO" to continue.
I have a table with name SQL_STATEMENTS and it has 1 column, Column name is STATEMENT_DESC
The contents of rows in Table above are:
select * from emp;
select * from dept;
Can I write a PLSQL block that can select these 2 rows from the table and execute them?
have an automated process which runs on an Oracle 8i database server as user abc. This process creates views/tables in other schemas, on the same database server, which point to objects owned by the abc user.
The issue I'm getting is that when I try to execute GRANT SELECT ON xyz.view123 TO PUBLIC as the abc user, I get an insufficient privileges.I should add that the abc user created the xyz.view123 table/view.
What grants/priviliges or whatever do I have to do to the abc schema?
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.
I have a procedure which will execute on every Monday. Same is not executed last Monday. Can I execute the Procedure on some other day with out changing the actual procedure?
View 1 Replies View RelatedI have written a package in that package i have written a procedure. That procedure has two i/p and two o/p variables. I am calling the procedure using the below syntax.
in built in type : Execute a Procedure
='Declare
v_customer_name varchar2(100);
v_id number;
v_out1 number;
v_out2 NUMBER;
begin
v_id := 1041;
XXFBI_OM_UTILITIES.XXFBI_CUSTOMER_CREDIT_DETAILS1(''' || ${item.order.SOLD_TO.value} || ''', v_id, v_out1, v_out2);
end'
Its working fine.
My question is how can i capture the value of out put variables v_out1 and v_out2 and assign to a DFF.
I have created the following procedure. Since I am using this first time I don't know how to execute this.
CREATE OR REPLACE PACKAGE GAFT_PROG_DIT.ram_package
IS
TYPE type_ots IS TABLE OF ORDER_TREND_SCORE%ROWTYPE INDEX BY PLS_INTEGER;
PROCEDURE InsertTrend( P_TYPE_OTS_REC IN type_ots );
END;
/
[Code]...
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 a way to grant EXECUTE on a group of procedures/functions/packages to a particular role , in a single statement ? Or we have to do it one by one... like
GRANT EXECUTE on event_main to role1,
GRANT EXECUTE on event_main2 to role1,
GRANT EXECUTE on event_main3 to role1,
I'm running a PL/SQL with a For Loop cursor, but when trying to execute it doesn't run. It is as if there is no data, but I ran the cursor separately in a SQL Plus session and it runs perfectly. I'm enclosing the file with the procedure.
View 18 Replies View RelatedI have two schema SCHEMA_A and SCHEMA_B in the same Database.
I want to execute a procedure UPDATE_CONTACT() which is present in SCHEMA_A from SCHEMA_B.
let me know whether the following SQL is correct:
BEGIN
SCHEM_A.UPDATE_CONTACT();
END;
I have a procedure p1 in schema s1.i want to call this procedure in schema s2 dynamically.how can i do?
View 4 Replies View RelatedRequirement is to build procedure where it has 10-12 input variables but some of them (input variables) may at times be NULL.Based on this, i thought of getting into EXECUTE IMMEDIATE but this would just return rows i mean DML stmt for EXECUTE IMMEDIATE.Also, on the requirment is all parameters are present then result set be based on range on start and end date.
View 3 Replies View RelatedI have connected to the database from two sessions
Box 1 : i have executed a procedure.it took few seconds to get executed
Box 2 : I have executed a block of procedure.It is taking 1 min 40 secs to get executed.
why it is taking much time from Box2(for executing a block from procedure) , as the same code executed successfully in a procedure from Box 1.
Here is the code I am using:
CREATE OR REPLACE
PROCEDURE CUBE_VIEW (VAR_DT IN VARCHAR2 DEFAULT '')
AUTHID CURRENT_USER
AS
START_DATE NUMBER;
END_DATE NUMBER;
VAR DATE;
BEGIN
IF VAR_DT IS NULL THEN
[code]....
The code complies successfully but when I am executing the code, here is the error I get. The code works successfully when I manually insert the values (as done in the commented out statement) but when use it from variable, it gives me error.
Connecting to the database LocalEnvironment.
ORA-00904: "END_DATE": invalid identifier
ORA-06512: at "TESTING.CUBE_VIEW", line 18
ORA-06512: at line 6
START DATE IS 90301
END DATE IS 111201
Process exited.
Disconnecting from the database LocalEnvironment.
I have created a procedure like this
CREATE OR REPLACE PROCEDURE SCOTT.GetEmpList(p_recordset OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset FOR
SELECT * FROM scott.emp
WHERE deptno=10;
END GetEmpList;
Now i would like to execute this procedure in sqlplus which will display the following:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7782 CLARK MANAGER 7839 6/9/1981 2450 10
7839 KING PRESIDENT 11/17/1981 5000 10
7934 MILLER CLERK 7782 1/23/1982 1300 10
something like
exec package name.procedure ?
I need to execute a .sql file in an oracle stored procedure.
View 9 Replies View RelatedI have a procedure in this procedure i use.
EXECUTE IMMEDIATE 'alter user '||use||' identified by '||modp ||' replace '||oldp;
but when i execute it show insufficient privilages but i create for this procedure as public.and grant execute facility to the user.
Oracle 11.2.0.1
Windows
create type np_type is varray(3) of number
/
create type cn_type is varray(3) of number;
/
create type cxn_type is varray(3) of varchar2(2000)
/
I created TEST table by below pl/sql block :
declare
execstr varchar2(2000) :='create table test(';
begin
for i in 1..80 loop
[Code]....
*ERROR at line 5: ORA-06550: line 5, column 8: PLS-00103: Encountered the symbol "MYPROC1" when expecting one of the following: := . ( @ % ;
The symbol ":=" was substituted for "MYPROC1" to continue.
SQL>The above procedure will insert the supplied numbered string into the test table which split the numbered string based upon supplied splitids of sp_table.
I have an automated process which runs on an Oracle 8i database server as user abc.This process creates views/tables in other schemas, on the same database server, which point to objects owned by the abc user.
The issue I'm getting is that when I try to execute GRANT SELECT ON xyz.view123 TO PUBLIC as the abc user, I get an insufficient privileges.I should add that the abc user created the xyz.view123 table/view.
What grants/priviliges or whatever do I have to do to the abc schema?
We are trying to execute a statement SELECT CURRENT_DATE FROM DUAL on Timesten 11.2.2 . It throws error unknown referenced column error. Command> select current_date from dual; 2211:
Referenced column CURRENT_DATE not foundThe command failed. But the following doc shows the support.
TimesTen PL/SQL Support: Reference Summary CURRENT_DATE function
Returns the current date in the session time zone. YIn TimesTen this returns the current date in UTC (universal time). TimesTen does not support local time zones.
How do i execute and display results of a procedure declared with ref cursor OUT parameter.
I am using SQL Developer and Oracle 10.2.
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?
Machine 1: Oracle server installed and database hosted on this Machine.
Machine 2: Oracle client is installed and connect through sqlplus to database in machine 1.
The requirement is: I need to have a Java/C program in machine 2 (where client available) which needs to be called from the stored procedure in server on machine 1. The objective is calling a java/C program from Oracle stored procedure but available on 2 different machines. Is it possible,
Environment: Oracle 11.2.0.1
UNIX AIX 6.1