I have created a Oracle function to send an email using the UTL_SMTP package. When using the methods Write_Data or Data, if my text does not contain a colon, the sent email will contain the inputted text in the email's body. However, if the text contains a colon, the text is not included in the email. Every example of this i've seen o...
I'm looking through an Oracle script I found online, but it runs a sed command to filter results from a trace file. I'm running Oracle on a Windows server, so the sed command isn't recognized. host sed -n '/scattered/s/.*p3=//p' &Trace_Name | sort -n | tail -1 I've tried reading the online documentation, but am still not sure how to ...
I had an idea that I could write a query to find all the decendent tables of a root table, based on foreign keys. Query looks like this: select level, lpad(' ', 2 * (level - 1)) || uc.table_name as "TABLE", uc.constraint_name, uc.r_constraint_name from all_constraints uc where uc.constraint_type in ('R', 'P') start with uc.table_name ...
In SQL Server it is easy to parse a vachar variable that contains a simple XML snippet constructed with attributes and load it into a temp table - see example below: declare @UpdateXML VARCHAR(8000) set @UpdateXML='<ArrayOfRecords> <Record Field01="130" Field02="1700" Field03="C" /> <Record Field01="131" Field02="1701" Field03="C" ...
I have a rather large Oracle PL/SQL script I'm testing and I wanted to know if it was possible to see what records were updated/deleted/inserted since the last commit to the database? I need a faster way to check that all the database actions were done correctly. I have access to the command line as well as Oracle's custom tool SQL Devel...
I have an Oracle 10G database. I ran following script to take the backup of the database. alter tablespace EMP2010 begin backup; host copy G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EMP2010.DBF G:\orabackup\database\ alter tablespace EMP2010 end backup; alter tablespace PAYROLL2010 begin backup; host copy G:\ORACLE\PRODUCT\10.2.0\ORADATA\OR...
I currently have two fairly long select statements, identical apart from the fact that the first is looking to see if the row exists via count(*) and the second selecting the row into the rowtype variable based on an if statement. This is because the data is required for further manipulation I know it is possible, but I am having a com...
I am generating some test-data and use dbms_random. I encountered some strange behavior when using dbms_random in the condition of the JOIN, that I can not explain: ------------------------# test-data (ids 1 .. 3) With x As ( Select Rownum id From dual Connect By Rownum <= 3 ) ------------------------# end of test-data Select x.id, ...
Hello, I need to know true tablespace size in Oracle. I have some tablespace and I need to know how many space it uses now and how many space is free (and maybe percent of free space). I found in web some sqls but all of them showed size based on water mark... which is not true space allocated now but as far as I know the highest value...
hi Experts I want backup with RMAN and after that for example delete scott.dept and then restore it. but i can't :( I write it : 1)rman target sys/manager@db 2)in sql*plus shutdown immediate; startup mount exclusive; ALTER DATABASE ARCHIVELOG; 2)CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'g:\db\db_cf%F...
Can someone explain what nested table objects are in Oracle ? While building an interface between to systems I discovered what was to me an odd column SYS_NC00054$. After some research I discovered that it was a nested table object from the function based index I created. ...
I'm a .net/c# developer but I need to set up an Oracle development rig. I have no clue about Oracle, and was hoping for some advice. Here are some things I'm interested in: What is licensing like? Is it possible to get copies of Oracle products for development just like we get SQL Server from MSDN? What operating system should I have o...
I'm faced with a situation where I have two tables: A and B. B has a foreign key to A. A has a column "Detailed" which identifies whether or not its children in B require their "Details" section to be filled out. If I have my lean structure there is no way for me to determine if a record in B needs to have its "Details" section filled...
I have a somewhat complex query that I want to use as a source in an SSIS package. I create my OLE DB Source, specify the access mode as SQL, and paste my query in the command textbox. When I click Preview, sample data comes back and everything looks good. However, when I try to run the package I get back "external columns are out of sy...
Hi, all. I'm looking for a strategy to allow automatic updates for a number of databases at customer sites through a publish-subscribe kind of mechanism. Right now there is a datacenter which has all the master data that get fed through extractions from hundreds of databases out there. The problem is that, whenever I need to do create a...
We use @OneToMany for our Parent->Child->Child->Child DB relationship: @OneToMany(cascade = CascadeType.ALL) @JoinColumn(name = "THE_ID", nullable = false ) private List<ChildClass> children = new ArrayList<ChildClass>(); We have a scenario with a lot of data (100K inserts) where the performance is atrocious (actually times out) when ...
I have table MV_ULICE in schema ADRESY. However in JPA I connect to database (Oracle) using different user then ADRESY. This user has privilege to access tables from schema ADRESY, so in JPA there was no problem with defining entities, since you can easily provide different schema in entity definition: @Entity @Table(name = "MV_ULICE", ...
Hoping this is possible and someone can assist but I need a means of obtaining the local IP Address (Windows box listed in ipconfig command info) from within Oracle ApEx. Is this possible as I have tried both owa_util.get_cgi_env('REMOTE_ADDR') as well as sys_context( 'userenv', 'ip_address' ) Just can't seem to get my local pc ip addr...
I am getting the error: "The Network Adapter could not establish the connection" from a web application deployed in Oracle Application Server 10g. The database is local, so there shouldn't be any connection issues. First test: I can connect to the DB no problem from SQL plus, run queries, etc. Second test: I can connect to the database...
I have a table containing hundreds of columns many of which are null, and I would like have my select statement so that only those columns containing a value are returned. It would help me analyze data better. Something like: Select (non null columns) from tablename; I want to select all columns which are not-nullable for all rows. Ca...