I'm writing some JDBC code which calls a Oracle 11g PL/SQL procdedure which has a Custom Object return type. I can get the code to call the procedure, but how do I access the returned Custom Object to obtain it's contained values?. An example of my code calling the procedure is below: PLSQL Code: Procedure GetDataSummary (p_my_key I...
Here is the table data with the column name as Ships. +--------------+ Ships | +--------------+ Duke of north | ---------------+ Prince of Wales| ---------------+ Baltic | ---------------+ Replace all characters between the first and the last spaces (excluding these spaces) by symbols of an asterisk (*). The number o...
Cannot stop and drop oracle Queue. Following code BEGIN DBMS_AQADM.STOP_QUEUE ( queue_name => 'TEST_QUEUE'); DBMS_AQADM.DROP_QUEUE( queue_name => 'TEST_QUEUE'); END; / produces following errors: ERROR at line 1: ORA-04068: existing state of packages has been discarded ORA-04065: not executed, altered or dropped stored procedure ...
Hi, In a WHERE part of query we have SELECT * FROM SomeTable st WHERE NOT EXISTS (SELECT 1 FROM Tab t1 WHERE t1.A = st.A OR t1.B = st.A) OR EXISTS (SELECT 1 FROM Tab t2 WHERE (t2.A = st.A OR t2.B = st.A) AND t2.C IS NULL) Seems like a good candidate for merging... But I'm staring on that for an hour without any idea. Would yo...
Hi, I've got a table which has an id and a clob content like: Create Table v_example_l ( nip number, xmlcontent clob ); We insert our data: Insert into V_EXAMPLE_L (NIP,XMLCONTENT) Values (17852,'<section><block><name>delta</name><content>548484646846484</content></block></section>'); Insert into V_EXAMPLE_L (NIP,XMLCONTENT...
I want to pass/bind an array of (key1, key2) to an Oracle PL/SQL stored procedure using PHP. I'm able to bind primitive types and arrays of primitive types, but haven't found a way to pass complex datatypes back and forth. Is this unsupported? So far I've been having to pass along multiple arrays -- one for each subtype in my complex ...
I'm trying to execute a PL/SQL function in EclipseLink which uses Oracle Specific types(eg. Boolean). I've tried using PLSQLStoredProcedureCall which complains that I'm not executing a procedure and I have tried using StoredFunctionCall but that returns PLS-00382: expression is of wrong type Has anyone developed a solution to invoke ...
updated_date = 08-Jun-2010; I have a query like this select * from asd whre updated_date <= todate('08-Jun-2010', 'dd-MM-yy'); but i am not getting any result. it is wotking only if todate is 09-Jun-2010... ie my equalto operator is not working properly. y is it like that ny help? ...
I am collecting runtime profiling data from PLSQL stored procedures. The data is collected as certain stored procedures execute, but it needs to accumululate across multiple executions of those procedures. To minimize overhead, I'd like to store that profiling data in some PLSQL-accessable Oracle memory-resident storage somewhere for ...
I am trying to cast a number, I think it is a double, to a decimal using the following: CAST(syr_ep AS decimal(10,3)) As EP which is still returning a double, so how do I achieve this? ...
Good day gurus, I have a script that populates tables on a regular basis that crashed and gave the above error. The strange thing is that it has been running for close to 3 months on the production system with no problems and suddenly crashed last week. There has not been any changes on the tables as far as I know. Has anyone encounter...
I have an anonymous pl/sql block with a procedure declared inside of it as well as a cursor. If I declare the procedure before the cursor it fails. Is there a requirement that cursors be declared prior to procedures? What other rules are there for order of declaration in a pl/sql block? This works: DECLARE cursor cur is select 1 fr...
I am using the Oracle Data Provider. I am having trouble calling a function that returns a record type defined as TABLE_NAME%ROWTYPE. How do you do this from C# ...
We have a problem that it seens not supported in oracle 10g what could be the cause? ...
I was trying to recompile PL/SQL package and no avail. because something obtained the lock and that wasn't released for long time. As soon as I kill all sessions I was able to recompile but encounter the same behavior (i.e. locked package) and I wonder what tools are avail to identify what could of obtain it and never release it? This ...
Hi I have problem with calling for my procedure. Oracle scrams PLS-00306 Error: Wrong number of types of arguments in call to procedure. With my type declaration procedure has exact the same declaration like in header below. If I run it as separate procedure it works, when i work in ODCI interface for extensible index creation, i...
We build software using Hudson and Maven. We have C#, java and last, but not least PL/SQL sources (sprocs, packages, DDL, crud) For C# and Java we do unit tests and code analysis, but we don't really know the health of our PL/SQL sources before we actually publish them to the target database. Requirements There are a couple of things ...
Hi folks. In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something in (1,1,2,3,5,8,13,21) for better readability and flexibility of my SQL statements. ...
Does anybody know if it is possible to return an associative array as the result of an Oracle function, if so do you have any examples? I have an Oracle package which contains an associative array declaration as defined below: TYPE EVENTPARAM IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; This is then used in a stored procedure ...
hi, i have a type as follows: CREATE OR REPLACE TYPE tbusiness_inter_item_bag AS OBJECT ( item_id NUMBER, system_event_cd VARCHAR2 (20), CONSTRUCTOR FUNCTION tbusiness_inter_item_bag RETURN SELF AS RESULT ); CREATE OR REPLACE TYPE BODY tbusiness_inter_item_bag AS CONSTRUCTOR FUNCTION tbusiness_inter_item_ba...