Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

package and function

user543623Mar 19 2012 — edited Mar 19 2012
1.
create or replace package emp_ac as
type emprectyp is record(empid int, salaryy int);
cursor cc return emprectyp;
FUNCTION nth_highest_salary (n INT) RETURN EmpRecTyp;
end emp_ac;

2. create or replace package body emp_ac as
cursor cc return emprectyp is
select idd, salary from emp;

FUNCTION nth_highest_salary (n INT) RETURN EmpRecTyp IS
emp_rec emprectyp;
BEGIN
OPEN cc;
FOR i IN 1..n LOOP
FETCH cc INTO emp_rec;
END LOOP;
dbms_output.put_line('ff');

CLOSE cc;
RETURN emp_rec;
END nth_highest_salary;
end emp_ac;

3. I want to call the package and function. How do i do it?
declare
type highest is record();
begin
highest := emp_ac.nth_highest_salary(2);
dbms_output.put_line('ff');
end;



I get error :ORA-06550: line 2, column 24:
PLS-00103: Encountered the symbol ")" when expecting one of the following:

<an identifier> <a double-quoted delimited-identifier>
current delete exists prior


Please advise.

Thanks.

Comments

OrionNet
Api,

You can simply create new temporary tablespace in new location and set it as default temporarty tablespace. And similar you can do that with undo tablespace
sqlplus '/as sysdba'


SQL>CREATE TEMPORARY TABLESPACE temp2   TEMPFILE '/new_location/temp2_01.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 28 MAXSIZE unlimited;

SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2

#drop old temp tablespace
SQL>DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;


#undo tablespace

SQL>create undo tablespace undotbs2 datafile '/new_location/newundo02.dbf' size 2000m;

#Set new tablespace as undo tablespace
SQL> alter system set undo_tablespace= undotbs2 ;

#Drop old undotbs tablespace
SQL> drop tablespace undotbs including contents;
Regards
sb92075
I want to move my undo and temporary tablespace to new path because of space issue.
I am using Oracle 10g release 2 and working on production server can't take shutdown without prior permission.
Create new UNDO & TEMP tablespaces, switch to new TS, & then drop old ones.
Surachart Opun
About create/change/drop

UNDO:

SQL>show parameter undo

NAME TYPE VALUE
------------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

SQL> CREATE UNDO TABLESPACE undotbs2 DATAFILE '+DATA_NEWPATH' SIZE 100M AUTOEXTEND ON;

http://www.oracle-base.com/articles/9i/AutomaticUndoManagement.php

SQL> alter system set undo_tablespace=undotbs2;

*** after that can drop UNDOTBS1

TEMP:
SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA_NEWPATH' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;


*** after that can drop old temp tablespace -> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES
708725
Ya thanks a lot for the answer but I have one more query...

Mine is the production server and I can't shutdown so may I do all this operation when database is open?

How do I know that users are accessing the old Undo tablespace or temporary tablespace or not, because after checking that no one is using the old ones, I can drop it. So when can I drop old undo and temp tablespaces?
oradba11
If you are trying to drop these tablespace ..which is being use by some transactions ..oracle will not allow to do that..
Read some documents on google ..thay will clear you doubts also..

enjoy
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 16 2012
Added on Mar 19 2012
4 comments
246 views