Accessing File From Directory?

Oct 7, 2009

have a log file in unix server under(/usr/home/oraj/log/abc.log) I am trying to access from oracle stored procedure as fallows

src_clob BFILE := BFILENAME('/usr/home/oraj/log', 'abc.log');

Exception:
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 716
ORA-06512: at "usr.LOAD_CLOB_FROM_XML_FILE", line 39
ORA-06512: at line 1

I dont want to create directory as such ('/usr/home/oraj/log') as it is already exists and all log files sits there.

CREATE OR REPLACE PROCEDURE Load_CLOB_From_XML_File
IS
dest_clob CLOB;
src_clob BFILE := BFILENAME('/usr/home/oraj/log/', 'abc.log');
dst_offset number := 1 ;
src_offset number := 1 ;

[code]...

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Accessing Scripts From Different Directory?

Jun 30, 2010

I have customized my sqlprompt. I put the code for that in a script p.sql.

Now I log into sql from multiple directories as I have different scripts in different directories. How do I access p.sql which is not in the currect directory.

Also I wanted to know if I could change directories from the sql prompt.

View 6 Replies View Related

JDeveloper, Java & XML :: Accessing XSD File Through PL/SQL

Feb 9, 2011

I wanted to know whether is there any utility which can

1. Create table from xml /xsd file.
2. insert records into the newly created table, through the given xml file.

View 1 Replies View Related

SQL & PL/SQL :: How To Get The File Name From Directory

Dec 12, 2012

How to get the file name from directory without Java. Is there any way to do it in Pl/Sql.

View 13 Replies View Related

SQL & PL/SQL :: Generates CSV File In Directory

Feb 17, 2012

This generates a CSV file in an directory. When it runs for the 1st time it take SYSDATE -7 data. After the 1st run it appends the data since the last run. Now my problem is the appending happens at the bottom of the file and every time the header appears. I want the header only the first time and the new data should keep appending at the top so that i get the latest data always at the top.

View 18 Replies View Related

There Is No File Audit In Adump Directory

Jan 6, 2013

I have an issue when I enable auditing feature in oracle,I use 'DB' to store the audit from user in oracle, my command is

SQL>alter system set audit_trail=db scope=spfile;
SQL>shutdown immediate;
SQL>startup;

in show parameter audit, I see that the audit_trail already changed to DB after I create a user and enable audit for this user, I can see in the audit table dba_audit_trail for insert, update, delete,etc but If I am not wrong, if I using DB to setting the audit parameter then I will get audit file both in oracle table and in adump oracle folder installation, but in my case I cannot find any file in adump directory.

my operating system is windows7 and I use oracle database 10g express edition

View 8 Replies View Related

Enterprise Manager :: Setup - No Such File Or Directory

Jun 17, 2010

This is the error message iam getting when trying to open EM... Im new to DBA and also to this DB....

[sandeep@Linux4 ~]$ emctl start dbconsole
TZ set to US/Eastern

Unable to locate file /home/sandeep/oracle/product/10.2.0/db_1/Linux4.localdomain.com_orcl/sysman/config/emd.properties : No such file or directory

View 16 Replies View Related

PL/SQL :: Directory List - How To Read File Names

Jun 7, 2013

i have one directory in this directory contains 100 file these files are related to table structured creation with dependence

so how to read file names from directory and executed in scheme with logs also

procedure or script

View 5 Replies View Related

Forms :: How To Get File Directory Path Dynamically In The Report

Sep 6, 2013

I want to get file directory path dynamically with out using directory in database or not hard code like below

the purpose is i need to check image in the path directory if not found i unable to generate report, is there any possible to get dynamically

function CF_URLFormula return Char is
v_handle utl_file.file_type;
v_file_dir VARCHAR2(60) := '/u002/app/applmgr/temp/';
begin
v_handle := utl_file.fopen(v_file_dir, :photo_name, 'R');
utl_file.fclose(v_handle);
RETURN v_file_dir||:photo_name;
exception
when others then
srw.message(100,SQLERRM);
return null;
end;

View 1 Replies View Related

Forms :: File Transfer From Server Directory To Client?

Oct 3, 2013

I write a file on database server by following

CREATE OR REPLACE DIRECTORY TEST_DIR AS 'c: emp'
GRANT READ, WRITE ON DIRECTORY TEST_DIR TO myuser
SQL> select * from all_directories;

OWNER DIRECTORY_NAME
------------------------------ ---------------------
DIRECTORY_PATH
----------------------------------------------------
SYS TEST_DIR
c: emp
SYS PUBLIC_DIR
E:PUBLIC_dir

and then

PROCEDURE run_query(p_sql IN VARCHAR2
,p_dir IN VARCHAR2
,p_header_file IN VARCHAR2
,p_data_file IN VARCHAR2 := NULL) IS

[code]...

but I get error wut_118.

View 1 Replies View Related

