SQL & PL/SQL :: Queries Returning Same Result With Different Access Path To Table?

Feb 7, 2012

Following are 2 queries, which return same results as far as the input parameter is NOT NULL

select /*+ gather_plan_statistics */ * from PX_CJQ where decode(:x,null,1,object_id) = NVL(:x,object_id);
select /*+ gather_plan_statistics */ * from PX_CJQ where object_id = NVL(:x,object_id);

However the execution plan differs a lot The FTS cost or rows accessed count also varies what could be the reason? The PX_CJQ is simply select * from dba_objects and has index on object_id which anyway is not being used in this case

variable x number
exec :x:= 28
Query - 1
***************
***************
SQL> >select /*+ gather_plan_statistics */ * from PX_CJQ where decode(:x,null,1,object_id) = NVL(:x,object_id);
SQL> >select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

[code]...

Query - 2
***************
***************
SQL> >select /*+ gather_plan_statistics */ * from PX_CJQ where object_id = NVL(:x,object_id);
SQL> >select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT

[code]...

29 rows selected.
SQL> >

View 8 Replies


ADVERTISEMENT

PL/SQL :: Combining Result Of Three Queries To Form A Single Table

May 12, 2013

I have three select queries. Each of them returns a single column. I want the result of these queries into a single table..

I tried this way..

select * from
(first select),(second select),(third select);

this gives duplicate rows...

View 4 Replies View Related

SQL & PL/SQL :: Join Two Queries Together To Get Result

Dec 12, 2011

How can i join two quires together to get result.

My requirement is:

First i want to select Table as we do

Select * from tab;

Then i want to describe the table as we do

Desc WO;

How can we join these two queries to have result.

View 21 Replies View Related

How To Display Result From 2 Queries - UNION ALL

Oct 21, 2012

I am trying to display the results from 2 queries, one is supposed to display the count of the employees, per department, who win over the average of the entire company and the other one is supposed to display the count of the employees, per department, who win under the average of the entire company.

I used a UNION ALL, but all it does is merge the results from the ones that win over and under the average into one row, is there a way to separate them? I tried assigning names to each salary using AS but it only displays the first I put in.

