Sort Through Schema Tables To Find If Particular Vendor Number Exist

Nov 14, 2006

I would like to be able to sort through a schema's tables to find if particular vendor numbers exist. The decode cross tab works fine, except that it needs static data for each table. How can I code a PL/SQL to populate the table_name and give a count for each row? See example below:

col table_name format a15
set numwidth 10
set lines 1000
set pages 50

select b1.table_name,
count(case when a.vendor='86444' then 1 else null end) "86444",
count(case when a.vendor='86445' then 1 else null end) "86445",
count(case when a.vendor='86469' then 1 else null end) "86469",
count(case when a.vendor='86470' then 1 else null end) "86470",
[code]........

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Query To Find List Of Tables Contains Particular Data In Schema?

Feb 3, 2012

I want to list the tables in a schema which contains a particular value or data.

View 2 Replies View Related

Application Express :: Interactive Report Sort Text As Number?

Jul 10, 2012

it is possible in an Apex interactive report to sort text as number?

For example, a series of text rows:

4
10
2

would sort as:

10
2
4

if it were text. What I would like to see is:

2
4
10

View 4 Replies View Related

Reports & Discoverer :: How To Find Page Number And Total Number Of Pages

Jan 26, 2010

i am using oracle developer 6i report builder i required this type of query

example

if (:page number LIKE '1')
then
srw.set_text_color('darkred');
end if;

return (TRUE);
end;

but page number is not my table database item how can i use builtan page &<pagenumber> use for conditional format.

View 34 Replies View Related

Vendor Code 17002 - Unable To Login To Database

Feb 21, 2011

I am unable to login to my database. I get an error that says "Io exception: the network adapter could not establish the connection" "vendor code 17002".

View 5 Replies View Related

SQL & PL/SQL :: Count A List Of Number Value And Find Maximum Number?

May 21, 2010

how do I count a list of number value eg 1,1,1,1,3,3,6,4 and find the one with maximum number which is 1

View 5 Replies View Related

SQL & PL/SQL :: Transform NOT EXIST Subquery In LEFT OUTER JOIN With Nested Tables?

Mar 22, 2012

these are the sample data :

CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(64);
CREATE TABLE department (
courses CourseList)
NESTED TABLE courses STORE AS courses_tab;
INSERT INTO department (courses)VALUES (CourseList('1','2','3'));

[code]....

The query returns the correct data, CourseList that are not subset of any other CourseList of the table.

I am trying to convert this not exists in a left outer join query to check if the performance is better, but I don't know how to do it.

I was making some variations of this code :

select d1.courses c_1, d2.courses c_2
from department d1,department d2
where d1.courses<>d2.courses(+);

but it is now working.

View 3 Replies View Related

Find Schema Stats Lock Or Unlock

Apr 20, 2013

How to find schema stats locked or unlocked?

View 3 Replies View Related

SQL & PL/SQL :: Create Tables From Different Schema?

Mar 18, 2012

I have created 2 different databases and each have their own schema user for eg we have database ALIVE with user allive and the other database RLIVE with user rllive , actually we have implemented new module in ALIVE database and in the process we created many tables , synonyms , index and other objects now we want to list out all the tables ,sequences as scripts which are not present in RLIVE and create them in RLIVE as new objects.

View 3 Replies View Related

PL/SQL :: Trigger From Other Tables Of Same Schema?

Jan 14, 2013

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit

i'm trying since some hours to get information from inside a trigger from another table of the same schema.My trigger table is PSE_BKB.NUM_PHANTOM_BP

First i tried simple solution ...

CREATE OR REPLACE TRIGGER PSE_BKB.NUM_TR_PHANTOM_BP
BEFORE DELETE OR INSERT OR UPDATE
ON PSE_BKB.NUM_PHANTOM_BP
REFERENCING NEW AS NEW OLD AS OLD

[code]...

The last version i tried was with a cursor definition inside the trigger as in the code-block below.For debugging purposes i've inserted a RAISE_APPLICATION_ERROR in the inner loop - see below. The variable v_obj_key is never set, like in all other variations i tried - i alway see the predefined 'gugus' from the declare section.

It seems oracle cannot read from other tables at this point. The :old.phantom_key is set (in this simple example there should come one obj_key back).

trigger:

CREATE OR REPLACE TRIGGER PSE_BKB.NUM_TR_PHANTOM_BP
BEFORE DELETE OR INSERT OR UPDATE
ON PSE_BKB.NUM_PHANTOM_BP
REFERENCING NEW AS NEW OLD AS OLD

