I am having a oracle table consisting of 6 columns,out of which one is date column. The date field has null entries in some rows...no other field is null.. Can I delete the rows having date field as null? or should I update some value in the date field for those rows...? Please provide me the correct way to do both these operations? th...
How can i prevent duplicate rows being selected in a select query? I have a table with the following fields: name type user1 user2 user3 date My query requires me to select data for a particular user only at a time which is entered by the user at front end.. Say user enters 1, then the select query should retreive data for user1 onl...
I have a following oracle stored procedure CREATE OR REPLACE PROCEDURE getRejectedReasons ( p_cursor IN OUT SYS_REFCURSOR) AS BEGIN OPEN p_cursor FOR SELECT * FROM reasons_for_rejection; END; However, when I run this stored procedure in sql-developer then I dont see anything. I just see something like this: Connecting to...
Hi, I am looking for a solution to automate EAR deployment for OracleAS and WebSphere application servers. Can you guys suggest some open source solution for this? Thanks, Venkat ...
Stored procedure that executes fine with a development database but fails to execute in production database? Error returned is :"PLS-00306: wrong number or types of arguments in call to <procedure naeme>" VC++ database oracle Closed:The error occurred due to wrong synonym. ...
Hi, Wonder if anyone can shed any light on a sql query I'm working with; I have built this query; SELECT SUM(TICKET_TYPE.PRICE) AS TOTALCINEMASALES, CINEMA.LOCATION, PERFORMANCE.PERFORMANCE_DATE FROM RESERVATION, TICKET, TICKET_TYPE, CINEMA, PERFORMANCE WHERE TICKET_TYPE.TICKET_TYPE_ID = TICKET.TICKET_TYPE_ID AND TICKET....
Which is the best Open Source free client for querying an Oracle database? It may additionally support other databases (MySQL, Postgres etc), since our development environment often requires switching between different databases. ...
I am using the .NET framework with the System.Data.OracleClient namespace. I have the oracle 11 client installed on my computer. I do not want to use the tnsnames.ora file to store connection information. Could someone please tell me what the connection string would look like if I did not want to use the tnsnames.ora file? I will be ...
Hi, I am using application builder and setting up select boxes to populate a query that spits out into a report below, I'm having a problem with one populating though, and I can't immediatly see why; Here's how I'm referencing; AND CINEMA.LOCATION = :pticketstown this works fine and pulls in the choice from the select box which is...
I need to use an IN clause like the following SQL SELECT * FROM tableA WHERE colA in (1, 2,3) How can i do this in EJBQL? i've tryed the following, which obviously failed: SELECT tab FROM tableA tab WHERE tab.colA in (:colValues) then in java i did query.setParameter("colValues","1,2,3") that gives me the following exception ...
This has been driving me crazy for a while: DECLARE TYPE AttrValueRec IS RECORD ( attr VARCHAR2(40), val VARCHAR2(2000), inst NUMBER(4) ); FUNCTION create_attrval(attr AttrValueRec.attr%TYPE, val AttrValueRec.val%TYPE, ...
I know that it is possible to use "OS authentication" to authenticate Windows users in Oracle database. The process is basically passing current Windows user id to Oracle for authentication. The question is - is it possible to authenticate domain group in Oracle (as it is possible with SQL Server integrated security), so that there wou...
A basic simple question for all of you DBA. When I do a select, is it always guaranteed that my result will be ordered by the primary key, or should I specify it with an 'order by'? I'm using Oracle as my DB. ...
We want to be able to select top N rows using a SQL Query. The target database could be Oracle or MySQL. Is there an elegant approach to this? (Needless to say, we're dealing with sorted data here.) ...
I have a query editor (Toad) looking at the database. At the same time, I am also debugging an application with its own separate connection. My application starts a transaction, does some updates, and then makes decisions based on some SELECT statements. Because the update statements (which are many and complex) are not committed yet,...
A bit of background first. My company is evaluating whether or not we will migrate our Informix database to Oracle 10g. We have several ESQL/C programs. I've run some through the Oracle Migration workbench and have been muddling through some testing. Now I've come to realize a few things. First, we have dynamic sql statements that are n...
Hi Guys, I've had some brilliant help before and I'm hoping you can get me out of a hole again. I've got a date coming in from a web service in this format: 2009-02-13T11:46:40+00:00 which to me looks like standard UTC format. I need to insert it into an Oracle database, so I'm using to_date() on the insert. Problem is, I cant get a...
I have a Delphi application that connects to an oracle database. When I try to log in as SYSDBA, I get the error: ORA-28009: connection as SYS should be as SYSDBA or SYSOPER When I try to log in with a user of "SYS AS SYSDBA", I get the error: ORA-01017: invalid username/password; logon denied How can I log in as sysdba using a db...
I have a SQL query that looks something like this: SELECT * FROM( SELECT ..., row_number() OVER(ORDER BY ID) rn FROM ... ) WHERE rn between :start and :end Essentially, it's the ORDER BY part that's slowing things down. If I were to remove it, the EXPLAIN cost goes down by an order of magnitude (over 1...
I'm considering adding an index to an Oracle table, but I'd like to first estimate the size of the index after it has been built (I don't need a precise size - just an estimate.) Supposing I have access to all of the meta-data about the table (number of rows, columns, column data types, etc) that I can execute any arbitrary Oracle SQL q...