Stack Overflow
Stack Overflow

Questions tagged [oracle9i]

Oracle is a relational database management system (RDBMS) product. Specific releases of the product are known as Oracle9i, Oracle 10g and Oracle 11g. Generally there are two releases within each major version. Questions tagged "oracle9i" are assumed to be specific to this version or features introduced in this version.

Oracle is an RDBMS product. The software has undergone a number of releases, including 11gR1 and 11gR2. Questions tagged with are assumed to be specific to this version or features introduced in this version. It is generally advisable to specify both the full database version (eg 9.2.0.2) and often useful to include the underlying operating system.

Questions should probably be additionally tagged for the widest readership.

Questions about SQL in general should probably be tagged .

Questions about PL/SQL in particular should probably be tagged as well as .

Where the question relates to installation or operation, it may be more appropriate to post in Server Fault.

611 questions
369
votes
9 answers

Best way to do multi-row insert in Oracle?

I'm looking for a good way to perform multi-row inserts into an Oracle 9 database. The following works in MySQL but doesn't seem to be supported in Oracle. INSERT INTO TMP_DIM_EXCH_RT (EXCH_WH_KEY, EXCH_NAT_KEY, EXCH_DATE, EXCH_RATE, …
jamz
  • 4,991
  • 4
  • 24
  • 19
167
votes
14 answers

How to select only 1 row from oracle sql?

I want to use oracle syntax to select only 1 row from table DUAL. For example, I want to execute this query: SELECT user FROM DUAL ...and it'd have, like, 40 records. But I need only one record. ...AND, I want to make it happen without a…
Ben
  • 1,914
  • 2
  • 13
  • 11
148
votes
6 answers

Oracle PL/SQL - How to create a simple array variable?

I'd like to create an in-memory array variable that can be used in my PL/SQL code. I can't find any collections in Oracle PL/SQL that uses pure memory, they all seem to be associated with tables. I'm looking to do something like this in my PL/SQL…
contactmatt
  • 18,116
  • 40
  • 128
  • 186
85
votes
19 answers

ORA-12560: TNS:protocol adaptor error

I Google[d] for this error ORA-12560: TNS:protocol adaptor error but not able to find the actual reason and how to solve this error ? Can anyone tell me a perfect solution to solve login problem.
Vishwanath Dalvi
  • 35,388
  • 41
  • 123
  • 155
75
votes
19 answers

ORA-01017 Invalid Username/Password when connecting to 11g database from 9i client

I'm trying to connect to a schema on 11g (v11.2.0.1.0) from a PC with 9i (v9.2.0.1) client. It seems to connect fine to some schemas, but not this one - it comes back with a ORA-01017 Invalid Username/Password error every time. The username and…
user1578653
  • 4,888
  • 16
  • 46
  • 74
67
votes
3 answers

Get a list of all functions and procedures in an Oracle database

I'm comparing three Oracle schemas. I want to get a list of all the functions and procedures used in each database. Is this possible via a query? (preferably including a flag as to whether they compile or not) Ideally it would be great to have a…
AJM
  • 32,054
  • 48
  • 155
  • 243
42
votes
8 answers

ORA-01008: not all variables bound. They are bound

I have come across an Oracle problem for which I have so far been unable to find the cause. The query below works in Oracle SQL developer, but when running in .NET it throws: ORA-01008: not all variables bound I've tried: Changing the Oracle data…
Charles Burns
  • 10,310
  • 7
  • 64
  • 81
35
votes
5 answers

ORA-00942: table or view does not exist (works when a separate sql, but does not work inside a oracle function)

When I have a sql statement like select * from table1, it works great, but as soon as I put it into a function, I get: ORA-00942: table or view does not exist How to solve this?
Victor
  • 16,609
  • 71
  • 229
  • 409
29
votes
6 answers

UTL_FILE.FOPEN() procedure not accepting path for directory?

I am trying to write in a file stored in c:\ drive named vin1.txt and getting this error .Please suggest! > ERROR at line 1: ORA-29280: invalid > directory path ORA-06512: at > "SYS.UTL_FILE", line 18 ORA-06512: at > "SYS.UTL_FILE", line 424…
Vineet
  • 5,029
  • 10
  • 29
  • 34
22
votes
1 answer

Why does a connect by expression in a FOR loop, execute only once?

I just found what I think is somewhat unexpected behavior in PLSQL vs SQL in Oracle. If I run this query on SQLDeveloper I get 5 results: select level lvl from dual connect by level <=5; But if i run this statement in SQLDeveloper: declare …
JGS
  • 813
  • 2
  • 8
  • 17
20
votes
7 answers

How do I do a manual uninstall of Oracle?

Sometimes my Oracle database on Windows gets hosed. How do I do a manual uninstall of Oracle?
Joshua
  • 26,234
  • 22
  • 77
  • 106
20
votes
7 answers

Oracle 9 - Resetting Sequence to match the state of the table

I have a sequence used to seed my (Integer based) primary keys in an oracle table. It appears this sequence has not always been used to insert new values into the table. How do I get the sequence back in step with the actual values in the table?
AJM
  • 32,054
  • 48
  • 155
  • 243
19
votes
11 answers

Using distinct on a column and doing order by on another column gives an error

I have a table: abc_test with columns n_num, k_str. This query doesnt work: select distinct(n_num) from abc_test order by(k_str) But this one works: select n_num from abc_test order by(k_str) How do DISTINCT and ORDER BY keywords work…
prateek gupta
  • 187
  • 1
  • 2
  • 5
19
votes
3 answers

How to manually initialize a collection of RECORDs in PL/SQL?

guys. Here's a simple sample two-dimensional array in PL/SQL, which is working perfectly. declare type a is table of number; type b is table of a; arr b := b(a(1, 2), a(3, 4)); begin for i in arr.first .. arr.last loop for j in…
Kirill Leontev
  • 10,641
  • 7
  • 43
  • 49
18
votes
2 answers

Oracle/SQL - Grouping items by action by day over time

Hi all I have a 'widget' table that has the following columns: widget, action, timestamp_. What we want to do is pull all the widgets that were rejected more than once a day between certain dates. So here's an example table widget action …
dscl
  • 1,616
  • 7
  • 28
  • 48
1
2 3
…
40 41