[code]...

View 11 Replies View Related

Performance Tuning :: How To Find All Indexes Scripts In Particular Schema

Aug 16, 2012

getting all indexes script in particular schema.

View 4 Replies View Related

Create One Trigger Before DML On All Schema Tables

Nov 28, 2011

We have an application is must be connected to our database for specific requirements in our company but this application has a very bug thing as we must write the super DB password "Like HR password as example" clear in some files and these files must be shared so developers can use the HR password to do any action !!! I know that this application is a problem but we have to install

I can do this by creating trigger on each table will restrict DML. As example: if the operating system user is XXX, the trigger restrict the action. But not logic at all to create more than 1000 triggers on schema (This will impact badly on DB performance).

So, i need to create one trigger to fire before doing any DML on all schema tables. As example: If "MMM" the administrator operating system user trying to do insert action, he can do the action. BUT If "DEV" the developer operating system user trying to do insert action, the trigger must fire here to restrict this action.

Be noted also, i need this trigger not depend on any specific tool like Toad as any user can simply rename the exe file for toad then he can pass the trigger. At least, trigger must depend on (Operating system user & Action_type)

View 11 Replies View Related

SQL & PL/SQL :: Get Row Counts Of Tables In Schema Without Using Num_rows

Aug 9, 2010

I am trying to get a row count(*) for all the tables in my schema. The NUM_ROWS column in DBA_TABLES is not appropriate in this case because they are as good as the last analyze. So I need to get real time counts.

I tried the following code but I can't seem to catch my error.

DECLARE

l_sql varchar2(150);
cursor tablelist is
select table_name from dba_tables where owner = 'ME';

[Code]....

My expected results are :

TABLE_NAME ROW_COUNT
---------- ----------

View 13 Replies View Related

Server Utilities :: Find All Objects Of Schema HR In Imported Database

Jul 25, 2010

I imported a schema HR from export DUMP ....i can find all the objects of schema HR in the imported database... but i got an error for a plan_table which is assigned to USERS tablespace in the source database.. ...

HERE COMES THE ERROR I GOT DURING IMPORT:

[oracle@localhost mom]$ imp file=exp_schema_ref.dmp log=imp.ref.log fromuser=hr touser=hr commit=y
Import: Release 10.2.0.1.0 - Production on Mon Jul 26 00:03:57 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: sys/sys as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via direct path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing HR's objects into HR
. . importing table "COUNTRIES" 25 rows imported
. . importing table "DEPARTMENTS" 27 rows imported
. . importing table "EMPLOYEES" 107 rows imported
[code]....

View 1 Replies View Related

How To Grant Select On All Tables Of User1 To Another Schema

Jan 18, 2012

User1 is having 10000 tables in his schema...How can i grant "select" on a all tables of a user1 to another schema(user2) so that in future when user1 will create tables , the user2 will have "select" access on those tables automatically.

I dont want user2 to have "select any table" privillege.

User2 should not have "drop" privillege on his own tables.

View 1 Replies View Related

Tool To View Schema And Relations Between Tables

Jul 4, 2008

Any tool or way to view the schema and relations between tables of an Oracle database?

View 1 Replies View Related

Server Utilities :: Import Only Tables Of A Schema

Jul 26, 2013

The way to do IMPORT of a schema's ALL the TABLES only. Through Data Pump. I don't want to import any other objects like 'package',procedure etc...

Is it the only way is specifying them within EXCLUDE parameter?

View 3 Replies View Related

Server Utilities :: How To Backup Tables In A Schema

May 31, 2012

How to backup the tables in a schema? I need som halp on this subjekt. I hav Dev envrnment that I must to backup.

View 9 Replies View Related

Security :: Enabling Audits Can They Be Applied On Tables Or Whole Schema

Apr 19, 2013

Enabling of auditing in oracle database, can this be done on some tables or applied on the whole schema/user.

View 5 Replies View Related

Backup & Recovery :: Selected Data From Tables Of Schema

Mar 7, 2012

I want to take backup of selected data from tables of a schema (as huge data, that is not used, causing slow query performance) I planned to create a seprate backup schema and tablespace to store the data from these tables. Then write procedures that can move the data to and fro among table of those schema. And create partitioned index on those backup tables.

View 11 Replies View Related

Forms :: Summit Office Supply Schema (Tables)?

Jan 21, 2013

I have this pdf (2 Volumes) from Oracle University courses, "Oracle Fusion Middleware 11g: Build Applications with Oracle Forms" In order to read the examples you must have "Summit Office Supply Schema" fmbs, mmb, pll, tables.(dmp)

