Your user wants a report sorted by date. This report is being created using C# and ODP.NET You do a sort on the Date column and the output is not exactly what the user expected. Upon some closer inspection, you find that the date column is a varchar2 field. The values in the column are stored in DD-MON-YY "01-JAN-10" format. How will ...
I have the following tables I need to find out the sum. Table A ID Name 1 Jason 2 Peter 3 Ravi Table B ID ID_SEC 1 11 1 12 1 13 2 21 2 22 2 23 3 31 3 32 3 33 Table C ID_SEC Value Include_Ind 11 100 Y 12 200 Y 13 300 N 21 10 Y 22 20 N 23 30 N 31 1000 N 32 2...
There is a way to use a calculated field in the where clause ? I want to do something like SELECT a, b, a+b as TOTAL FROM ( select 7 as a, 8 as b FROM DUAL UNION ALL select 8 as a, 8 as b FROM DUAL UNION ALL select 0 as a, 0 as b FROM DUAL ) WHERE TOTAL <> 0 ; but I get ORA-00904: "TOTAL": invalid identifier. So I hav...
Title says it all... I've seen the tutorials at http://www.olegsych.com/2007/12/text-template-transformation-toolkit/ but they don't translate directly to Oracle. ...
I know that fetching a cursor will give me access to variables like %ROWCOUNT, %ROWTYPE, %FOUND, %NOTFOUND, %ISOPEN ...but I was wondering if there are any other reasons to use Open - Fetch - Close instructions to loop a cursor rather than Loop the cursor with a FOR cycle... (In my opinion this is better becase it is simple) What...
I have two tables where I was querying on a date column in each table. In one case need to use the trunc() function on the date field to get values back, on the other I do not. That is this works on table 1: SELECT to_char( datecol1 ,'mm/dd/yyyy hh:mm:ss') FROM table1 where datecol1 =to_date('10/07/2010', 'mm/dd/yyyy'); But o...
I have a situation to choose between parsing XML documents in Oracle PL/SQL and parsing them in Java. The system receives the XML documents on a message queue and so the XML documents are not on the file system, unless I write them to the filesystem after reading from queue. And, the intent of parsing is to insert/update records in a bun...
I'm working on Oracle and Perl. I have three fields in a table (A, B, C), and I would like to form, for every row in the table, a string a_b_c using "join". C refers to a date. It can take null values. In that case , this "join" will return a warning "use of uninitialized values in join". I know that i have to select nvl(C,something) so ...
Consider the following scenerio.... I have a master user MASTER. I have a test user TEST. For both users the table structure are same. Both user can be on different oracle servers. then I create a database link as master_link by logging in as test user to sql plus using the following command CREATE DATABASE LINK master_link CONNECT ...
Hi, i am trying to fetch an id from an oracle table. Its something like "TN0001234567890345". What i want is to sort the values according to the right most 10 positions. I am using oracle 11g. Is there any funcrion to cut the rightmost 10 places in oracle ? thanks in advance tismon ...
I am using NetBeans 6.5.1 and Hibernate 3.2.5. I stepped through this tutorial by using a different, existing database (Oracle 10g XE). The creation of the config file works fine, as well as the creation of the HibernateUtil.java class. The tutorial then wants me to run the reverse engeneering wizard, but this wizard does not exist. Ins...
Having a Unicode (multi-byte charset) Oracle database with NLS_LENGTH_SEMANTICS=BYTE seems like a disaster waiting to happen. Field validation in most applications only check the number of characters is within bounds, not the byte sequence length in the database’s default character encoding scheme! If you've got a Unicode database, is th...
i want to create an insert script which will be used only to insert one record into one table. It has 5 columns and one of them is of type CLOB. when ever i am trying it says can not insert string is so long . larger than 4000. i need an insert statement with clob as one field. insert into tblTableName (id, name, description, account...
Hi, we have a Java program connecting via JDBC thin client to an Oracle 10g database. Everything was working fine, but now the DBA wants us to connect with a different username/password, which is supposed to have access to the same tables using public synonyms. Unfortunately the Java program no longer sees the tables (see error below wh...
With this, set serveroutput on size 900000; DECLARE test VARCHAR(255):=0; BEGIN SELECT id INTO test FROM sku WHERE id = 1515; dbms_output.put_line('Result:' || test); END; I'm having the error "no data found" When ID doesn't exist in database. How can I use something like nvl() here, so I can get a default value instead ...
Background I'm trying to migrate from an LDAP identity store to a database backed implementation. There will be a migration period in which it will be necessary to sync any changes to passwords on the database with the LDAP store. My question is if its possible to make a LDAP modify call from a stored procedure on the Oracle Database. ...
I have a DTO with 40+ properties. But in order to populate all properties I need to execute 4 separate queries. My first query is in charged of getting basic information. For each row returned I run 3 more queries based on the id's given from the main query (N+1 problem). I can set use eager loading but then I'm loading thousands of obje...
I have this code in sql: SELECT DISTINCT O.custid, P.productname, TO_CHAR(MAX((quantity) * D.unitprice) AS "Revenue" FROM corp.Orders O LEFT JOIN corp.Order_Details D ON O.orderid = D.orderid LEFT JOIN corp.Products P ON D.productid = P.productid GROUP BY O.custid, P.productname HAVING P...
Hi All. I'm designing an application that will use Oracle, and we have this department hierarchy that we need to map in our database. Some thing that looks like this (I'm pretty sure you all know what I'm talking about but I'll include a piece of the ERD just in case): So it will have data stored that looks like this: [1 | 0] [2 | ...
Burning out a brain cell... there has to be a simple way to do this. I've inherited the following tables: approval_path approval_path_steps applications application_roles requests role_approvals A user requests an application role for an application, which must go through an approval path, the steps of which are defined in approval_p...