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.
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]....
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?
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"
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].....
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
Can the control file be used to store scripts which are used to perform backups? I know the RMAN recovery catalog can.
Does the SHOW command display detailed reporting on the RMAN catalog contents other than configuration information?
i have created an file in my local system (sys name : System1). i have created a directory as below
SQL>CREATE DIRECTORY test_dir AS '\System1TEMP';
I gave rights as
SQL>GRANT ALL ON DIRECTORY TEST_DIR TO PUBLIC;
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
IF utl_file.is_open(fileHandler) THEN
utl_file.fclose_all;
END IF;
[code]......
but while executing the above procedure, Invalid File Operation error occurs.
Why does is the code blow reuturning a null for a simple subtraction and division?I am trying to do 4-13/9 for one and 4-22/9 for 22
select unit,
term_1201,term_1202,r_slope,r_intercept,
(r_intercept - term_1201) / decode((r_slope),0,1) as ONE,
(r_intercept - term_1202) / decode((r_slope),0,1) as TWO
[code]...
We are using the below procedure to generate the files on the DB server.
SQL>
SQL> create or replace procedure write_to_file(p_dir varchar2,
2 p_file varchar2,
3 p_mode varchar2,
4 p_clob clob) as
5 l_output utl_file.file_type;
6 l_amt number default 32767;
7 l_offset number default 1;
8 l_length number default dbms_lob.getlength(p_clob);
9 new_clob clob;
10 BEGIN
11
12 l_output := utl_file.fopen(p_dir, p_file, p_mode, 32767);
13 while (l_offset < l_length) loop
14 new_clob:= SUBSTR(p_clob,l_offset,l_amt);
15 utl_file.put_line(l_output, new_clob,true);
16 l_offset := l_offset + dbms_lob.getlength(new_clob);
17 end loop;
18 utl_file.new_line(l_output);
19 utl_file.fclose(l_output);
20 end write_to_file;
21 /
Procedure created
SQL>
This works fine we call this from PL/SQL developer tool. However, when this procedure is called from Java JDBC, it is giving error
java.sql.SQLException: ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "SYS.OBP_UPGRADE_UTIL", line 2816
ORA-06512: at "SYS.OBP_UPGRADE_UTIL", line 3029
ORA-06512: at line 1
DB schema used for both are same (SYS) Verified the directory on the DB server This Procedure called from package with AUTHID CURRENT_USER option.
My Application is running on the unix server - user oracle. My DB is in different unix box. I had created the folder called OUT with the permission of 777 in DB server. I created ORacle Directory DMPDIR for that folder /OUT. and executed grant read/write to that schema.
1) when i use the below code in sqlplus its writing the file into that folder.
DECLARE
vInHandle utl_file.file_type;
vNewLine VARCHAR2(4000);
[Code].....
But, when the same piece of code is executed from the package ( which called from the application)its throwing the ORA-29283: invalid file operation.
even i am able to copy the files into that folder with the user oracle.
My Login is System
Already created Directory MY_DIR in c:mydir
Showing Following error
1 DECLARE
2 src_lob BFILE := BFILENAME('MY_DIR','C:
mi2Blue.jpg');
3 dest_lob BLOB;
4 BEGIN
5 INSERT INTO lob_table VALUES(2, EMPTY_BLOB())
6 RETURNING doc INTO dest_lob;
[code].....
Windows 2003, DB 10.2.0.3...I have to read some files from remote folder, so on remote Windows machine I shared folder c:est (grant Everyone first, and Administrator then) and on DB Server I mapped the remote folder with letter T..All these operations were made with administrator privileges.
In Oracle I create a directory object with this command:
CREATE OR REPLACE directory T_DIR AS 'T:';
Then I granted read and write privileges to my user
GRANT READ, WRITE ON DIRECTORY T_DIR TO <user>; (I tried also to set PUBLIC and SYSTEM)
But when I try to open a file with UTL_FILE with this command
file_handler := UTL_FILE.fopen( 'T_DIR', 'Dati.ini', 'r' ) ;
the error is
Exception: ORA-29283: operazione file non valida
ORA-06512: a "SYS.UTL_FILE", line 475
ORA-29283: operazione file non valida
If I change my directory object in 'C: est' (local folder) it works correctly.I tried also restart DB while shared folders were connected but with the same result.I tried, from sqlplus, the command host (dir t:) and it works (folder is accessible)
Is it an Oracle bug? Is it a Windows bug?
I get below error when i tried to add the master site to the master definition site.
SQL> BEGIN
2 DBMS_REPCAT.ADD_MASTER_DATABASE(
3 gname => 'TEST',
4 master=> 'testdb2',
5 use_existing_objects => FALSE,
6 copy_rows => FALSE,
[code].......
This is regarding a problem we are facing during report(.xls) creation which is done using a procedure triggered by a job run.
The report file(.xls) file is not getting created when the job(using dbms_scheduler) calls.
The procedure uses utl_file to create an .xls file
We have a folder on the path /oracle/tata_aig_life/websales/dnld . This folder( dnld ) has the all the priviliges drwxrwxrwx.
We have a job scheduler as the attachment which in turn triggers a procedure(check the code attachment for the scheduler and the procedure).
In the procedure we first remove the file(.xls) created in the path and then recreate a new .xls file.
The reason for first removing the file and recreating is that the existing file is not getting updated with a new file when the job is run.
We capture the exceptions in a table. In the table the following exception is logged :ORA-29283: invalid file operation
The job is triggered and the files are created on the path mentioned in the procedure on the UAT Environment..
Also the files are created when we manually run the same procedure in the path.
The Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
The OS flavour is SunOS otlpsr5cora01 5.10 Generic_127111-11 sun4u sparc SUNW,Sun-Fire-V490 -------
I am trying to write data to a network shared folder. When I write to a local file it works perfectly. Below is my procedure.
CREATE OR REPLACE procedure nbpsbp_file as
type r_cursor is ref cursor;
refr r_cursor;
tab_name varchar2(20):= null;
tab_name1 varchar2(20) := null;
tab_name2 varchar2(20) := null;
[code]....
When I execute the above procedure, it gives me the following error
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
ORA-06512: at "NBPSBP_FILE", line 36
ORA-06512: at line 1
I have also set the parameter utl_file_dir = '\10.16.10.225 emp' When I set the utl_file_sir to a local folder, for example, c: emp, and use the same path in UTL_FILE.FOPEN, then it works fine and writes the desired output to text file. But when I give it a network address, it raises the above error.
i am populating 3 records in my data block.i do not want more than 3 rows.but when i click the plus icon in form menu one more row is getting generated which i do not want. i can restrict the new record addition through plus icon in menu bar for a particular block.
View 1 Replies View RelatedI 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.
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 :
mknod /oracle/migration/exp_pipe p
exp '/ AS SYSDBA' file= /oracle/migration/exp_pipe full=y | imp system/***@oracle_db file= /oracle/migration/exp_pipe full=y
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.
trying to do impdp but getting error
UDI-22303: operation generated ORACLE error 22303
ORA-22303: type "SYS"."DBMSOUTPUT_LINESARRAY" not found
ORA-00600: internal error code, arguments: [kokaocr], [], [], [], [], [], [], [], [], [], [], []
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_OUTPUT"
ORA-06512: at line
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?
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?
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.
Is it possible to perform a DML or DDL when a select query is in progress?
View 8 Replies View RelatedI 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?
How to perform db refresh.
View 1 Replies View RelatedI'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.
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.
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;
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?