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
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
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.
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.
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
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".
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;"
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);
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
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')) "
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>
Is there any difference between include program header before CREATE OR REPLACE PACKAGE statement and program header after CREATE OR REPLACE PACKAGE statement
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').
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:
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?
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... ?
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.
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
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..
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?
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?
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?
