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?
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.
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.
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
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.
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?
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.
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; /
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,
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.
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.
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.
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
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.
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 ???
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.
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,
