Dynamic cursor in PL/SQL
Title: Dynamic cursor in PL/SQL Hi gurus, I have to do a stored proc to compare the data of two tables in different schemas. I want to use a dynamic cursor and select the content of a table passed in argument. I saw in a document that I can work with REF CURSOR like this instance: schema_table := 'DIFF'; open schema_comp for 'select * from TABLE where table_name = :s' using schema_table; It works with the WHERE clause, but I want to use the FROM clause. I tried the following, but it was unsuccessful: schema_table := 'TABLE_DIFF'; open schema_comp for 'select * from :s' using schema_table; Has anyone ever tried this? TIA - Luc Demanche CETELEM Tél.: 01-46-39-14-49 Fax : 01-46-39-59-88
IAS-Apache and PHP
Title: IAS-Apache and PHP Hi gurus, Has anyone tried to include a PHP module to Apache in IAS ? Does Oracle support it? We have some PL/SQL applications (drive by OAS) that we need to migrate to IAS. We have some other new applications in PHP. Do we have to maintain two Apaches (one in IAS plus one for the PHP application) ? TIA Luc - Luc Demanche CETELEM Tél.: 01-46-39-14-49 Fax : 01-46-39-59-88
RE: init.ora
Title: RE: init.ora Hi Prasad, When your issue STARTUP command in svrmgrl or sqlplus, Oracle will check the file called initSID.ora in the $ORACLE_HOME/dbs directory. You could specify a initSID.ora file with this command : STARTUP PFILE=/PATH/initSID.ora Luc -Message d'origine- De: prasad maganti [mailto:[EMAIL PROTECTED]] Date: Monday, July 23, 2001 11:21 AM À: Multiple recipients of list ORACLE-L Objet: init.ora hi dbas is there anyway to see using what init.ora file i start my instance/db bcoz i hv somany init files in the same dir. they have same parameters. i forgot to remember what init file i used to start the db. can it be found from any table/or anyother source? prasad __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: prasad maganti INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OEM 2.2 client for Sun
Title: OEM 2.2 client for Sun Hi gurus, We work with OEM 2.1 client on PC and Sun stations and we wish to upgrade to OEM 2.2 (we have DBAs on Sun stations). I can't find OEM 2.2 client for Sun on the Sun Solaris 8.1.7 CD pack, I can only find it for Windows. Is it just me or does Oracle not release a client version for Sun? TIA - Luc Demanche CETELEM Tél.: 01-46-39-14-49 Fax : 01-46-39-59-88
RE: PFILE location in NT
Title: PFILE location in NT Hi Shahid, Did you specify a PFILE parameter in your startup ? If not, on NT, the default location for the parameter file is ORACLE_HOME\database. Your database is using the file in this location. Luc -Message d'origine-De: Shahid Nasir [mailto:[EMAIL PROTECTED]]Date: Tuesday, July 03, 2001 11:30 AMÀ: Multiple recipients of list ORACLE-LObjet: PFILE location in NT Hi all, I have two parameter files in two different directories on the same machine. Is there a way I can find out which parameter file is being used by the database. One way I tried was to make the changes in one file and see if it takes effect when the database is restarted. but I have more than 52 machines and it will be time consuming to do the same for all machines especially when these machines are on remote locations. Thanks. Shahid.
Question about LMT
Title: Question about LMT Hi all, I created a tablespace in LMT. I created all my datafiles with an extra 64 Kb for the bitmap index. My question is : If I want to resize my datafile, do I have to keep this extra 64 Kb ? Example : datafile size = 500M + 64 Kb = 512064 Kb resize to 50M = 51200 or 51264 Kb TIA - Luc Demanche CETELEM Tél.: 01-46-39-14-49 Fax : 01-46-39-59-88
RE: Question about LMT
Title: Question about LMT Hi Guy, My question is only for the extra 64 Kb. When we created datafiles, we added 64 Kb for the last extend (becausebitmap index need only 64 Kb). Is it important to keep this extra 64 Kb when we want to resize the datafiles. Luc -Message d'origine-De: Guy Hammond [mailto:[EMAIL PROTECTED]]Date: Wednesday, June 13, 2001 2:15 PMÀ: Multiple recipients of list ORACLE-LObjet: RE: Question about LMT Hi Luc, If you try to resize a datafile to a size below that of the actual data it contains, Oracle will warn you. Try setting Autoextend on if you're worried about running out of space on a recently shrunk file; looking at your numbers I'm guessing that you have plenty of space on the disk. As long as you keep an eye on the datafile size, it will be fine. Cheers, g. -Original Message-From: DEMANCHE Luc (Cetelem) [mailto:[EMAIL PROTECTED]] Sent: 13 June 2001 12:05To: Multiple recipients of list ORACLE-LSubject: Question about LMT Hi all, I created a tablespace in LMT. I created all my datafiles with an extra 64 Kb for the bitmap index. My question is : If I want to resize my datafile, do I have to keep this extra 64 Kb ? Example : datafile size = 500M + 64 Kb = 512064 Kb resize to 50M = 51200 or 51264 Kb TIA - Luc Demanche CETELEM Tél.: 01-46-39-14-49 Fax : 01-46-39-59-88
RE: Oracle 8i database ER diagram
Title: RE: Oracle 8i database ER diagram Hi Magesh, We use ER-Win to manage our ER diagram. You can do a Reverse Engineer to create your first ER diagram. After that, you make changes in your ER Diagram and apply this changes by ER-Win. Luc -Message d'origine- De: magesh [mailto:[EMAIL PROTECTED]] Date: Tuesday, June 12, 2001 11:00 AM : Multiple recipients of list ORACLE-L Objet: Oracle 8i database ER diagram Hi We are exploring the possibility of generating ER diagram from the Oracle 8i database. We want to know how it can be done. Thanks Regards Magesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: magesh INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Changing Java pool size to 0
Title: RE: Changing Java pool size to 0 Hi Ramesh The java_pool_size is use want you work with the JServer. Do you have a Java stored proc, EJB or CORBA in your DB ? If not, you don't need the java_pool_size. You could set it to 0. The default is 1m. Luc -Message d'origine- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Date: Tuesday, June 12, 2001 8:10 AM À: Multiple recipients of list ORACLE-L Objet: Changing Java pool size to 0 Hi List The following is config on my initsid.ora on NT4 /Oracle 816, DB size 25GB. shared_pool_size = 52428800 # INITIAL large_pool_size = 614400 java_pool_size = 20971520 Now my Question is Can I reduce java_pool_size to 0 as I am not using any Java module. I am using BLOB in my db also I am using DBstudio for DB operations on NT box (Unix users please excuse!!). Please suggest the effects and side effects of changing java_pool_size to 0. or suggest the figure. TIA Ramesh Papnoi (BrainBench Brainbuzz Certified Oracle 8/8i DBA Developer) The obstacles you face are mental barriers which can be broken by adopting a more positive approach. -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: BACKUP CONTROLFILE TRACE
Title: RE: BACKUP CONTROLFILE TRACE Hi Nirmal, The command 'alter database backup controlfile to trace' will dump the control file content on a text file with all the SQL command to recreate it. You gonna find your text file (.trc) in your USER_DUMP_DEST directory. To test it, you can do this : 1- execute the 'alter database backup controlfile to trace' command 2- remore all lines from the top of the file until STARTUP NOMOUNT 3- shutdown your database 4- delete your control files 5- execute your script -Message d'origine- De: Nirmal Kumar Muthu Kumaran [mailto:[EMAIL PROTECTED]] Date: Friday, June 08, 2001 6:37 PM À: Multiple recipients of list ORACLE-L Objet: BACKUP CONTROLFILE TRACE HI dba's, if i did 'alter database backup controlfile to trace' this command what will do?, is't create a os trace file?, if so which directory by default. For test case, to recover the db, if controlfiles are lost. and then i shutdown the db and removed the existing controlfiles from the oracle directory. Now, how can i create the control files from the backup trace file. Please help me. Regards, Nirmal -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nirmal Kumar Muthu Kumaran INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: sort_area_size + temp tablespace extent sizing
Title: RE: sort_area_size + temp tablespace extent sizing Stephane, What situation would make you choose a certain value (i.e.: using '3' versus '4.5')? -Message d'origine- De: paquette stephane [mailto:[EMAIL PROTECTED]] Date: Tuesday, April 24, 2001 6:03 PM À: Multiple recipients of list ORACLE-L Objet: Re: sort_area_size + temp tablespace extent sizing The extent size of the temp tablespace should be a multiple of the sort_area_size parameter + 1 block. If your sort_area_size is 128K and if you need to go to temp tablespace that means that 128K is not enough so you should allocate for more than 128K. temp extent = (sort_area_size*n)+ 1 block where n goes from 2 to usually 4,5 depending on your situation --- Adrian Roe [EMAIL PROTECTED] a écrit : Hi List, Quick question regarding temp tablespace extent sizing. I have always assumed that the extent size for a temporary tablespace needs to be equal to the size that sort_area_size is set to. A sort that can't fit into memory then goes and grabs temp tablespace extents of a size equal to sort_area_size. However, I have read somewhere that the extent size should be equal to sort_area_size + block size (the block being used for header infomation or something like that) eg. 8k block size and 64k sort area size results in 72K temp tablespace extent size. So, assume I have a sort area size of 128K and block size of 8k, do I need to set temp tablespace extent sizes to 128K or 136K, or doesn't it really matter. Thanks, Ade -- The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Adrian Roe INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Pour faire vos courses sur le Net, Yahoo! Shopping : http://fr.shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Manage of 9IAS
Title: Manage of 9IAS Hi gurus, I installed 9IAS on a Unix server. I installed all Oracle products with the Unix user ORACLE and its primary group OINSTALL. My installation is done, but I want to manage my IAS with another user called IAS and primary group DBA. My problem is : Everything (logs, httpd.pid) belong to Unix user ORACLE (OINSTALL). I can't manage 9IAS with another user. For those of you working with IAS, do you manage it using Oracle user ? Or have you changed the file permissions ? Or reinstall with ORACLE user and group DBA. ? TIA - Luc Demanche CETELEM Tél.: 01-46-39-14-49 Fax : 01-46-39-59-88
Crontab
Title: Crontab Hi gurus, I want to schedule a script to run on the fourth sunday of the month. How can I do that ? TIA - Luc Demanche CETELEM Tél.: 01-46-39-14-49 Fax : 01-46-39-59-88
insertion into CLOB
Title: insertion into CLOB
Hi gurus,
Oracle 8.1.6.2
Solaris 2.6
I'm doing some tests with a CLOB field. I created a stored procedure, which inserts text into a CLOB.
Here is my table :
create table relance (nom varchar2(50), texte CLOB);
Here is my code :
create or replace procedure insert_clob is
buffer varchar2(32767);
Lob_loc CLOB;
Amount BINARY_INTEGER;
Position INTEGER := 1;
begin
buffer := 'test de clob, insertion dans le clob pour par le suite le lire';
INSERT INTO relance(nom,texte) VALUES('luc',EMPTY_CLOB());
SELECT LENGTH(buffer) INTO Amount
FROM dual;
SELECT texte INTO Lob_loc
FROM relance
WHERE nom = 'luc'
FOR UPDATE;
DBMS_LOB.WRITE (Lob_loc, Amount, Position, Buffer);
end insert_clob;
My procedure has been running for 30 minutes. Is this normal ?
TIA
-
Luc Demanche
CETELEM
Tél.: 01-46-39-14-49
Fax : 01-46-39-59-88
