Using Declared Variable Within Select Statement?
Apr 5, 2011
here is what i am trying to do: im as using oracle 8 with sqltool
i have a Very large query. and i notice that many things are repeating. so i want to add them to a variable, instead of re-typing them. for example:
select SomeID from SomeTable;
i want SomeID to be put into a variable. but i still want to be able to get a normal select query at the end so that i can see the returned value:
i tried things like:
declare x number;
begin
set x=45454
select x from SomeTable;
end;
but could not get it to work.
View 2 Replies
ADVERTISEMENT
Jul 5, 2011
I'm trying to write a simple query so I can do some testing on my application. I am trying to do something like this:
SELECT
Location,
LEVEL,
FROM
S_ORG_EXT
where
Location = 'North America' and LEVEL ='Software'
OR location = 'North America'
and Active = 'N'
in the where statement, I have put in the 'Active' that isn't a column. I want to be able to be able to change that in the select part. But I am not able to do so.
this is what I have tried:
SELECT
Location,
LEVEL,
Active = 'N' --I want to change this in the to N or Y so I can get different results.
FROM
S_ORG_EXT
where
Location = 'North America' and LEVEL ='Software'
OR location = 'North America'
and Active = 'N'
View 7 Replies
View Related
Jul 25, 2010
I have a problem that i have hard coded the username.tablename in each select statement of all forms of my application. Now i want to use a dynamic variable in place of username in each select statement throughout the application. The example is:
select * from scott.emp
and i want to write it as:
select * from variable.emp
But at compilation of the form the compiler should know the above variable name.
I have tried to use following select statement but it does not work.
select user into :global.username from user_users
I think perhaps my problem would be solved with Dynamic SQL Statement but i have no experience by using this statement.
View 4 Replies
View Related
Nov 28, 2011
I am facing the same problem: SP2-0552: Bind variable "OLD" not declared. When my script create_trigger.sql is executed,there is no error but when i execute it inside a pl/sql block it get above error...In the trigger we are using if conditions
if(:new.sumthing=1)and (:old.sumthing=0)the
do this..
View 1 Replies
View Related
May 7, 2013
I am trying to create a simple trigger for practice. I am getting SP2-0552: Bind variable "NEW" not declared. error.
Below is the trigger script:
create or replace User_Cxt_Trigger
AFTER
INSERT or UPDATE OR DELETE ON NAME_VALUE_PREFS
referencing new as new old as old
FOR EACH ROW
[code]....
View 17 Replies
View Related
Aug 10, 2011
HOW to use variable P_TMPLID in following statement
TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;
because its throwing error while compiling
and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION
View 2 Replies
View Related
Sep 13, 2013
In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition
ON(source.DNO = target.DNO
AND source.BNO=target.BNO);
I thought that using UNIONALL for select statement of the schemas as below.
SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....
View 5 Replies
View Related
Mar 29, 2013
I have an Type-object typeObj1 that consists another Type-object typeObj2. this def has another Type-object typeObj3. how to access variable declared inside typeObj3. I have syntax below for each Type.
CREATE OR REPLACE TYPE typeObj1
AS OBJECT
(
SYSTEM_IDENTIFER VARCHAR2(50),
PROCESS_TYPE VARCHAR2(50),
abc typeObj2
)
/
[Code]...
/I have tried to access the type-object in where clause in following way
FROM TABLE(CAST(I_typeObj1 AS typeObj1)) ITTPRC,
......
Where
.......
AND (ADDKEY.ADDTN_INFO_KEY_TYP_NM IN (SELECT ADDTN_INFO_KEY_TYP_NM FROM TABLE(ITTPRC.abc)))
AND (ADTINF.ADDTN_RQST_TYP_VAL_DT IN (SELECT ADDTN_RQST_VAL_DT FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
AND (ADTINF.ADDTN_RQST_TYP_VAL_NUM IN (SELECT ADDTN_RQST_VAL_NUM FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
AND (ADTINF.ADDTN_RQST_TYP_VALUE IN (SELECT ADDTN_RQST_VALUE FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
In this way i am able to access the variable inside typeObj3. But problem is i am getting error "ORA-01427 single-row subquery returns more than one row" when i pass more that one typeObj2.
I passed the values like this in proc execution.
T_T_A_I_V := typeObj3('asdasd',NULL,NULL),
typeObj3('String654',NULL,NULL),
typeObj3('abcdef',NULL,NULL));
T_T_A_I_I := typeObj2('CampusCode',T_T_A_I_V),
typeObj2('PlanNumber',T_T_A_I_V);
What i have done is removed typeObj3 from typeObj2, variables defined in typeObj3 are added in typeObj2 then i got ride of above error. is it correct
View 4 Replies
View Related
Jul 4, 2010
i want to select dynamic column names in my select statement in my function.
View 4 Replies
View Related
Jan 11, 2012
I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).
When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:
SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 || ' User '||s1.username || '@' || s1.machine
3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text
||' is blocking the SQL statement on '|| s2.username || '@'
4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> '
6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 ,
7 v$session s2,v$sql sqlt1, v$sql sqlt2
8 WHERE s1.sid =l1.sid
9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
[code]...
From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?
View 10 Replies
View Related
May 26, 2010
am new to oracle based coding and am having a heck of a time trying to figure this out. Basically i need to declare 2 date variables, Begin date and End Date.
I then need an if statement that says if begin date = 0 or is null then set it to the first day of the previous month. and if end date = 0 or is null then set it to the last day of the previous month.
So for today 5/26/2010 i would need
begin date = 4/1/2010
end date = 4/30/2010
I have searched and tried but so far to no avail.
View 10 Replies
View Related
Dec 29, 2010
Why Blind select is better than Conditional select Statement?
View 10 Replies
View Related
Mar 1, 2012
open cp_cursor for 'Select curtailprogramkey from curtailProgram where curtailprogramid like 'l_rec.curtailprogramid%'';
NOTE: l_rec.curtailprogramid is varible. what is wrong in the above statement?
View 2 Replies
View Related
Apr 20, 2012
I have statement like SELECT * FROM DIVISIONS;
i have to run this statement for different table, and I would like to pass the table name as variable
ex:
create or replace precedure dynamic_execute
v_tbl varchar2(30);
begin
---- assign table name here
v_tbl := DIVISIONS;
EXECUTE IMMEDIATE('SELECT * FROM '||v_tbl);
end;
/
When I executed the procedure I got error, how to pass the table name as variable and execute the statement successfully.
View 5 Replies
View Related
May 29, 2012
I want to display Boolean value of a variable in DBMS Print statement. I am able to do it by using simple if condition checking .How to print the value of Boolean variable directly ?
SQL> declare
2 boo boolean := true;
3 begin
4
5 if boo then
6 dbms_output.put_line('boolean variable value TRUE by checking if condition ');
[code]....
ORA-06550: line 9, column 24:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored
View 6 Replies
View Related
Jan 12, 2011
If i inserted the values in table it gets inserting very few rows only.I dont know y it is?
View 15 Replies
View Related
Jan 24, 2012
In a select query, I want to substitute the column name from a variable (In turn from an array of column names). Details are given below. I am getting an error as mentioned below.
Create table Test1(IDNumber(10),
Namevarchar2(100),
Descriptionvarchar2(1000),
Sitevarchar2(100));
Insert Into Test1 values(10,'Thomas','Manager','LA');
Insert Into Test1 values(11,'George','Chairman','LA');
declare
MTest1Test1%RowType;
str1VarChar2(1000):='Name';
MValue1Varchar2(100);
begin
Select * Into MTest1 from Test1 where ID=10;
str1:='Select MTest1.'||str1||' from dual';
execute immediate str1 into mvalue1;
dbms_output.put_line('mvalue1 '||mvalue1);
end;
/
I am getting the error:
ORA-00904: "MTEST1"."NAME": invalid identifier
ORA-06512: at line 9
View 5 Replies
View Related
Jan 27, 2012
In a select query, I want to substitute the column name from a variable (In turn from an array of column names). Details are given below. I am getting an error as mentioned below. sort it out?
Create table Test1(ID Number(10),
Name varchar2(100),
Description varchar2(1000),
Site varchar2(100));
[Code]...
I am getting the error:
ORA-00904: "MTEST1"."NAME": invalid identifier
ORA-06512: at line 9
My objective is to get the name 'Thomas' in the variable - MValue1. I don't want to use the statement MValue1:=MTest1.Name because, moving forward, I want to have the column name to be dynamic, taken from an array of column names.
I need to achieve it by giving only one select query to get entire record and fetch the column values one by one, by substituting the column name from an array of column names, instead of writing that many lines of code / querying that may times to the database.
View 3 Replies
View Related
May 9, 2013
I wrote the following
DECLARE
VISCONNECTED BOOLEAN;
VCONEXAO EXEC_SQL.CONNTYPE;
VARQUIVO_SAIDA TEXT_IO.FILE_TYPE;
VCURSOR EXEC_SQL.CURSTYPE;
VCOLUMNVALUE VARCHAR2(2000);
VSTATUS PLS_INTEGER;
[code].....
But, on line "VSTATUS := EXEC_SQL.EXECUTE(VCURSOR);" i get the error (ORA-01007 - VARIABLE NOT IN SELECT LIST). Whats is wrong?
View 8 Replies
View Related
Jan 30, 2013
I'm new to XML to Oracle data integration. I do have clob column that has the XML data and trying to bring it into the Oracle relational tables, as a part of it we are developing PL/SQL procedure that calls this CLOB column, I want to know if we can variablize the data types ( Just like tablename. column name%type) in the select of the XML data:
select v.instrid, v.endtoendid, v.txid, v.cd, v.ccy,
replace(v.intrbksttlmamt,'.',',') as intrbksttlmamt, v.chrgbr, v.bic1, v.nm1, v.adrline11, v.adrline12, v.ctry1, v.iban1,
v.bic2, v.bic3, v.nm2, v.adrline21, v.adrline22, v.ctry2, v.iban2, v.cdtrref, v.addtlrmtinf
from the_data w,
xmltable(xmlnamespaces(default 'urn:iso:std:iso:20022:tech:xsd:pacs.008.001.01'),
[code]....
I do define the data types after every column name , the problem is incase there is change in datatype I do need to manually edit the procedure to change the datatype is there a way I can variablize them something like tablename.columname%type.
View 3 Replies
View Related
Apr 12, 2011
I'm trying to return the number of records in my link table that contains the excursion_id I pass in by counting them. It doesn't seem to like the select count(*) into my output variable.
create or replace
PROCEDURE BOOK_PASSENGER(
EXCURSION_ID IN excursion_booking.excursion_id%TYPE,
PASSENGER_ID IN excursion_booking.passenger_id%TYPE,
NUMBER_BOOKED OUT NUMBER)
AS
BEGIN
INSERT INTO EXCURSION_BOOKING VALUES(EXCURSION_ID, PASSENGER_ID, NULL);
SELECT COUNT(*) INTO NUMBER_BOOKED FROM EXCURSION_BOOKING WHERE EXCURSION_ID = EXCURSION_ID;
END BOOK_PASSENGER;
View 6 Replies
View Related
Mar 30, 2011
the moment my 11g database is connecting to a php web front end. this following procedure is the one I'm having trouble with.
CREATE OR REPLACE PROCEDURE "BSISSONS"."CREATE_EXCURSION" (
min_places IN excursion.min_places%TYPE,
max_places IN excursion.max_places%TYPE,
additional_charge IN excursion.additional_charge%TYPE,
[code]...
I can select into an output variable to return the value of the primary key of the newly inserted row back into the webpage, but i need to be able to 'select into' a temp variable to insert this value into another table on the same procedure. I get complie errors when i try to 'DECLARE' a variable after the 'AS' keyword
View 2 Replies
View Related
Apr 22, 2010
All I want to do is run a procedure (DO_H_RUN) that returns a number into "v1" and then use that variable to return the result sets in three selects. This is all in TOAD. If I do not have a where clause, the code executes fine (allowing me to see the data in multiple Grid tabs)! But, I want to filter the rows.
I've tried doing a number of different things (finding all sorts of stuff on the Web) like declaring cursor variables and the like but after spinning on this for a couple of hours, I'm stuck.
Here's my attempt (the names have been changed to protect the innocent!):
declare
v1 Numeric;
BEGIN
DO_H_RUN('Me', v1);
--DBMS_OUTPUT.PUT_LINE(v1); --This line works okay!
select * from h_run where h_run_id=v1; --NO GO
[code]....
View 10 Replies
View Related
Mar 22, 2013
simply select and works great:
select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity,gcrs.Area_name, gcrs.sector_name,
substr(gcrs.stream_name,1,case when instr(gcrs.stream_name,' (')=0 then 100 else instr(gcrs.stream_name,' (')-1 end) ISSUE_DIVISION,
case when gcrs.STREAM_NAME like 'NON-GT%' THEN 'NON-GT' ELSE gcrs.STREAM_NAME END as ISSUE_DIVISION_2
from table(f_carat_issues_as_of('31/MAR/2013')) i inner join v_gcrs_with_stream gcrs on i.segment_id = gcrs.segment_id
where UPPER(ISSUE_STATUS) like '%OPEN%'
Now I want to callte two columns:ISSUE_DIVISION and ISSUE_DIVISION_2
if they are equal in new columns should be value 1 if are not equal should be 0,how can I do it ?
View 4 Replies
View Related
May 5, 2010
I have a question about select statements, as I am new to them and don't know how to work all the commands yet.
I'm making a select statement that is about half right... it is shown below:
select t.warehouse_id,
t.quantity_on_hand,
c.product_name
from pahtest3.inventories t
join pahtest3.product_information c using (product_id)
WHERE warehouse_id in (7);
I need to take this select statement and make it so it shows all the products that don't have any quantities in the warehouse in addition to the ones that are already being shown in that select statement.
View 2 Replies
View Related
Jun 15, 2007
decode (a.cancel_time,'',sum ((to_date('23:59','hh24:mi') - a.alarm_time)*24*60), sum((a.cancel_time - a.alarm_time)*24*60)) Duration,
After executing this statement. I get negative values if cancel time is null
I want to subtract alarm_time by 24 hours, if cancel time is null. If not null then cancel-time - alarm_time
View 1 Replies
View Related
Aug 23, 2008
how to set the timing on for all SELECT statements in a procudeure...and i want that should come as a output for very SELECT statement slong with my original output.
View 6 Replies
View Related
Sep 1, 2011
I have a need to use the Alias name of a column within the same select statement( because I can't have another select statement using the first select as table - BO tool limitation).
Ex:
Select dept_id, agency, sum(quantity) as "sum_qty"
where sum_qty > 500;
Currently oracle won't allow using alias name Sum_qty in the same select statement. Is there a way to use alias within the same select statement?
View 12 Replies
View Related
Feb 11, 2011
I have the following tables:
create table lookups (code varchar2(20), amount number);
insert into lookups values ('Rent' , 500);
insert into lookups values ('Breakpoint' , 10);
create table products (id number, cost number, year varchar2(4));
insert into products values (1, 1000, '2011');
insert into products values (1, 2000, '2011');
insert into products values (2, 100, '2011');
insert into products values (3, 50, '2011');
commit;
I want to write a query which lists the IDs and the sum(cost), and a Y/N indicator which is set to 'Y' IF sum(cost) > ( (lookups.rent value) * (100 - lookups.breakpoint value))/100
I have written this query:
SELECT id,
sum(cost)cost,
year,
CASE
WHEN cost >
((SELECT amount
[code]....... ORDER BY id;
This returns
ID COST YEAR YN
--------- ---------- ---- -
1 1000 2011 Y
1 2000 2011 Y
2 100 2011 N
3 50 2011 N
The YN is correct, but it needs to sum the amounts. So there should only be one row for id1 = 3000.e.g.
ID COST YEAR YN
--------- ---------- ---- -
1 3000 2011 Y
2 100 2011 N
3 50 2011 N
I am not sure how to do this. Or is there a better way of doing this than using CASE.
View 4 Replies
View Related
May 6, 2011
Below is the query that I have:
Quote:
select a.contact, b.db_name
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b,
where a.DB_ID=b.DB_ID
and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%');
How can I add two more columns into the select statement and the two columns are in the tables that were in the sub query..I would like to have something like this:
Quote:
select a.contact, b.db_name, COLUM1, COLUMN2
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b, mgmt_targets t, mgmt_current_severity s
where a.DB_ID=b.DB_ID
and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%');
View 5 Replies
View Related