How To Audit Execute On Specified Procedure Or Function In Package
Oct 4, 2012
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 ???
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.
How can i test my Procedure /Function/Package, i mean how to unit & integration test my Procedure /Function/Package. Is there any tool available in the market, or we have to test in manually .
is it possible to obtain a called procedure/function name within package?
For a measuring and tracing purpose, I would like to store an info at the beginning of each procedure/function in package with timestamp + additional details if needed.
For example:
CREATE OR REPLACE PACKAGE BODY "TEST_PACKAGE" IS PROCEDURE proc_1 IS BEGIN
[Code]....
I would like to replace "???????" with a function which would return a name of called procedure, so result of trace data after calling TEST_PACKAGE.proc_2 would be:
I 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.
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 am using Oracle forms 6i, database 10g. Created a package containing 1 function in schema1.
Granted execute on this package to Schema2. Created a public synonym on this package.
Now when im using this package in a form and compiling in schema2, it is giving Error 0 at Line 0, column 0. Ora-00600: internal error code, arguments: [17069],[71101744],[],[],[],[]
If i compile in schema1, where package was originally created, it is compiling without any error. But i am not compile this in schema2.
I have written a shell script that will execute a procedure. The input parameters are constant.
#!/bin/ksh sqlplus user@server.com<<EOF set serveroutput on; var Return_Code number; var Return_Message varchar2(4000); exec test_pkg.Insert_test('IDD', null, 'BATCH',:Return_Code, :Return_Message);
Now I have to call same procedure but the input is a csv file.
Is there a way to call a procedure using csv file, I cannot load the table using SQL Loader because there is a complicated logic.For every row in CSV there should be 3 rows inserted into table and 2 rows updated.
As of JAN 2, three rows have to be inserted with dates JAN2, JAN 3, JAN 4 and 2 rows ( 3ODEC and 29DEC have to be updated). Also these days have to be business days.
So all this code is in procedure that uses a DB2 package for business dates.Instead of using sql ldr , if would like pass the csv file as input param.
-- Create a table type of the table you want TYPE tbl_test IS varray(100) of VARCHAR2(30);
-- Function that will return the table type FUNCTION fnc_test RETURN tbl_test;
-- End package END;
CREATE OR REPLACE PACKAGE BODY pkg_test AS FUNCTION fnc_test RETURN tbl_test IS -- type table_name_va is varray(100) of VARCHAR2(30); -- Variable of the type tbl_test
[code]...
But i am having problem calling this to test it.
declare TYPE tbl_test IS varray(100) of VARCHAR2(30); var_tbl_test tbl_test; begin var_tbl_test:= pkg_test.fnc_test;
The function is supposed to tell whether numbers stored as a string value evaluates to a date.I copied into an SQL windown in PL/SQL Developer and typed "commit;" after and then tried to view the results as I posted below to make sure the function worked.
However, I get an ora-06575 Package or Function GETDATE is in an invalid state.So would like to know first, if the code is correct? And if it is, do I need to do something else to make Developer take the code? Here is the code that gave me the error I used to try to test the function:
select GetDate ('010312', 'dd-mm-yyyy') from dual
Here is the code I used to create the function:
create or replace function GetDate (vDate varchar2, vMask varchar2) return varchar2 is
vReturnDate date; begin vReturnDate = to_date(vDate, vMask); return vReturnDate; exception when value_error then return 0; end GetDate; commit;
I 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?
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.
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
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.
in a certain procedure I'm trying to call a procedure from another package in the same Schema. Package-name: Haku_Hops, procedure-name: veto and submitted is a parameter called opnum. So the following brings no problem:
Hops_Haku.veto(opnum);
But what if in the beginning of the package body I create the following variable: hak_pah := 'Hops_Haku.'; Is it then in anyway possible to call this other procedure through this variable, like e.g. hak_pah||veto(opnum);?
Until now I've only gotten error, even after declaring the variable in the declaration part.
All this has within is an SQL statement which is built up (using the string, 'strSql') How can I view the output of a refCur to check what the final strSql is?
