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

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

Create Trigger To Execute A Stored Procedure During Source Code Compile?

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

Ora-06502 - Create Generate Procedure That Create Dynamic Procedure Through DBMS_SQL

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

SQL & PL/SQL :: How To Execute Ref Cursor Procedure

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

SQL & PL/SQL :: Execute Procedure In Cycle

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

How To Execute A Stored Procedure In Oracle

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

Can Execute Procedure On Some Other Day Without Changing Actual

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

Forms :: Execute Procedure Using Personalization

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

SQL & PL/SQL :: How To Execute Type Variable Procedure

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

SQL & PL/SQL :: Grant Execute On More Than One Procedure To A Role

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

SQL & PL/SQL :: Procedure Cursor For Loop Don't Execute

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

SQL & PL/SQL :: Execute A Procedure Present In Different Schema?

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

SQL & PL/SQL :: How To Call Procedure Dynamically Using Execute Immediate

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

SQL & PL/SQL :: DML In Execute Immediate Inside A Stored Procedure

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

Block Of Procedure Taking Time To Execute

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

ORA-00904 / Invalid Identifier - Not Able To Run EXECUTE IMMEDIATE In PL/SQL Procedure

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

Forms :: How To Execute A Stored Procedure Without Parameter

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

Client Tools :: How To Execute A Procedure Within Editor

Jan 15, 2010

something like

exec package name.procedure ?

View 3 Replies View Related

SQL & PL/SQL :: Execute File In Oracle Stored Procedure

May 16, 2012

I need to execute a .sql file in an oracle stored procedure.

View 9 Replies View Related

Execute AUDIT SELECT ON EMPLOYES From Procedure?

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

SQL & PL/SQL :: Using EXECUTE IMMEDIATE For DDL Command In Procedure For Password Change?

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

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

PL/SQL :: Execute Procedure By Passing Number Array

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

PL/SQL :: Create Sequence Using EXECUTE IMMEDIATE Statement?

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

PL/SQL :: Create Temporary Table Using Execute Immediate

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

Execute / Display Results Of Procedure With Ref Cursor OUT Parameter

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

Execute Java Program From Oracle Procedure Which Is On Different Machines

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

View A Stored Procedure Without Granting Execute Permissions?

Jul 13, 2012

Is there a way to grant rights to view a stored procedure without granting execute permissions?

View 2 Replies View Related

Client Tools :: How To Execute Stored Procedure With Cursor Within PL/SQL

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







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