PL/SQL :: ORA-00947 - Not Enough Values But Enough Values Are Being Returned
Dec 18, 2012
I created a package with some types, and every was compiling fine. However, when I ran the new function, I got an error: ORA-21700: object does not exist or is marked for delete
After a little research, I realized that the types would have to be declared outside the package. As soon as I did that, I suddenly started getting the "not enough values" errors on all my types. I compared the number of columns being returned, and the number of columns in the type, and they match.Here is my type code:
CREATE OR REPLACE TYPE TSA_CUSTOM.Lost_Plan as object (
LP_Key number, -- The member key of the plan that is going away
LP_Type varchar2(20),
LP_Dept varchar2(12),
LP_SubDept varchar2(12),
LP_Class varchar2(12),
LP_VendorName varchar2(50)
[code]...
I have a requirement like getting list of values from one table and inserting them into another table.I have tried with sub querying but didn't worked out because the select query is returning multiple values.
how to proceed further and the ways how can I write this requirement.
I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by.
As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE i_e NUMBER(10); BEGIN FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL) LOOP FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
I am searching the simplest way for ad hoc MINUS.I do:
SELECT * FROM uam_rss_user_XXXXXXX WHERE host_name IN ('XXX0349', 'XXX0362', 'XXX0363', 'XXX0343', 'XXX0342', 'XXX0499', [code]....
and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?
From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?
i want to add the values of rows in a table. for example if we enter marks for five subjects to a student we need to calculate the total marks and the average for that student. how to do this.
I have a dropdown in my jsp with all users from database. When I select a user I'm able to get the details of the corresponding result, but when I click on all it should give the total individually. Below is the code that I use to get individually.
I certainly know this is possibly but I am trying to do this on the fly and can't seem to work it out:
I have a table A: ID Name Priority ------------------- 1 Smith 1 1 SSmith 2 1 ASmith 3 1 BSmith 3 2 John 2 3 Ed 1
and I am looking to create the following table from this: ID Name Sum(Top3Priority) -------------------------------- 1 Smith,SSmith,etc 8
Now, I've got listagg working and everything appears to be going swimmingly but: for every listagg grouping on name I need to only sum the highest top 3 priorities. So in the example above there are four Smiths but I need to only sum the top 3 priorities which are 3,3,2 and ignore the 1 even though I do want all the listagg Smith's (SSmiht, ASmith, etc) in there.
Now I can sum the priority, but don't really know how to sum only the top 3 in any ID ? There can be 1 to n ID's so if there are only 2 ID's I want to sum those 2, if there are 3 all 3 and 4 upwards only the top 3.Here is a snippet of the SQL I am using
SLECT id, listagg(MN_CR_LOOKUP.f_name, ',') within group (order by Priority)) roadname, **** sum top 3 here ?**** count(*) "NumI", Sum("Elevation") "CombinedElevation" FROM jc,
im new to Database i have to count no of messages based on the minuties like 1-5, 6-15,16-30,>30 min how to write query
select count(*) from table1 where ( select ( (extract(day from column2)-extract(day from column1))*24*60 + (extract(hour from column2)-extract(hour from column1))*60 + extract(minute from column2)-extract(minute from column1)) as Processed_time from table1 ) IN (1,5)
AID UCD U_TXT UDATE PID 116 1 Req Documents 01-OCT-2011 100 116 2 AGG APPR 01-OCT-2011 101 116 3 Docs received 02-oct-2011 102 116 1 Tmp received 02-oct-2011 103 117 2 Notice sent 03-oct-2011 104
UCD - We have total 19 codes (1 to 19), each can have multiple rows for one AID.. like 1 repeated twice for AID 116. PID - Primary id (Primary key column)
Output I am looking -------------------- AID COL1 COL1_TXT COL1_DATE COL2 COL2_TXT COL2_DATE..ETC 116 1 'Tmp received' 02-oct-2011 2 AGG APPR 01-OCT-2011 117 2 Notice sent 03-oct-2011
If the same UCD repeated multiple times then we should get the max(PID) record for that UCD and for that AID
I tried with group by AID,PID. but couldn't bring the rows to columns. I have attached the script with the post
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)
CREATE TABLE TMP_split AS SELECT 1000 AS grp_id, 'abc' AS ATTRIB, 60 AS PCT FROM DUAL UNION ALL SELECT 1000 AS grp_id, 'pqr' AS ATTRIB, 40 AS PCT FROM DUAL;
CREATE TABLE TEST ( NAME VARCHAR2(100), STR NUMBER, ED NUMBER ) Insert into TEST (NAME, STR, ED) Values ('raj', 2, 4); Insert into TEST (NAME, STR, ED) Values ('kri', 1, 3);
i want to create database. i have created the schema & done all activity like decide foreign & primary key of table all things on paper? when i open oracle 8i & what should i write at front of sql prompt. For enter the value into database should i give the set path? How to store value into particular directory or folder in hard disk? should i directly start with create database?
I know proper syntax. Suppose i want to store the database & values in folder " D:apurva " what should i do. After entering oracle 8i it show sql> so what i do to store database in D:apurva . should i directly start with create table command in front of sql>
I created a Table with a single column varchar2.. in which I wanted to insert value like 'BBBBAB1'... till 'BBBBAB100'
Created a sequence starting with 1...
and inserted single row, and multiple rows using loop also; by using below code -
insert into Trans SELECT CONCAT('BBBBAB', Trans1.NEXTVAL) from dual;
but whenever I see the values they are not as required ... 'BBBBAB1' but one character 'B' is missing, and the values populating are 'BBBAB1'.. 'BBBAB100'
