SQL & PL/SQL :: Alternative To UTL FILE?
Jan 12, 2011
I have been told that I cannot use Oracle directory objects, external tables and the UTL_FILE package in my production environment for security reasons.
In this scenario what are the alternate options available in Oracle to write a file and save it in a specific location in the server. One of my requirement is to process a file and write the results of processing in a output file in the application server which in turn will be accessed by the online application.
View 7 Replies
ADVERTISEMENT
Nov 8, 2011
I've got to create 2 tables...
Table 1 include members of a certain club with specific criteria
Table 2 needs to include everyone else on the main database, not in the club, but with the same criteria.
So, ultimate goal is to have two tables, similar criteria, T1 = club members and T2 = non-club members
Table 1 ran just fine.
Table 2 is running and running and running. I'm looking for a more efficient way then the syntax below:
create table NON_club_Members nologging as
select distinct hcp.ID, count (distinct hcp.profile_key)
from mrtcustomer.table_1 hcp, Club_Members cm
where hcp.child_birth_dt between '31-OCT-2000' and '30-OCT-2011'
and hcp.ID not in
(select cm.id from Club_Members)
group by hcp.id
I've also tried <> and that also ran and ran and ran.
View 1 Replies
View Related
Jul 1, 2007
im used to using PL SQL via oracle. but lately ive been doing a lot of mysql and PHP and i really need to use something similar to DECODE in my queries. or am i forced to do the checks via PHP.
View 6 Replies
View Related
Dec 12, 2006
For whatever reason, in PHP, DESC statements do not work. They're simply unrecognized and you get an OCI "invalid SQL statement" error. It may be some sort of security feature. I've also had the same result with the SQL scratchpad in the Java EM, but not in SQL Plus. Is there an alternative method for getting details on a table that I can use that also doesn't require being logged in as a SYSDBA?
View 5 Replies
View Related
Dec 21, 2010
Is there any alternative to Union or Union all of two different table. Example would be great.
View 5 Replies
View Related
Aug 13, 2013
an alternative to Oracle Cursors.My table has huge amount of data and is taking time in processing. I had thought of using BULK COLLECT but it can only be used to insert, delete or update data and will not allow me to select data.
View 33 Replies
View Related
Feb 16, 2010
Is there any alternative of Soundex function. I have two table of city. I want to compare city of both table. Now problem is that one city name is 'HANGO'. In table A City is feed with spelling 'HANGO' and other it is spelled 'HANGU' OR 'HUNGU'. Similarly there are many cases. I used Soundex function but it is not working perfectly.
Is there any other way to compare data by sound.
View 1 Replies
View Related
Jan 31, 2012
Is there a way to put on tnsnames.ora the alternative IP of a server that if the first dont respond it tries the ip of the second? Example:
ORA92 =
(DESCRIPTION =
(ADDRESS_LIST =
try
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.1)(PORT = 1521))
except
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.2)(PORT = 1521))
end )
(CONNECT_DATA =
(SERVICE_NAME = tsh1.world)
)
)
View 6 Replies
View Related
Jan 5, 2011
Many moons ago, at a previous employer, I had access to the SQL Monitor tool that came with TOAD. I could really use an alternative to this now, as we don't have TOAD here, and it would need to be free. I'm getting a problem with a forms update failing, and I really need to see what's actually being submitted to the database by the form. We're still stuck on Forms 6 by the way
View 2 Replies
View Related
Sep 4, 2011
I am working in EBS 11i and database 9i. I know that we can not write subquery in IF condition like below.
IF deptno IN (select deptno from dept)
--
END IF;
Is there any alternative to achieve above scenario.
View 1 Replies
View Related
Sep 21, 2012
I have the following query but it is taking too much time because of the LEFT OUTER JOIN on HST table which is a huge table , is there an alternative to LEFT OUTER JOIN that can be used to optimize the code:
SELECT HST.COMP_CODE,
HST.BRANCH_CODE,
HST.CURRENCY_CODE,
HST.GL_CODE,
HST.CIF_SUB_NO,
HST.SL_NO,
SUM(CV_AMOUNT) CV_AMOUNT,
[code].....
View 10 Replies
View Related
Sep 26, 2013
1)my csv file contain 100 records,i want to load 60 records into one table and reaming into another table with same control file by using sql*loader ?
2)how to load the alternative records into table by using sql*loader
View 1 Replies
View Related
Jun 12, 2010
I have a table with:
1 million rows
average row length 200 bytes
50 columns
and this update statement
UPDATE mytable SET varchar2_4000_column = replace(replace(replace...300 times)
It looks at every row in the table (no WHERE clause) and does these 300 replace operations on this column for each row. Each replace replaces with a null so effectively it is removing strings. Much of the time these strings are not in the column.
This update statement takes 25 minutes and it is 98% CPU and 2% USER_IO time.
I figure that is what is taking all the time since it is a CPU bound statement. if rows in this table are persistent over time then tag rows with a flag to show which ones have already been processed and skip these next time around.
View 2 Replies
View Related
Aug 25, 2012
My clients want to print the output of reports of size 25*12 and 15*12 in 11g and I am able to do the printing but the page break required for each page is not coming properly and because of this they cannoy identify which is the 1st and 2nd paper..
But in 6i D2K, there is the Live Previewer through which I can print without any page break issues.
My query is what is the alternative for Live previewer in Oracle Reports 11g so that i can make it right.
View 1 Replies
View Related
Dec 1, 2011
I need consultation on an alternative to a query that I wrote and seems very expensive.
I have the following table:
CREATE TABLE TRY_B
(TID NUMBER PRIMARY KEY,
INFO1 VARCHAR2(10),
INFO2 VARCHAR2(10),
GROUP_NO NUMBER,
TYPEID NUMBER);
[code]....
My objective is to get the data of group_no that is repeated more than once and which includes exactly one occurrence of typeid = 0.
My current query is:
SELECT * FROM TRY_B
WHERE GROUP_NO IN
(
SELECT GROUP_NO
FROM TRY_B
GROUP BY GROUP_NO
[code]....
which works but takes very long time with the real data as my real table is huge.
View 6 Replies
View Related
Feb 9, 2011
Any alternative to filer the Initial using a single REGEXP_REPLACE ?
Oracle version 10.2.xxxx
Currently using two REGEXP_REPLACEs.
SELECT REGEXP_REPLACE(
REGEXP_REPLACE('K I Rajuvan K I', '(([[:upper:]]{1,2})) ','')
, '([[:upper:]]{1,2})$','') CITY
FROM dual;
View 8 Replies
View Related
Oct 15, 2012
I'm using apex 4.1 and create a page with Interactive report with one default primary report and several default alternative report. when i selected one alternative report, how do i know which one is i picked up? it looks obvious to the user but my point is when the user switches to another page, or there are something should change based on the selected report, i want the user see the new page with-in the context of what have selected for that IR report. for example, i have a "summary" report based on year, so the alternative report will be year-2011, year-2012, etc, when user selected this year, 2012, on the same IR page, there is another report based on year 2012, when user selected 2011, that report should show 2011 report.
View 0 Replies
View Related
Aug 19, 2009
TABLE NAME :ABC
(PARTITION BASE TABLE - 84 Partition and each Partition has 500-800 Millions of Records)
/* Step1 Create MATERIALIZED VIEW LOG */
CREATE MATERIALIZED VIEW LOG ON ABC;
/* Step2 Create MATERIALIZED VIEW Refresh after Every 20 Minutes */
CREATE MATERIALIZED VIEW MV_ABC
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE+20/ (24*60) AS
SELECT * FROM ABC
WHERE TMSTP > SYSDATE-1;
Scenario:Intially data extracted will be "Greater than SYSDATE-1 = 18/08/2009 11:27:39 " - 76 Millions of Records After 20 Min of FAST Refresh, it will add 2500 Records
i)Is query in the MVIEWS (MV_ABC) SYSDATE-1 will be changed on each FAST Refresh, as observation (as per definition) it is incrementing the records to intial extracted data?
ii)As COMPLETE Refresh will be expensive to execute on the partition base table on each refresh.
Is there any alternative way to achieve the Nature of COMPLETE REFRESH in Materialized View?
iii)Is Delete or Conditional Delete option is possible in Materialized View (i.e not a delete from the base table but the delete from MVIEWS)?
iv)What is the machnism for populating the logs for MVIEWS?Is it overhead for the system and better than a Trigger based approach?
View 11 Replies
View Related
Aug 6, 2012
In my project, the below function retrieve output very slowly. Is there any alternative code to replace this function with join
CREATE OR REPLACE FUNCTION f_johnson (i_case_id number,i_seq_num argus_app.case_reference.seq_num%type) RETURN VARCHAR2 AS
c_ref VARCHAR2(32500) := null ;
CURSOR c_refer IS
[code]...
View 2 Replies
View Related
May 30, 2011
Is it possible to get the same name of file name from imported text file?
What i've is, I import the text file then I exported it.
can I get the same name of the imported text file?
View 2 Replies
View Related
Nov 3, 2012
i have two tablespaces dictionary managed (SYSTEM,APPLSYSX) i tried to change to locally cause it will cause problem in future when trying to run OATM migration.i did it successfully on APPLSYSX,when i did it on system upon oracle procedure.i have to change all tablespaces to read only when i did that with tablespace APPLSYSD(alter tablespace APPLSYSD read only) i received errors
SQL> alter tablespace APPLSYSD READ ONLY;
alter tablespace APPLSYSD READ ONLY
*
ERROR at line 1:
ORA-01230: cannot make read only - file 636 is offline
ORA-01111: name for data file 636 is unknown - rename to correct file
ORA-01110: data file 636: '/vol5u/oracle/prddb/9.2.0/dbs/MISSING00636'
i have not this file on the OS
View 1 Replies
View Related
Jan 22, 2008
I am trying to compile a .pc file with the make file which I created. But when I try to give make command I am getting following error
>make testfile
cc -o testfile testfile.c
"testfile.c", line 117: warning: no explicit type given
"testfile.c", line 119: warning: no explicit type given
"testfile.c", line 121: warning: no explicit type given
"testfile.c", line 122: warning: no explicit type given
"testfile.c", line 123: warning: no explicit type given
Undefined first referenced
symbol in file
sqlcxt testfile.o
ld: fatal: Symbol referencing errors. No output written to testfile
*** Error code 1
make: Fatal error: Command failed for target `testfile'
View 16 Replies
View Related
Mar 18, 2010
I have created the below stored procedure and calling the procedure in when-button-pressed trigger. Problem here is that when I cancel the file selection in file open dialogue box its raising exception.
PROCEDURE TEST
IS
buffer_lines client_text_io.file_type;
v_outputstr VARCHAR2 (32767);
p_delimiter VARCHAR2 (10) := '","';
v_transaction_no VARCHAR2 (10);
BEGIN
[code].......
View 3 Replies
View Related
Nov 12, 2012
During a duplicate process to a new database name, rman crashed after the restore but before the switch datafile all.So now, we have under ASM the data files under the correct (new) diskgroup but v$datafile contains the previous names (and so diskgroup) and v$datafile_header is empty. RMAN is completly lost, our solution is to manually rename each file under SQL*Plus using ALTER DATABASE RENAME FILE. Unfortunately, we are using or migrating to OMF, so file names are meaningless and we are unable to associate ASM files with database files.
Any way (query or anything else) to associate the ASM files to the database files. Here's an abstract of what we have for one (small) tablespace:
ASMCMD [+ORAXQG1_L136_DG1/ORAXPG1/DATAFILE] > ls -l N47CAW*
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE NOV. 12 10:00:00 Y N47CAW1.276.799152039
DATAFILE UNPROT COARSE NOV. 12 10:00:00 Y N47CAW1.318.799151641
SQL> select file#, name from v$datafile where ts#=17
2 /
[code]...
View 11 Replies
View Related
Aug 29, 2013
I have to load data file into a table. And the requirement is as below:
Input Data:
1234|20130815|20130822|This is a test, this is the the part
3456|20130823|20130809|This is a test
3456|20130823|20130809|This is a test
3456|20130823|20130809|This is a test
3456|20130823|20130809|Siva 1234
The data should be inserted only in two rows as below:
When Value in first 3 fields is same, 4th field should be appended to the existing value in table.
1234|20130815|20130822|This is a test, this is the the part
3456|20130823|20130809|This is a testThis is a testThis is a testSiva 1234
View 3 Replies
View Related
Sep 17, 2010
I am receive the REP-50127 error, cannot write to file when trying to save a report to a file on the network via report parameters.
I am guessing the rwserver does not have permissions to the network drive.
Will the SERVER_IN_PROCESS=NO run the rwserver process as the user executing the report?
View 1 Replies
View Related
Aug 15, 2012
I'm looking for a solution to hyperlink to a local file (i.e. C:/test.txt). I want that the user clicks on a "browse file" button like the file browse item. after the user selected a file or just a folder the path should be shown in a display only item. this item should be clickable...
Is it possible to create a "Browse file" dialog? I know that to link to a file is possible with some HTML tags in the "pre element text" and "post element text" fields. something like "<a href="... but I don't know how this works.
View 2 Replies
View Related
Apr 9, 2011
Oracle10g to Sybase12.5 Migration:- How a Oracle dump file can be converted to any text file/xls file which will be loaded in sybase database later through BCP.
means
1.Exporting objects as dump file from Oracle.
2.Is there any tool/process available that can convert this into csv/txt/xls file.
3.This files can be loaded in sybase.
View 2 Replies
View Related
Mar 30, 2008
I am trying to have sqlldr running against a file:
C:oratest20080318
Is it possible I get SQL*Loader-500: Unable to open file and 503 file not found just because the file name does not have an extension?
I can see that any file name I try it looks after whateverFileName.dat! Is there a way to have sqlldr working with files that do not have extensions?
View 22 Replies
View Related
May 4, 2010
after precompiling the cmpre.pc file, i got cmpre.c file. when i try compiling this .c file, am facing the following error...
servername:/.../home/usr/compile-> cc -g cmpre.c
ld: 0711-317 ERROR: Undefined symbol: .sqlorat
ld: 0711-317 ERROR: Undefined symbol: .sqlcxt
ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information.
View 1 Replies
View Related
