So I've got a for loop that processes a list of IDs and has some fairly complex things to do. Without going into all the ugly details, basically this: DECLARE l_selected APEX_APPLICATION_GLOBAL.VC_ARR2; ...snip... BEGIN -- get the list ids l_selected := APEX_UTIL.STRING_TO_TABLE(:P4_SELECT_LIST); ...
I have a SQL Server 2005 database that is linked to an Oracle database. What I want to do is run a query to pull some ID numbers out of it, then find out which ones are in Oracle. So I want to take the results of this query: SELECT pidm FROM sql_server_table And do something like this to query the Oracle database (assuming that the ...
Has anyone seen this error when trying to call an external C function from an Oracle query? I'm using Oracle 10g and get this error every time I try to call one of the two functions in the library. A call to the other function returns fine every time, though the function that works is all self-contained, no calls to any OCI* functions....
Found the following in an Oracle-based application that we're migrating (generalized): SELECT Table1.Category1, Table1.Category2, count(*) as Total, count(Tab2.Stat) AS Stat FROM Table1, Table2 WHERE (Table1.PrimaryKey = Table2.ForeignKey(+)) GROUP BY Table1.Category1, Table1.Category2 What does (+) do in a WHERE claus...
I execute the following command and the error occurs: recover database until time '2009-01-09 12:26'; ORA-00283: recovery session canceled due to errors ORA-19907: recovery time or SCN does not belong to recovered incarnation; How can I solve this problem? ...
The app my colleagues and I maintain has an Oracle database at the back-end. We're looking at occasionally running the app in a 'restricted' mode with one of the database tablespaces set to read-only. We can easily move the necessary tables and indexes over to separate tablespaces which will be writable in 'restricted' mode. However, ...
There are a number of commercial products available: Exodus Evo Also Oracle Application Express (APEX) is releasing a Forms Converter in Oracle Application Express 3.2 (which is now in beta). Has anyone used any of these tools? How much of the process do they automate? What’s the quality of the converted code? Are they worth the co...
I work in a group of about 25 developers. I'm responsible for coming up with the database design (tables, views, etc) and am called apon for performance tuning when necessary. There are a couple of different applications that connect. Database access is via JDBC, hibernate, and iBatis SQL maps. Developers with various levels of exper...
AFAIK ROWID in Oracle represents physical location of a record in appropriate datafile. In which cases ROWID of a record may change ? The one known to me is UPDATE on partitioned table that "moves" the record to another partition. Are there another cases ? Most of our DBs are Oracle 10. ...
When trying to load a large text file into the oracle db using SQLLoader, we get the following errors: SQL*Loader-926: OCI-Error; uldlfca:OCIDirPathColArrayLoadStream for table <myTabele> SQL*Loader-2026: the load was aborted because SQL Loader cannot continue. SQL*Loader-925: Error in uldlgs: OCIStmtExecute (ptc_hp) This only happens...
In Oracle, you can write: update t set xml = updateXML(xml, '/a/b/text()', 'gaga') This works only if you already have some text in the <b> element. How to update the document and "add some text" in <b> if the document in the database looks like: <a> <b/> </a> ...
In Oracle, you can use the XMLElement() function to create an element, as in: XMLElement('name', 'John') But how to create an element in a specific namespace? For instance, how to create the following element: <my:name xmlns:my="http://www.example.com/my">John</my:name> ...
Hi all, I'm using oracle(10). I've got two tables as follows: Table1 (uniq rows): ID AMOUNT DATE Table2: ID AMOUNT1 AMOUNT2 ...AMOUNTN DATE Table2 is connected many to one to Table1 connected via ID. What I need is update-ing Table1.DATE with: the last (earliest) date from Table2 where Table1.AMOUNT - SUM(Table2.AMOUNT1...
Hi all, I have a table like this (Oracle, 10) Account Bookdate Amount 1 20080101 100 1 20080102 101 2 20080102 200 1 20080103 -200 ... What I need is new table grouped by Account order by Account asc and Bookdate asc with a running total field, like this: Acc...
I am trying to understand how does joins work internally. What will be the difference between the way in which the following two queries would run? For example (A) Select * FROM TABLE1 FULL JOIN TABLE2 ON TABLE1.ID = TABLE2.ID FULL JOIN TABLE3 ON TABLE1.ID = TABLE3.ID And (B) Select * FROM TABLE1 FULL JOIN TABLE2 ON TABLE1.ID = T...
Semi-regularly when using TOAD, I will try to connect to a DB and get the error "ORA-12638 Credential retrieval failed". I have to restart my TOAD to get past this. It's a pain if I have other connections active. Does anyone know how to avoid or get around this error? Thanks ...
Say I have an Oracle PL/SQL block that inserts a record into a table and need to recover from a unique constraint error, like this: begin insert into some_table ('some', 'values'); exception when ... update some_table set value = 'values' where key = 'some'; end; Is it possible to replace the ellipsis for something in ...
Hi everyone, can someone tell me why the following behavior occurs (Oracle 10.2): SQL> create table part_test ( i int primary key, d date ) partition by range (d) (partition part_test_1 values less than (to_date(' 2 3 4 5 1980-01-01', 'yyyy-mm-dd'))); create public synonym part_test for part_test; select obj...
What do the following mean in an sql sytax: (+) after a condition eg: "WHERE table1.col1 = table2.col2 (+) What does /* */ after select signify , i vaguely remember it being a suggestion to the optimizer, but where can i find more reference on this eg: select /* */ ... ...
We have a system with an Oracle backend to which we have access (though possibly not administrative access) and a front end to which we do not have the source code. The database is quite large and not easily understood - we have no documentation. I'm also not particularly knowledgable about Oracle in general. One aspect of the front end...