SQL & PL/SQL :: Unable To Create DB Package?

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


ADVERTISEMENT

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 View Related

SQL & PL/SQL :: Create Pipeline Function In Package

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

To Use UTL_FILE Package To Create OS File

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

Windows :: Create BAT File With Query To Run A Package

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

SQL & PL/SQL :: Create Or Replace - Package Body Created With Compilation Errors

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

SQL & PL/SQL :: Difference Between Include Program Header Before CREATE OR REPLACE PACKAGE Statement

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

Unable To Create User On Oracle DB

Nov 18, 2010

I have encountered a problem with oracle db where by i'm unable to create a user.

oracle@kapstadt4:/export/home/oracle/scripts> ls -l
total 12
-rw-rw-rw- 1 oracle dba 1115 Sep 10 15:34 crdb.sql
-rwxrwxrwx 1 oracle dba 429 Sep 10 15:39 create_advantage_user.sql
-rw-rw-rw- 1 oracle dba 597 Sep 10 15:38 create_advantage_user_objects.sql
-rw-rw-rw- 1 oracle dba 408 Sep 10 15:37 create_advantage_user_roles.sql
-rw-rw-rw- 1 oracle dba 976 Sep 10 16:04 tbs.sql
oracle@kapstadt4:/export/home/oracle/scripts> cat create_advantage_user.sql

-- creates an advantage database user
-- the role 'advantage_admin_user' must be available!

define user_name = &1
rem define default_table_space = &2

create user &user_name
identified by &user_name#123
default tablespace IN_DEV
temporary tablespace TEMP;
grant connect to &user_name;
grant resource to &user_name;
grant advantage_admin_user to &user_name;
connect &user_name/&user_name#123
@create_advantage_user_objects.sql

[Code] ........

View 5 Replies View Related

Unable To Create Public Synonym

May 22, 2011

I have exported and imported a schema from one server to another. In the source schema, I have a public synonym. I do not know the name of that synonym. In the destination schema, the public synonym is missing. How to create the public synonym which is missing in the destination database? In the source, I queried dba_synonyms, all_synonyms... but it returns no rows selected.

View 2 Replies View Related

SQL & PL/SQL :: Unable To Create Trigger Dynamically

Dec 3, 2010

I have created a procedure to build trigger dynamically using Dynamic SQL. Here procedure created successfully, when we execute the procedure to build trigger getting the following error.

Note: We are able to create a Trigger, but it is INVALID. if we get the code of a trigger and execute, trigger created a successfully with Valid Status.

ERROR:
ORA-24344: success with compilation error
ORA-06512: at "APPS.CREATE_TRIGGER", line 28
ORA-06512: at line 2

SQL*Plus internal error state 2087, context 47:0:0
Unsafe to proceed

View 2 Replies View Related

PL/SQL :: Unable To Create Logic Sequence?

Oct 23, 2012

create table t (a varchar2(20),b number(8));
insert into t values ('aa',4);
insert into t values ('ba',6);
insert into t values ('ca',7);
insert into t values ('da',8);

in place of 8 there can be any number between 1 to 100

if in place of 8 number is <10
insert into t values('ea',10); ---- this i need dynamic insert
if in place of the number between 11-19 then
insert into t values('ea',20); ---- this i need dynamic insert
and so on

i tried as below

select case when max(b) <10 then 10 when max(b) between 10 and 20 then 20 end from t;

but i cant write case for again and again upto nth

View 3 Replies View Related

Unable To Create Database On Windows 7

Apr 3, 2013

I installed Oracle 10g XE on windows7 to create a database i opened "start database command interface" and created an SID and followed some steps afterwards i am facing "TNS ADAPTER NOT FOUND"

View 7 Replies View Related

Unable To Create Audit Trail File?

Jun 29, 2012

problem with oracle 10 g . i started service through server i got some error

ora-09925 unable to create audit trail file.

3 files are missing in admin folder these are three files adump, dpdump, pfile was missing i have not taken any backup also.

in admin file also the database folder name was missing.

View 2 Replies View Related

Server Administration :: Unable To Create Database?

Aug 21, 2013

I am installing Oracle 12c R1 on Windows 2012. The windows is configured as a domain controller. I am logged in as the domain administrator.I created a normal domain user -- DomainNameORACUSER. I provided this as the existing user id during Oracle installation.