sql
Original
- sql Code
(
SELECT DE.DEPARTMENT_NAME, COUNT (EM.EMPLOYEE_ID) AS MAYORES
FROM DEPARTMENTS DE, EMPLOYEES EM
WHERE DE.DEPARTMENT_ID = EM.DEPARTMENT_ID
AND EM.SALARY > (SELECT AVG(EM.SALARY) FROM EMPLOYEES EM)
GROUP BY DE.DEPARTMENT_NAME
[code].....

View 3 Replies View Related

PL/SQL :: How SQL Queries Act In Back End To Give The Result

Jul 22, 2013

When we are running a query it is giving us the result based on the conditions .But to know what exactly is happening in the database when we are running a query against it and how finally it returns the result.

View 1 Replies View Related

SQL & PL/SQL :: Using Varchar Parameter In Queries With Number Column Can Result In Performance Degrade

Jul 4, 2013

I would like to know if using varchar parameter in sql queries with number column can result in performance degrade.

Ex: Procedure testa ( myparam varchar) is
begin
select col1 into var1 from table where colno = myparam;
end;

Here col no is a number column and myparam is varchar. I feel its better to change the parameter to number.

View 7 Replies View Related

PL/SQL :: Index Range Scan And Table Access By Index Rowid Versus Table Access Full

Oct 5, 2013

Let's consider such table that all rows fit into single block:

SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin

[code].....

why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2

View 8 Replies View Related

Converting MySQL Queries To Oracle Compatible Queries

Jan 23, 2007

our system has always been running on mysql database and recently we have switched to oracle. As the current system is coded using mysql query syntax, when i run this program using oracle database, i got a error. The language that I'm using is JSP.

this is the error message:

The following query could not run on oracle. To convert these mysql queries to oracle compatible queries.

SELECT productID,productName FROM products order by productName;

select newsID,newsDate,newsHeadLine1 from news order by newsDate Desc limit 3

SELECT fuji_products.productID, productName_Display FROM products,products_availability where products_availability.productID=products.productID and (product_status='enabled' or product_status='all') AND category='12'

SELECT catID, catSub1 from category where catSub = '"+ prodCat +"' AND catSub1 is not null group by catSub1 order by catSub1

View 6 Replies View Related

Returning A Table Without Creating Objects?

May 4, 2010

I'm creating a stored procedure where i get to return (OUT parameter) a cursor that points to a custom table. If I create an object, I could just do something like:

Quote: CREATE OR REPLACE TYPE TmpObjType AS OBJECT
(...);
CREATE OR REPLACE TYPE TmpObjTblType AS TABLE OF TmpObjType;
PROCEDURE tmp_proc (...,
out_param_resultset OUT g_cursor_type )
....
OPEN out_param_resultset FOR
SELECT * FROM TABLE(CAST(tmpObjTbl AS TmpObjTblType));
....

How do I return the table (referenced by a cursor) without creating objects?

View 1 Replies View Related

Update 230 Records In Table Returning Error

Nov 20, 2009

want to update 230 records in ins_spr table but its returning error..

Sql statement in blue color returning 230 records.

------------------------------------------------
update ins_spr set
SPR_EXCC = 'NORSk'
where spr_code = (select distinct spr_code from ins_spr where spr_levc = 'N' and spr_facc = 'ROS' and (sts_code = 'AP' or sts_code = 'LS') and spr_stuc in (select distinct a.sqe_stuc from srs_sqe a where a.SQE_EQEC = 'NP3M' and a.SQE_SQSC = 'BE' and a.sqe_stuc in ( select distinct b.sqe_stuc from srs_sqe b where a.sqe_stuc=b.sqe_stuc and SQE_EQEC = 'NP3S' and SQE_SQSC = 'BE')))

OR

update ins_spr set
SPR_EXCC = 'NORSK'
where spr_code = (select spr_code from ins_spr where spr_levc = 'N' and spr_facc = 'ROS' and (sts_code = 'AP' or sts_code = 'LS')
and exists (select sqe_stuc from srs_sqe a where sqe_stuc = substr(spr_code,1,8) and SQE_EQEC = 'NP3M' and SQE_SQSC = 'BE'
and exists ( select sqe_stuc from srs_sqe b where a.sqe_stuc=b.sqe_stuc and SQE_EQEC = 'NP3S' and SQE_SQSC = 'BE')))

View 1 Replies View Related

SQL & PL/SQL :: Function Returning A Table Inside Where Clause?

Apr 5, 2010

DECLARE
cnt number(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM TBL_ADDRESS WHERE ADDRESS_ZIP
IN (SELECT * FROM TABLE(MY_PACK.STR2TBL('46227')));
DBMS_OUTPUT.PUT_LINE (cnt);
END;

MY_PACK.STR2TBL() is a function which takes '|' delimited string, extracts values and returns a table of zipcodes. The function works fine and returns 46227 but the count returned is 0 instead of 280(count returned by replacing inner select with '46227').

View 22 Replies View Related

SQL & PL/SQL :: Function Returning Table Of Dynamic Structure

Apr 24, 2013

I have a requirement to be coded like this:

A function to return pl/sql table(cant use ref cursor) whose columns varies every time it runs i.e,

means
type pl_tab_type is object(col1 varchar2(1000), col2 varchar2(1000))
type pl_tab is table of pl_tab_type

func f return pl_tab
as
...
end;

note : pl_tab_type will vary for each run of function f

i.e.,for example, pl_tab_type can be changed to as follows:

type pl_tab_type is object(col1 varchar2(1000), col2 varchar2(1000),col3 varchar2(1000))

how to return pl/sql table of dynamic type from func,

View 12 Replies View Related

SQL & PL/SQL :: Interface Table Compared With Normal Table - Result Dumped If Match Found

Aug 17, 2012

Oracle 10g, Windows XP

There is an interface table and there is an normal transcational table..interface table is being compared with normal table and if match found the result is dumped into another normal table.

I am using two cursors one is to query the interface table and in a for loop pass the results to the second cursor..The interface table is having 5000 + rows and the transcation table is having more than 3.7 millions ..and the program is taking lots of time to execute..took almost 35-45 minutes..

create table x_interface /* INterface table */ -- 5000 + rows
( name varchar2(80), addr_line1 varchar2(35), addr_line2 varchar2(35), addr_line3 varchar2(35),
addr_line4 varchar2(35), addr_line5 varchar2(35), addr_line6 varchar2(35), suffix varchar2(35),
city varchar2(15), state varchar2(10), zcode varchar2(10))
[code]....

View 7 Replies View Related

Multiple Table Query Returning Duplicates With Wrong Values

Oct 20, 2012

These are the tables I'm working with:

SQL> desc custinfo;
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTID VARCHAR2(4)
CUSTNAME VARCHAR2(18)
CUSTADR VARCHAR2(12)
CUSTCITY VARCHAR2(10)
CUSTSTATE CHAR(2)
CUSTZIP VARCHAR2(5)
CAPACITY NUMBER(3)
HOUSECODE VARCHAR2(2)
[code]...

I don't understand why the custid is the same for each customer, and why it's selecting every customer and not just those with more than 150 gallons ordered.

For this one use the oil tables that you set up and use a subquery. Select the minimum average fall use from the house table. Then show all customers whose number of gallons delivered times two is greater than the minimum.

View 4 Replies View Related

Server Administration :: ORA-30967 - Operation Directly On Path Table

Sep 30, 2013

While doing scanning of character set, error occurred in between.

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

Current database character set is AL32UTF8.
Enter new database character set name: > WE8MSWIN1252
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..64): 1 >
Enumerating tables to scan...

. process 1 scanning REINS.RI_TD_EXECUTION_LOG[AAASwXAAAAABwkAAAA]
. process 1 scanning INS.SERVICE_TRANSACTION_LOG[AAAS5hAAAAADWiAAAA]
. process 1 scanning REINS.RI_TD_VOUCHER_DETAILS[AAASx+AAAAAC6KAAAA]
. process 1 scanning REINS.RI_TT_VOUCHER_DETAILS[AAAS0OAAAAAAMiAAAA]
. process 1 scanning CONFSYS.DML_AUDIT_LOG_FOR_DESIGNER[AAASnwAAAAAAsIAAAA]
. process 1 scanning REINS.RI_TD_TRANSACTION_LOG[AAASx0AAAAACsoAAAA]
[code]...

View 3 Replies View Related

Create Table With Limitation In Queries

Oct 10, 2011

I wonder how I can limit the queries in a table? This limitation would be that from a certain time, it allows queries. Before the opening hours, permitting no select.

Example: Allow SELECTs in table only after 16 p.m.

View 1 Replies View Related

SQL & PL/SQL :: Multiple Parallel Queries Against Table

Jun 23, 2011

I would like to know the following.

If multiple queries are run in parallel(at the same time) against a table or a set of tables (query referencing multiple tables), does it reduce the performance.

In other words is Oracle capable of reading (selecting from) the same table multiple times in parllel.

View 2 Replies View Related

Queries Will Fetch Second Highest Sal From Emp Table

Jan 19, 2012

go through both the given queries...

SELECT * FROM EMP A WHERE 1=(SELECT COUNT(DISTINCT(SAL)) FROM EMP B WHERE B.SAL>A.SAL);
SELECT * FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM (SELECT DISTINCT(SAL) FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=2);

both queries will fetch second highest sal from emp table.which sorting is used by oracle in order by and group by clause.

View 1 Replies View Related

Update AGENTS Table With Data Based On Queries?

Feb 3, 2009

I was trying to update the AGENTS table with the data based on queries. But all the records updates with the same data. I can't understand why.

Write a PL script to populate these columns as follows:

TRAVEL_STATUS is one of three values:
GLOBETROTTER: agent has visited five or more different locations in the course of his missions
ROVER: agent has visited between one and four locations
SLOB: agent has been on no missions.

CONTACTS is the number of targets that share the agent's home location.

some of the tables and columns are
agents table: agent_id, location_id
targets table: target_id, location_id
missions_agents table: agent_id, mission_id
missions_targets: target_id, mission_id
missions table: mission_id, location_id

My code is

DECLARE
status AGENTS.TRAVEL_STATUS%TYPE;
contact AGENTS.CONTACTS%TYPE;
CURSOR loc_cur
IS
SELECT
a.AGENT_ID id,

[code].....

View 7 Replies View Related

SQL & PL/SQL :: Inserting Result Set Of Query Into Another Table

Jul 24, 2012

CREATE TABLE tbl_emp
(
name VARCHAR2(20),
price NUMBER,

[Code]...

NAME PRICE DATAENTRD ROWRANK

aaa 9999 24.07.2012 05:56:00 1

aaa 10000 24.07.2012 05:55:58 2

I want to insert this result into another table, how can I do it??

Quote:TABLE

CREATE TABLE tbl_emp_result_set
(
name VARCHAR2(20),
price NUMBER,
dataEntrd date,
rowrank number
)

View 8 Replies View Related

Application Express :: ORA-24247 / Network Access Denied By Access Control List

Apr 5, 2013

When I invoke SOAP Web-Service using APEX_WEB_SERVICE.MAKE_REQUEST, then I'm able to get response from web-service. However all German character are replaced by JUNK data. However data is coming fine when I test web-services using SOAP UI.

I tried to invoke web-service using UTL_HTTP. However when I use UTL_HTTP, then I'm getting following error.

ORA-24247: network access denied by access control list

<li> Why German characters are replaced by Junk data while invoking web-service from APEX, while it's working fine from SOAP UI

<li> When I can access web-service successfully using APEX_WEB_SERVICE, then why it's throwing ORA-24247 error when I call using UTL_HTTP?

DB: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
APEX: 4.0.2.00.07
Web-Server: EPG
SELECT * FROM NLS_DATABASE_PARAMETERS;

[code]...

View 4 Replies View Related

SQL & PL/SQL :: Query Where Condition Has 4 Rows In Table But Result Is Zero Row

Oct 29, 2012

There are 4 rows in table with stat_flag 'Updated Record' and stat_date with todays date.

stat date has date & time both, for that reason just trying to format with yyyy.mm.dd

I am getting zero rows as result.

where STAT_FLAG = 'Updated Record' and to_date(stat_date,'yyyy.mm.dd') = to_date(sysdate(),'yyyy.mm.dd')

View 4 Replies View Related

SQL & PL/SQL :: Show Result Of Query Just Like Pivot Table?

Jul 20, 2011

me in building a query. I want to show the result of the query just like pivot table.
Test case
CREATE TABLE CPF_YEAR_PAYCODE
(
CPF_NO NUMBER(5),
INC_DATE DATE,
PAYCODE_TYPE CHAR(1 BYTE),

[code]...

I want that my query should look like the format as attached in the xls sheet.

View 1 Replies View Related

PL/SQL :: Getting Different Result While Running Dashboard SP Using Temporary Table?

Jul 9, 2013

 I am getting different result when I run my dashboard procedure I am using temporary table with "ON COMMIT PRESERVE ROWS", below is the information I am running my attendance dashboard procedure which will display the employee attendance status based IN and OUT punches the status like AA-full day absent, GG-Full day Present, AG-First half absent,GA-Second half absent.

Now when I run the first time my procedure for first time I am getting status AA even though IN and OUT timings are correct and if run it again then it is displaying the status for same employee as GG I didn't understand the problem where it is effecting the status

View 4 Replies View Related

ORA-24247 / Network Access Denied By Access Control List (ACL)

Dec 12, 2012

I am having oracle database version 11.2.0.3.0 standard edition, where one of my users requirement is that he wanted to send mails from oracle database but he is getting below error

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at "HF_REPORTING.SEND_MAIL", line 12
ORA-06512: at line 18

is it possible to send mails from oracle database in STANDARD Edition?

View 1 Replies View Related

SQL & PL/SQL :: ORA-24247 / Network Access Denied By Access Control List (ACL)

Nov 15, 2011

While Running the proc,it's raising the below error.

Error report:

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "LISTER_SHIFT.SOLN_GENERIC_SENDMAIL", line 33
ORA-06512: at line 1

[code]....

View 12 Replies View Related

PL/SQL :: Table Access Full

Aug 11, 2012

i have query lie below.

select
(SELECT MIC.MICR_BANK_NAME FROM M_ECS_MICR_MST MIC WHERE MIC.PK_MICR_ID = BANK.ECS_MICR_CODE) AS BANKNAME,
, (SELECT PAY.PAYMENTCODE FROM M_ECS_PAYMENT_MODE PAY WHERE TO_CHAR(PAY.ID) = BANK.ECS_FACTORING_HOUSE) AS
[code].....

i have used composite index below column which used in the tbl_bank_statement table.like column name tbl_bank_ statement (policy_ no,ecs_ micr_code,ecs_factoring_house,ecs_mandate_status)

but still this table giving me TABLE ACCESS FULL.

View 3 Replies View Related

PL/SQL :: ROLES And Table Access

May 23, 2013

I am using Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production.I have a table D_GEOGRAPHY_25729 in RDS schema and I have created a role ACC_D_TABLES logging as ADMIN.

Now I have given GRANT SELECT access for table D_GEOGRAPHY_25729 to role ACC_D_TABLES.

There is another schema TRANS in the same database which has the role ACC_D_TABLES. But after logging in this schema, I am not able to query directly from table D_GEOGRAPHY_25729. I need to use like below to access the table.

select * from RDS.D_GEOGRAPHY_25729 Having the correct role ACC_D_TABLES to TRANS schema, why should I use RDS.D_GEOGRAPHY_25729 ? Is it not possible without prefixing the RDS schema name?

It is possible when I create a public synonym for D_GEOGRAPHY_25729 in RDS schema, but if i do so everyone can access this table.

View 7 Replies View Related

PL/SQL :: Grant Table Access

Aug 15, 2013

I have a schema TEST1 and it has a view 'TEST_VIEW'. I granted read only permission on the view to TEST2 schema. grant select on TEST_VIEW to TEST2. 

But TEST2 is used  by an application which requires to read view metadata and then sync with application afterwords start reading. Is there any special permission to grant to access metadata of the view / table.

View 4 Replies View Related

SQL & PL/SQL :: Inserting Result Set Of Query Into Corresponding SQL Table Type Variable?

Aug 28, 2012

I am unable to insert the result set of query into corresponding SQL Table type variable where as same functionality can be accomplished by PL/SQL table type variable. Can't we access the same by using SQL type variable?

Ex:

Step 1:

SQL Object type , Table type Objects creation :
drop type sql_emp_tab_type ;
drop type sql_emp_type ;
create or replace type sql_emp_type as object
(
empno number,
ename varchar2(20),

[code]...

Step 2: Accessing the table type object from PL/SQL block

SQL>
SQL> declare
2
3 tab_type_var sql_emp_tab_type := sql_emp_tab_type();
4
5 begin
6
7 tab_type_var.extend(10);

[code]...

Step 3: Instead of SQL Table type , if we define the corresponding PL/SQL table type variable

SQL> declare
2
3 --tab_type_var sql_emp_tab_type := sql_emp_tab_type();
4
5 type pl_sql_emp_type is record
6 (
7 empno number,

[code]...

View 3 Replies View Related







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