PL/SQL :: Pulling DDL Information For A Table

Nov 5, 2013

We are running Oracle 10g. I need to pull the DDL information from our Oracle tables. The following SQL statement returns the result as a "HUGECLOB". Is there a way to convert the result to text in varchar2 data type? Since tables being processed have numerous partitions, the DDL information for them is quite large, therefore, using substring would not be a viable alternative. 

[code]select DBMS_METADATA.GET_DDL('TABLE','EMPLOYEE') from DUAL; [/code]

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: Pulling All Records From The Customer Table?

Nov 21, 2012

I am writing a query to give me all CustomerID's in the report. I will give you the tables that I am trying to get the query from.

1. Customer table:
Custid (pk)
memberid
fname
lname
bname

2. Cust_cokeid table:
Cokeid (pk, fk)
Custid (pk, fk)

3. Cokeid table:
Cokeid (pk)

[code]....

Here is the query I have wrote:

SELECT customers.custid AS CUSTID,
customers.memberid AS MEMBERID,
customers.bname AS BNAME,
drpepper_rebate.drpepid AS DRPEPID,
drpepper_rebate.totalcarb AS TOTALCARB,
drpepper_rebate.totalncb AS TOTALNCB,

[code]....

I have more then 700 customer records in the Customers table, but so far I can only pull 500 records.

Reason I am trying to pull all the records from the customer table is because I want to find out who is missing cokeid and Drpepid.

View 7 Replies View Related

SQL & PL/SQL :: Pulling Data From A Stage Table

Dec 20, 2010

I am trying to insert data from tables that I build from a stage table. I have pasted my package body code below and error message.

create or replace
PACKAGE BODY etl_contact_sales
IS
PROCEDURE insert_contact_sales
IS
start_dt DATE := sysdate;
v_error_message varchar2(150);
v_error_block varchar2(30);
v_exception exception;
v_process_name varchar2(30);
[code]...

View 11 Replies View Related

Oracle Inventory / Sql Table Not Pulling Any Data

Aug 14, 2013

There are two oracle inventory tables, I found that used to pull up data before but not anymore . It is

�INVFV_INV_BY_PERIOD_AND_ITEM�

and �MTL_BIS_INV_BY_PERIOD� .

I need to use all the columns/data that are in these two tables. I am not finding any equivalent table. where can I retrieve these information from?

Select * from INVFV_INV_BY_PERIOD_AND_ITEM;
Select * from MTL_BIS_INV_BY_PERIOD;

View 3 Replies View Related

Pulling One Record For Each Page?

Dec 1, 2009

I have an alerts system for a web site built in an Oracle DB. There are multiple alerts that can be assigned per page, but I want to display only the most recent. I have 5 pages but each page can have different alerts.

I have a parent table of all the alerts and a child table with the page assignments. I want to pull one record (the newest) for each page assignment. The pages can increase so I can't hardcode this.

I keep thinking I need to do something like:

SELECTalertID, headline
FROMalerts_V
WHEREalertID IN (
SELECTdeptID
FROMso_departments
)

But it's obviously not quite what I need and not the correct syntax.

View 5 Replies View Related

Fetch All Information From Big Table For Those Key Present In Small Table

Jun 28, 2012

I am having two tables

Table 1 having 16 cror rows .
Table 2 having 1000 rows

I joined both the tables and fetch all inforamtion from big table for those key present in small table.Join query taking more time to fetch the rows .

View 2 Replies View Related

SQL & PL/SQL :: Partitioned Table Information

Jan 8, 2013

How to know weather table is partitioned or not ? Is there any data dictionary view to know the information about the Partitioned type,keys on table ?

View 4 Replies View Related

SQL & PL/SQL :: How To Get Information From Another Identical Table

Oct 16, 2010

adjusting a query. I have a table which looks like this:

