SQL & PL/SQL :: Create A Procedure Using EXECUTE IMMEDIATE?
Apr 14, 2013
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
View 3 Replies
ADVERTISEMENT
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
Nov 9, 2009
I 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 Related
Mar 31, 2004
ORA-06502...I have database on oracle 9i on Solaris 9. I create a generate procedure that create dynamic procedure through DBMS_SQL. On this database I got the ORA-06502 error. When I tried to run the same procedure on the same database on oracle 8i on NT this work fine.
View 3 Replies
View Related
Sep 22, 2012
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].....
View 2 Replies
View Related
Oct 27, 2011
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.
View 4 Replies
View Related
May 22, 2012
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.
View 1 Replies
View Related
Aug 21, 2012
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 Related
Aug 24, 2010
I 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.
View 2 Replies
View Related
Apr 26, 2013
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]...
View 2 Replies
View Related
Aug 9, 2010
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,
View 2 Replies
View Related
May 13, 2010
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 Related
Apr 27, 2010
I 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;
View 4 Replies
View Related
Jan 2, 2011
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 Related
Apr 28, 2011
Requirement 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 Related
Feb 9, 2011
I 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.
View 2 Replies
View Related
Feb 4, 2012
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.
View 3 Replies
View Related
Jun 17, 2010
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
View 5 Replies
View Related
Jan 15, 2010
something like
exec package name.procedure ?
View 3 Replies
View Related
May 16, 2012
I need to execute a .sql file in an oracle stored procedure.
View 9 Replies
View Related
Dec 16, 2010
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.
View 3 Replies
View Related
Nov 6, 2011
I 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.
View 6 Replies
View Related
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
Oct 26, 2012
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.
View 7 Replies
View Related
Dec 14, 2012
Is it possible to create sequence using EXECUTE IMMEDIATE statement. The sequence name will be the bind variable.
DECLARE
TEMP VARCHAR2(20);
BEGIN
TEMP := :P2_INFO;
EXECUTE IMMEDIATE 'CREATE SEQUENCE' TEMP;
END;
This creates a sequence named TEMP, but i want the name :P2_INFO entered by user.
View 10 Replies
View Related
Jun 11, 2012
How can i create a temporary table using EXECUTE IMMEDIATE ??
Like:
CREATE GLOBAL TEMPORARY table new_table as (Select * from old_table);
View 11 Replies
View Related
Apr 12, 2012
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.
View 4 Replies
View Related
Oct 10, 2012
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
View 3 Replies
View Related
Jul 13, 2012
Is there a way to grant rights to view a stored procedure without granting execute permissions?
View 2 Replies
View Related
Apr 5, 2011
To 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
View 4 Replies
View Related