SQL & PL/SQL :: ORA-14551 - Cannot Perform DML Operation Inside Query
Dec 9, 2010
Attempted to execute the Procs below with
Select OTMP_TCIS_RS.Get_UserInfo('EN') from dual; but i get the following error:
ORA-14551: cannot perform a DML operation inside a query.
The intention of the code is to perform an insert into my table based on passing in values via an object into Stored Procedure Apply_Users_Update
Package Definition
create or replace
PACKAGE OTMP_TCIS_RS AS
--1 PROCEDURE Get_UserInfo
PROCEDURE Get_UserInfo(
o_OutCode OUT INT,
i_language IN VARCHAR2);
FUNCTION Get_UserInfo(
i_language IN VARCHAR2)
RETURN NUMBER;
[code]....
View 5 Replies
ADVERTISEMENT
Aug 9, 2011
Calling function
select PACK.MAIN('blah') from dual
generates:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SYSADM.NEW_QUANTUM_PACK", line 756
ORA-06512: at "SYSADM.NEW_QUANTUM_PACK", line 245
Unfortunately the Body is not accessible to see.The spec of the function is:
FUNCTION MAIN (mvar IN varchar2) RETURN varchar2; I read somewhere that I can call it like:
var myVar VARCHAR2; call PACK.MAIN('blah') into :myVar
But this generates: ORA-01008: not all variables bound
View 3 Replies
View Related
Jun 16, 2010
I have a function declared as PRAGMA AUTONOMOUS_TRANSACTION.
If i execute this function everything is fine.
If I call this function from a remote database, I have this error message:
"ORA-14551: cannot perform a DML operation inside a query".
select function('parameter') from dual;
Result: "OK"
select function@dblink1('parameter') from dual;
Result: "ORA-14551: cannot perform a DML operation inside a query"
View 14 Replies
View Related
Oct 8, 2013
Am calling the Function Batch to insert an update statemtnt into Batch_statement table in the DOWNLOAD_FUNC .But its failing with the error
SQL Error : ORA-14551: cannot perform a DML operation inside a query
Below Is the
FUNCTION BATCH(numTABLE_ID IN NUMBER, varSTMT IN VARCHAR2) RETURN NUMBER IS
BEGIN
INSERT INTO BATCH_STATEMENT(QUEUE_ID,TABLE_ID,STATEMENT,QUEUE_SEQUENCE_ID)
VALUES (numQUEUE_ID,numTABLE_ID,varSTMT,1);
RETURN 1;
[code].....
View 27 Replies
View Related
Apr 3, 2012
I want to perform some operation with case statement. But I am confusing with ora 00932 error. My question is what data type should I use while performing case function?
SQL> select * from samp;
NAME EMPID SALARY DEPT
---
sony 10680 8200 sap
bala 10708 4300 .net
sam 10600 9000 oracle
chris 10655 5500 java
rose 10487 8700 oracle
[code]....
My big question is
different datatypes, then use consistent datatypes. For example, convert the character field to a numeric field with the TO_NUMBER function before adding it to the date field. Functions may not be used with long fields.
// just I am trying to perform basic operation. why oracle didn't support?
View 1 Replies
View Related
Dec 2, 2010
Is it possible to perform any operation using oracle like addition and division in csv file before loading data in oracle. and after the operation changes must save.
Is it possible or not.
View 2 Replies
View Related
Aug 27, 2012
Is it possible to perform a DML or DDL when a select query is in progress?
View 8 Replies
View Related
May 13, 2010
I am getting the same error on a form I am setting up under version 10.1.2 Form Builder. But when I press Shift-F1 I get a Getting Started with Internet Explorer popup. I am running the form under OAS. I have a parent/child relationship between 2 tables that I query on the form.
A copy of the fmb file is attached to access it.
View 11 Replies
View Related
Feb 16, 2011
I am new to SQL and I am just wondering if there is a solution to a problem I am having.I am using the piece of code below.Essentially what I am doing is selecting a field from a table and ordering that field in descending order. Using the Row_Number feature I then specify which Row I want to return.
Every day the row I will want is the Count of field1 for that day divided by 100 minus 1. This returns a single value of field1 and a single value of R.
I perform this operation every day. The only fields I change every day are the dates and the value of R. I use a seperate piece of SQL code to calculate R each day.
My problem is I have to often populate historical tables with this data. I can only run the code once for each day and for each value of R. Is there anyway I can alter this code such that it can return multiple values of field1 over several dates?The only way I can think of is to repeat the code multiple times using UNION but I am hoping there is a more efficient way.
SELECT *
FROM (SELECT Field1,
ROW_NUMBER() OVER (ORDER BY field1 desc ) R
FROM table
WHERE date >= TO_DATE ('20110215', 'YYYYMMDD')
AND date < TO_DATE ('20110216', 'YYYYMMDD')
)
WHERE R = 1227
--Note: 1227 = (count(field1)/100)-1
View 5 Replies
View Related
Nov 3, 2011
i am trying to do something the following .. but I can't get the syntax correctly for the select statement inside the secondary_loop ...
EmailBodyHTML := '';
main_loop := '';
secondary_loop := '';
[Code]....
View 4 Replies
View Related
Jul 25, 2013
Below is the block which i am trying to test in scott schema. I dont want to substute IN clause values directly. So i have written cursor and have added in separate variable separeated by comma.But its not working.
declares varchar2(1000);s1 varchar2(1000);v number := 0;v1 varchar2(2000) := 'SCOTT';j number := 0;cursor hhis select ename from emp;beginselect count(*) into v from emp; for i in hh loops := s||''''||i.ename||''''; j := j+1;if j <> vthen s := s||',';end if;s1 := s1||s;s := null; end loop;dbms_output.put_line(S1); case when v1 in (s1) then dbms_output.put_line('Y'); else dbms_output.put_line('N'); end case;end;
View 3 Replies
View Related
Mar 17, 2013
To display highest marks,least marks,average marks,total marks of the student name entered.
desc stud;
Name Null? Type
----------------------------------------- -------- ----------------------------
SID NUMBER
NAME VARCHAR2(20)
M1 NUMBER
M2 NUMBER
How do I do that using PL/SQL and without Cursor.
View 4 Replies
View Related
Sep 6, 2012
how can i put my query inside my main query.
select *
from (select pa_request_id
,max(status) status
,max(approved_amount) approved_amount
,min(level_id) level_id
,max(req_amount) req_amount
from target_aggregation_attendee
group by pa_request_id)
where status = 'Approved')
My Main Query:
select
TARGET_EMPLOYEE.FIRST_NAME||' ' ||TARGET_EMPLOYEE.LAST_NAME as Requestername,
TARGET_EMPLOYEE.GE_ID as requesterGEID,
TARGET_ATTENDEE.FIRST_NAME||' ' ||TARGET_ATTENDEE.LAST_NAME as AttendeeName,
TARGET_ATTENDEE.ATTENDEE_TYPE_FLAG as Attendeetyflg,
TARGET_ATTENDEE.US_GO_ATTENDEE_FLAG as usgoflg,
TARGET_ATTENDEE.COMP_GOVT_AGENCY_DEPT as Atcomp,
[Code]....
View 4 Replies
View Related
May 1, 2011
I have a problem executing a function.
There are two tables,
1. Table with column names of a second table mapped to certain variables.
ex: Table1
col1, var1
col2, var2
col3, var3
--------
--------
2. Table with the values for columns as given in table 1 ex:
col1, col2, col3, col4, col5,
a , aa , 1 , x1 , p
b , ab , 2 , x2 , q
c , ac , 3 , x3 , r
I have to select values from table2.col1 do some processing and calculate values and store it in a table then do the same thing with col2 and so. This needs to be done for all the columns that appear in table1.For example in table 1 i have only three columns mentioned thus i have to process col1, col2 and col3 from table2. col4 and col5 will not be processed since they do not appear in the first table.
The problem is i have hundred columns in table 2 and the user can add up to hundred columns in table 1 as and when it is required.
I have created a cursor to first select column name from table 1 where variable is not null.For each value in cursor i put it in a local variable.
Second step is to select values from table2 where instead of column name i am using the local variable.But the problem is instead of choosing values from col1 the query returns the value as col1 (the value of local variable)
View 2 Replies
View Related
Oct 21, 2012
I have 2 tables, ASSIGNMENT and RESEARCH_PAPER. For each research paper, I need to find out :
1. The number of assignments created from it (after a given constant assign date)
2. The number of assignments created from it that have been approved.
3. The number of unique users who have either created or approved an assignment from it
Test data :
create table research_paper (id int, name varchar2(100));
create table assignment (id int, r_paper_id int, assigner_id int, assignee_id int,
approver_id int, assign_date timestamp, approved_yn varchar2(10));
insert into research_paper values (1, 'A');
insert into research_paper values (2, 'B');
[code]....
Assignment :
id r_paper_id assigner_id assignee_id approver_id assign_date approved_yn
-----------------------------------------------------------------------------------------------------------
11 100 200 100 23-10-12 12:00:00.000000000 AMY
22 200 100 200 22-10-12 12:00:00.000000000 AMN
32 100 200 101 24-10-12 12:00:00.000000000 AMY
[code]....
Research_paper:
id name
----------
1A
2B
Expected result :
r_paper_id created approved unique_users
-----------------------------------------------
1 3 2 4
2 3 2 3
I wrote the following query for that :
SELECT rp.id r_paper_id,
COUNT(*) created,
COUNT(
CASE
WHEN a.approved_yn = 'Y'
[code]....
But it fails, saying that 'single-row subquery returns more than one row' when I introduce the 'unique_users' clause. The remaining fields of the output are correct.
View 7 Replies
View Related
Jun 13, 2013
Can I use CASE statement Inside a Oracle Report Query. I'm using Oracle Reports Builder 10g.
My Report Query looks like this,
select invh_code, invh_number, invh_date, invh_cm_code, im_description
from invoice_head, invoice_det, unit_of_measurement, item_master
where invd_invh_code = invh_code and im_code = invd_item_code
AND
(case :p_flag when 1 then (substr(invd_item_number,0,(length(invd_item_number)-4)) BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO)
else 1
end)
order by invh_number
:p_flag is a parameter that i pass from oracle form and based on that value (:p_flag=1) i need to include this specific condition
else omit that condition.
But the CASE statement is throwing Error
ORA-00907 :Missing Right Paranthesis
(case :p_flag when 1 then (substr(invd_item_number,0,(length(invd_item_number)-4))
==> BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO)
View 6 Replies
View Related
Apr 11, 2007
I'm trying to perform a SELECT INTO in a SQL*Plus script.the script i'm working towards to, looks something like this;
set term on
set echo off
set serveroutput off
set verify off
ACCEPT ReportName PROMPT 'Please enter a report name; '
PROMPT
PROMPT retrieving current settings for &ReportName:
select STARTDATE, ENDDATE, SUFFIX
into pStart, pEnd, pSuffix
from MyTable
where NAME = '&ReportName';
[code]....
I'm basically trying to perform a select into based in user input and then put the retrieved data back to the console with a prompt. Is this possible within a sql script running on SQL*Plus?
View 3 Replies
View Related
Feb 9, 2011
I got 4 fields:
event_date (01/01/1900 hh:mm:ss)
team_count (number)
interview_count (number)
duration (01/01/1900 hh:mm:ss) - duration of each interview
The calculation is:
(interview_count * team_count * duration(hh:mm)) + event_date
i.e.:
(3 * 2 * 02:30) + 01/01/1900 08:00:00
= 15:00:00 + 01/01/1900 08:00:00
= 01/01/1900 23:00:00.
all I care about is the hh:mm in the result.How can I preform this calculation in pl/sql?
View 7 Replies
View Related
Oct 21, 2011
Below is the code that i tried to perform the sum operation.
CREATE TABLE TEST11
(
FISCAL_TIME_ID NUMBER,
data_id number,
M_VALUE NUMBER,
Y_VALUE NUMBER
);
[code].....
The result I got is
SQL> SELECT FISCAL_TIME_ID, DATA_ID, M_VALUE,
2 SUM(m_value) OVER (PARTITION BY fiscal_time_id, data_id
3 ORDER BY FISCAL_TIME_ID) AS YTD_VALUE
4 from test11;
[code].....
But what I am actually want to get is.
FISCAL_TIME_ID DATA_ID M_VALUE YTD_VALUE
-------------- ---------- ---------- ----------
20110500 3 2 2
20110700 3 50 52
20110800 3 52
20111000 3 250 352
20111100 3 300 652
That is, the YTD_Value column is nothing but sum of M_VALUE column + previous fiscal_month_id's ytd_value column.
Test case:
The YTD_VALUE for fiscal_time_id 20110700 is obtained as current M_VALUE + previous fiscal_time_id's ytd_value => 50 + 2
I tried with the SQL but i could not get the result.
View 5 Replies
View Related
May 6, 2010
I would like to perform a reorganization of the partition table. The table contains 144 partitions and 2038 indexes partitions with that. I would like to ask you to make me understand the best possible way to perform the reorg with cascade options in one attempt.
Also, am not sure how to get the information of the index type is Globally / Local index partition. Is there any document for reference that details about the reorg of partition tables?
View 7 Replies
View Related
Mar 3, 2012
How to perform db refresh.
View 1 Replies
View Related
Aug 16, 2011
I'm using WEBUTIL_C_API, to perform functions of a DLL coupon issuer and I try to tax problems.
problems:
1) performs the function and the application closes. The code that does this is below:
FUNCTION FUN_REDUCAO_Z (impressora varchar2) RETURN pls_integer IS
DLL_FUNCAO webutil_c_api.FunctionHandle;
lv_plist webutil_c_api.ParameterList;
param1 webutil_c_api.ParameterHandle;
ret PLS_INTEGER:=0;
BEGIN
[code]....
I am running these functions in the web client.
View 4 Replies
View Related
Dec 26, 2011
I have detail block in my form and i need to give option to user to search item in that block.
For example I display 100 items from item master and user need to search particular item by either code or name.
View 6 Replies
View Related
Oct 16, 2012
Is there a way to perform a pattern check on a value ?
For example: 654321HD9
The pattern is 6 numbers, followed by 2 letters, followed by 1 number. The data type for the attribute is a string.
Examples of right or wrong
654321HD9 - correct value
654321HD - wrong value
654321111 - wrong value
HD1111111 - wong value
The pattern has to be as i mentioned above (6 number, 2 letters, 1 number) otherwise its wrong. My pattern does not cover all cases.
select REGEXP_SUBSTR('654321HD9', '[0-9]+[A-Za-z]+[0-9]+') from dual;
View 6 Replies
View Related
Sep 26, 2012
I am attempting to perform regular updates on several Oracle tables. The scripts performing the updates are scheduled to run every two minutes, get a value and update the table with that value.
The value doesn't always change but the scripts will still attempt to perform an update.
The same script is part of 7 objects, all of them are scheduled to run at the same time. They update the same table but never the same row.Even though the script is mostly the same on the 7 objects, they run completely independently of each other. The first object will usually perform the update without any problems but when it comes to the second object the script will time out.
View 9 Replies
View Related
Nov 2, 2013
I have a dataguard configuration (physical standby).Database A is the primary database and database B is the standby database (I do not use the broker).Both are Oracle RDBMS 12cR1 EE. The Apply process uses the current log (standby redo logs).On database B, when querying v$managed_ standby;, I get:
ClientProcess process Status ---------- ARCH ARCH CONNECTED ARCH ARCH CONNECTED ARCH ARCH CONNECTED ARCH ARCH CONNECTED RFS ARCH IDLE MRP0 N/A APPLYING_LOG RFS LGWR IDLE
It works fine. Before to perform a switchover,- from database A (current physical database), I get switchover_status = 'TO STANDBY'- from database B (current standby database). I get switchover_status = 'NOT ALLOWED'. What switchover_status = 'NOT ALLOWED' means exactly and how to get further with the switchover?
View 2 Replies
View Related
Mar 7, 2011
I am attempting to perform a TSPITR on a newly created tablespace. The error indicates that the tablespace is not in the recovery catalog in which case it is. The database is Oracle 11g on Win2k8 64bit enterprise edition.
List of Datafiles in backup set 52
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 3959509 06-MAR-11 +DATA/orcl/datafile/system.257.742678049
2 Full 3959509 06-MAR-11 +DATA/orcl/datafile/sysaux.258.742678049
[code]...
View 4 Replies
View Related
Aug 3, 2012
I am getting the following errors when I try drop a tablespace.
I already did the following.
a) The tablespace & its datafiles offline.
b) I have purged dba_recyclebin.
SQL> drop tablespace db_maintenance including contents and datafiles;
drop tablespace db_maintenance including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
View 14 Replies
View Related
Oct 11, 2010
I have a staging table with 70 million rows and nearly 90 columns. We just want to verify and perform some validations on the data and then move this data to final tables. There staging table has a primary key in it.
We are planning to create the final table in following ways.
1. Create n number of final tables.
2. Create n number of final tables with partitions based on the primary key value in staging table.
3. Create single final table with partition based on the primary key value staging table.
View 2 Replies
View Related
Jun 26, 2013
Below is my import command for importing specific function from export file but iam getting below errors
impdp system/PASSWORD schemas=TNC6 directory=dumpdir dumpfile=FULL01-02-2011.dmp logfile=IMP.log include=FUNCTION:"IN ('TNC_IS_NUMBER')"
ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00936: missing expression
View 4 Replies
View Related
