I have a table that I need to insert a set of records into based on user input. IE: Selection from a list of items to choose from. For this example, let's just say it's a collection of int's. Not a lot of good examples on the internet. There's only one related question here on SO, but it lends no answers either (both answers are dead en...
Hi, i am using ExecuteOracleNonQuery and getting rowid as output parameter.How can i optimize subsequent related queries by using rowid? ...
We have such a oracle database which contains "Tranditional Chinese" character and english, and the environment is : PARAMETER VALUE NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET WE8PC850 NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR ...
I'm trying to get callbacks for oracle streams to work on ORACLE XE. I'd like to be able to perform a demo of using JMS to fire a pl/sql procedure. There are a million other ways of doing this, I'm sure. The idea is to enqueue a message and have the callback fire off. The call back is created using DBMS_AQ.REGISTER All I seem to get is...
I occasionally do something like.... IF very-likely-condition THEN NULL; ELSE <<code to deal with the unlikely condition>> END IF; Which gives a PLW-06002 unreachable code warning from the PL/SQL compiler on the NULL line atfer the IF. Now whilst I can clearly ignore the warning and/or refactor the IF statement to be a NOT, I...
I write a simple procedure. I try to store selection result in variable. I use "SELECT INTO" query but I can not doing this. Example: DECLARE v_employeeRecord employee%ROWTYPE; BEGIN SELECT * INTO v_employeeRecord FROM Employee WHERE Salary > 10; END; ...
I create a simple type: create or replace TYPE SIMPLE_TYPE AS OBJECT (ID NUMBER(38), NAME VARCHAR2(20)); Simple test: DECLARE TYPE ObjectList IS TABLE OF SIMPLE_TYPE; tmp SIMPLE_TYPE := SIMPLE_TYPE(1, 'a'); o ObjectList := new ObjectList(SIMPLE_TYPE(2, 'a'), SIMPLE_TYPE(3, 'a')); BEGIN IF tmp.EXISTS(tmp) THEN dbms_out...
I have a scenario in oracle where i need to be able to reuse the value of a pseudo column which was calculated previously within the same select statement something like: select 'output1' process, process || '-Output2' from Table1 I don't want to the repeat the first columns logic again in the second column for maintenance purposes, c...
I am using the REGEXP_LIKE function in Oracle 10g to find values in a column with a suffix of _#(like _1, _2 etc). I can find _# in any part of the value with the query below but can I return only values with _# at the end ? SELECT * FROM Table WHERE REGEXP_LIKE (COLUMN,'_[[:digit:]]') ...
what is the default value of a variable (VARCHAR2) at the time of declaration in PL SQL? Can i check it against NULL once after i declare a variable? ...
Every single book that teaches programming (or almost anything else) starts off with a whole bunch of spiel on why what it's about (C++, mysql, waterskiing, skydiving, dentistry, whatever) is the greatest thing in the world. So I open the MySQL O'Reilly book, and read the intro, and get the traditional sermon. The main points that the bo...
Hello, I've spent hours trying to find a fix to this problem, but since I'm unable to find I've decided to ask here. I'm doing a procedure to retrieve information from a table based on the row's ROWID and with the results I'm doing some custom query using execute immediate. I've reduced the problem to the following lines, which I'm exec...
I have a WCF based web service hosted in windows sever 2003 machine. The database is Oracle 10G on solaris. The web service uses transactionscope extensively and ado.net transactions in some places. Most of the transactions involve only one resource (multiple oracle stored proc calls). Some of them are two resources (MSMQ and oracle stor...
we have created following anonymous block........... DECLARE sql_str long(32000); where_str long(32000); counter NUMBER(3):=0; BEGIN sql_str:='SELECT '||' A.bio_id ,'; where_str:=' where '||'A.bio_id=B.bio_id AND'||' A.bio_id<>0 and rownum<25 AND (' ; LOOP counter:=counter+1; sql_str:=sql_str||'decode(A.wk_unit...
Consider a table with 3 columns: ID (unique, taken from an Oracle sequence), CATEGORY and CODE (no constraints on these last two). Each category has multiple codes attached to it, but the codes must be unique within that category. Example: ID CATEGORY CODE 1 1 X 2 1 Y 3 1 Y //wrong The ...
I write simple procedure. DECLARE connection_id LINE.CONNECTION_ID%TYPE := 11009; tmp_integer INTEGER; BEGIN SELECT COUNT(*) INTO tmp_integer FROM LINE WHERE LINE.CONNECTION_ID = 11009; DBMS_OUTPUT.PUT_LINE(connection_id); DBMS_OUTPUT.PUT_LINE(tmp_integer); END; Result of the launch: 11009 3 It is good result. I have only...
Can anybody let us know an easier way to get the result as shown by following query? We need to find a way that can show us the same result as the following query: SELECT DISTINCT A.bio_id , DECODE(A.wk_units2 - B.wk_units1,0,NULL,A.wk_units2) prev, DECODE(A.wk_units2 - B.wk_units1,0,NULL,B.wk_units1) curr, DECODE(A.wk_uni...
Hi, I'm trying to send parameters to a web site, with no success. Can anyone please show me a way of how to do it? an example of one way of doing it in ASP for instance is like that: Response.Write(http://ads.hevre.co.il/External_Leads_insert_fullDetails.asp?afid=2512&Firstname="AAA"&Exfield1="BBB"&"&phone="321321321"&a...
Hi I am trying to use my .net user control inside jsp page. The jsp is served by Tomcat in Oracle BPM 10g. I tried: adding mime tags for dll in web.xml Adding address to iexplore trusted zones. Using server generated adresses instead of fuego calls like: classid="..//webRoot/webResources/ImageEditor.dll#ImageEditor.Editor" The dll...
I wrote a stored-procedure in Oracle and now, I want to launch it in Java code. I will describe a problem. I have a object type: TYPE PERSON_TYPE AS OBJECT (ID NUMBER(38), NAME VARCHAR2(20)); And table type: TYPE PERSON_TYPE_TABLE AS TABLE OF PERSON_TYPE; My procedure looks like this: PROCEDURE EVALUATE_PERSON_PROC(P_PERSON_ID IN ...