|
 |
| Recent blog posts: | |
|
| Forums: | | |
|
| Discuss: | |
|
| Recent forum topics: | |
|
| Recent forum comments: | |
|
| Модератор: | |
|
Sunday, 6 August 2006
|
| using PL/SQL tables to store values Guest 15:10:06 |
| | Hello All,
I am trying to write an application for Student Transfer Articulation.
I have 3 fields(1st one is a drop down menu for "Subject", 2nd one is a
text field for "Course Number" and 3rd one is a drop down menu for
"Year") to make up a row on my form. I am allowing a maximum of 10 rows
on the form.
I am planning to use PL/SQL tables to store the values that are entered
on the form.
Can someone let me know how I can do this ? I mean, how do I actually
get the values from the form into the PL/SQL table and then retrieve
those ? I have the front end ready and want to know how things would
happen once I click on the "Submit Query" button.
I am new to PL/SQL etc and hence the question.
- Ardy
|
| |  8 answers | Add comment |
|
| how to get the cursor to return 2 sets of values Guest 01:21:42 |
| | Hello All,
I have a cursor as follows.
cursor StvsbgiC(p_inst varchar2 default null) is
select *
from stvsbgi
where stvsbgi_code = p_inst or p_inst is null
order by stvsbgi_desc;
In my first procedure, my value of p_inst is null and hence the result
set of the cursor is computed accordingly. At the end of the 1st
procedure, p_inst gets a value.
Now in my 2nd procedure, how can I get the cursor to return me the
result set based on that value ?
If I try to access the cursor in my 2nd procedure, I am still getting
the results of the 1st computing of the cursor(when p_inst was null).
Any input would be appreciated.
thanks
- Ardy
|
| | Add comment |
Saturday, 5 August 2006
|
| tuning advisor ... again Akimeu 16:04:07 |
| | Hi all...
As mentioned in one of my previous posts, we do not have the Enterprise
Manager installed on our systems. So, I ran the addmreport, and then
the sql in sqlplus to see the recommended changes. However, there are
quite a few recommendations that state:
Run SQL Tuning Advisor on the SQL Statement with SQL_ID "blahblah123".
How can I accomplish this throught sqlplus without the EM??? If need
be, I can install EM on another machine... but, at the same time, I will
have to exp the database, etc... over as well. Would like to avoid that
if AT ALL possible. Thanks for your insight.
Regards,
Al
|
| | 6 answers | Add comment |
|
| How to Get Every two weeks settlement --allways Wednesday Guest 15:53:23 |
| | Hi,
I am trying to get solution for this. I need to get settlement every
two weeks and it alwyas happens on "Wednesday"
Example :To Day's Date Jul 19
My output look like this
Date Day
Jul19 Wed
Aug 2 Wed
Aug 16 Wed
Aug 30 Wed
....so on till End Date which is 30 years
Kindly let me know ASAP
Oracle Version:Oracle 9i, restriction no PL/SQL
Regards
Jay
|
| | 13 answers | Add comment |
|
| ORA-01861 Literal Does Not Match Format String Guest 14:09:51 |
| | I am using the following code:
Select trunc (To_Date (call_start_dt)),
count(*)
from IVRREPORTING.CALLDETAIL
where
(call_start_dt > '2006-07-01 00:00:00'
and
call_start_dt < '2006-07-31 00:00:00'
and
id_calllogging is null)
group by
trunc (To_Date (call_start_dt))
Getting the following error:
ORA-01861 Literal Does Not Match Format String
Can anyone offer some advice?
|
| | 4 answer | Add comment |
|
| Partitioned Table Indexes Dereck L. Dietz 11:37:32 |
| | Where I work they have a number of partitioned tables but they have not
partitioned any of the indexes on these tables.
Pardon my naivity but would/could someone explain whether, in general, this
is a good idea or not?
Thanks.
|
| | 3 answer | Add comment |
|
| Count Function - Oracle 9i Raghu 04:44:50 |
| | When a column is defined as not null for a table, count(*) and
count(column) should return the same result. Correct?
However I am getting different results. What could be the reason?
Thanks.
|
| | 1 answer | Add comment |
|
| getting closer.... with auto sql tuning w/o em Akimeu 03:47:48 |
| | Ok... So thanks to Brian P. and Dan Morgan, I am a bit further in my
quest of trying to see the tuning results from addm/advisor through
sqlplus w/ out EM. However, I am not stuck again... any help would be
appreciated.
I ran the addmrpt and awrrpt, from which I see the SQL ID's for
particular expensive sql statements. I now try to pass those sql id's
to the dbms_sqltune.create_tuning_task api and am getting error message:
Invalid SQL Statement
All I'm doing is something like this from sqlplus:
exec dbms_sqltune.create_tuning_task(sql_id=>'id',
plan_has_value=>null,scope=>'COMPREHENSIVE', time_limit=>60,
task_name=>'tester',description=>'description');
(docs ref:
<http://download-east.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_sqltun.htm#1001021>)
Thanks for your insight.
|
| | 2 answer | Add comment |
|
| newbie : writing an efficient query Dn.Perl@Gmail.Com 03:44:53 |
| |
I have a table t1 : time1 unique, qty
Entries : (1, 10) (2, 20) (3,30) (32,20)
I want to find out the time1 values where qty is the same.
select a1.time1, a2.time1
from t1 a1, t1 a2
where a1.qty = a2.qty and a1.time1 <> a2.time1 -- and a1.time1 >
a2.time1
produces output :
-------
32 2
2 32
But I want just one line in the output. Uncommenting the clause
achieves the effect but somehow it seems a contrived way of
getting the desired result. Besides if unique key is on (date + time),
it would make the task more difficult. I think a more logical way
would help me write the query efficiently whether the unique
key was on a single field or multiple fields.
Please advise about a logical way of writing the query.
|
| | 6 answers | Add comment |
|
| Reverse engineer CREATE DATABASE sql Carkbster@Gmail.Com 01:54:29 |
| | I've seen various scripts out there that can extract the CREATE
DATABASE SQL for an existing SID, but most seem to be limited to
version 8.1 or below.
Has there been any newer scripts to extract the SQL for Oracle 10g
databases?
-Crispin
|
| | 7 answers | Add comment |
|
| Re: ORA 10G -ORA-12546 Error [semi solved] Nwjb 01:28:37 |
| | Le Tue, 27 Jun 2006 15:18:00 +0200, nwjb <j.bra.invalid.paspub@online.fr>
a Г©crit:
Le Wed, 21 Jun 2006 13:42:50 +0200, nwjb > <j.bra.invalid.paspub@online.fr> a Г©crit:> Le Tue, 20 Jun 2006 22:56:34 +0200, joel garry <joel-garry@home.com> a >> Г©crit:>> nwjb wrote:>>>> Le Tue, 20 Jun 2006 10:34:26 +0200, StefanKapitza>>>> <skapitza@volcanomail.com> a Г©crit:>>>> nwjb wrote:>>>> >> Le Mon, 19 Jun 2006 15:33:55 +0200, <ravisista@gmail.com> a Г©crit:>>>> >> nwjb wrote:>>>> >> >> I forgot to mention: was OK before we changed IP adress of the>>> ards>> [...]>>> Where are all your sqlnet.ora files and what are their permissions and>>> what is in them.>> where:>> /opt/oracle/product/10G/network/admin>> permissions:>> 640 (rw r ) oracle oinstall>> content:>> names.directory_path= (TNSNAMES,EZCONNECT)>> Do you have TNS_ADMIN set?>> Dont think so , is it an environment variable?>> What does ipcs say the mode is for your SGA?>> owner oracle , permission 640>> ll $ORACLE_HOME/bin/oracle>>> -rwsr-s--x ...>>> That is right.>>> jg>> More investigation gives following result:>> This machine is being installed , so much work is done being connected >> as root.>> And things differ:>> telnet x.x.x.x>> login: root>> password: XX>> . /v/xxxxx (creating ORACLE_HOME , SID ... and others)>> sqlplus scott/tiger -> does not work (insufficient priveleges TNS)>> su - root>> . /v/xxxxx>> sqlplus scott/tiger -> WORKS !!!!!>> Has anyone any idea ??>
Answering to ourselves:
root is member of oinstall and oracle groups .
logging in as root and doing id shows ONLY root group.
suing root gives correct groups.
Looking for the reason.
--
J.BratiГЁres
Enlever paspub pour rГ©pondre
Please remove paspub when answering
|
| | 4 answer | Add comment |
|
| "limit 10" vs "rownum <=10" Mark Harrison 00:26:53 |
| | So, we've got some inter-database portability issues we're trying
to address.
sqlite uses: select * from foo limit 1;
oracle uses: select * from foo where rownum <= 1;
What's the conventional wisdom on how to handle this?
Is there something in oracle we can turn on to help?
Right now we're thinking of some logic like:
if oracle, string substitute "limit x" with "where rownum <= x"
and try to handle the cases when there is already a where clause, etc.
Many TIA,
Mark
--
Mark Harrison
Pixar Animation Studios
|
| | 6 answers | Add comment |
Friday, 4 August 2006
|
| last time a table was modified Guest 23:16:03 |
| | Is there a way to check the last time a table was modified?
Is this a built-in Oracle field for all tables?
Thanks.
|
| | 3 answer | Add comment |
|
| GUI/web tool for statspack Yinshu Zhang 23:15:32 |
| | I tried statspack viewer enterprise from Alexey Danchenkov, but the GUI
is bit awkward to me. Is there any other tool doing simular job?
Nothing fancy but show stats in graphic, stats historical data in chart
fashion, it would be great if has baseline function.
|
| | 6 answers | Add comment |
|
| Help Designing a Billion rows System Dpafu 22:48:51 |
| | Hello, I need to build a new system that will have a table that will
keep data for 60 days (aprox. 1 billion rows). Once the data is there,
more data will be loaded (aprox. 10M rows/day loading around 40 files
with 40k rows each file).
The system needs to reject and report this new data whenever a
duplicate value is found (90% of data is suppose to be valid; the other
10% is suppose to be duplicated). I also need to maintain this table
with the latest 60 days of data.
What approach do you recommend?
I was planning to have a partitioned table per day (is it possible to
have 60 partitions?), use a PK on those unique values, use external
table (or SQL*Loader) to load the data and use the HINT APPEND to
insert new data. Then use another process to drop old partitions and
create a new one for today's data.
I'm not very familiar with partitioned tables, so any example and
help is appreciated.
What do you think about this approach?
Any other ideas?
Thanks,
Diego
|
| | 3 answer | Add comment |
|
| Stored Proc Recieving XML doc (UTF-8) into Clob data type. LineVoltageHalogen 21:27:38 |
| | I was hoping that someone might be able to shed some light on a debate
I am having here with my colleagues. In short we have a stored
procedure (Oracle 10G R2) that is taking in an XML document (UTF-8
encoding) and is shredding it and then dumping the data into relational
tables (nvarchar2 attributes to support the unicode data). That is the
readers digest version of the chain of events. The shredding process
within the procedure can only accomodate clob and not nclob and it is
here where the problem lies. I think that if data comes in via the xml
doc and it has actualy UNICODE code points that once the clob recieves
it some funky conversion will take place and the code points will be
converted to something other than what they were intended to be. If
anyone can shed any light on this I would be greatful.
Regards, TFD.
|
| | 2 answer | Add comment |
|
| Question: SQL, XPath and several namespaces Uwe M. Kuechler 20:20:03 |
| | Hi all,
my customer needs to update attributes within an xmltype column. So far
I haven't figured out how to deal with nested elements containing
another namespace.
This is an example table:
CREATE TABLE XML_TAB
(
TRXID NUMBER(15) NOT NULL,
REQUEST SYS.XMLTYPE
);
Here's an example of what the XML looks like:
<?xml version="1.0" encoding="ISO-8859-1"?>
<PutOrderPurchaseIn
xmlns="http://www.mysite.de/schemas/putorderpurchasein/">
<trxId>550000005559889</trxId>
<portfNo>0176744001</portfNo>
<isin>LU0815900775</isin>
<trxAmtTrxCcy>4000.00</trxAmtTrxCcy>
<trxCcy>EUR</trxCcy>
<acctUseMainAcctFlag>true</acctUseMainAcctFlag>
<orderer1Name>Doe</orderer1Name>
<orderer1FirstName>Jane</orderer1FirstName>
<ns1:CommonDataIn
xmlns:ns1="http://www.mysite.de/schemas/commondatain/">
<ns1:endUserId>53111861</ns1:endUserId>
<ns1:endUserName>Doe</ns1:endUserName>
<ns1:endUserFirstName>John</ns1:endUserFirstName>
<ns1 rocurerId>81555000</ns1rocurerId>
</ns1:CommonDataIn>
</PutOrderPurchaseIn>
This is what I tried to do (among many others) to select attributes in
the namespace ns1:
SELECT extractvalue(request
, '/PutOrderPurchaseIn/CommonDataIn/endUserId',
'xmlns="http://www.mysite.de/schemas/commondatain/"'
) x
FROM xml_tab
WHERE trxid = 550000005559889;
X
----------------
As you can see, a null value is returned.
Now, it's no problem to select or update the attribute "trxId" in
namespace "...putorderpurchasein" with the very same method, but how
would I do this for any attribute in Namespace ns1, like "endUserId"?
It looked so trivial, yet it isn't.
Regards,
Uwe
|
| | 5 answers | Add comment |
|
| Oracle Stream and Migration Aman.Oracle.Dba 20:16:43 |
| | Please Tell me if it is possible to migrate or clone database through
Oracle Stream utility,
and if yes why is it faster than Imp/Exp ( Full Database ).
|
| | 2 answer | Add comment |
|
| Move table sys.aud$ to another tablespace Guest 20:13:08 |
| | In metalink, there is an article talking about moving sys.aud$ to
another tablespace.
1)create tablespace "AUDIT" datafile '$HOME/data/aud01.dbf' size 500k
default storage (initial 100k next 100k pctincrease 0)
2)create table audx tablespace "AUDIT" storage (initial 50k next 50k
pctincrease 0) as select * from aud$ where 1 = 2
3) rename AUD$ to AUD$$
4)rename audx to aud$
5)create index i_aud2
on aud$(sessionid, ses$tid)
tablespace "AUDIT" storage(initial 50k next 50k pctincrease 0)
Can't we just do "alter table AUD$ move tablespace"???
Anything wrong with doing alter table stetement on sys.AUD$ table?
|
| | 4 answer | Add comment |
|
| concatenating clob with string in SELECT Guest 20:01:09 |
| | I have dynamically created an INSERT statement that is populating a
table with a CLOB field. The value being inserted into the CLOB field
is generated by concatenating a bunch of strings. In some case, it
exceeds the 4K limit for string concatenation. The concatenation will
have the 4G limit of a CLOB, if one of the fields being concatenated is
of a clob data type. How can I create a clob field to be used in the
concatenation in the SELECT statement?
For example,
insert into table1 (field1, clobfield) SELECT field1, <4k
string>||<4kstring> from table2;
I would like to put a clob in the concatenation string to make this
work. Something like ...
insert into table1 (field1, clobfield) SELECT field1, <4k
string>||<4kstring>||<clob literal> from table2;
|
| | 5 answers | Add comment |
|
| rman and flash recovery area EdStevens 18:54:12 |
| | Oracle 10.2.0.1.0 on Solaris 10
In the Data Guard
Concepts and Administration, section 10.3.4, in discussing the NONE
clause of the rman ARCHIVELOG DELETION POLICY, it says,
"Archived redo log files that are backed up or obsolete are deleted to
make space if the disk space in the flash recovery area becomes full."
I understand this to mean that the archiver (or some other background
process apart from an rman run) will automagicly bump out obsolete
archivelogs if needed to make space for new ones.
The doc goes on to give two examples of configuring ARCHIVELOG DELETION
POLICY in a DG setup. The second example (backups are taken on the
primary db) is our situation, so they say to set the deletion policy on
the primary to NONE and on the standby to APPLIED ON STANDBY.
Now, perhaps I'm connecting dots that weren't meant to be connected,
but I understand all of the above to mean that if I use the suggested
settings, the recovery area of the standby will eventually fill up, but
it is not a concern because obsolete archivelogs will get thrown out so
that the archiver doesn't get stopped.
Or do I still need to use the above suggested settings, *and* run an
rman 'crosscheck archivelog all' and 'delete expired archivelog all'
against the standby?
|
| | Add comment |
|
| PL/SQL question regarding nested loops B. Williams 17:16:35 |
| | I have some code that uses for loops and I chaged the outer loop into a
simple loop and inner loop into a while loop, but the out put is coming out
different. The outer loop is supposed to run 3 times while the inner loops
is supposed to run 6 times. Wouls someone look at my code and tell me what I
have done wrong. Thanks in advance.
Original code
set serveroutput on
declare
v_test NUMBER := 0;
begin
<<outer_loop>>
for i IN 1..3 loop
dbms_output.put_line('Outer Loop');
dbms_output.put_line('i = ' || i);
dbms_output.put_line('v_test = ' || v_test);
v_test := v_test + 1;
<<inner_loop>>
for j IN 1..2 loop
dbms_output.put_line('Inner Loop');
dbms_output.put_line('j = ' || j);
dbms_output.put_line('i = ' || i);
dbms_output.put_line('v_test = ' || v_test);
end loop Inner_Loop;
end loop outer_loop;
end;
/
Modified code
set serveroutput on
declare
i PLS_INTEGER := 0;
j PLS_INTEGER := 1;
v_test NUMBER := 0;
begin
loop
i := i + 1;
dbms_output.put_line('Outer Loop');
dbms_output.put_line('i = ' || i);
dbms_output.put_line('v_test = ' || v_test);
v_test := v_test + 1;
exit when i = 3;
while j <= 2 loop
dbms_output.put_line('Inner Loop');
dbms_output.put_line('j = ' || j);
dbms_output.put_line('i = ' || i);
dbms_output.put_line('v_test = ' || v_test);
j := j + 1;
end loop;
end loop;
end;
/
|
| | 2 answer | Add comment |
|
| ** One Million FR.EE Visitors ** Raul Vega 16:50:54 |
| | Stephan Ducharme just became a millionaire on the Internet
.. and he was caught on tape, revealing everything!
Click here : <http://www.freeadguru.com/cgi-bin/i.pl?c=a&i=26130>
Rush on the page before it gets banned!
YkJ9DhvChe4rYnrCh7l5XKJBDi9yGW5GNRfjZLpsOJs9bueJwj06E5V8h5O2btlowE2Ud6f3r
|
| | 1 answer | Add comment |
|
| 9.0.2 varchar2(32767) acting like varchar2(4000) Dave in Roanoke 15:32:26 |
| | I have oracle 9i rls 2.
I am experiencing a size problem using varchar2.
(4000 limit?) .
I have ( as shown below) a type with field that
I expect to be able to hold 32k of text.
CREATE OR REPLACE TYPE PO_Comment_Detail AS OBJECT (
PO VARCHAR2(14),
Head_or_Line VARCHAR2(14),
buyer_code varchar2(6),
POComment VARCHAR2(32767)
)
I have a procedure that works flawlessly except for
populating POComments. It accumilates a good deal of
into a varchar2(32767) and trys to put it into POComment.
To get data into POComments I have to reduce its size
substr(AComment2Write,1,4000) works and
substr(AComment2Write,1,4001) doesn't.
Any thoughts on what is happening or more to the point
how I can make it work ?
Thanks
Dave
|
| | 7 answers | Add comment |
|
|
