help with query
How to list the departments in Oracle's EMP table which have either two clerks or three managers? ...
oracle
Oracle - Updating one column or another based on a condition
I want to update a record in a table but based on a condition I will either update one column or another but I do not want to have 2 separate statements because the statements are very long and detailed. Here is the basic idea with over simplification to get to the point. PROCEDURE Animal_something(p_updater VARCHAR2) begin if p_...
How do I return a nested table from an oracle function using Java?
I have the following type declaration and Oracle function: CREATE OR REPLACE TYPE var_outcomes_results IS TABLE OF VARCHAR2(80); CREATE OR REPLACE FUNCTION getValuesAbove(in_nodeID IN table1.KEY_SL%TYPE, in_variable IN VARCHAR2) RETURN var_outcomes_results IS currentID table1.KEY_SL%TYP...
Identifying and Resolving Oracle ITL Deadlock
I have an Oracle DB package that is routinely causing what I believe is an ITL (Interested Transaction List) deadlock. The relevant portion of a trace file is below. Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds w...
Ensure Oracle row represents a unique timespan
I have to make a process in Oracle/PLSQL. I have to verify that the interval of time between start_date and end_date from a new row that I create must not intersect other start_dates and end_dates from other rows. Now I need to check each row for that condition and if it doesn't correspond the repetitive instruction should stop and afte...
Need help in setting application name with JPA (EclipseLink)
hello everybody i am using JPA with EclipseLink and oracle as DB and i need to set the property v$session of jdbc4 it allows to set an identification name to the application for auditing purposes but i had no lucky setting it up....i have been trying through entitiyManager following the example in this page: http://wiki.eclipse.org/Conf...
How to truncate a table with Spring JdbcTemplate?
I'm trying to truncate a table with Spring: jdbcTemplate.execute("TRUNCATE TABLE " + table); Get the error: Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [TRUNCATE TABLE RESULT_ACCOUNT]; nested exception is java.sql.SQLException: Unexpected token: TRUNCATE in statement ...
ORACLE: Parameter reference in WHERE doesn't work
Hello, I have created a simple static function in oracle 10g to get the reference of an object based on his pk. STATIC FUNCTION getRef(nome IN VARCHAR2) RETURN REF folder_typ IS fl_r REF folder_typ := null; BEGIN SELECT REF(fl) INTO fl_r FROM folder_tab fl WHERE fl.nome = nome; RETURN fl_r; END getRef; Thi...
How to make a GRANT persist for a table that's being dropped and re-created?
I'm on a fairly new project where we're still modifying the design of our Oracle 11g database tables. As such, we drop and re-create our tables fairly often to make sure that our table creation scripts work as expected whenever we make a change. Our database consists of 2 schemas. One schema has some tables with INSERT triggers which ...
oracle connection manager Java Client example
Hi Experts, I am new to oracle connection manager. Can some help me with a Java Client code example to talk to a oracle database thru oracle connection manager. Thanks Sam. ...
How can fill a variable of my own created data type within Oracle PL/SQL?
In Oracle I've created a data type: TABLE of VARCHAR2(200) I want to have a variable of this type within a Stored Procedure (defined locally, not as an actual table in the DB) and fill it with data. Some online samples show how I'd use my type if it was filled and passed as a parameter to the stored procedure: SELECT column_value cu...
NHibernate, updating a db column to a function call when persisting a domain object.
I have a domain class that when persisted to Oracle must update a column to sysdate. NHibernate must only generate 1 SQL. e.g. update person set age = 12, stamp = sysdate where id = 1; Can this be done? EDITED: Could be something like: Person person = (Person)session.Get(typeof(Person), 1); session.SetFunction(person, "stamp", Funct...
How can I store large amount of data from a database to XML (memory problem, part two)?
I've asked the question the other day and rewritten the code to reflect the solutions, but now I have a new problem. Here is the code. This is the calling/writing function; I have 8 types of proizvod which makes 8 XML files. After an XML file is created, it needs to be zipped by a custom zip class: generateXML(tmpParam,queryRBR,proizvo...
How can I convert data encoded in WE8MSWIN1252 to utf8 for use in Python scripts?
This data comes from an Oracle database and is extracted to flatfiles in encoding 'WE8MSWIN1252'. I want to parse the data and do some analysis. I want to see the text fields but do not need to publish the results to any other system so if some characters do not get converted perfectly I do not have a problem with that. I just do not w...
(Oracle) How get total number of results when using a pagination query?
I am using Oracle 10g and the following paradigm to get a page of 15 results as a time (so that when the user is looking at page 2 of a search result, they see records 16-30). select * from ( select rownum rnum, a.* from (my_query) a where rownum <= 30 ) where rnum > 15; Right now I'm having to run a separate SQL statement...
NULL-keys for key/value table
(Using Oracle) I have a table with key/value pairs like this: create table MESSAGE_INDEX ( KEY VARCHAR2(256) not null, VALUE VARCHAR2(4000) not null, MESSAGE_ID NUMBER not null ) I now want to find all the messages where key = 'someKey' and value is 'val1', 'val2' or 'val3' - OR value is null in...
SQL Query Syntax : Using table alias in a count is invalid? Why?
Could someone please explain to me why the following query is invalid? I'm running this query against an Oracle 10g database. select count(test.*) from my_table test; I get the following error: ORA-01747: invalid user.table.column, table.column, or column specification however, the following two queries are valid. select count(test...
Alright to truncate database tables when also using Hibernate?
Is it OK to truncate tables while at the same time using Hibernate to insert data? We parse a big XML file with many relationships into Hibernate POJO's and persist to the DB. We are now planning on purging existing data at certain points in time by truncating the tables. Is this OK? It seems to work fine. We don't use Hibernate's s...
Error at creating a temporary clob from oracle in c#
Hi everyone, I want to create a temporary clob. That's my function: public static OracleLob CreateTemporaryClob(OracleConnection conn, OracleTransaction tx, string data) { OracleLob tempClob = null; byte[] bData = new System.Text.ASCIIEncoding().GetBytes(data); string cmdText = "declare a clob; begin dbms_lob.createtempora...
JDBC Code Change From SQL Server to Oracle
In the JDBC code, I have the following that is working with SQL Server: CallableStatement stmt = connection.prepareCall("{ call getName() }"); ResultSet rs = stmt.executeQuery(); if(rs != null) { while(rs.next()) { //do something with rs.getString("name") } } Multiple rows are returned for the above situation. I under...