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]....
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;
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
There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I want to create a procedure that will call the above function. Here's the PL/SQL:
CREATE OR REPLACE PROCEDURE TEST ( V_COUNT OUT NUMBER ) AS V_FOO NUMBER(2,0); BEGIN
[Code]...
There's a Public Database Link called PER_ACC in Local. When I try to create this procedure I get: Encountered symbol "@" when expecting one of the following: .(*%&................
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?
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.
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.
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
I like to duplicate the database DATABASEA to DATABASEB and need to be run in same server. I tried to bounce the listener & databaseb instance multiple times but no lucky.
Step 1 : The instance DATABASEB is in Nomount stage
Step 2 :
==> tnsping DATABASEB TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 11-SEP-2012 07:36:54 Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = client )(PORT = 1521)) (CONNECT_DATA = ( SERVER = DEDICATED) (SERVICE_NAME = DATABASEB)(UR=A))) OK (10 msec)
Step 3 :
==> rman target sys/*****@DATABASEA auxiliary sys/********@DATABASEB Recovery Manager: Release 11.2.0.2.0 - Production on Tue Sep 11 07:37:50 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: DATABASEA (DBID=1723462779) RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
i want to perform full export + import of an oracle 11g database as fast as possible. i was thinking to perform the exp+imp on the same command.in exp i can perform something like this :
i know that i can do both action in impdp when using a dblink, but the problem is that some objects in the database cannot be copied via a dblink. the question is if there's a corresponding datapump command to the old exp+imp command i presented.
We have access to a remote Oracle database in Germany and need to insert selected data to our local Oracle database. Our problem is that we have to do several joins (7 tables) on the remote database as well as using one where clause (always the same: P.T_LIEF_LFNT_1='12803193').
Unfortunately we do not have rights to create a view on the remote database. Is there another way to send the entire query to the remote database for processing? Also, the query returns approximately 34,000 rows.
Here is our current query:
INSERT INTO PRIMUS(PARTNO, SORT_FORMAT, TP_WORKSPACE,
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;
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
i have Oracle 10g data guard set up on windows environment.....i need to know the what are the right steps to perform failover in case the primary database gets fail.
I have ORACLE XE 11g installed on two machines and have been trying my luck to get my local db to query/insert/update a table from the remote db.
I have created the public database link and querying the dba_db_links shows that the links is successfully created (see the bottom of the post). I have also created synonyms for my remote table and queries like select name from jforum_forums succeed, with jforum_forums being a public synonym referring to user.jforum_forums@corona.magic.ubc.ca.
However when I try to insert a row into the remote table, there is a query to the remote DUAL to fetch the last generated ID for a given table similar to the following: SELECT jforum_forums_seq.currval FROM DUAL. Statements of such fail and they either throw an ORA-02019: connection description for remote database not found or ORA-01729: database link name expected. I tried including the database link with the name of the table making it like SELECT jforum_forums_seq.currval FROM DUAL@corona.magic.ubc.ca but that didn't work either.
PS, I should mention that global_names are set to true in both databases and that my database link has the same name as the global_name of the remote database.
OWNER ------------------------------ DB_LINK -------------------------------------------------------------------------------- USERNAME ------------------------------ HOST -------------------------------------------------------------------------------- CREATED
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,
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)
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.
I want to update my table base on changes in a column of a table from remote database automatically. I just want to pick some updated column then update to my own. i have made a trigger but failed.
CREATE OR REPLACE TRIGGER TG_UPD_ID after update OF STUD_ID on STUDENT@SCHOOLDB for each row begin
update MARKS set ID_ST = :new.STUD_ID where STUDID_STUDENT = :old.IDS;
end;
Error report: ORA-02021: DDL operations are not allowed on a remote database 02021. 00000 - "DDL operations are not allowed on a remote database" *Cause: An attempt was made to use a DDL operation on a remote database.
For example, "CREATE TABLE tablename@remotedbname ...".
*Action: To alter the remote database structure, you must connect to the remote database with the appropriate privileges.
Am running forms 10g on windows vista service pack 1 and am having problems with the FRM-40514 error. (Operation requires a database connection).
I can connect quite alright from form builder, but when i run a form it request for login credentials again. I dont understand why this is so, when am already logged on in form builder.I tried to tacke this by creating the following
This code suppreses the logon screen and runs the form without logging on to the database. I know this because when I try to enter and execute query it brings up the message: FRM-40514: Operation requires a database connection.
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)
I have 2 database . One is production and other is Integration and I have one table "emp" which is on integration server and a synonym of that table with the same name"emp" on production. On production we have a proc in which we are using "emp", but some time our integration server goes down, so is there any way to ping the integration server inside the proc and if it returns success then we will execute the rest of code of proc , otherwise it will send a mail to us saying that integration server is down.
The Oracle DB in question is 11.2.0.1, x64, Server 2008.I also have a SQL Server 2005 database that runs a third party product, "PaperVision", which we use to manage documents of various kinds. This SQL Server server is also Win 2008, x64.Now, on the server that runs SQL Server, I have a simple view which is defined as such :
This view works great from the SQL Server side. I also created a database link from Oracle to the SQL Server machine, and it also works great.It is defined as such :
CREATE PUBLIC DATABASE LINK PVE_SQLSERVER CONNECT TO EVP_PVE_USER IDENTIFIED BY <PWD> USING 'PVE_SQLSERVER';
Where EVP_PVE_USER is a user created on the SQL Server machine with rights to select from this view.I know it works because I get results with a sql command like :
select * from VW_PVE_DOCS_1_1@PVE_SQLSERVER;
I also created a view on the Oracle server that refines this information. It is defined as such :
CREATE OR REPLACE FORCE VIEW EVPDBA.VW_PVE_CONTRACTS_INALERT ( DOCID, EFFECTIVE_DATE, EXPIRATION_TYPE, ALERT_PERIOD_START, ALERT_PERIOD_END, ACRONYM, [code]....
This view also works fine, i.e., I can select * from it from the sql command line.Now, the problem comes in when I need to run a procedure that processes this view every night and/or week.I have stripped everything out of this procedure that is not relevant, and it is defined as such for this forum :
CREATE OR REPLACE PROCEDURE EVPDBA.TESTME is tnum number := 0; begin select count(*) into tnum from VW_PVE_CONTRACTS_INALERT; end; /
If I execute this procedure from the sql command line, all is well.When I run it from a scheduler job, I get
ORA-01010: invalid OCI operation ORA-02063: preceding line from PVE_SQLSERVER ORA-06512: at "EVPDBA.TESTME", line 5 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_ISCHED", line 185 ORA-06512: at "SYS.DBMS_SCHEDULER", line 486 ORA-06512: at line 1
I am aware that DBMS_SCHEDULER performs a commit when scheduling a job, however, this is not scheduled from a trigger.I scoured the forums and have found a few things that seemed relevant, but not much. One had to do with the version of the JDBC driver between two Oracle databases, but I wonder if the age difference between Oracle 11 and SQL Server 2005 (Express) might be an issue. The fact that all command line select statements and running the procedure work fine implies to me that there is an additional issue raised due to the scheduler.
The other posts I found talked about performing a commit just before any select that ultimately pulls across a db link. I did this, and still no luck.One other useful fact - the job appeared to run succesfully at 5am, yet trying again at 8am threw the error, so it may be sporadic. (Although during regular daytime hours it is a very repeatable error).
I am looking into reformatting things to use the older DBMS_JOB, however, I really like the log history of job details and other functionality available with SCHEDULER.
I have a simple script which fetch data from another SQL server database.
The remote SQL Server database is accessed by oracle thru gateway, and a simple DB link is made in local database to the remote oracle datbase (which actually access the MS SQL Server)
The SQL script empties all the data in local database, and fills it with the fresh data from MS SQL server.
Problem is sometimes SQL Server is down, and i lose all the data in local database. The script runs automatically as schedualed job. I want to make my script intelligent enough to terminate the script if the other database is down.
I installed oracle 10g and developed simple software using forms 6i. Its working well as a stand alone system.
I want to share it in network. so I installed forms 6i as oracle client , copied the tnsnames.ora from the oracle installation path to forms installation path.(inside NET80/Admin folder)
When i try to connect with user name and password , it comes ORA-12203 Unable to connect to destination. I checked LSNRCTL in the database machine, it is listening.
