This probably isn't as complicated as it should be, but Business Objects seems to be very, very strict in how types are used versus SQL Developer. Here's the piece of the statement in question: ship_date between '01' || '-' || w_current_ora_month || '-' || to_char(to_date(w_last_day, 'DD-MON-RR HH:MI:SS AM'), 'yy') and to_char(to_date(w...
Hi, Is there a way to do this?. I found adding, DBMS_LOCK.sleep() to the beginning of the trigger code by googling, but it blocks the insert itself from happening. I would like to insert the data but the trigger should be fired only after an arbitrary delay. Thanks. ...
How can you remove non-ASCII values from a table in Oracle with PL/SQL? ...
ORDER BY CASE WHEN v_SORT_TYPE = 'ASC' THEN CASE WHEN v_SORT_ORDER = 'lname' THEN CAST(lname AS VARCHAR2(45)) || ',' || ROWNUM WHEN v_SORT_ORDER = 'code' THEN CAST(code AS VARCHAR2(52)) || ',' || ROWNUM END ASC WHEN v_SORT_TYPE = 'DSC' THEN CASE WHEN v_SORT_ORDER = 'lname' ...
How to find all table references from Oracle 10G PL/SQL functions and procedures? I definitely can execute the following SQL statement: select * from dba_source where text like '%tbl_c%' but I wonder how to find all functions that call functions that refer to table used. For example I can have a function A that calls function B that...
I have a table called Book. This table has 3 columns viz id, price and discount. If price is greater than 200 then discount should be 20%. While inserting data in Book table the discount value should be updated based on price value. How can this be handled when data is inserted or updated in Book table? Please provide all possible solu...
Possible Duplicate: A good reference for Oracle PL/SQL I need make a procedure to fill some tables in oracle, i'm new in oracle, i need receive two ids, with that make a couple of loops, iteratos, or something like that, and then make a few of sql, the procedure will be called into a trigger, but i can't find a good and comple...
Hi All,I got a problem with oracle database ,i created a stored procedure and i wanted to pass an array of items' ids to this procedure to select the data according to array of items using "in" clause,the available solution to this as i found was to create a function and pass a string value with all item's ids seperated by a comma ,and t...
Heyho, I need to grab some datas from actions which been done from date A 00:00:00 and date B 00:00:00 (in this case Date A: 16.07.2010 Date B: 20.07.2010) so i wrote this select-statement: Select avg(cnt),fext from ( Select to_char(mytable.dateadded, 'DD.MM.YYYY') dateadded, fext, count(id...
Hello, Tiredness prevents me from finding this one... Say you have the following tables: Parent PARENT_ID (LONG) Child CHILD_ID (LONG) PARENT_ID (LONG, FK) HAS_GRADUATED (BOOLEAN) I want a query to return the following true (1, in the case of Oracle) if the parent has at least one child that has graduated, and false (0, in the ...
Recently, I encountered a BEFORE INSERT OR UPDATE trigger on a table. In this trigger, the author relies on the INSERTING and UPDATING functions (both return a BOOLEAN) of the DBMS_STANDARD package to determine if the trigger was fired before an insert or before an update. For example: CREATE OR REPLACE TRIGGER CUSTOMER_TRIGGER BEFOR...
I have a query like this: SELECT X.Y( 'p1', 'p2', 'p3', 'p4', '100') from dual I know what dual is but this part X.Y( 'p1', 'p2', 'p3', 'p4', '100') really makes me wonder. What does this syntaxis mean? Are X and Y tables or what? ...
Im new to cursors in Oracle. I have a piece of SQL that is contained in a variable. I want to open a cursor with this sql. How do I do this? Seems simple but all the examples I find just have the sql typed directly below the "open cursor_name for" statement. Here is what I would like to run (assume I have variable v_sql with my sql quer...
Heyho, I've gotta write a Procedure which Inserts a resultset from a select-statement into a table. A co-worker of mine did something similar before to copy values from one table to another. His statement looks like this: CREATE OR REPLACE PROCEDURE Co-Worker( pId IN INT ) AS BEGIN INSERT INTO Table1_PROCESSED SELECT * FROM...
Heyho, I'm trying to set a Procedure like: Create or Replace Procedure MyProcedure1( x in number default 1, y in number default 1 ) AS Begin If x = 1 then MyProcedure2; EndIf; If y = 1 then MyProcedure3 EndIf; End MyProcedure1; MyProcedure3 must be done after MyProcedure2 is completely done, because it needs some values which are set...
I am trying to sum INTERVAL. E.g. SELECT SUM(TIMESTAMP1 - TIMESTAMP2) FROM DUAL Is it possible to write a query that would work both on Oracle and SQL Server? If so, how? Edit: changed DATE to INTERVAL ...
This is a particular problem that I have come across many times, but I have never really found a simple solution to this (seemingly) simple problem. How to you ensure that a given parent has a fixed number of children? 1) Example. How do you make sure a given class has only , say, 50 students enrolled..? create table class( class_...
I need to write a query which takes rows and converts it into columns - here's my table: Count fname lname id ----------------------------- 1 abc def 20 2 pqr 20 3 abc xyz 20 4 xyz xyz 20 1 abc def 21 1 pqr xyz 22 2 abc abc...
Hi, Does anybody know if there is a way to find what the length of the longest row in a column in Oracle? Basically I need to get the length of the longest row and then use that length plus 1 with SUBSTR to make the output of the column one character longer than the longest string. Thanks EDIT: Thanks for the advice. However, the MA...
Hi, to execute an .sql script file from ANT it works fine using the following task: <sql classpath="${oracle.jar}" driver="oracle.jdbc.OracleDriver" url="jdbc:oracle:thin:@@@{db.hostname}:@{db.port}:@{db.sid}" userid="@{db.user}" password="@{db.password}" src="@{db.sql.script}" /> But if the .sql file not only c...