Tony Andrews in another question gave an example of: IF p_c_courtesies_cd || p_c_language_cd || v_c_name || v_c_firstname || v_c_function || p_c_phone || p_c_mobile p_c_fax || v_c_email is not null THEN -- Do something END IF; as a clever (if not a tad obscure) alternative to the Oracle COALESCE functi...
I have a series of Oracle databases that need to access each other's data. The most efficient way to do this is to use database links - setting up a few database links I can get data from A to B with the minimum of fuss. The problem for me is that you end up with a tightly-coupled design and if one database goes down it can bring the cou...
I need to pass an array of integers from Hibernate to PL/SQL function. The current solution is the covert the array to a comma separated string and surround it with parenthesis to use it as parameter. This is solution is outlined here. But, this approach doesn't look like a good solution when an array of 200k elements need to be passed ...
Hi, I'm working on an application that uses mod_plsql with Oracle 10G to generate web pages via PL/SQL stored procedures called directly from the web browser. I'm looking for a way to only accept parameters via POST requests, and not GET requests. Ie, in PHP I would only want the value of $_POST['parameter_name'] and not $_GET['paramete...
I have SQL SELECT statement that returns: Error: ORA-00600: internal error code, arguments: [qerpfAllocateR], [], [], [], [], [], [], [] If I narrow my results by adding one more condition in WHERE clause everything is ok. Anyone knows what is happening? EDIT: select * from ( select tbl1.col1, ..., tbl1.points from tabl...
If I have the following code being called from multiple threads in an application, is there a deadlock risk? The transaction used to connect to the database for this is opened just before this call and closed once it returns. Application : Java Database: Oracle FUNCTION reserveWork(in_batch_id NUMBER, in_work_s...
This is a pretty basic question, but here goes: I have an array of VARCHAR2 strings in PL/SQL. How can I print every element in that array? ...
Hello. Does anyone knows a good formatter for PL/SQL free and offline? Thank you very much. ...
I am encountering an issue where Oracle is very slow when I attempt to delete rows from a table which contains two CLOB fields. The table has millions of rows, no constraints, and the deletes are based on the Primary Key. I have rebuilt indexes and recomputed statistics, to no avail. What can I do to improve the performance of delete...
I have a query that I am generating the XML from in Oracle using the DBMS_XMLGEN package. As an example, I am using a cursor as follows: SELECT A.NAME primaryName, (CURSOR(SELECT B.NAME AS NAME FROM B WHERE B.ID=A.ID)) SYNONYMS FROM A I have a query context, qtx, and set the query to the above for that context. Calling: res...
In MySQL, I can create an access a session variable by using a single @. Example initialization: set @myVar = true; Some trigger containing this code: if (@myVar is not true) then execute something What is the equivalent in Oracle 10g? ...
I'm trying to return sequence.nextval to my program from a proc stored in a package. I'm pretty green when it comes to PL/SQL and I'm kind of at a loss as to what is happening. The error that is actually being return is PLS-00306: Wrong number or types of arguments in call to PROCGET_BOOKMARKID line 1, column 7 statement ignored. He...
Are there any static code analysis tools for stored procedures written particularly in PL/SQL and T-SQL? ...
Hi! I'm trying to log all the errors in my database into a table. So as user sys i wrote the following code: CREATE TABLE servererror_log ( error_datetime TIMESTAMP, error_user VARCHAR2(30), db_name VARCHAR2(9), error_stack VARCHAR2(2000), captured_sql VARCHAR2(1000)); / CREATE OR REPLACE TRIGGER...
DISCLAIMER: Let's just say I have a pet project to satisfy my insane desire to write a decent Oracle client that isn't based on Java, while appeasing my coding hobby. END OF DISCLAIMER What I'd like to be able to do is retrieve the schema information for subprograms, functions, package specifications and package bodies from an Oracle 9i...
declare begin for i in (select * from emp) loop if i.sal=1300 then update emp set sal=13000; end if; end loop; end; This code is updating all the records with salary 13000. Instead i want to update records having salary 1300 to the value 13000. Can you tell where I made a mistake? I am accesing records using i...
Hello, I have a view in one of my Oracle Database that is taking too long to execute. When the statement runs, it does not seem to stop. Is there anyway that we can verify the performance of this view or how we can check to see if the statement session is 'hanging'? Thanks, N2EE UPDATE I realised that the issue is with the underlyin...
Can someone please explain what the "partition by" keyword does and give a simple example of it in action, as well as why one would want to use it? I have a SQL query written by someone else and I'm trying to figure out what it does. An example of partition by: SELECT empno, deptno, COUNT(*) OVER (PARTITION BY deptno) DEPT_COUNT FROM...
I'm writing a function which will eventually use one of several possible cursors, for simplicity sake I'll use two here. In the body of the function I want to be able to use a single variable to describe whichever cursor gets used. Do I use a reference cursor for this or something else? In the example below, I want the FOR LOOP to use e...
I thought that the quotation mark (") was simply a type of grouping marker but I'm debugging some NHibernate code and notice that while SELECT * FROM site WHERE site_id = 3; Works fine SELECT * FROM "site" WHERE site_id = 3; fails with a table or view does not exist error. What gives? ...