So I have the following user defined type in my oracle database: CREATE OR REPLACE TYPE METRIC_IMPERIAL_DISTANCE AS OBJECT ( METERS_FEET INTEGER, CENTIMETERS_INCHES INTEGER, FRACTION NUMBER ) I can do the following: SELECT t_id, get_distance_breakdown (h.height, h.unit_of_measure_id) height_breakdown FROM heights h an...
How to 'group by' a query using an alias, for example: select count(*), (select * from....) as alias_column from table group by alias_column I get 'alias_column' : INVALID_IDENTIFIER error message. Why? How to group this query? ...
Hi everyone. I've a terrible memory. Whenever I do a CONNECT BY query in Oracle - and I do mean every time - I have to think hard and usually through trial and error work out on which argument the PRIOR should go. I don't know why I don't remember - but I don't. Does anyone have a handy memory mnemonic so I always remember ? For examp...
Is there any technology out there that will allow you to do side-by-side updates of production schemas? The goal is to have zero down time when applying updates to a schema in production. Weblogic 10 has a similar feature for their JEE apps where by you deploy the new version of the app and new connections go to the new app, while the e...
Hi All, I am having trouble with a very simple Perl process. I am basically querying an Oracle database and I want to load it into Excel. I have been able to use DBIx::Dump and it works. However, I need to be able to use a variety of Excel formatting tools. And I think Spreadsheet::WriteExcel is the best module that outputs to Excel ...
I am told by someone that when calling Oracle from ADO.net, when calling multiple inserts in a loop, where each insert causes a trigger to fire that includes within it's PL-Sql a Commit statement, that it is impossible to stop that commit from actually commiting the transaction. i.e., I want my ADO.Net code to begin a transaction before...
I am trying to create a report in SSRS2000 that will query an ORACLE database and pass one parameter, I am getting the following error message: ORA-01036: illegal variable name/number But have been unable to find much help elsewhere on the web with this error code I was hoping that this would be fairly simple to do, does anyone have a...
If I wanted to replace the * with a column name, what would it be? create type mytable$t as table of number; / declare mytmou mytable$t := myTable$T(); cnt pls_integer ; begin mytmou := myTable$T(1,2,3,4,5,6); SELECT count(*) into cnt From Table (mytmou); dbms_output.put_line(cnt); end; 6 ...
A client sent us a oracle database we need to test against. We don't use oracle or have any oracle expertise in house. We need to setup the database so we can connect to it and debug a problem. I did a fresh install of oracle 9 (the version the client is running) and the management tools. I cannot for the life of me get it to import t...
Working with an Oracle 9i database from an ASP.NET 2.0 (VB) application using OLEDB. Is there a way to have an insert statement return a value? I have a sequence set up to number entries as they go into the database, but I need that value to come back after the insert so I can do some manipulation to the set I just entered in the code-...
Hi, Got into a situation where in a schema i have a table, say table ACTION , while i got a synonym called ACTION as well which refers to another table to another schema. Now, when i run the query select * from ACTION it will select the records from the table, but not the synonym. Anyway for me to select from the synonym AND the tab...
There are several similar Oracle tecnologies - Oracle Streams, Oracle Change Data Capture and Database Change Notification. Do you know what is difference between those? Are they related or Oracle Advanced Queueing? ...
In Oracle I can declare a reference cursor... TYPE t_spool IS REF CURSOR RETURN spool%ROWTYPE; ...and use it to pass a cursor as the return value... FUNCTION end_spool RETURN t_spool AS v_spool t_spool; BEGIN COMMIT; OPEN v_spool FOR SELECT * FROM ...
How do I check for an open connection in jdbc for oracle database? Note: conn.isClosed() cannot be used for this. ...
I'm trying to run an application using Oracle ADF Business Components on a JBoss Server. I've maneged to deploy it, but when I try to load the page I get this: java.lang.ClassCastException: org.jboss.resource.adapter.jdbc.jdk6.WrappedConnectionJDK6 cannot be cast to oracle.jdbc.OracleConnection Is there a way solve this? Maybe configur...
I'm getting an Exception while trying to insert a row in oracle table. I'm using ojdbc5.jar for oracle 11 this is the sql i'm trying INSERT INTO rule_definitions(RULE_DEFINITION_SYS,rule_definition_type, rule_name,rule_text,rule_comment,rule_message,rule_condition,rule_active, rule_type,current_value,last_modified_by,last_modified_dttm...
What is the string concatenation operator in Oracle SQL? Are there any "interesting" features I should be careful of? (This seems obvious, but I couldn't find a previous question asking it). ...
Does the SCN itself encode a timestamp or is it a lookup from some table. From an AskTom post he explains that the timestamp to +/-3seconds is stored in raw field in smon_scn_time. IS that where the function is going to get the value? If so, when is that table purged if ever? If so, what triggers that purge? If it is, does that make...
I am using: set constraints all deferred; (lots of deletes and inserts) commit; This works as expected. If there are any broken relationships then the commit fails and an error is raised listing ONE of the FKs that it fails on. The user fixes the offending data and runs again. then hits another FK issue and repeats the process. Wh...
In PL/SQL, I would like to pass in a "source" schema as a parameter to a stored procedure. For instance: BEGIN CURSOR my_cursor IS SELECT my_field FROM <schema>.my_table ... I want the 'schema' value to come from an input parameter into the stored procedure. Does anyone know how I could do that? P.S. Sorry if this is a st...