PL/SQL :: How To Create A Package With Procedures
Aug 15, 2013
By referring through net I have written the following PCK file. I have installed my Oracle 11g database in VirtualBox(Win XP). I can able to select the tables using query from the host(Windows 7)
CREATE OR REPLACE PACKAGE APEX_SYS_PCK AS TYPE refcursortype IS REF cursor PROCEDURE GET_USER_DETAILS( D1 OUT refcursortype, P_USER IN VARCHAR2, P_PASS IN VARCHAR2);END APEX_SYS_PCK; / CREATE OR REPLACE PACKAGE BODY APEX_SYS_PCKAS PROCEDURE GET_USER_DETAILS( D1 OUT refcursortype, P_USER IN VARCHAR2, P_PASS IN VARCHAR2) IS BEGIN OPEN D1 FOR SELECT * FROM APEX.PERSONS; END GET_USER_DETAILS; END APEX_SYS_PCK;/
When I try to run the script I am getting the following error. How to solve this problem?
Project: sqldev.temp:/IdeConnections%23vAPEX.jprF:PACKAGESAPEX_SYS_PCK.sqlError: PL/SQL: Compilation unit analysis terminatedError(1,14): PLS-00905: object APEX.APEX_SYS_PCK is invalidError(1,14): PLS-00304: cannot compile body of 'APEX_SYS_PCK' without its specification
View 9 Replies
ADVERTISEMENT
Aug 30, 2007
I could execute a package for eg if i had a package with procedures related to statistics and i run them each night, could i just do an exec on the package and it would run all those procedures??
Its not possible but i could call each procedure from ONE procedure
View 2 Replies
View Related
May 4, 2010
I think there is no limit for number of procedures and functions can be written in a package, but little much confusion.
What is the maximum number of Procedures and Functions can be written in a package? is such type of limit is there or not ?
View 4 Replies
View Related
Apr 6, 2011
how many functions and Procedures are exist in Package. Is it possible to identify through query.
Eg: I have one Package:- ABC. This ABC package having 2 functions and 3 procedures. I want, total no.of functions and total no.of procedures through query.
View 1 Replies
View Related
Apr 16, 2013
how i cant get the private package procedures(the procedures that are defined only in the pcakage body) information?Specially the name and arguments??
Every simple Oracle client presents this type of information, so I think there is a way to get it?
View 2 Replies
View Related
Jul 6, 2012
I have huge package having numerous procedures. Therein there is one wrapper procedure which is invoked from front end and which in turn calls other stored procedures within the package. The stored procedures called return the result to the wrapper procedure which in turn calls other stored procedures.
The ultimate result is returned to the calling environment by the wrapper procedure itself.I need to know the time taken in totality by the wrapper procedure along with the individual execution time of each procedure called by the wrapper procedure. I am not allowed to modify the code for adding timestamp capturing though.
View 1 Replies
View Related
Jan 27, 2011
1- I do not have access to TOAD for Oracle yet
2- I can connect to SQLPLUS: Release 9.2.0.1.0
3- We have many stored procedures in packages that are available from the TOAD for Oracle interface
4- I need to be able to see the specs/body of some packages containing some procedures.
5- I am connected to the appropriate DataBase1 (for example), but from here what to do from SQLPLUS command prompt ?
For example: SchemaName1.PackageName1.ProcedureName1
View 39 Replies
View Related
Aug 16, 2013
i want to give privilege of create trigger,procedures and functions privileges to a user "A"on the schema "B". how can i do it. i've already given select,insert,update,delete privilege to user "A"
how can user "A" create trigger(etc.) on tables of user "B".
View 1 Replies
View Related
Jul 16, 2012
Im doing some create view and create procedures for my work.In creating view, its just done perfectly.with create procedures work, I got some problem with the result.
So here is the coding:
create or replace
PROCEDURE "USP_EDW_CASH_MARGIN"
(
result_cursor OUT TYPES.cursor_type
)
[code]....
After i run this coding.
I got these errors :
1)Error(46,5): PL/SQL: SQL Statement ignored.
2)Error(46,5): PLS-00394: wrong number of values in the INTO list of a FETCH statement
It says that the error is with this code "FETCH v_cursor INTO v_row;"
View 6 Replies
View Related
Mar 1, 2012
I´m having a trouble creating a db package. I´ve changed my job and now i´m working full on DB instead of Forms Developer.
So my trouble is that i want to create some like a global cursor on DB package, i know you can create global variables and use it on all the procedures/function inside the package. But how about cursors?
My problem: I dont´know how to declare into the spec. I don´t know how to use it on body.
My actual
I have the structure below:
PackageSpec:
CREATE OR REPLACE PACKAGE [i]MyPackageName[/i]
IS
CURSOR [i]myGlobalCursor[/i](par1 IN VARCHAR2, par2 IN DATE)(
);
PROCEDURE [i]MyProcedure1[/i](par1 IN VARCHAR2, par2 VARCHAR2);
[code]....
View 13 Replies
View Related
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
Apr 26, 2013
I want to use UTL_FILE package to create OS file. How to resolve this error. Oracle11g under XP.
SQL> create directory my_dir as 'c: emp';
Directory created.
1 create or replace procedure test_1(md in varchar2)
2 is
3 file utl_file.file_type;
4 begin
5 file := utl_file.fopen(md,'abc.log','w');
6 utl_file.put_line(file,'EMPLOYE REPORT');
7 utl_file.fclose(file);
8* end;
SQL> /
Procedure created.
SQL> execute test_1('MY_DIR');
BEGIN test_1('MY_DIR'); END;
RROR at line 1:
RA-06510: PL/SQL: unhandled user-defined exception
RA-06512: at "SYS.UTL_FILE", line 98
RA-06512: at "SYS.UTL_FILE", line 157
RA-06512: at "SCOTT.TEST_1", line 5
RA-06512: at line 1
View 2 Replies
View Related
Dec 14, 2010
I need to create a bat file which include a query to run a package. I use plsql developer to develop the package. its username,password and database is user,pswd,db1 respectively. the query to run the package is "SELECT
COLUMN1 AS "LAST NAME",
COLUMN2 AS "FIRST NAME",
COLUMN3 AS "LOCATION"
FROM TABLE(PKG.GET_SUM('09-NOV-2010','12-NOV-2010')) "
what code should I write to create a bat file.
View 13 Replies
View Related
Mar 8, 2012
CREATE OR REPLACE PACKAGE pkg_mkt_hub_load_collection
AS
PROCEDURE sp_final_load_mkt_hub;
END pkg_mkt_hub_load_collection;
/
CREATE OR REPLACE PACKAGE BODY pkg_mkt_hub_load_collection
AS
c_default_limit CONSTANT PLS_INTEGER:=5000;
[code]....
show error
error code
SQL> @pkg_mkt_hub_load_collection.sql
Package created.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY PKG_MKT_HUB_LOAD_COLLECTION:
LINE/COL ERROR
-------- -----------------------------------------------------------------
57/4 PL/SQL: Statement ignored
PLS-00306: wrong number or types of arguments in call to 'MULTISET_INTERSECT_ALL'
SQL>
View 11 Replies
View Related
Mar 2, 2010
Is there any difference between include program header before CREATE OR REPLACE PACKAGE statement and program header after CREATE OR REPLACE PACKAGE statement
View 4 Replies
View Related
May 28, 2013
I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').
What I would need is something like $$PLSQL_UNIT
View 8 Replies
View Related
Jan 15, 2012
I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
View 4 Replies
View Related
Dec 13, 2009
create type employee is object(
name varchar2(30),
member procedure change_name(new_name varchar2)
)
[Code]....
Now how to use the change_name procedure to change the name of the employees in the table?
View 1 Replies
View Related
Aug 29, 2012
I have a schema sys_eg .I dont have any DBA privileges .How can i delete all the procedure with one sql or pl/sql.
ORACLE 11G
I am using sql developer 3.2 version tool
View 6 Replies
View Related
May 23, 2013
runtime monitoring procedures....need a sample script
View 1 Replies
View Related
Mar 17, 2011
I have a job that runs two procedure parallely . The code is as follows:
DECLARE
jobno1 NUMBER;
jobno2 NUMBER;
flag NUMBER;
BEGIN
dbms_job.submit(jobno1,'pc;', next_date=>SYSDATE);
dbms_job.submit(jobno2, 'pc2;', next_date=>SYSDATE);
[code]....
And the stored procedure (in SQL File) is as follows:
procedure pc:
create or replace procedure pc
as
begin
for idx in 1..100 loop
dbms_output.put_line(idx);
end loop;
end;
/
procedure pc2:
create or replace procedure pc2
as
begin
for idx in 1..1000 loop
dbms_output.put_line(idx);
end loop;
end;
/
However, when I execute the job , I get the following output:
1
2
3
.
.
.
.
100
[code]....
Which indicates the job 1 got executed 1st and then job2. But this is not what I want.
View 4 Replies
View Related
Aug 28, 2007
whats the best way to modify a table via a procedure for example if i have a table with records: id, fn, ln, address, tel, dob
how would i go about creating a procedure that modifies a record. The problem is what param needs to go in? i could certainly have different procedures that modify different things like one for modifying tel, one for modifying address, but is there a way to combine it all into ONE procedure so that if user wanted he could put in exec modifyTable(2, 099898787) to modify tel and then modifyTable(2, 'a new address') to modify address... ?
View 6 Replies
View Related
May 4, 2010
we are doing a performance tuning on a large database. Our main aim is to capture all the packages/procedures/view that are running on database. Below given is the format we have prepared
Eg:
SPIdSP_Name CPU TimeI/OLine Executed
1Emp_AllDetails221
1Emp_AllDetails582
1Emp_AllDetails25103
[code]...
Requirement:Write an Procedure that captures the above given information in the background of database and stores the information in a table.write the procedure to capture the above given information.
View 17 Replies
View Related
Apr 8, 2011
I come from a Microsoft SQL background and am having trouble adjusting to the PL/SQL syntax. I'd like to build this up to pass back a certain value back into a web form.
IF (SELECT COUNT(*) FROM EXCURSION WHERE EXCURSION_ID = 75)= 1 THEN
DBMS_OUTPUT.PUT_LINE 'True';
ELSE
DBMS_OUTPUT.PUT_LINE 'False';
END IF;
I'm having trouble writing values out at the moment
View 7 Replies
View Related
Jul 21, 2011
I've got some quantity of procedures in package
procedure1
procedure2
...
procedureN
I should call them in cycle instead of typing each of them manually. I know it's a bad idea to do something like this "dynamically".
View 13 Replies
View Related
Oct 8, 2012
I am using for writing text files data to database. The problem here is let us assume there are 6 records in text file and if there is a problem at 2nd record, the later records are not getting inserted.
CREATE OR REPLACE PROCEDURE PROC1
IS
temp varchar2(500);
tmp_name varchar2(5);
tmp_no varchar2(4);
.
BEGIN
WHILE NOT end_of_file
LOOP
IF i = 18 THEN
tmp_no := temp;
END IF;
IF i = 21 THEN
tmp_name := temp;
END IF;
END LOOP;
END;
/If i=18 and temp = '12345' here, then tmp_no := temp; won't work (tmp_no varchar2(4);)
Similarly, If i=21 and temp = 'ABCDEFG' here, then tmp_name := temp; won't work (tmp_name varchar2(5));
how to handle this through EXCEPTIONS so that even if there is a problem with 1 record, while loop remain working for further records..
View 4 Replies
View Related
Jun 2, 2009
Where are the server side procedures are stored? Where are the server side procedures are visible??
View 5 Replies
View Related
Jul 22, 2010
Let us say there is an oracle table A. How do i find out what stored procedures/packages/views are using this table A.
Reason to know: When some DDL changes happen on table A, how do i know what are the impacted sp/pkg/views which should also be modified. Is there any query with which i can find this?
View 5 Replies
View Related
Feb 21, 2010
When creating objects, is there a way telling Oracle not to store(overwrite) procedures or other DDL in the database until the statements have no compilation errors?
View 4 Replies
View Related
Jun 2, 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 any way to rename a column in a table A, so that it is changed wherever it is used, like all the procedures that uses this column, or other tables that has reference to this table A?
View 5 Replies
View Related
