SQL & PL/SQL :: Providing Execute Privileges To Function In A Package?

Jun 6, 2013

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

View 2 Replies


ADVERTISEMENT

PL/SQL :: Providing Execute Privileges To Function In A Package

Jun 6, 2013

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

View 5 Replies View Related

SQL & PL/SQL :: Execute Privileges To DBMS_LOB.SUBSTR Function

Mar 15, 2011

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.

View 2 Replies View Related

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 ???

View 1 Replies View Related

SQL & PL/SQL :: Insufficient Privileges When Creating Tables Using Execute Immediate?

Apr 30, 2010

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?

View 3 Replies View Related

Execute GRANT SELECT ON Xyz - Insufficient Privileges?

Sep 30, 2011

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?

View 2 Replies View Related

Schema Permissions - Execute GRANT SELECT ON Xyz - Insufficient Privileges?

Sep 30, 2011

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?

View 1 Replies View Related

SQL & PL/SQL :: Difference Between Stand Alone Function And Function Declared In A Package?

Mar 11, 2010

What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.

View 2 Replies View Related

SQL & PL/SQL :: Execute Privilege On A Package Which Is In User

Dec 5, 2011

how will i know if i have execute privilege on a package which is in user?

View 6 Replies View Related

Forms :: Ora-00600 Cannot Execute Package Of Other Schema

Oct 21, 2010

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.

View 3 Replies View Related

Server Utilities :: Execute Package Using Sqlldr And Input CSV?

Apr 23, 2012

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.

View 1 Replies View Related

PL/SQL :: Privileges Required For Calling A Function

Sep 6, 2012

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;

View 2 Replies View Related

Executing Function In A Package?

Mar 24, 2008

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 .

View 1 Replies View Related

SQL & PL/SQL :: Can Use Htp Package In Oracle Function

May 14, 2010

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;

View 4 Replies View Related

SQL & PL/SQL :: How To Use Ref Cursor In A Package Or In A Function

Sep 20, 2012

what is ref cursor?

How to use ref cursor in a package or in a function?

View 7 Replies View Related

SQL & PL/SQL :: Calling A Function In Package

Mar 9, 2010

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]...

View 2 Replies View Related

SQL & PL/SQL :: How To Execute Function In Developer

Oct 9, 2012

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;

View 9 Replies View Related

SQL & PL/SQL :: Create Pipeline Function In Package

Feb 13, 2013

Can we create a Pipelined function in A Package ? I know we can create it standalone function.

View 11 Replies View Related

PL/SQL :: How To Test Procedure / Function / Package

Sep 16, 2012

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 .

View 6 Replies View Related

PL/SQL :: Get A Called Procedure / Function Name Within Package?

Jan 11, 2013

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:

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.

View 7 Replies View Related

Export / Import Of Procedure - Function / Package

Jun 9, 2012

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 Related

Client Tools :: How To Add Function To Existing Package

Nov 11, 2010

I want to add my function to the existing package.

View 8 Replies View Related

SQL & PL/SQL :: ORA-06575 Package Or Function In Invalid State

Feb 15, 2012

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;

View 5 Replies View Related

PL/SQL :: Call VB Function From A Package Stored Procedure

Apr 30, 2013

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 Related

Execute UNIX Shell Script From Function?

Apr 27, 2012

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?

if further information is required.

Oracle: version: 11.2.0.1
UNIX : AIX -5.9

View 3 Replies View Related

SQL & PL/SQL :: Date Error In Execute Immediate Query Through Function?

Jun 25, 2012

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

View 12 Replies View Related

PL/SQL :: Can't Compile And Execute Function (Create Or Replace)

Jun 6, 2013

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]....

View 6 Replies View Related

PL/SQL :: Unescape Data Of Table Column Using Package Function

Feb 8, 2013

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].......   

View 17 Replies View Related

Execute Create Procedure / Function From SQL Script In SQLPlus

Feb 17, 2010

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?

View 8 Replies View Related

SQL & PL/SQL :: Function That Returns A Table Type Inside A Package Body

Jul 26, 2011

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?

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved