How does one create date/time ranges for TIMESTAMP WITH TIMEZONE timestamps with a range precision down to the level of seconds? Here is my sample query, however I can only create ranges down to the day level: SELECT COLUMN_NAME, MY_TIMESTAMP FROM TABLE_NAME WHERE (MY_TIMESTAMP BETWEEN SYSDATE - INTERVAL '1' DAY AND SYSDATE - INTERV...
Hi Guys! I'm thinking of a way on how to benchmark an Oracle procedure. My idea is this, put a logging function on each part of the procedure you wish to benchmark. Store the time it entered that part. Then, create another app (using PHP) to compute the difference between each execution. The PHP app is needed to make it as layman as po...
My target DBMS's are both Oracle and MySQL SELECT users.* , hr_orders.position_label , hr_orders.is_boss , deps.label AS dep_label -- preprocessor removes AS when using Orcale , users.id IN (?) AS show_on_top -- Orcale doesn't accept this IN thing FROM -- some joins here WHERE -- some filters Is it possible to ...
I have a dynamic PL/SQL that will construct the SELECT statement based on what the searching criteria input from the users,likes: l_sql := 'SELECT * INTO FROM TABLEA WHERE 1=1 '; IF in_param1 IS NOT NULL THEN l_sql := l_sql || 'AND column1 = in_param1 '; END IF; IF in_param2 IS NOT NULL THEN l...
VARIABLE StayWorkflow_Id_max number; BEGIN SELECT max(StayWorkflow_Id)+1 into :StayWorkflow_Id_max from MVStayWorkflow; END; / insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id, User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState, PreviousState, WorkflowTime, UserStamp, TimeStamp) ...
Hi all; my table; Date | Cost 01.01.2010 | 100 02.01.2010 | 200 03.01.2010 | 300 04.01.2010 | 400 10.01.2010 | 800 11.01.2010 | 800 12.01.2010 | 800 25.01.2010 | 500 26.01.2010 | 500 05.02.2010 | 600 13.02.2010 | 700 15.02.2010 | 700 ı want to make "date between '01.01.2010' and '28.02.2010' " weekly vi...
Dear all we have a dot net application and it connecting to Oracle and fetching data and moving to SQL server. it was working very fine. just started giving error ORA-12560: TNS:protocol adapter error . Tnsping also giving this error. but if i stop this application and tnsping then its success. again starting the application on the firs...
I have a strange issue with a query running in my JDeveloper ADF web application. It is a simple search form issuing a select statement to Oracle 10g database. When the search is submitted, ADF framework is (first) running the query, and (second) running the same query wrapped within "select count(1) from (...query...)" - the goal here i...
When this code is executed in SQL Developer against Oracle 11g I get an error, begin dbms_scheduler.create_job( job_name => 'comuni_34', job_type => 'plsql_block', job_action => 'begin com_auth_api.expire_old_passwords; end;', start_date => to_date('2009-jan-01 01:15:00', 'yyyy-mon-dd hh24:mi:ss'), repeat_interval => 'freq=dai...
I have tried a lot but could not download blob data more than 1MB from oracle database. What is the solution for it. I have following header: ini_set("memory_limit", "200M"); set_time_limit(0); header("Pragma: public"); header("Expires: 0"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Cache-Control: p...
Hello, what is the best way to track changes of a materialized views in Oracle. I know, that triggers are available and CDC does not seem to work with materialized views. Are there any clever solutions? Thanks for your help Johannes ...
the problem is this : I implemented a trigger on the table called CLAN_AFFILIATI that increases (if inseriemento) and decreases (in case of cancellation) an attribute (NUMAFFILIATI) of another table called CLAN. what I would do is block the update NUMAFFILIATI of Clan by the user and had thought to r another trigge on CLAN that did this:...
I need to take information from two different data bases. select * from TABLE_ONDB2 where column_on_db2 in ( select column_on_db1 from TABLE_ONDB1 ); Problem is both are on different db instances so I am not able to figure out how to put table names and column names etc. I hope my question is clear. ...
I have filesystem-like (but numerical) path of arbitrary length which I need to canonicalize to a fixed depth (2 or 3). Basically, I want this result: /01/02/007/008 -> 01/02/007 /01/02/007 -> 01/02/007 /01/02 -> 01/02/NA /01 -> 01/NA/NA I want to do with using Oracle's regexp_replace() function, which a...
Hello guys; I think this question has been done before, but I don't find a clear answer. I have migrated SQl Server 2005 database to oracle 11. I am using subsonic 2.1. I have manged to create all the data layer classes, and generates everything well. But the problem turns up when I try to make an insert in the database: Oracle does not...
Hello, I am using a standalone Oracle.DataAccess manual installation for my project to avoid a full Oracle client installation on customers machines (no oracle home). For this I followed this guide and it works pretty well. Actually the user/password is stored in a configuration file but I want to use the current AD user so I need to p...
I'm looking for an Oracle Regular Expression that will match tokens in any order. For example, say I'm looking for "one two". I would want it to match both, "one token two" "two other one" The number of tokens might grow larger than two, so generating the permutations for the regex would be a hassel. Is there an easier way to do this...
I am really confused between which certification to take up? Both of them are good for DBA. I have started working with Oracle and have no knowledge of both the databases. ...
In Oracle how do I convert a "timestamp with time zone" to the current time zone of the server? I need to dynamically figure out what the current time zone of the server is. create table test_table1 (rec1 timestamp with time zone) insert into test_table1(rec1) values ( to_timestamp_tz('1/1/1900 09:00:00 AM US/EASTERN','dd-mm-yyyy hh:mi...
I have a .Net 2 solution with some asp classic pages. I get a specified cast is not valid error when I access the page on my local. Below is the vb function where I get the Error: Public Function Retrieve(ByVal vntSeqId As String, ByRef vntPart As String, ByRef vntPartKey As String, ByRef vntEntDate As String _ , ByRef vntStat...