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 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 ???
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> grant dba to temp;
Grant succeeded.
SQL> connect temp
Enter password:
Connected.
SQL>
SQL> create or replace procedure tempproc
2 is
3 begin
4 execute immediate 'create table temp_table(a varchar2(10))';
5 end;
6 /
Procedure created.
SQL> exec tempproc;
BEGIN tempproc; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEMP.TEMPPROC", line 4
ORA-06512: at line 1
SQL>
SQL> declare
2 begin
3 execute immediate 'create table temp_table(a varchar2(10))';
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> desc temp_table;
Name Null? Type
----------------------------------------- -------- ----------------------------
A VARCHAR2(10)
Why am I not able to create a table from procedure using execute immediate?
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?
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?
What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.
View 2 Replies View Relatedhow will i know if i have execute privilege on a package which is in user?
View 6 Replies View RelatedI 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.
11.2
For procedures, we need to grant EXECUTE privilege.
For example:
grant execute on scott.process_salary to john;Lets say I have a function SCOTT.GET_EMPIDS and I want another user john to be able to invoke this fuction. So, what is the privilege that needs to be granted to John.
Is it SELECT or EXECUTE ?
grant select on SCOTT.GET_EMPIDS to john;or
grant execution on SCOTT.GET_EMPIDS to john;
I have created one function in the package.
function : Tier_wh
package : Order_DESC
Function defined as
function TIER_WH( message in out Xorder_desc)
...
...
end;
Xorder_desc is defined as
create or replace type Xorder_desc type
(order number(10),
location number(10),
wh varchar2(20)
);
how to execute this function which is defined in the package .
can we use htp package in function?
something like
if event_id = p_event_id THEN
htp.tableRowOpen;
htp.tableData(htf.bold('Evnt'), 'RIGHT', cattributes=>'CLASS=bptext');
htp.tableRowClose;
end if;
what is ref cursor?
How to use ref cursor in a package or in a function?
I have this following pakg
CREATE OR REPLACE PACKAGE pkg_test AS
-- 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;
[code]...
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;
Can we create a Pipelined function in A Package ? I know we can create it standalone function.
View 11 Replies View RelatedHow 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 .
View 6 Replies View Relatedis 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:
11.1.2013 09:00:01 START.*TEST_PACKAGE.proc_2*
11.1.2013 09:00:01 START.*TEST_PACKAGE.proc_1*
11.1.2013 09:00:01 END.*TEST_PACKAGE.proc_1*
11.1.2013 09:00:01 END.*TEST_PACKAGE.proc_2*
I tried to use "dbms_utility.format_call_stack" but it did not return the name of procedure/function.
Can we Export & Import of Procedure, Function & Package selection by name, as we can export & import of one or more table by name
View 2 Replies View RelatedI want to add my function to the existing package.
View 8 Replies View RelatedThe 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 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 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'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 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'm trying to un-escape the data of a table column using the package function - UTL_URL.Unescape . But the problem is my column is a CLOB and length of my data exceeds that acceptable by the function.
So, i'm just trying to simulate that function behavior normally. My logic is to scan all characters with pattern '%yy' (HEX) and then convert it into DECIMAL using TO_CHAR(,'XX') . But got stuck in this regexp-replace.
with xx as(
select '<p>%3Cp%3Ewefwef%3C/p%3E</p>' col from dual
)
select
UTL_URL.unescape(col) x1,
regexp_replace(col,'%([a-zA-Z0-9]){2}','2|.') x2
from
[code].......
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?
CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/
CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
/
[code]....
I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?
I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?