I have some code that generates XML from some tables. The tables that I'm querying were generated from an XSD file the describes the XML I am supposed to be generating. The problem is that the names of the tables and fields follow a different naming convention than those in the schema - for example, an element named "personID" in the sch...
Hi, I am trying to figure out how I could poll for changes that are made to an Oracle Table without using a trigger. The only changes I currently care about are new/inserted records. Any suggestions would be greatly appreciated. I also don't really want to have to use other tables to keep track of what has changed. Thanks! S ...
Hi Required immediate response, I want to take dump of some selected tables from schema, can any body tell me is it possible? Can anybody provide procedure by executing that we can take dump. e.g. I have schema, testuser, and tables (T1,T2,T3,T5,T9), i want to take dump of T1 & T5 only. Thanks in advance ...
I'm curious to find out how people manage their packages in their applications. For example, in our development instance, an application developer may want a change to a stored procedure. However, changing the stored procedure will break the existing Java code until the DAO layer is updated to accommodate for the changes. My typical p...
The following query generates the error below in 10g: select DBMS_METADATA.GET_DDL('TABLE','TEST_TABLE','TEST') from dual; Got: ORA-04031: unable to allocate 128 bytes of shared memory ("shared pool","SELECT /*+rule*/ SYS_XMLGEN(...","sql area","qeeOpt: qeesCreateOpt") ORA-06512: at "SYS.DBMS_METADATA", line 1546 ORA-06512: at "SYS....
I've recently have started writing unit tests for PL/SQL code in Ruby. Are there any other language combinations where you write your code and unit tests in two completely different languages? ...
Selecting the return value of an Oracle stored function that doesn't contain DML can be done by simply selecting the function: select function_name() from dual; If the function contains DML (in this case some inserts to log the arguments passed to the function), the above query is not allowed. (ORA-14551) How can I select/view the re...
I'm using the pl/sql htp package to create simple web pages. Now I need to use different templates based on the device accessing the pages (Desktop Browser or Mobile Blackberry Client ). Is this possible in pl/sql? ...
Say I have a program that searches a database for columns to modify, as part of a database conversion process. If I attempt to alter a column with a functional index defined gives the following error: ORA-30556: functional index is defined on the column to be modified Looking up the ORA code, the solution is to "Drop the functional i...
I ran a data copy function in Toad that disabled all constraints and then failed. That left me with a bunch of disabled constraints. Most of them I've been able to enable successfully, but the flashback ones are giving my stored proc fits. Questions: 1) Is it necessary to reenable the constraints on the flashback tables? My assumpt...
i want to develop a procedure for following scenario. I have one source, one target and one error table. Target and Error tables have all fields that are present in source tables. But the data type of all fields for error table are varchar. Error table don't have integrity, foreign key and other constraints. Error table also have t...
Hi, I have some (5) rename statements in a plsql script drop table new; rename old to new; "old" tables hold very valuable information. As I see it, if the rename command is guaranteed to be atomic, then I´d have one problem solved. Is it atomic? If not, is there a way to do a "safe" rename ? Thanks in advance ...
Does anyone have any frameworks/apps/methodologies for creating Unit tests with Oracle?. I'm using Oracle 11g. I also have Team Foundation Server installed, where we do all our other development work with Visual Studio on a web app. Edit: I'm looking to run unit tests on Packages, Procedures and Functions within Oracle (PL/SQL) ...
I want to write a PLSQL stored procedure that accepts a table name as argument. This table is source table. Now inside my procedure i want to manipulate the fields of that table. EX: I want to insert the records of this source table into another target table whose name is XYZ_<source table name>. The column names for source and targ...
It so happens that I have to get a date from a varchar2 column in oracle, but it is inconsistent in formatting. Some fields might have '2009.12.31', others '2009/12/32 00:00:00'. Is there any standard construct I could use, so that I don't have to go through begin to_date(date, mask1) exception begin to_date(date,mask2) except...
After many Google and SO searches, I cannot find a definitive answer to this simple question: How can I define a procedure inside of another procedure to use? I know that there are nested blocks and nested procedures, but I haven't seen the exact syntax for what I want. i.e. create or replace PROCEDURE TOP_PROCEDURE (...) IS -- nested...
Hi. I am having problem with importing csv files containing values in japanese characters. When I do so it will display garbage when I query. my OS is japanese. My encoding for oracle NLS_LANG is JAPANESE_JAPAN.JA16SJISTILDE. I don't know what the problem is. When I try to import the very same file in some of my office mates' PC it just ...
Hi, I have a procedure that returns an OUT parameter. procedure foo (in_v IN INTEGER, out_v OUT integer) BEGIN ... EXCEPTION WHEN OTHERS THEN --sh*t happend out_v := SQLCODE; END That parameter will be 0 if everything goes OK, and <> 0 if something ugly happened. Now, if sh*t happens along the way, an exception will be th...
Hi, I want to declare a cursor on a table that does not exist. Of course, my procedure doesnt compile. This table is a temporary table, and is created by a pre process. It will exist on runtime, but at compile time its another story. For my select / updates an other DML operations, I've used EXECUTE IMMEDIATE 'operation from tmp_tab...
I have a SQL script that is being executed in TOAD. Currently I have it laid out with just statement after statement, thusly: select such-and-such from somewhere; delete other-thing from somewhere-else; And so on. Some of the where clauses end up being repetitive because I have complex inner queries to get particular IDs to operate...