CREATE TABLE PF.PF_ANOMALIES
(
ID VARCHAR2(10 BYTE) NOT NULL,
PLAN_ID VARCHAR2(10 BYTE),
CLIENT_ID VARCHAR2(10 BYTE),
SEDOL_CODE VARCHAR2(7 BYTE),
LTY_ID VARCHAR2(10 BYTE),

[Code]....

I have the following query that will return me the anomalies of client transactions (based on joining other tables for extra info too)

SELECT pc.client_id,
pc.client_name,
NVL(pa.sedol_code, 'N/A'),
DECODE(ps.stock_name, NULL,
DECODE(plt.lty_name, NULL, 'N/A', UPPER(plt.lty_name) || ' LEDGER - ' || pa.orig_price_ccy),

[Code]....

-- Get cash anomalies on top as these will have 'N/A' sedols I have another temp table that also gets populated with anomalies:

CREATE GLOBAL TEMPORARY TABLE PF.PF_ANOMALIES_TMP
(
ID VARCHAR2(10 BYTE) NOT NULL,
FVD_TYPE VARCHAR2(10 BYTE) NOT NULL,
CLIENT_ID VARCHAR2(10 BYTE),
SEDOL_CODE VARCHAR2(7 BYTE),

[Code]..

My problem is that I also want my query to return rows from this tmp table too.

View 17 Replies View Related

Server Administration :: How To View Table Constraints Information

Mar 13, 2010

I know that you can view all constraints in a table using $USER_CONSTRAINTS or $USER_INDEXES view. What i find difficult is that the information in those views is not really well presented.

I find it very hard to follow. For example, just to find out which columns are foreign keys and which tables they refer to is really tedious. Is there a simpler way to view all the constraints especially foreign keys of a table and including which table columns they refer to in the child table.

I like to see a diagramatic representaion of all the tables involved so i like to see both the parent and the child tables, which columns are related and using which FKs etc. is there an easier way to view this information?

View 4 Replies View Related

How To Delete Table Information In SQL Loader Log File Output In Oracle

Jan 19, 2011

I have a SQL Loader Log File Output. I would like to delete Table Information from the output file.Following is my code.

//PATH OPENED AS TEXT BY STREAM READER
System.IO.StreamReader strm = File.OpenText(batchFileName);
//OUTPUT

[Code]....

View 8 Replies View Related

SQL & PL/SQL :: Copy Supplemental Login Information From Renamed Table To New Tables?

Oct 19, 2010

I renamed 100 tables and recreated them, now I need to copy supplemental login information from renamed table to new tables. Environment is oracle 10G.

OS - Solaris

View 9 Replies View Related

Query MP3 ID3 Information?

Apr 25, 2007

Is it possible to query a mp3 file's ID3 information, or the properties where you can put in artist, album, etc. information?

View 1 Replies View Related

Changing A Lot Of Information In Database

Feb 18, 2011

I have been implementing a script to change a lot of data in a database production.Because of this the database will be 100% dedicated to the execution of that script, in the sense that nothing else will be running in this period (the application will be stopped).

what can i do to improve performance of that execution? is there any oracle manual online for this type of problem? I do not know if it's possible, but I'm thinking of things like disabling locking mechanism (if possible I could run instead of a process many processes in parallel), disabling index growing (during the process), disabling constraints.

View 1 Replies View Related

SQL & PL/SQL :: Fetch Information For A Foreign Key

Jul 13, 2010

the view from where we can fetch information for a foreign key viz. reference table, referenced columns etc.

View 3 Replies View Related

Security :: See Audit Log Information?

Dec 17, 2011

i enabled auditing by setting

audit_trail=db,extended in spfile,and restart the database and after that i issued command to audit as below :
audit select on emp by access;

but how to see audit log information(ie from which table),i checked dba_audit_trail but it doesnot have any record.

View 4 Replies View Related

SQL & PL/SQL :: Information On Package Contents

Oct 25, 2012

I am developing some automated test packages for my PL/SQL Packaged code. Going forward I can code the test package in conjunction with the code but I have some historic packages that I would like to develop these test packages for.

To save time I would like to employ oracle data dictionary views in order to construct the framework for my test package. This includes using SQL to get a list of procedures / functions within the package in order to create the test procedures (spec and body). I can do this in a basic way using the user_procedure view with something like...

SELECT 'PROCEDURE test_' || LOWER(procedure_name)
|| ' (p_result OUT VARCHAR2 IS BEGIN JTA.ACCOUNT_PROFILE_MAINT.' || procedure_name || ' END '
|| LOWER(procedure_name) || ';'
FROM user_procedures WHERE object_name = 'ACCOUNT_PROFILE_MAINT' AND subprogram_id != 0 ORDER BY subprogram_id;

However, the above only really works (in simplistic form.. without parameters) for procedures within the package. I would also like to be able to determine if the procedure listed is actually a function or procedure (so that I can alter the syntax accordingly to generate a correctly formatted string calling the program unit).

So, initially how do I determine the type of package program unit I have (Proc/Function)? Do I need to go to all_source to get this information or are there other views available I can join to?

Eventually I would like to extend this to be able to automatically include any parameters in the generated calling string.. again, is there any other option apart from all_source to get this information?

View 3 Replies View Related

Capture Information From AWR Snapshots

Oct 20, 2010

The sqlcache is getting over-written.I would like to capture information from AWR snapshots and feed those (as a workload) into the DBMS_ADVISOR.I can't see where it's possible (other than manually creating my workload from AWR information)

View 3 Replies View Related

PL/SQL :: Retrieve Primary Key Information

Dec 20, 2012

How can i retrieve which tables have composite primary keys, and only one of the primary key columns is a foreign key to another table?

Like:
CREATE TABLE club (
clubId NUMBER,
name VARCHAR(20) NOT NULL,
PRIMARY KEY (ClubId)
);
  
CREATE TABLE  team (
teamid NUMBER(10),
clubid NUMBER(10),
teamname VARCHAR(10) NOT NULL,
PRIMARY KEY (teamid, clubid),
FOREIGN KEY (clubid) REFERENCES club (clubid));So in this case, the team table

View 4 Replies View Related

How To Get Information Of Hidden Parameters

Sep 16, 2012

)How do you view the value of the parameter that is being used by instance? show parameter..?
2)How do you get the information of hidden parameters
3)What is the database object that stores information related to various types of db connections over network
4)How do you verify since when the db session is running
5)How do you verify the Originating machine details of the database session
6)How do you verify the name of program that the db session is running
7)What is the naming convenion of Base tables. Where is the information of base tables stored?
8)How are dynamic views created. Whre is the information of dynamic views stored?

