How can I optimize this query if given the following query returns either all entries in the table or entries that match only up to current date ? btw: The Query is targeted to a Oracle Linked Server on MS Sql 2005 as an Inline function.. Do not want this to be a table value function.. ALTER function [dbo].[ftsls031nnnHades](@withExpir...
BEGIN dbms_output.put_line('Welcome to PL/SQL'); END; / I have this code in sample.sql file. How to run sample.sql? ...
Hi Folks! Greetings to all! I want to create a pl/sql query by declaring variables for the following eg: :stay_id = (SELECT Stay_Id from MVStay where StayNumber = 'xxxx' AND StayState = 2); -- get passage linked to the stay and is 'discharged' :passage_id = (SELECT Passage_Id from MVStayWorkflow where Stay_Id = :stay_id and WorkflowActi...
using pl/sql how do I open a directory? ...
Using pl/sql how do I locate a file in a directory and move the file? ...
Possible Duplicate: Using pl/sql how do I locate a file in a directory and move the file? How do I locate a file in a directory using PL/SQL? ...
i have a PL/SQL procedure using MERGE : MERGE INTO table_dest d USING (SELECT * FROM my_Table) s ON (s.id = d.id) when matched then UPDATE set d.col1 = s.col1 when not matched then INSERT (id, col1) values (s.id, s.col1); now lets say the query s returns mutiple rows with same id wich will returns an ORA-00001: unique const...
---------- User ---------- user_ID(pk) UserEmail ---------- Project_Account ---------- actno actname projno projname ProjEmpID ProjEmpMGRID Where ProjEmpID,ProjEmpMGRID is the user_id and ProjEmpMGRID can be null. I need to look up the useremail and display the table project_account. I need to query with actNo which has duplicate valu...
Hello, am a newbie to Oracle/PL SQL.I've 2 tables A and B. A has a column CustId,Age,Location and Date. Table B has 2 columns CustId,CustName. What would be the sql query to show show CustName and Location for a given age? Thanks. ...
Hi Guys! I'm thinking of a way on how to benchmark an Oracle procedure. My idea is this, put a logging function on each part of the procedure you wish to benchmark. Store the time it entered that part. Then, create another app (using PHP) to compute the difference between each execution. The PHP app is needed to make it as layman as po...
I have a dynamic PL/SQL that will construct the SELECT statement based on what the searching criteria input from the users,likes: l_sql := 'SELECT * INTO FROM TABLEA WHERE 1=1 '; IF in_param1 IS NOT NULL THEN l_sql := l_sql || 'AND column1 = in_param1 '; END IF; IF in_param2 IS NOT NULL THEN l...
VARIABLE StayWorkflow_Id_max number; BEGIN SELECT max(StayWorkflow_Id)+1 into :StayWorkflow_Id_max from MVStayWorkflow; END; / insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id, User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState, PreviousState, WorkflowTime, UserStamp, TimeStamp) ...
Hi all; my table; Date | Cost 01.01.2010 | 100 02.01.2010 | 200 03.01.2010 | 300 04.01.2010 | 400 10.01.2010 | 800 11.01.2010 | 800 12.01.2010 | 800 25.01.2010 | 500 26.01.2010 | 500 05.02.2010 | 600 13.02.2010 | 700 15.02.2010 | 700 ı want to make "date between '01.01.2010' and '28.02.2010' " weekly vi...
the problem is this : I implemented a trigger on the table called CLAN_AFFILIATI that increases (if inseriemento) and decreases (in case of cancellation) an attribute (NUMAFFILIATI) of another table called CLAN. what I would do is block the update NUMAFFILIATI of Clan by the user and had thought to r another trigge on CLAN that did this:...
I'm looking for an Oracle Regular Expression that will match tokens in any order. For example, say I'm looking for "one two". I would want it to match both, "one token two" "two other one" The number of tokens might grow larger than two, so generating the permutations for the regex would be a hassel. Is there an easier way to do this...
Hi guys, I am here to get records based on categories. My table foo has fields [id, name, class]. my records can be like: 1, ram, 10 2, hari, 9 3, sita, 10 4, gita, 9 5, rita, 5 6, tina, 7 8, nita, 8 9, bita, 5 10,seta, 7 ...and more... Now i would like to get result with each record from different class.. i.e something like 1, r...
Each row in a cursor should be joined with another table and the whole result should be returned a one cursor Say cursor1 return 5 rows. Each of these 5 rows should be joined with table1. and the final result for all the 5 rows should be returned a 1 row. Plz help ...
CREATE TABLE DIALOGUE_TABLE(EXPIRE_TIME TIMESTAMP); Following code snippet is inside stored proc :- PO_EXPIRETIME :- OUT PARAM of procedure a varchar2 SELECT TO_CHAR(SYS_EXTRACT_UTC(EXPIRE_TIME)) INTO PO_EXPIRETIME FROM DIALOGUE_TABLE; When I run Stored Proc from server using EXEC and print PO_EXPIRETIME time stamp is pr...
As said in the title, I want to select the number of events per team and per year. The select statement below works fine but doesn't give me exactly what I am looking for. SELECT Team.team_id, TO_CHAR(Event.START_DATE_TIME, 'yyyy') AS year, count(event_id) AS events FROM Team LEFT OUTER JOIN Event ON Event.team_id = Team.team_id...
When I am executing the package I get an error message: error in the emp_test ORA-06530: Reference to uninitialized composite Can u explain how I can initialized an object type in a package? CREATE OR REPLACE TYPE emp_obj AS OBJECT ( emp_no number, salary number, job varchar2(20) ); CREATE OR REPLACE PACKAGE BODY e...