indicatively some tables (not confuse it with other summit user)
customers,
departments,
warehouses,
orders,
employees,
order_items,
product_information,
inventories

View 2 Replies View Related

Server Utilities :: Exporting Database Schema / Tables Without Data

Sep 22, 2010

I'm trying to export a relatively large database but it's a bit more complicated than that.For one schema I need a full export / import (data included).

For another 10 schemas I need them empty, with the exception of a table in some of them which needs to be exported / imported with all data inside.Is it possible to do this with datapump utility (impdp, expdp)?

Afterwards I will be running some scripts to populate the DB instance with critical data / metadata.

View 1 Replies View Related

Export/Import/SQL Loader :: IMPDP Into Schema With Truncated Tables

Aug 2, 2012

I've got a schema that I've truncated all tables. I have a full schema export I took awhile back, and I'm wanting to import this into the schema to basically 'reset' it.

First time run, I got the :

ORA-39151: Table "xyz.tablename" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

I've been reading through, and see suggestions to add to the par file:

CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=APPEND

And I've seen others use the option for:

table_exists_action=replace

I basically want to put the data back into the tables, and have the indexes rebuilt.....

View 1 Replies View Related

SQL & PL/SQL :: Create Block Of Code That Would Search In All Tables In Schema For Column_name

Jun 14, 2011

I want to create a block of code that would search in all tables in a schema for a column_name where its data_length is like 4000 let's say. This data_length is actually dedicated for a comment column. If found, all not null column with like 4000 data_length will be changed by string "Comment has been removed". I have the following script below but it seemed lacking.

begin
for rec in (select table_name, column_name from user_tab_columns where column_name like ?data_length? = 4000 order by 1,2) loop
begin
execute immediate 'update '||rec.table_name||' set '||rec.column_name||' = ''Comment has been removed'' where '||rec.column_name||' is not null';
commit;
[code]........

View 6 Replies View Related

SQL & PL/SQL :: Efficient And Fast Method Required For Audit Trailing Tables In A Schema?

Nov 17, 2011

efficient & fast method for Audit trailing tables in a Schema for any insert /update /deletes for a table. I do not want to use db triggers because of performance issues.

presently we have as system which does audit trail as below:

1. If a user changes a column value for a table ( update/insert/delete) then we call a db package and pass the parameters like table name,col name,user, operation (ins/update/delete),old value, new value,date modified,user modified etc

2. the called package will insert a record in the audit trail table with the parameters passed

3. The audit trail table is used for report generation .

View 1 Replies View Related

SQL & PL/SQL :: How To Find Number Of Rows Of View

Oct 22, 2010

is there a way to find out how many rows a View has? Something similar to NUM_ROWS with regular tables perhaps? Or do I have to use Count(*)?

View 4 Replies View Related

SQL & PL/SQL :: How To Find Next Closest Number In Series

Jan 21, 2012

i have a data something like

010030
110495
210960
311425

[code]...

and user enters the figure like 13025now i would like to find the closest next value to the number entered by user, which is 713285 .how can i get this result, i have search a lot on Internet and i found some analytic functions can work out. but don't know how ..

View 14 Replies View Related

SQL & PL/SQL :: Query To Find The Number Of Alphabets?

Apr 20, 2013

I need to find out that assume i have a table having 2 column

Num Name
1 Adam
2 Akanksha
1 barren
2 bosli
3 Benergee
4 Bhawna
3 anjani

I want a query as if A is there 2 times then there should be 1 then 2 then there is b coming in 4 places then it should be 1 2 3 4 and again there is anjani so 3 should be there as 1 and 2 in first 2 places and the num should be automatically generated number based on the count of the alphabets

View 6 Replies View Related

SQL & PL/SQL :: Find A Negative Number In String?

Apr 24, 2012

I am currently working on a Data Dictionary project where we need to run a few rules against the give data sources to see if they all comply together.

One of the rule is to check if the no. is negative or not. So for that what I tried to do was to check if first the field is number or not and then check on if it is negative or not.

This is the code I am currently trying on and is not looking good.

SELECT 1 FROM DUAL
WHERE decode(DECODE( TRANSLATE('-123.45','-0.123456789',''), NULL, 1,0), 1,substr('-123.45',1,1) ,' ' ) = '-'

View 13 Replies View Related

SQL & PL/SQL :: Find Biggest Age Number In Table?

Mar 2, 2010

i want a simple query to find out for example the highest age in the table.

View 9 Replies View Related







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