View 8 Replies View Related

Redo Log Information - Arch Vs LGWR?

Oct 25, 2012

Redo log information can be transmitted in one of two ways from the primary database to the standby database: either by ARCH or LGWR.

1. when ARCH involves
2. when LGWR involves
FAL_Client = (Should i enter net service name or Db name or service_name
FAL_Server = Should i enter net service name or Db name or service_name )
FAL_CLIENT='whichone'

[code]...

View 9 Replies View Related

Sequence Nextval Storage Information?

Dec 23, 2012

Sequence nextval is a pseodocolumn, but where the value of nextval is stored and why before we use CURRVAL for a sequence in our session, we must first initialize the sequence with NEXTVAL?

View 5 Replies View Related

SQL & PL/SQL :: Hierarchical Query To Get Metadata Information?

Mar 5, 2010

i'm trying to display the heirarichal relationship between the tables (parents-child-subchild).

[b]table structure[/b]
DEPT
|PK-DEPT_ID
|
EMP
|pk-EMP_ID

[code]....

Expected output

table_name path
DEPT DEPT
EMP DEPT/EMP
EMP_AUTHORIZATION DEPT/EMP/EMP_AUTHORIZATION
EMP_AUTHRIZATION_CARD DEPT/EMP/EMP_AUTHORIZATION/EMP_AUTHRIZATION_CARD
EMP_AUTHRIZATION_DUP DEPT/EMP/EMP_AUTHORIZATION/EMP_AUTHRIZATION_CARD/EMP_AUTHRIZATION_DUP

but by using below query i am getting complete heirarichy.

SELECT LEVEL,
Table_Name,
Constraint_Name,
R_Constraint_Name ,
SYS_CONNECT_BY_PATH(Table_Name, '/') Path

[code]....

View 6 Replies View Related

SQL & PL/SQL :: Clause Retrieving Information From Varray

Apr 12, 2012

select *
from
table(it_count_tc('bbs_swe_tve_db'))

gives this output:

COLUMN_VALUE
------------
0
2
37
8
0
1
0
0
1
3
0
0

12 rows selected

BUT: what I what is like this

Jan Feb Mars April May ...
0 2 37 8 0 ...

it_count_tc is a function returning a varray of 12 integers input is a varchar2 string

View 2 Replies View Related

SQL & PL/SQL :: Version Wise Objects Information?

Dec 11, 2012

Is there any way to capture the version wise objects (Packages) information from the oracle data base.

Ex : I have package P1 which was created on 01-NOV-2012 with version 1 . After 10 days same package has been updated version 2 with some enhancements.Like that the package will be updated according to the latest requirement.

Now I need to capture the total audit trail history of the package with version wise specific changes and when changes has been occurred hoe can I achieve this?

View 2 Replies View Related

Security :: Audit Trail Information?

Jun 7, 2011

I am trying to setup logon/logoff auditing for our databases which reside in 9i and 10G on sun solaris servers. I am asked to turn on auditing sending the audit data to syslog! How exactly do you do that?

View 3 Replies View Related

SQL & PL/SQL :: Taking Selective Information From String?

Jan 31, 2011

I want to take selective output from String input like

Input:'Six Thousand * Four Hundred Seventy Two Rupees And Zero'

Output:'Six Thousand Four Hundred Seventy Two Rupees'

I am using substr and instr functions but not getting the desired output.

View 22 Replies View Related

Client Tools :: How To See Information Before Commit

Feb 24, 2013

I want to see the pending operations before commit. How can I view what operations are going to commit when I enter the commit.

View 7 Replies View Related

Forms :: Getting Hardware Information In Oracle 6i?

Feb 9, 2010

How Can We Get Hardware Information In Oracle forms 6i for example harddisk size,volume,processor etc.

View 1 Replies View Related

How To Collect Synonym Information And Compare

Oct 29, 2013

We are making Oracle Insight Application based on Oracle 11gR2 DB.We have two DEV environments. On Oct 17, we had trouble in one DEV environment.From dba_common_audit_trail , we found some user did drop public synonym statement and we doubt it as cause of the problem. I would like to collect synonym information of the two DEV environment.1How could I do that ?2And we are thinking of using Logminer to investigate the cause of problem.Logminer is usable to investigate the cause of problem if we have the REDO log of Oct 17 ?

View 5 Replies View Related

PL/SQL :: How To Retrieve Information About Columns Of Tables

Nov 16, 2012

I am trying to get information about the columns of tables in Oracle databases. In MySQL, show columns provides good deal of information.But in Oracle, we do not have such equivalent call."describe" shows only the primary key.

But it does not show whether any column has unique constraint or an index.

I created a column in a table with unique constraint (but not a primary key), but it was not listed with CONSTRAINT_TYPE = 'U' in dba_constraints view, but was listed with CONSTRAINT_TYPE = 'C' - Check constraint.

View 6 Replies View Related







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