SQL & PL/SQL :: Returning Two Or More Records - Giving Error?
Apr 1, 2011
i am trying this query
SELECT
CASE
WHEN TYPE = 'COUNTRY' THEN (SELECT CODE FROM LIST_T)
END mi
FROM LIST_T
and in that subquery i am returning two or more than records so it is giving me the error like ORA-01427: single-row subquery returns more than one row
how do i solve this error and i must to fetch two or more records from the subquery if it possible in any other
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')))
I am running a query in our Clarity PPM database to return a list of all Support projects. This returns a simple list of project code and project name:
The query has the project resource tables associated with it, so I am able to list all resources allocated to the project. But for now i am only selecting a DISTINCT list of projects.
I have a separate query which returns a list of support resources.
select res.full_name, res.unique_name , dep.description from niku.srm_resources res, niku.pac_mnt_resources pac, niku.departments dep where res.unique_name = pac.resource_code and pac.departcode = dep.departcode and res.is_active = 1 and description like 'IMS%' and UPPER(dep.description) like '%SUP%'
What I need to be able to do in the first query, is return only projects that do NOT have a resource that appears in the resource list in the second query.
(the res.unique_name field in the second query can be linked to the same in the first query)
Logically, the process would be: 1. Identify Support Project 2. Identify Resources allocated to the project team 3. Compare with List of Support Resources 4. If any Resources in that list do NOT appear on the project, then return project.
I have a question. If i insert some values to a table and then write a create statement. But if the create statement gives me error (eg: table name already exist). And without commiting if i come out the session will the insert commit?
CREATE OR REPLACE TRIGGER LOGONTRIG AFTER LOGON ON DATABASE DECLARE v_temp NUMBER; V_bypass_trg_flag VARCHAR2(1);
[code],...
This trigger got successfully compiled. and when am trying to connect to the db with other user its giving me error "ora-06512 : at dbms_session".When i grant dba privillage to that user am successfully able to login to db.
i'm not able to open layout in the custom form.automatically it will be closing and giving this error. Oracle Forms Designer has encountered a problem and needs to close.
I am attempting to make a job on an active database and I keep getting the error "An error occurred checking if the script file directory exists. Correct the error and try again. Error message: Agent Unreachable"
I can't find any information about the error on the logs and I don't knwo what to do about the error. I really need the script the job makes but how to get it.
Enter user-name: sys as sysdba Enter password: ERROR: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist SVR4 Error: 2: No such file or directory
We just upgraded to APEX 4.2.2.00.11 and we are getting ORA-24247: network access denied by access control list (ACL) errors form apex_mail. This had been working correctly prior to the upgrade.
select * from t_act_rwrd_reimb_stay a where reimbursed_amt < 0 and invoice_date = '01-JAN-1900' and not exists ( select 'x' from t_act_rwrd_reimb_stay b where a.GPM_ID_STAY = b.GPM_ID_STAY and reimbursed_amt > 0 )
The above table t_act_rwrd_reimb_stay --> is from a view on the database Prod2 and this view is pulling the data from the database prod1 via dblink
When I run the same query on Prod1 it returns 3 rows. Is there any reason this is not fetching data on prod2 view? or whatz wrong with the above query
or for example if I run the above query as follows with the dblink "@prod1" it returns the data properly
select * from t_act_rwrd_reimb_stay@Prod1 a where reimbursed_amt < 0 and invoice_date = '01-JAN-1900' and not exists ( select 'x' from t_act_rwrd_reimb_stay@prod1 b where a.GPM_ID_STAY = b.GPM_ID_STAY and reimbursed_amt > 0 )
I'm needing to return results per month counting records that meet a certain criteria. Some months there will be no results but I need to return a zero rather than having that month omitted from the result set.
This is the pre-existing query:
SELECT TO_CHAR(CRSCHED_DATE,'YYYY/MM'), Count(CPMA.RECORDNUMBER) FROM CPMA.CPMA CPMA WHERE (CPMA.CRSCHED_DATE Between TRUNC(ADD_MONTHS(SYSDATE,-12),'MM') And LAST_DAY(ADD_MONTHS(SYSDATE,-1))) AND (CPMA.CHGSTATUS='Duplicate') GROUP BY TO_CHAR(CRSCHED_DATE,'YYYY/MM') ORDER BY TO_CHAR(CRSCHED_DATE,'YYYY/MM')
The results returned are accurate, but any month(s) with no records meeting the specified criteria are skipped in the result set.
While I was debugging the code of already existing application (Oracle apps fnd_global package), I copied a package to make changes in it.
It was strange that with Original package fnd_global I am getting below error when I call it over database link:
"ORA-20001: Oracle error -20001: ORA-20001: Oracle error -2074: ORA-02074: cannot SET NLS in a distributed transaction has been detected in fnd_global.set_nls.set_parameter('NLS_LANGUAGE','AMERICAN'). has been detected in fnd_global.set_nls."
PACKAGE CALLS ARE AS FOLLOWS:
Database 1 (Oracle Apps LSH module) CUSTOM procedure to call CDR_PUB_API_GVA has CDR_PUB_API_GVA INITIALIZATION BLOCK OF CDR_PUB_API_GVA CALLS Fnd_Global.apps_initialize (when copied to fnd_global1, then it's call don't throw any error)
Database 2 (External application) has dblink to Database1 calls custom procedure over dblink
While the copied package is not throwing any error (I copied fnd_global to fnd_globa1).
How do you specify the value in START WITH dynamically during runtime. For example consider the bewlo scenario:
CREATE TABLE ISCT ( ITEM_NO VARCHAR2(15 CHAR) NOT NULL, ITEM_TYPE VARCHAR2(3 CHAR) NOT NULL, ITEM_TYPE_SCO VARCHAR2(3 CHAR) NOT NULL,
[Code].....
Now if you create a view:
create or replace view test_v (ITEM_NO, ITEM_TYPE, ITEM_NO_SCO,ITEM_TYPE_SCO) as SELECT T.ITEM_NO, T.ITEM_TYPE, CONNECT_BY_ROOT T.ITEM_NO_SCO,T.ITEM_TYPE_SCO FROM [Code] .......
Then run the below query:
select * from test_v where item_no_sco = '00245905' --
the output is got within a second. but the real scenario is that both table icont and isct have millions of rows. In that case even this small query of the view takes 10minutes as there a FULL TABLE JOIN.
How do I give the START WITH in the view to make it faster i.e. even with millions of rows the output of this comes in seconds:
I am trying to bulk update records in oracle using XML , front end is vb.net.Now the problem when i updating for 1000 - 5000 records on my development server. Its getting updated.
But when we are updating on the production server for 100000-200000 records , we receive error
"ORA-01460: unimplemented or unreasonable conversion requested "
My oracle is sitting on UNIX, i have a sql loader scripts which load the data in oracle at every 10 min and bad files is written into a directory. since the file names are same it overwrite the badfiles in case of error record. i can devise a code to write the bad file with different name. I want to write error record into oracle table, is this possible and how can i achieve ?
create table test_pc (event_id number(10), parent_event_id number(10)); insert into test_pc values(2001,null); insert into test_pc values(1006,null); insert into test_pc values(1004,null); insert into test_pc values(2002,2001); insert into test_pc values(1001,2002); insert into test_pc values(1002,1001); insert into test_pc values(1003,2005);
In order to derive parent child relationship I have ran the following query :
select * from test_pc start with parent_event_id is null connect by nocycle prior event_id = parent_event_id;
It is giving all the records except the last one (i.e. event_id = 1003 and parent_event_id = 2005), because the parent_event_id does not exists in the table.
But my requirement is to show all the record, i.e. if the parent_child relationship is present then show accordingly and also show the rest of the records where the parent_child does not exists even the parent_event_id exists.
i hav create a report and use order by in the query of the report when i run my query on sql prompt it gives output in a sorted manner but when i am using it in report builder then it is not giving the output in sorted order.
dbms_metadata is giving me code that will create more datafiles than the original, and furthermore won't run: firstly, because two files are named with a null string, and secondly because it includes RESIZE commands which won't work because they nominate OMF file names. Or is dbms_metadata unreliable?
I am using Oracle 10g Express Edition on Fedora core 5 32+ bit os. The problem is when I use the SQL Plus command line to make SQL statements I can not get the previously typed command back at the prompt when I use the up and down arrow keys on my keyboard. This is quite easy when I am using a shell, but here with this Oracle command line interface it is not working at all. Here is the example as what actually is happening whe I press the up or down arrow keys.
I am running the following delete query and it has been running for over 2hrs:
delete from dw.ACCOUNT_FACT where rowid in (select rowid from DW.ACCOUNT_FACT minus select max(rowid) from DW.ACCOUNT_FACT
[Code]..
Here is the explan plain result:
explain plan for delete from dw.ACCOUNT_FACT where rowid in (select rowid from DW.ACCOUNT_FACT minus select max(rowid) from DW.ACCOUNT_FACT group by CRTORD_FIPS_CD, LAST_PAYMENT_DT, ORDER_NUM,
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access(ROWID="$kkqu_col_1")
I have all constraints disabled. How do I make this delete finish faster? We're trying to remove duplicates from this table using the criteria giving in the statement.
In a pl/sql procedure, when I am doing an update, I need the old value to be returned and stored in a local variable, so that the same can be used for future purpose.
Note : I know the "OLD:" option is present when we use TRIGGER, but in my case , the table I am updating is a old table and I am not permitted to create a trigger for it.
create table testing ( id number (10), key number (10) ) insert into testing values (1,10) insert into testing values (1,10) insert into testing values (2,10) insert into testing values (2,20) insert into testing values (3,10)
i got a table(deptid, deptname, address,city,zip, state, other columns) i want to write a query to determine any error(records with different values) because i expect all records grouped-by(deptid, deptname, address,city,zip, state) to have the same deptid(pls note that deptid isnt unique),
or a specific deptid should have only one record based on the grouping (deptid, deptname, address,city,zip, state),