The software installs fine, but when creating the DB using the database configuration assistant, an error is shown "error in process:...inoradim.exe. Enter password for Oracle service user: DIM-00019: create service error. O/S-Error: (OS 87) The parameter is incorrect". The DB is not created.

I skipped the db creation and completed the installation. When tried to create the db using the database configuration assistant after installation, I received the same error.I performed the same installation on Windows 8 and was successful in creating the db.

View 5 Replies View Related

Replication :: Unable To Create Materialized View

Mar 13, 2008

I have to create a materialized view for a table which does not have index on any field.

While creating a Mview i am getting an error "TABLE DOES NOT HAVE THE PRIMARY KEY CONSTRAINT".

application developers do not want to create an index on the base table onto which MView is to be created.

is there any way to create a materialised view for the table without index, or is it necessary to have the index on the base table before creating MView on it.

View 1 Replies View Related

SQL & PL/SQL :: Unable To Create Index On Large Table

Sep 30, 2012

I am trying to create a new index on large table of size around 100GB. but i am getting the following error:

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP.

temp tablespace size is : 20 GB.

does it mean that the whole index will be created at temp tablspace first?

View 3 Replies View Related

Installation :: (INS-32012) Unable To Create Directory?

Feb 20, 2013

i am trying to install oracle client on D:User-Applicationsoracle.

But i am getting this error: [INS-32012] Unable to create directory: D:User-Applicationsoracle.

i am installing on windows box my previous installations are sucessfull but i dont know why i am getting this

View 4 Replies View Related

Unable To Create INITIAL Extent For Segment In Tablespace

Jun 12, 2012

ORA-01658: unable to create INITIAL extent for segment in tablespace

I am getting above error when a batch job is running.Its insterting data in tables. I checked free space i dont know y its not using avalable space.

Database Version: 11202

select sum(bytes)/1024/1024/1024, sum(maxbytes)/1024/1024/1024 from dba_data_files where tablespace_name='test_data';

sum(bytes) sum(maxbytes)
---------------- -------------
60 0

select sum(bytes/1024/1024/1024) from dba_free_space where tablespace_name='test_data';

sum(bytes/1024/1024/1024)
----------------------
50

View 2 Replies View Related

Server Administration :: Unable To Create Control File

Jun 3, 2010

I am not able to create control file from scrip . it showing following error

ORA-01081: cannot start already-running ORACLE - shut it down first
CHARACTER SET WE8ISO8859P1
*
ERROR at line 19:
ORA-02236: invalid file name

View 4 Replies View Related

Forms :: FRM-30087 - Unable To Create File (Developer 9i)

Jun 5, 2003

i have successfully compuled and run my Developer9i form on the web but when i make a slight change in the form (add any control or do anything with form) and then recompile it it gave me error: FRM-30087: Unable to create form file why forms show this error and how it can be solved?

View 5 Replies View Related

Enterprise Manager :: Unable To Create EMCA In Oracle 11g

Mar 28, 2011

I am using Oracle 11g Rel1 on Window 2003 Server R2 Enterprise Edition x64 with SP2. I am trying to configure DB console using command throuth emca. But I am getting the below mentioned error. Even I tried after manually deleted the repository then tried to create using emca command.

==========================================================
Mar 28, 2011 3:11:16 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Mar 28, 2011 3:16:16 PM oracle.sysman.emcp.EMConfig perform
SEVERE: Error starting Database Control
Refer to the log file at D:appAdministratorcfgtoollogsemcamonetaemca_2011_03_28_15_09_53.log for more details.
Could not complete the configuration. Refer to the log file at D:appAdministratorcfgtoollogsemcamonetaemca_2011_03_28_15_09_
53.log for more details.

C:Documents and SettingsAdministrator>
===========================================================

logfile attached for your reference.

View 1 Replies View Related

Server Utilities :: Unable To Create Public Synonym?

May 22, 2011

I have exported and imported a schema from one server to another. In the source schema, I have a public synonym. I do not know the name of that synonym. In the destination schema, the public synonym is missing. How to create the public synonym which is missing in the destination database?In the source, I queried dba_synonyms, all_synonyms... but it returns no rows selected.

View 3 Replies View Related

Server Utilities :: Unable To Create Control File?