Forms :: Webutil File Transfer Directory Listing

May 25, 2010

Is there anyway to get a listing of the files in a specified directory on an ftp server using webutil?

View 2 Replies View Related

Listener File Rapidly Increasing In Diag Directory Of 11g

Jul 6, 2012

I noticed that listener file in diag/trace is rapidly increasing. I am using 11g Rel 1 on window 2008 server Its 1 GB in couple of days. Is there any way if I can turn it off ?

View 5 Replies View Related

Open Excel File From Oracle Directory On Local Machine

May 31, 2013

I have an application where I generate a excel using data in a table and write excel file in oracle directory.Now my requirement is to open that file from local machine .How can i achieve it.I am using Apex 4.2,Oracle 10g .

View 1 Replies View Related

Forms :: Copy Image Or Text File From Source Directory To Destination

Oct 31, 2011

how do i copy an image or a text file from ex: directory c to directory d

View 5 Replies View Related

Application Express :: Automatic Save File From Apex To User Local Directory

Jun 13, 2012

how can save pdf file from apex to user local directory on button click?

View 2 Replies View Related

Export/Import/SQL Loader :: Setting For External Table - Create File In Directory DIR-1?

Nov 20, 2012

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

I am new in external table so i have tried following cmd.

create directory dir_1 as 'E:ora_dirt' ;
grant read, write on directory dir_1 to HR;
select * from all_directories;
create table emp_ext
(emp_id number,
emp_name varchar2(30)

[code]...

since I am not able to see DIR_1 in E: drive due to which i havnt created  'emp.dat' file and on executing select on external table i m geting expected error *"ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04043: table column not found in external source: EMP_ID"*

how to create that file in directory "DIR_1" .

View 2 Replies View Related

Accessing Something From Table Not Joined To Others

Jun 17, 2008

I have a schema whereby a table is not joined with other tables.

the info on that table can be gotten manually (by doing a query) and then using that info in another query. so is there a way of getting info from that table?

View 8 Replies View Related

SQL & PL/SQL :: Accessing Dmp Via External Table

Aug 5, 2011

when i am writing dump from external table, it is accessing records from dump.but when i am trying to access other dumps(create thru expdp) it is giving error.the logic i am following is mentioned below-

CREATE OR REPLACE DIRECTORY "DIR_GMS" AS 'D:Gopal_works est_env_files'

GRANT READ ON DIRECTORY dir_gms TO gopal;
GRANT WRITE ON DIRECTORY dir_gms TO gopal;

-- creating dump file in directory

CREATE TABLE emp_ext
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dir_gms
LOCATION ('emp_ext_dmp.dmp')
)
[code]......

i am able to see records.

New point:
-- taking export thru expdb
expdp hr/hr tables=EMPLOYEES directory=DIR_GMS dumpfile=HR_EMP.dmp logfile=expdpEMP.log
then i created one EXTERNAL TABLE TO access it.

CREATE TABLE emp_xt (
EMPLOYEE_ID NUMBER(5),
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50))
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dir_gms
LOCATION ('HR_EMP.DMP')
);

while accessing, it is giving error:

SELECT * FROM EMP_XT

ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
ORA-31619: invalid DUMP FILE "D:Gopal_works est_env_filesHR_EMP.DMP"
ORA-06512: AT "SYS.ORACLE_DATAPUMP", line 19

View 13 Replies View Related

SQL & PL/SQL :: Accessing XML Values From A Table?

Jan 29, 2013

i have a table which has 2 columns.1st column has userId and the other contains an xml data as a link.on clicking that link a new file opens containing the data in xml format.

<fields>
<field key="Public Email">piyush@chand.com</field>
<field key="Location">bangalore</field>
<field key="Website" />
<field key="Birthday">0001-01-01 00:00:00</field>
<field key="Gender">Male</field>
<field key="Language">English</field>
</fields>

i need to access location of a particular userId.How can i do that?

View 6 Replies View Related

SQL & PL/SQL :: Using Index But Not Accessing Record

Jun 20, 2010

i'm create table

CREATE TABLE EQU_PARAM_MONITORINGX
(
SERIAL_NO VARCHAR2(32 BYTE) NOT NULL,

[Code]....

why accessing record not using index, but using full table scan?

View 2 Replies View Related

Accessing Oracle 11g R2 From A Client

May 2, 2013

I have installed Oracle 11g R2 on Oracle Linux and Oracle Instant Client on Windows 7. I am trying to access the server from the Oracle client but I am getting the following error:

ORA-12560: TNS: protocol adapter error

I have set the below TNSNAMES.ORA file on the client machine and the LISTENER.ORA file on the server:

#TNSNAMES.ORA

OCCELLUS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle.localdomain)(PORT = 1521))
)
CONNECT_DATA =
[code]........

The ORACLE_SID is OCELLUS.

The host name is oracle.localdomain

