I was trying to come up with a real life programming problem that could be best solved by using autonomous transactions within autonomous transactions but could not think of any. Can you give me any ideas? Edit: I mean something like this: PROCEDURE outer_procedure IS BEGIN -- some code auto_proc1; END; / PROCEDURE auto_proc1 IS...
I have a table which has data like this id test_val type_cd #------------------------- 101 TEST22 M 102 TEST23 M 103 TEST01 M 103 TEST01 H 104 TEST02 M 104 TEST02 H 105 TEST03 H I would like to fetch the max(id) for each type_cd and its corresponding test_val i...
How would I write a loop that has a select in the “in” clause of the loop that selects a col of type varchar(10), but then inserts those values in a col that wants them to be varchar(9)? Basically I’m trying to “typecast” from one precision to another, if that makes any sense. Example: FOR V_TEN IN (SELECT THIS_IS_VARCHAR_TEN FROM TABLE...
Hi, I was asked in an interview,a question from oracle sql.this seemed to be a simple question but i had no clue to answer.could anybody help? if there is string like "newyork is a beautiful city" in a colum. select column_name from table_name; will result newyork is a beautiful city what is the query required to give the output...
I have a cursor in an ORACLE function that I am dynamically generating using the arguments of the function. I define the text of the cursor and in certain cases I include the following statement: sql_stmt := sql_stmt || 'AND MOD ( LOG_ID, :logsetmax ) = :logset '; I open the cursor and pass arguments in using the ORACLE OPEN-FO...
I am on Rails 2.3.5 and use oracle 10 as my database,use oracle_adapter ,ruby-oci8 to connect oracle host. But I often got exception as the log info show: Completed in 463ms (View: 18, DB: 166) | 200 OK [http://192.168.30.128/auctions?page=1] /!\ FAILSAFE /!\ Mon Feb 01 19:02:11 +0800 2010 Status: 500 Internal Server Error ORA-12...
Hi all, i keep getting the following errror, 'ORA-01008: not all variables bound', im guessign its all based on my pPostcode param but im not sure. I am a beginner the the whole PLSQL secne and any help would be greatly apriciated here is my procedure: procedure all_customer_postcode(pPostcode in carer.postcode%type ...
Since the System.Data.OracleClient library has been deprecated, we are in the process of migrating our code base to use Oracle Data Provider for .NET (ODP.NET) instead. One of the issues that we have encountered is that the System.Data.OracleClient uses parameter name binding as opposed to binding by position and all of the code directly...
What does ":" stand for in a query ? INSERT INTO MyTable (ID) VALUES (:myId) How does it fetch the desired value? Edit: Also what is that sign called? I wanted to search on google, but what's the name for ":"? Chosen Answer: Chose the answer for the link and the upvotes. But also check Jeffrey Kemp's answer ...
Hi, I am working on NHibernate with oracle in ASP.Net. Now i am trying TDD(Test Driven Development). Can you tell me the best way to develop the TDD for NHibernate with oracle? I am using MbUnit with microdesk but it is not better approach for oracle but SQL server. Please give suggestions... ...
ORA-20118 is a custom exception from the stored procedure. The stored procedure runs just fine from PL-SQL developer, so the problem is in Spring. What I need to do is to get Spring to rollback the SP when it gets the ORA-20118 exception back from the SP. How do I do that? or maybe just get spring to correctly handle the 20118 code c...
SELECT executed during the first command T1 is a transaction, in turn, DELETE command (at the time T2) is the first command transaction B. What will be the result of the SELECT statement at the time of T3 (a transaction)? The SELECT statement in T3 will return a row (because Transaction B is not committed yet) ? ...
I developed an application that uses Delphi 7, ADO and ORACLE, the provider I use is OraOLEDB (I need use this provider because the BLOB fields support). now I want to distribute this application with the provider. I search the web to download the Oracle provider, but has a size of 174 mb. I need to install this file on all client machin...
I need to generate an id with the following features: Id must be unique Id consist of two parts 'type' and 'auto incremented' number 'type' is integer and value can be 1, 2 or 3 'auto incremented' number starts with 10001 and incremented each time id is generated. type is selected from a web form and auto incremented number is from t...
I'm trying to test the utility of a new summary table for my data. So I've created two procedures to fetch the data of a certain interval, each one using a different table source. So on my C# console application I just call one or another. The problem start when I want to repeat this several times to have a good pattern of response time...
The classic way to query an SQL database case-insensitively from Java is as follows: String name = ...; // get the user's input (case is unknown) String sql = "select * from Person where lower(name) = ?"; Object jdbcBindVariable = name.toLowerCase(); // ... using JDBC, bind that variable and run the SQL query The problem is that lowe...
Hi all, I am new in Oracle. I install Oracle 8 Lite with Developer 2000. Now I want to create a database for me. For that I open Oracle8 Navigator and Create a database which user is system. But in Oracle SQL*Plus I can not communicate with the database. Or even after creating a table using Oracle8 Navigator I can't alter the table. Pl...
My question is as follows: User name = Admin Whenever I perform an insert/update/delete operation on a table "a". I need to have a trigger that would insert the username in table "b" Is that possible? ...
Hi i have a table that is 5 GB now i was trying to delete like below: delete from tablename where to_char(screatetime,'yyyy-mm-dd') <'2009-06-01' But its running long and no response. Meanwhile I tried to check if anybody is blocking with this below: select l1.sid, ' IS BLOCKING ', l2.sid from v$lock l1, v$lock l2 where l1.block ...
Hi, I created and launch the SQLJ Java procedure (using SQL Developer). I have some bugs and I would like to debug Java source code. What is the best way to do this? Is it possible to print to the SQL Developer console some information using System.out? ...