I wrote a PL/SQL script to set a sequence's value to the maximum value of a table's primary key: DECLARE max_idn NUMERIC(18, 0); seq_nextval NUMERIC(18, 0); increment_amount NUMERIC(18, 0); BEGIN SELECT MAX(mbr_idn) INTO max_idn FROM mbr; SELECT mbr_seq.nextval INTO seq_nextval FROM DUAL; increment_amount := max_id...
I can understand that many years ago there would be this kind of limitation, but nowadays surely this limit could easily be increased. We have naming conventions for objects, but there is always a case that turns up where we hit this limit - especially in naming foreign keys. Does anybody actually know why this isn't a bigger size - or ...
I have a small table of measurement units in Oracle (10.2.0.4). It's defined as CREATE TABLE Units ( UNIT_ID number, UNIT varchar2(12) ) It's populated with a few records, and one of those records has a unit value of 'μL'. When I try to query for that record using this query... select * from units where unit = 'μL' .. I g...
Hi everybody, cmd.CommandText = "SELECT alarm_id,definition_description,element_id, TO_CHAR (alarm_datetime, 'YYYY-MM-DD HH24:MI:SS'),severity, problem_text,status FROM aircom.alarms WHERE status = 1 and TO_DATE (alarm_datetime,'DD.MM.YYYY HH24:MI:SS') > TO_DATE ('07.09.2008 09:43:00', 'DD.MM.YYYY HH24:MI:SS') order by ALARM_DATETI...
If I have materialized view in Oracle which is defined as REFRESH FAST ON COMMIT every 15 minutes. It works when initially created and refreshes happily. What can cause it to stop fast refreshing? I can see that it has stopped refreshing based on this: select mview_name, last_refresh_date from all_mviews; ...
I seem to be seeing a lot of messages like this in my log: Trigger DEV."MBR_TRG" was disabled before the load. Trigger DEV."MBR_TRG" was re-enabled by another process. SQL*Loader-951: Error calling once/load initialization ORA-00604: error occurred at recursive SQL level 1 ORA-00054: resource busy and acquire with NOWAIT specified Thi...
Hello members, I'm working on the following 2 tables on Oracle 10g. I'm attempting a rather simple task, but can't get my query right, perhaps due to my lack of understanding of the basics. I want to query out the account_no from TEMP which is not present in BMF. Two Tables: Table 1: BMF: 1372 rows account_no | trans_amount | tra...
I am hoping to find how I can get the kb size of a result set in OracleDB. I am not an sysadmin, but often run queries that return over 100k rows and I would need to find a way to determine what is the total kb size. thank you ...
In Oracle 10g, I'd like to create a regular expression to list the characters that are different between two strings. Here is the reason : I have a table with a field that contains sometimes Unicode characters that are not in the french language. I am able to list the rows containing these non standards characters to make a future c...
Hi, I require a means of checking to see if a string has the following exact pattern within it, i.e.: (P) Examples where this would be true is: 'Test System (P)' Unsure though how to check for cases when the string that doesn't have '(P)', i.e: 'Test System (GUI for Prof)' - in this case, this would be false but I am using REGEXP_...
Hi all, I need to call database function within in qt application. But, it fails with below error; QOCIResult::exec: unable to execute statement: ORA-06576: not a valid function or procedure name DB: Oracle Driver: QOCI The Function returns record table. C++ code: QString qs = "CALL func()"; QSqlQuery query; query.exec(qs); // thi...
Problem Enterprise Manager starts and then hangs. Environment RAC installation on Windows, comprised of two nodes, node1 and node2. Enterprise Manager is installed on node1. We are able to get dbconsole to run briefly and and then it fails. emagent.trc from node1 shows what appear to be two sets of relevant errors. The first s...
This is the first time I've dealt with Oracle, and I'm having a hard time understanding why I'm receiving this error. I'm using Oracle's ODT.NET w/ C# with the following code in a query's where clause: WHERE table.Variable1 = :VarA AND (:VarB IS NULL OR table.Variable2 LIKE '%' || :VarB || '%') AND (:VarC IS NULL OR table.Variable3...
How to create a partition table in oracle10g? ...
When i execute the below SQL command using single quotes to enter a number, i got an error if remove the single quotes,it is successfully updated. knowing that the type of the field HEIGHT is NUMBER. The strange thing is that i tried to use the same sql statement with single quotes on different machines, some machines execute it success...
I have one table with the following Column. BoxNumber Status 580 4 581 4 582 4 583 4 584 2 585 2 586 4 587 4 588 4 589 4 590 2 591 2 I...
In the answer to a previous post (Tuning Rows-to-Cols Query), I learned how to more efficiently construct a row-to-cols query which allows for filtering by date. However, I now need to take this one level further. The schema for the query below is as follows: SAMPLE (1-to-many) TEST (1-to-many) RESULT (1-to-MANY) Each sample has one or...
I have a SQL*Loader control file that has a line something like this: FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '#' Normally, I'd use a quotation mark, but that seems to destroy emacs's python syntax highlighting if used inside a multi-line string. The problem is that we are loading an ADDRESS_LINE_2 column where only 7,000 out...
I have a PL/SQL function with BOOLEAN in parameter: function get_something(name in varchar2, ignore_notfound in boolean) This function is a part of 3rd party tool, I cannot change this. I would like to use this function inside a SELECT statement like this: select get_something('NAME', TRUE) from dual This ...
Hi, I need to open up my database to a remote QA team.I want to ensure that they have read-only access to all my database objects (table records,procs, functions, views,etc) . But they should not be able to delete or drop database objects. How do i do that in oracle 10.2g ? Is there a built-in role which can be assigned to the user ...