Mar 8, 2011

I tried a lot to load data to table from excel(.csv) using sql*loader the oracle version of sql*loader doesn't support the control file created using notepad(.ctl).Though i given a filename with extension as .ctl it seems as a .txt file. Is there any alternate way to create it?

View 3 Replies View Related

Unable To Create Database (Oracle 12c R1 12.1.0.1.0 / Windows 2012 )

Aug 30, 2013

I have been trying out the new 12c on Windows 2012 Essentials with the same type of errors.  The OraDim step fails and cannot create the services.  It fails a bit differently if I choose "Create New Windows User", choose a "Use Existing Windows User", or choose the Windows Built-in user. 

View 3 Replies View Related

Forms :: Unable To Create Object For Excel 2007 / Windows 7?

Aug 11, 2010

The below Oracle Forms code works fine for Excel 2003/Windows XP/IE6 but doesn't work for Excel 2007/Windows 7/IE8.

*********************
application := Client_OLE2.CREATE_OBJ('Excel.Application');

Client_OLE2.SET_PROPERTY(application,'Visible','TRUE');

workbooks := Client_OLE2.Get_Obj_Property(application, 'Workbooks');

args1 := Client_OLE2.create_arglist;
************************

There is no error while executing the form, by debugging we found that the control skips all the above code.

I'm using Oracle forms 10.1.2.3 and jre 1.6.0_16.

View 3 Replies View Related

SQL & PL/SQL :: Unable To Create Materialize View (Refresh On Fast Commit)

Nov 19, 2010

I am not able to create materialized view with 'Refresh Fast on Commit' when the table is residing on the another user environment, I am not able to find the reason why?

You can re-generate the scenario at your end with the following code-

--Login to System-
create user manu identified by manu;
grant connect, resource to manu;
create user arpit identified by arpit;
grant connect, resource to arpit;
grant create materialized view to manu;

[code]....

but when I am doing it with on demand options, materialized view is being created-

--Login to Manu-
create materialized view arpit_view
build immediate
refresh on demand
as
select * from arpit.arpit
/

View 5 Replies View Related

Export/Import/SQL Loader :: Unable To Create Master Table

Jul 2, 2013

While exporting on 11g xe on windows 32, i got messageORA-31626: job does not existORA-31633: unable to create master table

"System.Sys_export_schema_06"ORA-06512: at "SYS.DBMS_SYS_ERROR", LINE 95ORA-06512: at "SYS.KUPV$FT", line 1020ORA-01658:

unable to create INITIAL EXTENT FOR segment in tablespace SYSTEM I checked and found there is no pace in table space 600(allocated) 600(used mb) 0(free mb) 1(used) Q1) Is there any table which i can empty to free some space in system schema.Q2) Is there any way to shift some space from other table space to system becuse i have free space inother table space.i could find many segments of system table space, can i  delete few of them, if they are not used ?

View 7 Replies View Related

Reports & Discoverer :: REP-0118 / Unable To Create A Temporary File In Windows 7

Mar 14, 2012

While Running a Report through form i am getting this below error.

REP-0118 Unable to create a temporary file

But while running the same report in a windows 98 or windowsXp PC i dont get this error.

View 1 Replies View Related

Data Guard :: Unable To Create Physical Standby Database Using RMAN?

Nov 4, 2010

why RMAN is unable to create physical standby database ?

RMAN> connect auxiliary sys/system@DGREPL

connected to auxiliary database: DGREPL (not mounted)

RMAN> connect target sys/system@REPL

connected to target database: REPL (DBID=2730563515)

RMAN> run{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;

[code]....

View 19 Replies View Related

Data Guard :: Ora 09925 - Unable To Create Audit Trail File

Aug 19, 2012

[oracle@RSASPGERP02 ~]$ cd /
[oracle@RSASPGERP02 ~]$ . .bash_profile
[oracle@RSASPGERP02 ~]$ sqlplus / as sysdba
sql*plus:Release 10.2.0.4.0 - production
Error:
ORA-09925:Unable to create audit trail file

[code]....

its standby archieving problem ,the problem appears when try to connect directly or through telenet and we try to login directly using oracle user we receving following message and login fail, "GDM could not write to your authorization file,this could mean that you are out of disk space or that your home directory could not be opened for writting"

View 14 Replies View Related







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