Pinging between the client/server works fine. I have turned off the firewall on both the client (Windows firewall) the Server (IPtables) but got the same problem!

I have also set the ORACLE_HOME, TNS_ADMIN and PATH in the Windows environment variables.

View 14 Replies View Related

SQL & PL/SQL :: Accessing Data From Server By Using DBLINK

Oct 22, 2010

We are accessing data from the server ADM.WORLD by using DBLINK.We got the following error.

PL/SQL: ORA-04052: error occurred when looking up remote object
sysadm.PS_HP_INC_ELIG_VW@ADM.WORLD
ORA-00604: error occurred at recursive SQL level 1
ORA-28000: the account is locked
ORA-02063: preceding line from ADM

For that we checked in the server ADM.WORLD for the account the account is showing locked .After that we successfully accessed the object sysadm.PS_HP_INC_ELIG_VW@ADM.WORLD.

For next day also the account is locked.Why the account is frequently locking.

View 15 Replies View Related

SQL & PL/SQL :: Accessing A Table Contains Array In Remote DB?

Jan 30, 2009

How to access (create Synonym and Materialized View) a Table contains an Array of type object which is on Remote Database connecting through DBLink.

View 27 Replies View Related

Application Express :: Accessing APEX_COLLECTION

Jun 6, 2013

I am very new to APEX_COLLECTION. I have problem in accessing APEX_COLLECTION that I created. Below is the pl/sql code I have written:

declare
l_query varchar2(200);
cname varchar2(300);
Begin
APEX_COLLECTION.DELETE_COLLECTION(p_collection_name => 'ACTION_NAMES');
l_query := 'select name from test_table where id in (''n406'' , ''n409'' , ''d080'' , ''o4505'' , ''a1593'')';
[code]........   

It is throwing following error on execution:

ORA-01422: exact fetch returns more than requested number of rows I want to display the names collected using APEX_COLLECTION package and also use it in further processing within the pl/sql code block.

Apex info:

Application Express 4.1.0.00.32
DB details - Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Web server architecture - APEX listener
Browser(s) and version(s) used - Chrome version 24/ Firefox version 3.6 and version 18

View 4 Replies View Related

SQL & PL/SQL :: Accessing V$mystat Gives Table Or View Does Not Exist

Jul 1, 2010

I am using the sid of v$mystat to create a unique filename in my pl/sql procedure.

I have granted access to v$mystat to the user that is accessing it from system user as:

SQL>GRANT SELECT ON V_$MYSTAT TO ar;
Grant succeeded.

SQL> commit;

Commit complete.

now when i login as user ar and do a select on v$mystat it works fine:

SQL> select sid from v$mystat WHERE ROWNUM = 1;

SID
----------
290

However, when i do the same from my PL/SQL procedure it throws an error saying :

SQL> @FILECREATE
53 /

Warning: Function created with compilation errors.

SQL> show errors
Errors for FUNCTION FILECREATE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
22/8 PL/SQL: SQL Statement ignored
22/35 PL/SQL: ORA-00942: table or view does not exist

My PL/SQL function can be found as an attachment.

View 11 Replies View Related

SQL & PL/SQL :: Format For Accessing Data From Other Database Or Schema?

Aug 25, 2011

Can we use this format for accesing data from other DB or Schema?

In From clause

database_name.schema_name.table_name

View 7 Replies View Related

Enterprise Manager :: Accessing OEM From A Remote Machine

Jan 25, 2011

I am having 3 oracle 10.2.0.5 version standard edition databases running on windows platform on 3 different servers. OEM is configured for all the 3 databases and we are able to access these OEM from their respective servers.

As per my knowledge, I should be able to access the OEMs of all 3 databases from my local machine. But Iam facing problem in accessing the OEMs from my local machine.

what changes need to be done so that I can access the OEMs of all 3 databases from one single local machine rather than checking it by logging into their respective servers.

View 7 Replies View Related

Replication :: Block Corruption While Accessing Some Table

Mar 16, 2011

The database is running in archivelog mode and we have a standby with Maximum performance.There is no RMAN backup..We have noticed there is block corruption while accessing some tables.Now i would like to know are the corrupted blocks also replicated to the physical standby? Is there a way to recover the data from these corrupted blocks without shutting down the database ?

View 1 Replies View Related

SQL & PL/SQL :: Create Materialized View For Accessing Data

Feb 29, 2012

1.As we can create materialized view for accessing data from other schema but same database. will it be effective or it will act as a normal view.

2.Will materialized views can be created in Fast mode for the above scenario?

View 4 Replies View Related

Forms :: Poor Performance In Accessing Remote Database?

May 13, 2010

I have installed Oracle 10g on one system and Oracle developer on another machine, means i have different machines for DB Server and Application server. It all working excellent inside the company premises, but if i want to access my Oracle DB and application server outside the company then it gives me problem..how to access application (forms and reports ) remotely outside the company...having same db.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved