RE: TRUNCATE IN PL/SQL
You can try the following , not sure if it will work though..let me know if it does... :-) DECLARE myCur number; mySQL varchar2(2000); BEGIN mySQL := 'TRUNCATE TABLE (table_name)'; myCur := DBMS_SQL.open_cursor; DBMS_SQL.PARSE(myCur, mySQL, DBMS_SQL.NATIVE); DBMS_SQL.CLOSE_CURSOR(myCur); END; Regards, Karthik -Original Message- Sent: Thursday, June 21, 2001 10:55 AM To: Multiple recipients of list ORACLE-L Hello guru , how can I execute a truncate table in PL/SQL ?? It only work for delete DML only ? why ? Raymond Lee Infopro Sdn Bhd Block B3 Level 8, Leisure Commerce Square No. 9, Jalan PJS 8/9 46150 Petaling Jaya Selangor , Malaysia Tel : 603-7876 ext : 266 Fax : 603-78761233 Email : [EMAIL PROTECTED] "Friendship with oneself is all important, because without it one cannot be friend with anyone else in the world " - Eleanor Roosevelt -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Karthik (GEP) 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:
Title: RE: Hi , When trying to exceute a PL/SQL program which reads data from a file into a record (using utlfile) , I get an error as listed below ORA-04030: out of process memory when trying to allocate 8208 bytes (PLS non-lib hp,PL/SQL STACK) ORA-07324: smpall: malloc error while allocating pga. HP-UX Error: 12: Not enough space Any ideas on what the problem is Regards, Karthik Mohan TCS Consultant at GEP (HK) DialComm 3310962 ISD : 852-26290962 Please see the official ORACLE-L FAQ: http://www.orafaq.com 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: SQL PROBLEM
Title: MS access I do not seem to have exactly understood u r problem..but try this anyway select count(*) from awhere l_id in (select b.bl_idfrom a,bwhere a.o_id = 17and a.o_id = b.bo_id )OR(a.o_id = 17) COUNT(*)-- 2 1 row selected. The table data is as listed below SQL> select * from a; O_ID L_ID -- -- 17NULL NULL 42 2 rows selected. SQL> select * from b; BO_ID BL_ID-- -- 17 42 17 43 17 44 3 rows selected. Regards, Karthik Mohan TCS Consultant at GEP (HK) DialComm 3310962 ISD : 852-26290962 -Original Message-From: Niyi Olajide [mailto:[EMAIL PROTECTED]]Sent: Friday, May 25, 2001 4:36 PMTo: Multiple recipients of list ORACLE-LSubject: RE: SQL PROBLEM Can you try this: select count (1) from ( select 1 from table_ a where ord_id = 17 union select 1 from table_b where ord_id = 17) / Hi, Favour me in suggest a hint in writing a sql for following scenaio: Table A has 2 columns ord_id and line_id example rows are: ord_id line_id 17 null null 42 Table B has columns ord_id and line_id ord_id line_id 17 42 17 43 1744 I have to write a sql to count(*) from table A where ord_id=17 .. as u see from table B line 42 is row of ord_id=17 ..so i want count(*) should show =2 from tabel A but if i do count(*) from table A where ord_id=17..it shows 1.. i need to put another condition which will check line id with TABLE B and if it is for this particular order ...add that also in count(*). I know scenario is little fuzzy sorry for it..but if u got my point kindly respond. Thanks Harvinder
RE: unix command question
Title: RE: unix command question You can also try the following ll |grep ^d Regards, Karthik M -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 02, 2001 11:20 AM To: Multiple recipients of list ORACLE-L Subject: Re: unix command question Haven't seen this one mentioned yet: find . -type d -print | xargs ls -ld Jared On Tuesday 01 May 2001 19:55, David A. Barbour wrote: > ls -Ra | grep / > > That's ls -Ra 'pipe' grep / > > You know, the spell checker has fits with the stuff on this list. > > Regards, > > David a. Barbour > Oracle DBA, OCP > > Roy Ferguson wrote: > > what I would like to see is all directories...not files starting with a > > particular letter but all directories... > > > > ls -d - doesn't work > > ls -ld p* - doesn't work either > > > > >Roy, > > > > > >You could do say ls -ld p* to list the directories starting with p. > > > > > >Rgds, > > > > > >raja > > >-- > > > > > >On Tue, 01 May 2001 13:35:46 > > > > > > Roy Ferguson wrote: > > >>what is the ls command to view only a list of directories? > > >> > > >>ls -la lists both files and directories...I want to view only a list of > > >>directories... > > >> > > >>environment is sun sparc solaris 2.6 > > >> > > >>thanks in advance > > >> > > >>roy > > >> > > >>-- > > >>Please see the official ORACLE-L FAQ: http://www.orafaq.com > > >>-- > > >>Author: Roy Ferguson > > >> 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). > > > > > >Get 250 color business cards for FREE! > > >http://businesscards.lycos.com/vp/fastpath/ > > >-- > > >Please see the official ORACLE-L FAQ: http://www.orafaq.com > > >-- > > >Author: Viraj Luthra > > > 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). > > > > Roy E. Ferguson II > > Intel Sacramento > > 916-854-1123 > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Roy Ferguson > > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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: how to install Oracle Developer2000 on Windows98 ?
Title: RE: how to install Oracle Developer2000 on Windows98 ? Hi Bamabang, Did you check the entry for the database in your tnsnames ora file ? Regards, Karthik Mohan TCS Consultant at GEP (HK) DialComm 3310962 ISD : 852-26290962 -Original Message- From: Bambang Setiawan [mailto:[EMAIL PROTECTED]] Sent: Saturday, April 21, 2001 5:16 PM To: Multiple recipients of list ORACLE-L Subject: how to install Oracle Developer2000 on Windows98 ? dear listers, any body knows how to install Oracle Developer2000 on Windows98 ? I installed it on my machine but I can't even connect to my database thanks in advance , regards, bambang <> Bambang Setiawan <> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bambang Setiawan 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).
Problem in Customer Interface
Hi, I am working in a multi-org AR setup (Ver 10.7) and trying to do a customer interface. I need to duplicate a single customer address under three different orgs. I therefore populated the ra_customers_interface table with three identical records except for the orgID. But when I run the customer interface under a specific responsibility which is tied to a certain org , the addresses gets duplicated under the same org i.e. all the three records are populated in the same org . The Customers Interface script seems not to be able to recognise different orgs. I also compared a single org and the multi org executable ( the script is called RACUST) and they seem to be identical in size. I think that the executable should be different since a multi-org program would be different from that of a single org . Can someone please confirm this? Pls let me know if you have any suggestions as to how I can overcome this problem. Regards, Karthik Mohan TCS Consultant at GEP (HK) DialComm 3310962 ISD : 852-26290962
RE: about analyse table..
Title: RE: about analyse table..
Hi Saurabh,
The Analyse command helps to collect information on the specified table and updates the system table called user_tables with this info
This helps to optimize any query which is run on the table after the table is analyzed. You can take help from the SQLPLUS prompt if it is avaliable. The help is also listed below in case you do not have HELP option
help analyze
ANALYZE command
PURPOSE:
To perform one of these functions on an index, table, or cluster:
* to collect statistics about the object used by the optimizer and
store them in the data dictionary
* to delete statistics about the object from the data dictionary
* to validate the structure of the object
* to identify migrated and chained rows of the table or cluster
SYNTAX:
ANALYZE
{ INDEX [schema.]index
{ { COMPUTE STATISTICS
| ESTIMATE STATISTICS [SAMPLE integer {ROWS | PERCENT}]
| DELETE STATISTICS }
| VALIDATE STRUCTURE }
| {TABLE [schema.]table | CLUSTER [schema.]cluster}
{ { COMPUTE
| ESTIMATE [SAMPLE integer {ROWS | PERCENT}]
| DELETE } STATISTICS
| VALIDATE STRUCTURE [CASCADE]
| LIST CHAINED ROWS [INTO [schema.]table] } }
where:
INDEX
identifies an index to be analyzed. If you omit schema, Oracle
assumes the index is in your own schema.
TABLE
identifies a table to be analyzed. If you omit schema, Oracle
assumes the table is in your own schema. When you collect
statistics for a table, Oracle also automatically collects the
statistics for each of the table's indexes.
CLUSTER
identifies a cluster to be analyzed. If you omit schema, Oracle
assumes the cluster is in your own schema. When you collect
statistics for a cluster, Oracle also automatically collects the
statistics for all the cluster's tables and all their indexes,
including the cluster index.
COMPUTE STATISTICS
computes exact statistics about the analyzed object and stores them
in the data dictionary.
ESTIMATE STATISTICS
estimates statistics about the analyzed object and stores them in
the data dictionary.
SAMPLE
specifies the amount of data from the analyzed object
Oracle samples to estimate statistics. If you omit
this parameter, Oracle samples 1064 rows. If you
specify more than half of the data, Oracle reads all
the data and computes the statistics.
ROWS
causes Oracle to sample integer rows of the table or
cluster or integer entries from the index. The
integer must be at least 1.
PERCENT
causes Oracle to sample integer percent of the rows
from the table or cluster or integer percent of the
index entries. The integer can range from 1 to 99.
DELETE STATISTICS
deletes any statistics about the analyzed object that are currently
stored in the data dictionary.
VALIDATE STRUCTURE
validates the structure of the analyzed object. If you use this
option when analyzing a cluster, Oracle automatically validates the
structure of the cluster's tables.
CASCADE
validates the structure of the indexes associated with the table or
cluster. If you use this option when validating a table, Oracle
also validates the table's indexes. If you use this option when
validating a cluster, Oracle also validates all the clustered
tables' indexes, including the cluster index.
LIST CHAINED ROWS
identifies migrated and chained rows of the analyzed table or
cluster. You cannot use this option when analyzing an index.
INTO
specifies a table into which Oracle lists the
migrated and chained rows. If you omit schema,
Oracle assumes the list table is in your own schema.
If you omit this clause altogether, Oracle assumes
that the table is named CHAINED_ROWS. The list table
must be on your local database.
PREREQUISITES:
The object to be analyzed must be in your own schema or you must
have the ANALYZE ANY system privilege.
If you are using Trusted Oracle in DBMS MAC mode, your DBMS label
must match the creation label of the object to be analyzed or you
must satisfy one of these criteria:
* If the object's creation label is higher than your DBMS label, you
must have READUP and WRITEUP system privileges.
* If the object's creation label is lower than your DBMS label, you
must have WRITEDOWN system privilege.
* If the object's creation label and your DBMS label are
noncomparable, you must have READUP, WRITEUP, and WRITEDOWN system
privileges
