I need to write a report that generates summary totals against a table with date ranges for each record. table data: option start_date end_date opt1 6/12/2009 6/19/2009 opt1 6/3/2009 6/13/2009 opt2 6/5/2009 6/6/2009 What I want out is basically this: date option count 6/1/2009 opt1 0 6/1/200...
Hi there, I want to write a map-like object type in PL/SQL. What I mean is a key-value-pair list, where a value can be another key-value-pair list. Simple, or so I thought. Here are the two simplified CREATE OR REPLACE TYPE TKey AS OBJECT ( name varchar2(240), value_text varchar2(2000), value_map TMap ) CREATE OR REPLACE T...
Is there a way to easily/elegantly update multiple columns in a table with record values from a query in Oracle 10g? I have a table (call it Source) which has for simplicities sake, 2 columns, ID and KEY. ID Key ---- ---- 1 1000 2 1000 3 5000 4 1000 .. 101 8000 102 9000 103 7000 104 ...
I need to use dynamic SQL execution on oracle where I do not know the exact number of bind variables used in the SQL before runtime. Is there a way to use a variable number of bind-arguments in the call to EXECUTE IMMEDIATE somehow? More specifically, I need to pass one parameter into the unknown SQL but I do not know how often it will...
I have a script that uses one VARRAY multiple times. But, I can't seem to figure out how to reset the VARRAY after looping through it once. I wrote the following basic script to help me troubleshoot: DECLARE TYPE multi_show_id_type IS VARRAY (60) OF VARCHAR2 (10); multi_show_id multi_show_id_type := multi_show_id_type (); coun...
Is it possible to determine where Oracle is installed using pure PL/SQL? Use case: In order to call an external C library, I need to know its exact path (for the create library call). My DLL will reside in Oracle's BIN directory, but I can't hard-code the path of the DB installation in my scripts... ...
While writing the SQL procedure for creating a CSV file from the contents of the table, I am stuck at the error below: ERROR at line 1: ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 488 ORA-29283: invalid file operation ORA-06512: at "DSI_DPIPE.BUSSHOURS", line 28 ORA-06512: at "DSI_DPIPE.BUSSHOURS", line 55 ORA-0...
I want to know about SQL and PL/SQL's drawbacks. ...
I am writing a procedure to create a CSV file with the data in an Oracle table. I used "spool filename;" but an error is coming. Can I use spool in PL/SQL? ...
Hello all, I'm trying to fill in for a colleague in doing some Oracle work, and ran into a snag. In attempting to write a script to modify a column to nullable, I ran into the lovely ORA-01451 error: ORA-01451: column to be modified to NULL cannot be modified to NULL This is happening because the column is already NULL. We have sev...
I've got the following function that I wish to call: CREATE OR REPLACE PACKAGE utils AS TYPE item_list IS TABLE of items.item_id%TYPE; FUNCTION getParentsForItem(p_item_id IN items.items_id%TYPE) RETURN item_list; END utils; But I'm unsure of how to bind a java Collection to the return type of getParentsForItem. ...
In light of the "Hidden features of..." series of questions, what little-known features of PL/SQL have become useful to you? Edit: Features specific to PL/SQL are preferred over features of Oracle's SQL syntax. However, because PL/SQL can use most of Oracle's SQL constructs, they may be included if they make programming in PL/SQL easier...
The PL/SQl cursor was getting hanged due to some reason, this makes my app to hang when try to loop through the ResultSet.Is there a way to handle this, like timing out the resultset if the database hangs? ...
I have a PL/SQL package in an Oracle 10g database and I want to write a function that returns the name of the schema that the package (and hence the function) is defined in. Anyone know how to do this? ...
As a part of my daily operations i need to run a procedure, get the output in a notepad and copy some part in mail and send it to business... If I can shoot a mail from d/b, then it would save some good time. Is it really possible? If yes can anyone share some sample codes. many Thanks in advance! ...
I'd like to look up the location of a specific character in a varchar variable. ...
What is the difference between SQL, PL-SQL and T-SQL? Can anyone explain what the difference between these three are and a scenario where each would be relevantly used? ...
It's probably just the vocabulary I am missing to find out how to do this: A job scheduled to run regularly every 5 mins, however keeping track that there are never two instances of it running at the same time, i.e. the next instance would be postponed or skipped if the prior runs longs than 5 mins. What is the easiest/most elegant way...
I have a rather simple PL/SQL query (...) DECODE(upper(Table.Column), 'SOMETHING', '---', TABLE2.COLUMN2) as VALUE (...) How can I apply the WHERE clause using above statement, because when I use something like: WHERE upper(VALUE) like 'SOMETHING' it gives me an empty recordset (and I am absolutely positive that there are records w...
Hi everybody, I am currently tracing a performance leak in a stored procedure. Having a timestamp put out right after the initial "begin" and one right before the final "end" (I am doing a commit before) says the procedure takes abt. 10 secs to finish. However, I have to wait 2mins+ for it to end. Can anybody tell me where the rest of ...