View A Stored Procedure Without Granting Execute Permissions?
Jul 13, 2012Is there a way to grant rights to view a stored procedure without granting execute permissions?
View 2 Replies
ADVERTISEMENT
SQL & PL/SQL :: Granting Permissions For Create Type / Procedure?
May 18, 2011I have a question regarding how to grant create type and create procedure roles correctly.Currently if I do this:
create user vackar_temp identified by "123456789";
grant create type to vackar_temp ;
Then as vackar_temp:
create type temp_col as table of number;
I get the following error:
ORA-01031: insufficient privileges
I've also tried:
grant resource to vackar_temp ;
But that doesn't work either If I run this:
select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
[code]...
I can see that the user does have the create type role:
User, his roles and privileges
------------------------------
VACKAR_TEMP
CREATE TYPE
RESOURCE
CREATE CLUSTER
CREATE INDEXTYPE
[code]...
How To Execute A Stored Procedure In Oracle
May 22, 2012how 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.
SQL & PL/SQL :: DML In Execute Immediate Inside A Stored Procedure
Apr 28, 2011Requirement 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 RelatedForms :: How To Execute A Stored Procedure Without Parameter
Jun 17, 2010I 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
SQL & PL/SQL :: Execute File In Oracle Stored Procedure
May 16, 2012I need to execute a .sql file in an oracle stored procedure.
View 9 Replies View RelatedClient Tools :: How To Execute Stored Procedure With Cursor Within PL/SQL
Apr 5, 2011To keep things simple: lets say that this is my procedure:
create or replace procedure testSp(x out sys_refcursor )
is
begin
open x for
select 1 from dual;
end;
how would i be able to execute this from with in sqlTools to see the result ??? i have tried everything...
my goal is in the end to execute this from Magic 8 (to those who herd of it). p.s this needs to work in oracle 8
SQL & PL/SQL :: Error With Execute Immediate To Pass Db_link To Stored Procedure?
Oct 19, 2011I have the following error when I try to pass a Procedure parameter carrying db_link name to a stored procedure:
SQL> CREATE OR REPLACE PROCEDURE AFESD.P_DM_VCONTRACT_ITEM (CON_CONNECTION VARCHAR2)
2 IS
3 T_CONNECTION VARCHAR2(50);
4 T_SQL VARCHAR2(500);
5 BEGIN
6 T_CONNECTION := 'X_DM_TEST@' || CON_CONNECTION;
[code]....
The insert statement succeeds when I try to run DBMS_OUTPUT.put_line output.
SQL & PL/SQL :: Execute Stored Procedure With Table Of Number As In Parameter?
May 31, 2011I have one stored proc with three in parameters as
number, varchar and table of number
what statement I need to write in pl/sql to execute it ...
execute getdetails(1,'xyz', ????????????)
Security :: New Procedures Not Working Even After Granting EXECUTE
Sep 2, 2010oracle 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"
I have created two new procedures. When I log in as ADMIN, I can execute these procedures.. but when i log in as WC_GUEST or WC_TECH, I cannot.. One of the procedure name is WCL_RPRTD_PARTS_REP_SEARCH, to which, I have given this: grant execute on WCL_RPRTD_PARTS_REP_SEARCH to WC_GUEST,WC_TECH;
Also, I have changed the ROLE of ADMIN, to the same role as these two users and tried.. it works fine.
Application Express :: How To Execute Stored Procedure From APEX Page
Oct 17, 2012I just started to play with this system and don't understand how to execute the the stored procedure (that creates report) from the APEX page. I know that they have sample documentation and java script call (show below):
<script language=”JavaScript” type=”text/javascript”>
function callMyRep() { var formVal1 = document.getElementById(’P_PAR1′).value;
var url;
url = ‘rep1?p_session_id=&SESSION.’+'&p_par1=’+ formVal1; window.location.href=”f?p=&APP_ID.:12:&SESSION.”;
w = open(url,”winRep”,”Scrollbars=1,resizable=1,width=800,height=600″); Close here. X
[code]....
In this example rep1 is the stored procedure that creates report.How can I execute it using url?
Create Trigger To Execute A Stored Procedure During Source Code Compile?
Nov 9, 2009I 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 RelatedSQL & PL/SQL :: View Log Of Stored Procedure After Called By Application?
Aug 27, 2012How can I view the log of a stored procedure after it is called by an application?
View 5 Replies View RelatedPL/SQL :: Materialized View Based On Stored Procedure Data
Sep 23, 2013is it possible to base a Materialized View on results returned from a stored procedure?If not, do you see any other way except of filling a table with data from the stored procedure and then basing the MV on it?
View 4 Replies View RelatedSchema Permissions - Execute GRANT SELECT ON Xyz - Insufficient Privileges?
Sep 30, 2011I 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?
PL/SQL :: Call More Than One Stored Procedure In New Stored Procedure
Dec 24, 2012Execute sp1 param1...param6
Execute sp2 param1...param8
Execute sp3 param1...param4
All these stored procedures deals with insert/updated transactions . i need to create a new stored procedure to execute all this in a single stored procedure which will be something like
create procedure sp4(param1...param8)
as
begin
Execute sp1 param1...param6
rollback if any error
Execute sp2 param1...param8
rollback if any error
Execute sp3 param1...param4
rollback if any error
end;
SQL & PL/SQL :: Execute Dynamic Query Stored In Table?
May 27, 2011I want to execute a dynamic query which is stored in a Table.
Output of that query should be stored in database server.
Is there any way i can create a dynamic procedure? I have created a sample code but issue is i cannot make the below data type dynamic as per the query.
en com_fund_info_m%ROWTYPE;
CREATE TABLE TEMP (SQLSTATEMENT VARCHAR2(100))
DECLARE
TYPE r_cursor IS REF CURSOR;
c_emp r_cursor;
en com_fund_info_m%ROWTYPE;
[code].....
.NET Stored Procedures :: How To Send Execute String To Oracle Database
Oct 31, 2013I would like to send a raw command to the Oracle server. from .NET (System.Data.OracleClien), but I got invalid statement (ORA-00900) exception
.Excecute string:string SQL = "EXECUTE SP_THREADFILE_INSERT('" + Guid.NewGuid() + "','" + filename + "',utl_raw.cast_to_raw('" + content + "'))";
SQL & PL/SQL :: Trigger To Execute Immediate Create Or Replace View?
Jan 30, 2013i 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.
SQL & PL/SQL :: Create A Procedure Using EXECUTE IMMEDIATE?
Apr 14, 2013I 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
SQL & PL/SQL :: How To Execute Ref Cursor Procedure
Sep 22, 2012I 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].....
SQL & PL/SQL :: Execute Procedure In Cycle
Oct 27, 2011My 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.
Can Execute Procedure On Some Other Day Without Changing Actual
Aug 21, 2012I 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 RelatedForms :: Execute Procedure Using Personalization
Aug 24, 2010I 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.
SQL & PL/SQL :: How To Execute Type Variable Procedure
Apr 26, 2013I 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]...
SQL & PL/SQL :: Grant Execute On More Than One Procedure To A Role
Aug 9, 2010Oracle 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,
SQL & PL/SQL :: Procedure Cursor For Loop Don't Execute
May 13, 2010I'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 RelatedSQL & PL/SQL :: Execute A Procedure Present In Different Schema?
Apr 27, 2010I 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;
SQL & PL/SQL :: How To Call Procedure Dynamically Using Execute Immediate
Jan 2, 2011I 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 RelatedScheduling Job In Stored Procedure
Dec 21, 2011PFB code i used to schedule a job as per my requirement. And the procedure is executing fine, but when im about to run it is getting hang.
create or replace procedure scheduler_alert(frq varchar2,intrvl number) is
begin
dbms_scheduler.create_job(
job_name=>'scheduler_alert',
job_type=>'stored_procedure',
job_action=>'alertlog_error',
start_date=>SYSTIMESTAMP,
repeat_interval=>'FREQ='||frq||';INTERVAL='||intrvl,
enabled=>true,
auto_drop=>false);
end;
/
When im trying to run the job it is getting hang.
exec dbms_scheduler.run_job('scheduler_alert');
