hi I want to call create table/ alter table command from a procedure. Is it possible? My requirement is to change the datatype of a column in all tables. So, I am just getting the column name from user_tab_cols. Now I want to create a temp table which requires create statement .. but i am unable to use that within a proc. Can anyon...
Hi. I'm part of a team of clinical data programmers. We're taking on new responsibilities for loading and manipulating XML data in Oracle (specifically clinical and electronic medical record data). We don't have a lot of history with XML, XQuery, or Oracle XML services. What specific suggestions would you have for training our team? Our...
hi, I have the following table in an Oracle DB id date quantity 1 2010-01-04 11:00 152 2 2010-01-04 11:00 210 1 2010-01-04 10:45 132 2 2010-01-04 10:45 318 4 2010-01-04 10:45 122 1 2010-01-04 10:30 1 3 2010-01-04 10:30 214 2 2010-01-04 10:30 5515 4 2010-01-04 10:30 ...
Hi all, I found in this article, that since ORACLE 10g, there is a way to make a particular connection-session compare strings case-insensitive, without needing any crazy SQL functions, using an ALTER SESSION. Does anyone know if, in 11g, there might be a way to make the database to always operate in this mode by default for all new co...
Platform : Windows running Oracle 10g I have to modify some old dll library codes and precompile with Oracle Pro*C/C++ 9.0.1.1.1, now I discovered the codes for(;;) whenever not found do break is no longer working, it just keep looping. I have prepared some codes here showing before and after precompile and also a log. Thanks in advanc...
Hello, I am new to oracle, I have to create a function that returns current date and time. I am using this to return date; SELECT CURRENT_DATE FROM dual; Thanx ...
Hi, I have around 3.5 million records in a particular table in Oracle 10g and I want to query for all records with one particular column having NULL value. My question is, is it worth to create an index on that column just for the purpose of that one query? Will the time saved by using the index compensate the time taken to create it? ...
I got a little problem. I'm using Full outer Join in Sql server - so far it works but now I add a table and it doesn't work as it should. Here is my code: SELECT * FROM Table1 h , (db1..Table2 s FULL OUTER JOIN db1..Table3 k on k.attributT3_1 = s.attributT2_1 and left(k.attributT3_2,4) = year(s.attributT2_2) and substring(k....
I have a compound trigger and in the after statement I have an update to other table that has also a compound trigger, like in the code below: create or replace trigger TRIGGER for insert or update on TABLE COMPOUND trigger after STATEMENT is begin update THEOTHERTABLE set VALUE = VALUE + 1 where COD = 1; end after STATEMENT; end; ...
I have an Oracle database on one PC. I have to migrate it to my other PC. Both use separate oracle installations. Now how can I export it from my one PC and then import it into other PC? What are the commands for exporting and then importing it? I want structure as well as data to be exported and then imported. I am using Oracle db...
How can I get the location of the tnsnames.ora file by code, in a machine with the Oracle client installed? Is there a windows registry key indicating the location of this file? ...
Hello, I've got an Oracle package that contains many functions. In the package body, these functions can get very complex. Is it possible for SubSonic to generate against specific functions inside of a package? Right now if I have a package called ProjectData and there are functions in there such as GetEmail, CloneProject, GetProject...
How to create xml in oracle using sql query. ...
First, I know that the sql statement to update table_a using values from table_b is in the form of: Oracle: UPDATE table_a SET (col1, col2) = (SELECT cola, colb FROM table_b WHERE table_a.key = table_b.key) WHERE EXISTS (SELECT * FROM table_b WHERE ta...
When and how sqlstm.sqpadto = sqlstm.sqadto can cause segmentation fault? I am getting segmentation fault and getting this line in the core file. I am using proC ...
I am trying to parameterise all our existing sql, but the following code is giving me a problem: command.CommandText = String.Format("SELECT * FROM({0}) WHERE ROWNUM <= :maxRecords", command.CommandText); command.Parameters.Add("maxRecords", OracleType.Int32).Value = maxRecords; The error reported is "ORA-01036: illegal variable name/...
I export an oracle "schema" using exp userid=/ file=pt.dmp log=pt.log owner=FOO buffer=10000000 statistics=NONE direct=Y and then import it into a different schema on the same oracle instance on the same SID using imp userid=/ file=pt.dmp fromuser=FOO touser=paul When I try to access the stored procedures with the new user, I get...
In Oracle/PLSQL, the instr function returns the location of a substring in a string. If the substring is not found, then instr will return 0. I want to search multiple substrings in a string and return the first non-zero value. This can be acheived using regexp_instr, but I'd like a non-regexp_ solution. Example: regexp_instr('500 Or...
I'm drawing a design for a system to do daily business functions for my company. It will consist of a Oracle 10g database with Pl/SQL packages and a Java-based web application. All of this is running on a Solaris 10 server. Aside from handling transactions from the web interface, scheduled tasks need to run on the database to run calc...
I am evaluating Oracle SQL Developer. My tnsnames.ora is populated, and a tnsping to a connection defined in tnsnames.ora works fine. Still, SQL Developer does not display any connections. Oracle SQL Developer Soars mentions, that if you have Oracle client software and a tnsnames.ora file already installed on your machine, Oracle S...