SQL & PL/SQL :: Accessing A Table Contains Array In Remote DB?
Jan 30, 2009How 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
ADVERTISEMENT
Enterprise Manager :: Accessing OEM From A Remote Machine
Jan 25, 2011I 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.
Forms :: Poor Performance In Accessing Remote Database?
May 13, 2010I 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 RelatedAccessing Something From Table Not Joined To Others
Jun 17, 2008I 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?
SQL & PL/SQL :: Accessing Dmp Via External Table
Aug 5, 2011when 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
SQL & PL/SQL :: Accessing XML Values From A Table?
Jan 29, 2013i 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?
SQL & PL/SQL :: Accessing V$mystat Gives Table Or View Does Not Exist
Jul 1, 2010I 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.
Replication :: Block Corruption While Accessing Some Table
Mar 16, 2011The 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 RelatedSQL & PL/SQL :: Insert Statement In Trigger Accessing And A Select From A Table
Jun 6, 2010In a trigger(on update of a table t1) I am trying to write, I am doing an insert on t2 accessing ':new' values of the update on t1.
But in my Insert statement, I am having get one of the column values from another table. How can I write my insert statement in such a way as to insert values contained in ':new' pseudo columns and a select from another table. Below is my insert statement in the trigger :
-------
IF (:old.GROUP_YELLOW <> :new.GROUP_YELLOW) THEN
INSERT INTO TEST.W_THRESHOLD_LOG
(THRESHOLD_LOG_WID, CHANGE_DATE, MEASURE_TYPE_WID, MEASURE_NAME, CUSTOMER_WID, CUSTOMER_NAME, USER_ID, CHANGED_ITEM, PREV_VALUE, NEW_VALUE)
VALUES(TEST.W_THRESHOLD_LOG_SEQ.NEXTVAL, SYSDATE, :new.MEASURE_TYPE_WID, 'Rolling Stabilty' , :new.CUSTOMER_WID, 'Customer1', 'User1', 'GROUP_YELLOW', :old.GROUP_YELLOW , :new.GROUP_YELLOW);
END IF;
-------
In the above code if the hardcoded value 'Customer1' need to be picked from another table,
i.e .
SELECT NAME FROM W_CUSTOMER_DIM WHERE CUSTOMER_WID = THRESHOLD.CUSTOMER_WID
how can I rewrite my query to the above value from the select into my insert statement..?
SQL & PL/SQL :: Writing Procedure In User 2 Schema Accessing Table Product - Not Compiled?
Dec 17, 2010I have got 2 users as user1 and user2.I have used the following statements from user 'user1':
create role GENEVAOBJECTS;
grant select, insert, update, delete on PRODUCT to GENEVAOBJECTS;
grant GENEVAOBJECTS to user2;
In the above statements, product is a table. Now, I could able to access this table from user 'user2'. But however if I write a procedure in user2 schema accessing the table product, then the procedure is not getting compiled.
create or replace procedure test_prc as
v_test number(9);
begin
select product_id into v_test
from PRODUCT where rownum=1;
[code]...
why I cannot access that table from procedure?
PL/SQL :: Insert Values Into A Nested Table Or Array With A Loop
Oct 8, 2013How to create this pl/sql process to add elements to a nested table or varray within a loop. Here's the scenario: I have an apex package that has some pl/sql processes and some stored procedures. I am dealing with Inspection Areas. An Inspection Area has several sectors. I already have the loop that lists all the Inspection Areas and a loop inside that loop that lists all the sectors. There is an if statement that determines whether or not the sector name gets stored in the varray or table. I am not sure how to correctly do this and am not sure whether to use a nested table or varray. I've posted somewhat of a pseudo coded example below
If (you_belong_in_table) then
variable := store_me_in_varray /* OR */
variable := array_type(sector.sector_name)
i := i + 1;
end if;
/* Now we output our varray or table */
start loop
output(sector names one by one)
end loop I hope this makes sense. I more so just need the syntax to be able to continually added values to a table or varray while I'm already inside a loop; and also how to output those values end the end as well.
PL/SQL :: Uses And Advantages - Associative Array / Nested Table And Varray?
Jun 4, 2013What is the real time uses for Associative array, nested table and varray ?
View 2 Replies View RelatedPrecompilers, OCI & OCCI :: Receiving Records Of Table In Array Of Structure
Mar 12, 2009sample code in OCI in C for receiving records of table in array of structure? Or dynamically storing the result-set in an array..using array of pointers to structure..
View 6 Replies View RelatedPrecompilers, OCI & OCCI :: How To Pass Array Of Strings To And Return Table Set From Proc
Jan 24, 2011I have to write a PL/SQL procedure, which is supposed to take an array of strings as input. This array will have simple strings as elements, like
'000887S','000780S'.
Now I have a query in the procedure, which will return a row, for each of the array elements. For example:
SELECT
su.EMPLOYEE_ID,su.FIRST_NAME,
su.LAST_NAME
FROM
USERS su,
[code]......
In the place of the '?' in the above query, the array elements have to be passed. So we will get one row from the above query for each array element.
Now we either have to loop through the array elements to fetch the result set for the above query for each array element, or we can use some other method too. Our objective is to collect all the rows of the above query for each array element as a table data and this procedure has to return this table set.
what will be the best way to pass such a set of data to the proc and best way for the proc to return this result set. Like we can use arrays, table type data,ref cursors, etc.
SQL & PL/SQL :: Calling A Remote Table In SP?
Feb 21, 2011I have created a remote link to a sql server db in oracle 11.0.7 works fine in sql plus. I can select tables by select * from table@linkname...the link is a public link But when I put it in the procedure it says invalid table name. then I created a public synonym for the table - i can select from the synonym in sql plus - but in the procedure it says synonym translation invalid...Also I cant grant any privileges to the synonym as it says ddl operations are not allowed on remote objects
View 1 Replies View RelatedDBLink Not Querying Remote Table
Apr 16, 2013I successfully created the following DBLink in my DEV environment:
CREATE PUBLIC DATABASE LINK PROD
CONNECT TO USER1 IDENTIFIED BY pwd98
USING 'PRD';
I have a table in both schemas. In PROD it has 0 recs, in DEV it has 134 recs.
select count(*) from hold@PROD.
The above query gives me 134 recs instead of 0!
Here is my TNSNAMES entry for PRD:
PRD=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=localhost)
(PORT=1529)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=PRD_USR)
))
SQL & PL/SQL :: Remote Grant Through Insert Statement Into Sys Table?
Jun 18, 2013I want to grant a privilege through an insert statement into a sys table.Why do not grant the privilege through the classic way : grant select on t to l_user; ?
Because I want to do it remotely.I am connected to db1.I want to grant select on t2 to u2_b from u2_a.I assume that all DDL are DML. So a grant is equivalent "somewhere" to an insert.I tried to do my requirement locally, and here is the output.
SQL> conn scott/aa
Connecté.
SQL> -- step 1 : try to grant "normally" a select on dept to hr from scott
SQL> grant select on dept to hr;
Autorisation de privilèges (GRANT) acceptée.
SQL>
SQL> conn sys/a as sysdba
Connecté.
SQL> -- step 2 : Then, we connect to sys to see the row inserted in dba_tab_privs
SQL>
SQL> col GRANTEE format A10
SQL> col OWNER format A10
[code]...
Then if I can do it locally, I can do it remotely through a db link.
SQL & PL/SQL :: Pass Object (or) Table As Argument To Remote Procedure?
Jul 18, 2012Does it possible to pass object (or) table as an argument to a remote procedure?
View 2 Replies View RelatedSQL & PL/SQL :: Ftp File To Remote Server By Reading Data From Table?
May 1, 2012I need a way to ftp file to remote server by reading data from table. I searched a couple of sites which asked me to use Chris xutl_ftp package..but unfortunately the site is no accessible..
Here is the code
CREATE OR REPLACE PACKAGE UTL_FTP
AUTHID CURRENT_USER
AS
/**
* LICENSE: GNU Lesser General Public License (LGPL)
* Copyright (C) 2003-2006 Russ Johnson (john_2885@yahoo.com)
[code].....
Accessing File From Directory?
Oct 7, 2009have 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]...
SQL & PL/SQL :: Accessing Scripts From Different Directory?
Jun 30, 2010I 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.
SQL & PL/SQL :: Using Index But Not Accessing Record
Jun 20, 2010i'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?
Accessing Oracle 11g R2 From A Client
May 2, 2013I 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.
JDeveloper, Java & XML :: Accessing XSD File Through PL/SQL
Feb 9, 2011I 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.
SQL & PL/SQL :: Accessing Data From Server By Using DBLINK
Oct 22, 2010We 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.
Application Express :: Accessing APEX_COLLECTION
Jun 6, 2013I 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
SQL & PL/SQL :: Format For Accessing Data From Other Database Or Schema?
Aug 25, 2011Can we use this format for accesing data from other DB or Schema?
In From clause
database_name.schema_name.table_name
SQL & PL/SQL :: Create Materialized View For Accessing Data
Feb 29, 20121.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?
Windows :: Accessing Oracle 7 Database From Server 2008
Jan 11, 2013We have been upgrading our servers to Server2008 and are getting..
[ORA-3134: Connections to this server version are no longer supported.]
..using the drivers we used to use in XP and Server2003 to access a legacy Oracle7 db. Connections to this db are needed for typical CRUD functionality by multiple applications, some written in Classic ASP and some in C# .NET 3.5 & 4.0. I have tried ODBC drivers (System.Data.Odbc) and also ODP (Oracle.DataAccess.Client) to no avail.
Any existing driver solution to make this connection without have to resort to a custom HLI interface?
I would think we aren't the only ones needing to access Oracle7 from Server2008.
Server Administration :: System / Manager Not Accessing In Oracle9i?
May 31, 2010Recently i am installed oracle9i in my laptop.In oracle scott/tiger accessing. But system/manager not accessing.
View 4 Replies View Related