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

SQL & PL/SQL :: Improve Procedure Which Is Looping Through Query To Make Inserts

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

SQL & PL/SQL :: Using Minus Operator To Get Changed Data Info Between Two Tables

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

SQL & PL/SQL :: How To Make Procedure From Select Statement

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

SQL & PL/SQL :: How To Make General Procedure Print Cursors

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

PL/SQL :: No Output From Procedure

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

How To Output Parameter To App C# Procedure PL/SQL

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

PL/SQL :: Calling Procedure From Another And Get Combined Output

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

SQL & PL/SQL :: Possible To Make Query With Analytic Function

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

Forms :: How To Make Block Query

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

How To Make Index Use In Query Execution

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

PL/SQL :: Best Way To Send A Procedure / Function Output To The Application

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

SQL & PL/SQL :: Make Query Like Separate List Of Orders

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

Forms :: How To Make Query Mode Work (f11)

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

How To Transfer Output File From One Server To Another Using Stored Procedure

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

SQL & PL/SQL :: Create Stored Procedure With Array As Output Parameter?

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

Reports & Discoverer :: How To Make A Query In Data Model

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

Windows :: Calling Stored Procedure With Input And Output Parameters From Batch File?

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

Groupby Query - Not Getting Output?

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

SQL & PL/SQL :: Combine Query Output In One Row

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

SQL & PL/SQL :: Query To Get Output In Particular Format

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

SQL & PL/SQL :: Can Add Description To Output Of Query

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

Reports & Discoverer :: Create A Procedure In Program Unit And Assign Output To A Field In Report?

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

Sqlplus Query Output Into File?

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

SQL & PL/SQL :: Oracle Query For Days Output

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

SQL & PL/SQL :: Column Renaming From Query Output?

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

SQL & PL/SQL :: Query Output Into Text File

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

SQL & PL/SQL :: Query In Vs Exists - Both Are Giving Different Output?

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

Forms :: To Output Query In Vertical Way

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

SQL & PL/SQL :: Remove Blank Spaces In The Query Output

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







Copyrights 2005-15 www.BigResource.com, All rights reserved