PL/SQL :: Procedure To Make Minus Query As Output For 2 Tables
Nov 30, 2012
create a procedure so that I could get minus query of 2 table as a result.
I have two table 1- src_table_list ,2- tgt_table_list both tables have 2 columns : serial_no,table_name and 100 records each. and details mentioned in column "table_name" are actually tables name which present in my testing database.
so I need one procedure which will pick one table_name from src_table_list and one table_name from tgt_table_name each time recursively and provide minus query as a result. as below.
select c1,c2,c3,c4 from table1 --(fetched from src_table_list)
minus
select b1,b2,b3,b4 from table2 --(fetched from tgt_table_list)
create the procedure..as I have to prepare minus query for more than 200 tables and then I need to test them for integration testing..
View 11 Replies
ADVERTISEMENT
Oct 10, 2013
I am trying to improve a procedure which is looping through a query to make inserts.
FOR P IN (
SELECT O.TYPEID
,o.KEY
,O.ID
,O.NAME
,O.LGNUM
,O.LGNAME
[code]....
View 12 Replies
View Related
Feb 14, 2013
I am using the below query to show the difference of data between two tables using minus operator.
teh result is correct, but is there a way can it show with a flag with new rows and updated rows something like "N" for new row and "U" for updated row.
select CURRENT_STG_GLACCTS.TABLE_NAME,
CURRENT_STG_GLACCTS.ACTIVE,
CURRENT_STG_GLACCTS.BUSINESSUNITID,
CURRENT_STG_GLACCTS.COST_CENTER,
CURRENT_STG_GLACCTS.GLACCT,
[code].......
View 7 Replies
View Related
Feb 8, 2011
i do have below query in oracle db just joining two views,
select docview.firstname as "First Name", docview.lastname as "Last Name", docview.mrn as "MRN", docview.physician as "Physician", docview.surgicalcong "Surgical Consent Missing", docview.admissionnotemissing "Admin Assessment Missing",
(case (docview.doctype) when 135 then 'Doctor Chart Checklist' else 'DS Chart Checklist' end) as doctypename,
docview.hpmissing "History & Physical Missing", docview.nursingassessment "Nursing Admission/Assessment",
[Code] ........
how can i make a procedure with below two steps,
1) load the result of above query to some temp table / or even a fixed table (which ever easier)
2) list all data from the temp table / fixed table created in first step
so in the final expecting result is
execute myprocnamehere -- will result same as running above query
View 3 Replies
View Related
May 27, 2011
how to print cursors? I mean like columnname : value. of couse curosr.columnname gives what I want. but I want genrally used way.
CREATE OR REPLACE PROCEDURE PROC_TEMP
IS
BEGIN
FOR C1 IN ( SELECT sbjct_id,sbjct_step
FROM RND.SBJCT_ORDR M) LOOP
DBMS_OUTPUT.PUT_LINE('c1 : '||c1);
END LOOP;
END;
View 3 Replies
View Related
Dec 4, 2012
When I run the below procedure it does not return any data. But it returns stating Procedure created. When I just run the query it returns the result.
So what am I going wrong here.
set serveroutput on
CREATE OR REPLACE PROCEDURE PROD.STATUS_COUNTS
(
p_count IN OUT number,
p_status IN OUT varchar2,
p_mpp IN OUT number
)
AS
BEGIN
[Code]....
View 7 Replies
View Related
Sep 17, 2013
this PROCEDURE for Paging.
PROCEDURE cursor_example
IS
p_id NUMBER;
p_status number;
p_rownum number;
[code]...
View 1 Replies
View Related
Jun 3, 2013
I have created two Stord procedures, which does have same input type of input parameter but different output parameters. I want to call these two procedures from a single call and get the combined output. Below is the code I have tried:
CREATE OR REPLACE PROCEDURE sp_all_attribute_difference
(
i_product_id_new IN NUMBER,
o_product_attr_diff_list OUT product_attr_diff_list,
o_usage_attr_list OUT usage_attr_diff_list
)
IS
[code]....
I am not able to compile tis proc.
View 5 Replies
View Related
Nov 4, 2011
Is it possible to make query with analytic function or somehow to represent output data for next example (sql: ???):
Table: Order
sql: select * from order
Sql output>
Item Barcode Qty
---- ------- ---
1 100100 2
2 100200 1
3 100300 3
Table: Order
sql: ???
Sql output>
Item Barcode Qty ElementarQty
---- ------- --- ---
1 100100 2 1
1 100100 2 1
2 100200 1 1
3 100300 3 1
3 100300 3 1
3 100300 3 1
View 2 Replies
View Related
Jan 30, 2012
I have one form, when i press f7 , it says the "The function has no meaning in this context" , how to make it query enabled block.Actually i am going to that block using when button pressed and execute_query.
View 12 Replies
View Related
Jun 6, 2013
I have the below query for which ename column has an index. As of my knowledge below queries 1st and 2st will not use index. Hence i used the 3rd statement and that too its not using the index. Finally i used the 4th query, but even the 4th query is not using the index. Then how do i make this query to use my index??? Do i need to create a function based index for this?
1. select * from emp where ename !='BH' ;
2. select * from emp where ename <> 'BH';
3. select * from emp where ename not in ('BH');
4. select * from emp where ename < 'BH' or ename > 'BH';
View 4 Replies
View Related
Sep 8, 2013
What is the best way to send the output of a PL / SQL function / procedure to the application in Oracle 11g. I understand that it could be different for applications built in JAVA , .Net , SAP etc.
View 49 Replies
View Related
Mar 1, 2010
I have table called Orders with lots of fields from Imports Fields are ConsigneeKey,Shipto,ProductCode. and another table called Product. Main fields are ProductCode,Price,Weight,StdCube. Now I have two list of Consineekey,One which is shipto X area and another is of Y area. I want to make query like separate list of Orders which is shipto X,Y and Common Products in Both X and Y. I will supply list of Consigneekey in where clause.
View 8 Replies
View Related
Apr 2, 2012
I have created a custom form. I have field called Order No. When i query on Order no.. i should be able to pick order no as well as other fields related to it. how do i acheive this functionality. I mean the f11 and ctrl f11 querying functionality.
View 18 Replies
View Related
Apr 9, 2012
We are running a store procedure by calling it from shell script.The shell script is running in Server1 and the stored procedure is running in server2(which is the data base server.).
Functionality of the stored procedure:Generating a tab delimited file and place in a directory.
Requirement: The requirement is that the store should be modified in such a way that it should drop the output file to Server1 itself(The server from where it is getting called).
Attaching the store procedure for reference. mIn the store procedure,we have a variable "lv_file_dir" which currently refers to the directory location in Databse server(Server 2) We have to change this SP such that it drops the generated file in Server1 itself(From where the shell script is running).
Attached File(s)
Procedure.txt ( 3.28K )
Number of downloads: 4
View 2 Replies
View Related
Jul 14, 2011
how to create stored procedure with an array as an output parameter.I mean when we need to return multiple rows...
View 7 Replies
View Related
Jul 6, 2013
I have one report consist of two user parameters like FROMDATAE and TODATE and two queries in data model..
The 1st query is..
SELECT WONO,MCV_DATE,QTY FROM MCSHOP1 WHERE MCV_DATE BETWEEN :FROMDATE AND :TODATE;
It created two user parameters.ie FROMDATE and TODATE.
And 2nd query like this
SELECT MCVN FROM MCSHOP1 WHERE WONO=:WONO OR WONO LIKE 'RW%'||:WONO;
I dont know how to make 2nd query in data model. becoz the WONO will come from 1st query and LIKE command is there..But I tried in formula Column....but it returns more than one row...
View 4 Replies
View Related
Oct 4, 2011
when i am calling stored procedure with input and output parameters from batch file .. I am getting the following message ..
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 4 11:48:51 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 14
code which i have written ...
DEClARE
RETCODE NUMBER;
RETMSG VARCHAR2(200);
EXEC SP_SELCT_EMPLOYEE_DATA(277080,'EMPNAME ','EMAIL@EMAIL.COM', 9028045686,111333,25000,'PUNE',35,RETCODE ,RETMSG );
EXIT
Procedure Name :
PROCEDURE SP_SELCT_EMPLOYEE_DATA (
-- A_EMPLOYEE_ID IN VARCHAR2,
--A_JOB_ID IN EMPLOYEES.JOB_ID%TYPE,
P_EMPLOYEE_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE,
P_EMPLOYEE_NAME IN EMPLOYEES.EMPLOYEE_NAME%TYPE,
P_EMAIL IN EMPLOYEES.EMAIL%TYPE,
[code]....
View 2 Replies
View Related
Jun 25, 2013
I have a table with data as follows
Source name address city
File Y N N
File N N Y
DB Y N Y
DB N Y N
XML Y Y N
I am trying to get output as follows
Source Y/N CountName CountAddress
File Y 1 0
File N 1 2
DB Y 1 1
DB N 1 1
XML Y 1 1
XML N 0 0
View 2 Replies
View Related
Aug 13, 2010
If I have below two sql statment.
select count(*) from table_a;
select count(*) from table_b;
How can I output it in one row together like below
Table_a | Table_b|
10 | 20|
give me detailed example ....
View 2 Replies
View Related
Mar 8, 2010
I have attached an SQL script.I would like to have the output in the fashion given below -
PERIOD_START_DATEPERIOD_END_DATEEMPLOYEE_NUMBERFULL_NAMELoan AmountLoan TypePay Value Outstanding Amount
1-Jul-0931-Jul-0924200Jonathan48000048004000
1-Jul-0931-Jul-0924200Jonathan32600022702990
the one in bold and underlined is the Column heading.
View 5 Replies
View Related
May 10, 2011
i have created a table called table2,with only one column as text,
SQL> select regexp_replace(text,'[:]',chr(10)) text from table2;
TEXT
--------------------------------------------------------------------------------
1
amar
11-jan-2011
15000
2
manju
22-feb-2011
20000
and i have to get output like this,
TEXT
--------------------------------------------------------------------------------
ID
1
NAME
amar
DATEOFJOINING
11-jan-2011
SALARY
150000
is it possible to get output like above?
View 20 Replies
View Related
May 8, 2013
i just want to know how can i create a procedure in the program unit and assign the output to a field in the report.
View 7 Replies
View Related
Feb 4, 2004
How can I put an Oracle sqlplus query output into file?
SQL> select myfield from userDB where IMSI like '22803%' and rownum < 11;
I would like to put the output in a file, say '/tmp/mydata.dat'
View 6 Replies
View Related
Aug 5, 2010
I want to pull out the output only when Day is 1 and 2 and 3. I don't want if either Day is 1 or Day is 2 or Day is 3 is present.
Test Case
--Creat Table
create table dummy_name
(name varchar2(5) not null,
day number(3) not null);
--Insert Values
insert into dummy_name values ('A', 1);
insert into dummy_name values ('B', 2);
insert into dummy_name values ('C', 3);
insert into dummy_name values ('B', 1);
insert into dummy_name values ('B', 2);
insert into dummy_name values ('C', 1);
[code]...
View 8 Replies
View Related
Jul 14, 2010
I want to be able to name a column created from my query.
Query is:
select A.OrigRef, A.DisplayName, A.ExtCode, count(CalcId)
from OrigRefView A, CalcView B where A.OrigRef = B.NewRef and A.OrigRef like 'AB%'
group by A.OrigRef, A.DisplayName, A.ExtCode
order by A.SusRef
and it returns the Count in "column4" Is there a way I can get the query to output a different column name without creating a whole new table (i.e. not by creating a new table for my query output and then running a new procedure at the end to rename the column...)?
i.e. I want "column4" to read "CountofCalId"
View 4 Replies
View Related
Mar 9, 2011
I am trying to get query output into textfile.The following procedure is working fine by creating directory as follows in sys user.The output is getting onto text file and it seen on server machine even.My question is I want to see the text file on local machine also instead of everytime connecting to server machine drive.How can I perform that?
create or replace directory INFO_DIR as 'D:Swapna';
grant read, write on directory INFO_DIR to fairpoint;
CREATE OR REPLACE PROCEDURE FAIRPOINT.utl_file_test_write_xls(filename in VARCHAR2 )
IS
output_fileutl_file.file_type;
v_pathVARCHAR2(500);
v_stringVARCHAR2(4000);
v_sqlstrVARCHAR2(2000);
BEGIN
BEGIN
[code]....
View 5 Replies
View Related
Feb 3, 2012
SQL> select count(*) from emp where empno not in ( select mgr from emp );
COUNT(*)
----------
0
SQL> select count(*) from emp T1
2 where not exists ( select null from emp T2 where t2.mgr = t1.empno );
COUNT(*)
----------
9
I fired above query and I totally confused that why both are giving different output.
View 6 Replies
View Related
Dec 27, 2011
i have output query in 1 row, for example
Id Name Salary
100 John 2000
i need to output the same query in vertical way like:
100
John
2000
View 3 Replies
View Related
May 6, 2010
I write a Query to get Deptno wise Employee Names as fallows..
SQL> select max(decode(d.deptno,10,e.ename)) dept10,
2 max(decode(d.deptno,20,e.ename)) dept20,
3 max(decode(d.deptno,30,e.ename)) dept30
4 from emp e,
5 dept d
6 where e.deptno = d.deptno
[code]....
But i don't want to include the blank(null) spaces in the query output.
The Required output should be like this..
DEPT10 DEPT20 DEPT30
------ ------ ------
CLARKADAMSALLEN
KINGFORDBLAKE
MILLERJONESJAMES
SCOTTMARTIN
SMITH TURNER
WARD
View 5 Replies
View Related