I've got a relatively simple update statement: update sv_konginfo ki set AnzDarl = 1 where kong_nr in ( select kong_nr from sv_darlehen group by kong_nr having count (*) = 1); which runs okay on its own (about 1 second for about 150.000 records). However, if I truncate the table and then re-insert the records: trun...
Does anyone know, why Oracle's NVL (and NVL2) function always evaluate the second parameter, even if the first parameter is not NULL? Simple test: CREATE FUNCTION nvl_test RETURN NUMBER AS BEGIN dbms_output.put_line('Called'); RETURN 1; END nvl_test; SELECT NVL( 0, nvl_test ) FROM dual returns 0, but also prints Called. nvl_tes...
Since this seems more of a syntax question, my search didn't produce any useful results. I'm attempting to make a query that gets European data from an American database using GMT time. I need to get the European time, but can't seem to get it working correctly. Lets say "myDateField" = '01-01-2010' SELECT TO_CHAR(myDateField + (60 / ...
We have a requirement to store Sharepoint List Data into Oracle. Is it possible to Consumes Sharepoint Webservices(SOAP-based) into ORACLE DB using PL/SQL, like to access _vti_bin/lists.asmx?op=GetList and parse the list data and store in the DB. Has anybody tried this ??? Are there any things (Security, environment) things to consider...
Hi there, At the moment I am learning sql and begin to encounter problems with certain triggers. This is very basic but I do not know how to solve it. The problem is this: I have two tables Person and BankAccountInfo. The table Personincludes personal information. as identification number, name, birth date, etc. . TheBankAccountInfo` ...
Assuming that all foreign keys have the appropriate constraint, is there a simple SQL statement to delete rows not referenced anywhere in the DB? Something as simple as delete from the_table that simply skip any rows with child record? I'm trying to avoid manually looping through the table or adding something like where the_SK not in (...
Hello all, this is my second time using ORACLE xe and Apex. Here is what I'm trying to do .. I'm trying to execute the following query SELECT * FROM EMPLOYEES WHERE JOB_TITLE = 'CLERK' but not from sql command prompt but from gui/apex and here is how- I have created page one with one textfield and one submit button. Now of course I'd...
Hi, Is there a standard or recommended method of exporting the data from an Oracle DB into a SQL Server or mySQL database? Was thinking exporting the Oracle data into XML then importing the XML into SQL Server...or this recommmened? Thanks, ...
I am trying to retrieve data form an Oracle database using jdbc (ojdbc14.jar). I have a limited number of concurrent connections when connecting to the database and these connections are managed by Websphere connection pool. Sometimes when I make the call I see an UncategorizedSQLException exception thrown in my logs with one of the fol...
I have a materialized view defined this way: CREATE MATERIALIZED VIEW M_FOO REFRESH COMPLETE ON COMMIT AS SELECT FOO_ID, BAR FROM FOO WHERE BAR IS NOT NULL GROUP BY FOO_ID, BAR / COMMENT ON MATERIALIZED VIEW M_FOO IS 'Foo-Bar pairs'; I wrote as a sort of cache: the source table is huge but the number of different pair...
I'm using an Oracle database(10g) which contains a stored procedure called Foo. Foo takes 2 datetime as IN parameters and 1 cursor as OUT parameter. I've been trying to make the connection and the query to the database with the OleDbCommand, but since Foo needs a cursor I have no choice but to use a OracleCommand(right?). When I try t...
Following on from my previous question, I'm looking to run some performance tests on various potential schema representations of an object model. However, the catch is that while the model is conceptually complete, it's not actually finalised yet - and so the exact number of tables, and numbers/types of attributes in each table aren't d...
I'm refactoring the data import procedure for an enterprise application and came across a snippet I'd like to find a better solution. When importing data we have to create a unique entity for each data set and there is a counter in a field to be used to assign this id sequentially. You read the field to get the next free id and increment...
how can I make oracle procedure with oracle xe, how can I check if input is valid or not? ex: if my input is number and I type in char that procedure prints out something in that case, I've dealt with SQL but not with these kind of procedures? any help is appreciated UPDATE This was a dummy example .. what I meant is to start from the ...
I have a large table (6m records) containing data licensed from a vendor. The table contains an NVARCHAR2(1) column with Y/N values. I have created a view to filter out records with a value of 'N', and this view will be queried extensively. What is the best way to index the NVARCHAR2(1) column? ...
Hello, I'm doing an Asp.Net application which will, eventually, generate 'on the fly' report with parameters entered by the user. I'm trying to understand how to dynamically generate Crystal Report reports. Actually, I've got a stored proc being called and filling a DataTable with the results. But there is one part missing to my problem...
Hi, I have created a table in oracle XE, and I have a field with type date. I would like if possible when I insert a row, that it automatically fills that field with the current date from the system. I am inserting the rows from the SQL prompt. Thanks ...
Hello, I have the following in my string "1406984110015" what I would like is to get; str1 = 14 str2 = 06 str3 = 84 I am creating a function in ORACLE that would in this case return 14.06.84 Any ideas? ...
Hello, I am trying to figure out a format spec of to_char() that would give me the following result. to_char(0.1, '[FORMAT_SPEC]') gives 0.1 and: to_char(1, '[FORMAT_SPEC]') gives 1. I've tried the following solutions: to_char(0.1) gives '.1'. to_char(0.1, 'FM0.099') gives 0.1, which is okay, however: to_char(1, 'FM0.099')...
In cx_Oracle (or Oracle in general), is it possible to allocate a cursor for each query, or to reuse a cursor across several queries. def getSomeData(curs): # case 1: pass in a cursor, which is generally curs.execute('select ...') # reused across queries return curs.fetchall() def getSomeData(conn): # ca...