I have a cursor that is used to get some preliminary information for some other processing. It is possible that the query backing the cursor may not return any rows, and in these rare cases, we want to raise a special exception (handled and logged elsewhere so processing is not compeltely halted) so that the user knows about what is most...
Is it possible to execute a stored procedure inside a trigger? Thank you ...
Why does the following error occur on Line 7 of the query below? Error: ORA-01861: literal does not match format string Query: 01: SELECT hour 02: FROM (WITH all_hours AS 03: (SELECT TO_DATE ('2000-01-01', 'yyyy-mm-dd') 04: + NUMTODSINTERVAL (LEVEL - 1, 'hour') hour 05: FROM DUAL 06: ...
Hi, I am trying to implement a "if exists, update, otherwise, insert" data access method in NHibernate. My database is Oracle 10g. I am getting this "could not execute native bulk manipulation query" error when try to run this code, if I run the insert or update individualy, it works just fine. Thanks! string sql = @"DECLARE ...
By default if I create a field in an entity like: @NotNull boolean myBoolean; And I let Hibernate auto-create my tables. What Oracle data type will this map to? ...
I am running a stored procedure that returns 2 cursors and none of them have any data. I have the following mapping xml: <resultMap id="resultMap1" class="HashMap"> <result property="firstName" columnIndex="2"/> </resultMap> <resultMap id="resultMap2" class="com.somePackage.MyBean"> <result property="unitStreetName" column="street...
When I insert a row into a table with 1000+ entries, and attempt to return the row ID (be it from an autoincrement trigger/seq, or from setting the value manually in the insert statement), I get a truncated value: $db = OCILogon(DATABASE_LOGIN, DATABASE_PASSWORD, DATABASE_NAME); $mysqldate = date('Y/m/d G:i:s'); $db_vid_id = 748; $auth...
I'm new to PL/SQL and I'm trying to use a table of records, but I don't know how to use this feature. What is the problem? DECLARE TYPE TIP IS RECORD ( F1 SMALLINT, F2 SMALLINT); TYPE Ve IS TABLE OF TIP; v ve; IND SMALLINT := 0; BEGIN WHILE(IND<20) ...
I'm learning how to work with Oracle and am using C#/Visual Studio. Just as a reference, I'm following this simple tutorial, and have all the prerequisites done (database installed and ODAC with dev tools installed). The following code that's supposed to create an object for connection to a database throws an exception saying "Object ref...
I'm running some test to migrate data from one database to another and for that i need to delete and recreate same tables, views and other stuff. So what is the SQL statement(s) in Oracle to wipe everything (delete Tables, Views, Sequences, Functions, Procedures, etc.). I know that I can use "DROP" but sometimes that's not convenient eno...
What is the equivalent of: TRUNC(SYSDATE) ...in SQL Server 2005? ...
Hi, i have a table containing 15+ million records in oracle. its sort of a log table which has a created_ts column of type "date" . i have a simple "non-unique" type index on created_ts column. i have a simple range query : select * from table1 where created_ts >= ? and created_ts <= ?; when i run this query from SQLPlus or SQ...
How can I create a public synonym in oracle 7.3.4 for a common table located in different users. ...
Hi, I want to return multiple values from a query in oracle. For ex: select count(*) from tablename a where asofdate='10-nov-2009' and a.FILENAME in (case when 1 = 1 then (select distinct filename from tablename where asofdate='10-nov-2009' and isin is null) else null end); I am getting error: ora 01427 single ro...
Hi, I want to assign mulitple values into a variable and use that variable in where-clause. For Ex: declare v_filename varchar2(300) := ''('filename1','filename2')''; cnt number; begin select count(*) into cnt from table_name where filename in v_filename; end; Please advise. Thanks, Deepak ...
I'm new to pl/sql ! I'm trying to sort a table of records, using a simple bubble-sort algorithm. What is the problem? Where could I find more information about using table of records ? DECLARE text VARCHAR2(50); TYPE TIP_VECTOR IS TABLE OF INT INDEX BY BINARY_INTEGER; TYPE contorRecord IS record ( codASCII VARCHAR2...
I have an ASP.NET application (vb.net codebehind) that has serious performance problems because of its storage of TIFF files in one server share. There are over a million .TIF files there now! The application tracks the scanned images of property with a corresponding row in an Oracle database table. We have this idea that it might be bet...
hi, i have the following situation. table looks like this CREATE TABLE CompetitionsLanguages ( competition REF CompetitionType SCOPE IS Competitions, language REF LanguageType SCOPE IS Languages ); i need this table to have a unique constraint on (competition,language) combination. oracle tells me that i cant put a UNIQUE or PK o...
ODP.Net exposes the ability to pass Associative Arrays as params into an Oracle stored procedure from C#. Its a nice feature unless you are trying to use the data contained within that associative array in a sql query. The reason for this is that it requires a context switch - SQL statements require SQL types and an associative array p...
Hi, could anyone tell me if it makes a difference to Oracle 10g whether I use: SELECT col1 FROM myTable WHERE col2 = 'someval' AND col3 = "someotherval" or SELECT col1 FROM SELECT col1, col2, col3 FROM ( SELECT * FROM myTable ) WHERE col2 = 'someval' ) WHERE col3 = "someotherval" According to the explain plan, the...