I created a user and granted connect, resource to the user. but trying to select columns from the view USER_HISTORY$,it's showing no value, i tried to login the user several times, but it's displaying no value, any particular reason or any minor mistake?
View 1 Replies
Our DBA set up a Heterogeneous Service to one of our MS SQL Servers and we can use a DB Link to SELECT a table from the SQL Database. This works just fine but the problem is any nText column on the SQL Database Table doesn't appear. All the columns are shown EXCEPT the nText columns.
how to correct this? We need the ability to select the nText columns also.
I'm trying to get a list of values from a table, and I am just not seeing the answer -I have a table with 4 records (say A, B, C, and D), each record has a GUID to uniquely identify it.I have a 2nd table that holds the relationships (parent-child).
At this point, I have 2 rows:
A>B
A>C
So, A is a parent to B, and A is a parent to C (D is not related to anything at this point)
I would like to be able to go into the D record and see that it is NOT related to A (A would be the parent in this instance) - the trick however is I only want 1 instance of A to show up.attempted to use some inner/outer join qualifications and I just cannot seem to get the data to display properly.
We have a view that shows the all the data that we expect, so we created a report to show that data, the report worked, then all of a sudden the report stopped showing all the data. (we did have a server move)I checked the view and the all the data is there....
I have created a new report based on the view but it does the same thing, it doesn't bring all the data up (i get very old data), there are no conditions or anything.
other tables and views seem fine. i have recreated the view and i still have the same problem.
how can we mask value of some columns in table? For example: user A is supervisor, he can query salary column in employee table, but for user B, he is staff member, he can query salary column but system just shows ***** or something like that for salary value.
View 6 Replies View RelatedI have a two question.
Question 1:How to select all columns from table except those columns which i type in query
Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data
I have a table with around 80 columns. All i need is to select first 40 columns.
Is there any way to select first 40 columns without giving all the 40 Column Names in select clause.
We wrote one data load process to load GZ files into Database.during process we will change client facing view definitions to backup table so that we can work on base tables.
This view definition changes are related to FROM and WHERE clause (not columns/type). during load process, client/user may connect to current server and accessing these views. My question is what will be the reflection of changing view definition while user is accessing view?
I created a scenario-
STEP1: Created a view-
create or replace view view_01 as
select object_name from dba_objects union all
select object_name from dba_objects union all
select object_name from dba_objects union all
[code]....
View definition is replaced by new definition while select is executing on that view. select returned number of records as per view definition one.
I have some applications on Apex public workspace (apex.oracle.com), and suddenly the Tree View, on Application Builder stopped showing any content. If I select "Component View", I see the page components, but if I switch to tree view, it shows me only some empty frames.
View 2 Replies View RelatedI have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.Here is a sample data :
Employee table :
Employee_id Emp_fname emp_lname emp_mname dept salary emp_height emp_weight
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
The expected result is :
result1 result2 result3 result4 result5 result6 result7 result8
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
My boss make a requirement in exist database as some user can view salary column at employment table by SQL and some user can view salary column at employment table by SQL.
The boss do not like to make changes front SQL. Ooracle 11g vault or Oracle Label Security is best for this requirement?
my oS is 2008 32 bit window and DB is 11.2.0.1
I am looking for Masking options/techniques to mask few columns. I am aware of the option Oracle Data Masking Pack. But its costly.
View 6 Replies View RelatedI am receiving an "ORA-00942 Table or View does not exists" error message when I try to run the following SQL statement:
SELECT id_status INTO :ls_id_status
FROM USER_ADMINISTRATION
WHERERTRIM(user_id) = :as_userid;
where :as_userid is an argument passed to the SQL.
Here are some background facts in order to save time regarding actions already taken:
(1) The userid used to login successfully is the same one passed to the above SQL;
(2) I have confirmed that the table does exist - we are using it in an application;
(3) The case of the table name is upper case as seen above and is correct;
(4) The spelling of the table name is correct;
(5) The schema name was prefixed to the table and I still got the same error (not shown above);
(6) The userid that I used is assigned to a role and the role has SELECT permissions to the above table.
I have the following questions :
1. After creating a view, how do I associate that view with certain level of security. As in, only a few users must be allowed to access that view.
2. How can I create a new user-login and password for my database application?
We are trying to audit a view. We are currently seeing that view in production instance and trying to find out when exactly it got invalidated.
View 4 Replies View RelatedIs there a way to view/obtain the password once a database link has been created. Some sys dba table?
View 7 Replies View RelatedI've created a stored procedure which creates itself a view (a MV to be honest); the instructions to create this m.view are dinamically built insinde my procedure, so each time i run it, based on the different input parameters, i've got a different result (my output m.view can have three colums the first time, or ten the next time) how can I read my output view to put the data into file? I've tried with "select * bulk collect into my_array from my_ output_ view"...after declaring "my_array" as a varying array with the max number of colums I could ever have...but nothing: if the array dimension doesn't match the number of columns that i've on my view, i.e. i receive "ora-00947 not enough values" error.
Is there a method to dimension dinamucally the array to store my data? Or should I change the code to fetch some other way the data i need to put to a file?
I have a column which holds the data in the below format.
Source Data :
>SNO_SDSDQ-8192-BN>SNO_54-99-24120-8192
>SNO_SDSDQ-8192-BN>SNO_54-99-24120-8192>SNO_54-90-16489-008G
>SNO_SDPMDB-008G-11>SNO_54-90-18008-008G>SNO_54-62-08791-008G>SNO_20-81-00327
>SNO_SDPMDB-008G-12>SNO_54-90-17830-008G>SNO_54-62-08598-008G>SNO_20-81-00327
[code]..
Problem Statement :
Split the above data into individual components and create columns / aliases dynamically.If the column is present then place the data under the created column.
Example 1) :
>SNO_SDSDQ-8192-BN>SNO_54-99-24120-8192
Result
Column Name : SKUCol_54-99
Data :SNO_SDSDQ-8192-BNSNO_54-99-24120-8192
Example 2)
>SNO_SDSDAA-002G-101-J>SNO_54-90-16002-002G>SNO_54-62-05781-002G>SNO_20-81-00135-5
Column Name : SKU54-90Col_54_62Col_20_81
Data :SNO_SDSDAA-002G-101-JSNO_54-90-16002-002GSNO_54-62-05781-002GSNO_20-81-00135-5
Column Name can be derieved from the components like SNO_54-62-05781-002G
i.e. SNO_54-62-05781-002G ==> SNO_-05781-002G
"54-62" will give the Col_Name
The First Column will always have data starting with
SNO_SD%
This column is constant and will be named as SKU.
I need this data to be placed in the same record / row but under different columns as per the data set.Basically, Can the data be split into multiple parts based on delimeters and the columns are created based on the unique data in the parts that form the data in the column.
In my requirement, I have many calculated columns needs to be modeled for the fact table, I am thing using a Materialized view for all computed columns and expose this MV for reporting tools. Using Materialized view as a fact table to query is a good approach?
Actual table columns
ART_NO, ART_PRICE, PROFITTable along with actual columns
ART_NO, ART_PRICE, ART_PRICE_LAST_YEAR, ART_PRICE_TRIM, ART_PRICE_EURO, PROFIT, PROFIT_LAST_YEAR, PROFIT_TRIM, PROFIT_EURO.
I'm writing a VPD function to be used for column masking. The predicate (WHERE-clause) it generates may take many different forms. In particular, it may contain inner-selects; for example,
"exists(select '*' from B where B.VAL = '123' and A.KEY = B.KEY)"
where A is the table that is associated to the VPD function, and B is some other table.
if this is OK for column masking? If not, my VPD function may sometimes work and sometimes fail, in unexpected ways.
The Oracle Database Security Guide (11g Release 1) says
Column-masking conditions generated by the policy function must be simple Boolean expressions, unlike regular Oracle Virtual Private Database predicates".
This seems to indicate that there are cases where a VPD function works for row-level security, but not for column masking.
an example of a 'regular Oracle VPD predicate' that doesn't work for column-masking?
Developers they use Toad and in that they want to use session browser option so that they can see the SQL running and open Cursor,other session specific details...So what grant/ permissions can be given to them so that the Schema they logged on can access those information.
View 4 Replies View RelatedI have 4 tables , Certain users need to (Select ,insert,delete) certain rows only based on nApplication id
All the tables have Application Id , Based on this application id user will (Select ,insert,delete)
I tried row level security DBMS_RLS.add_policy but it hangs every time when i query the data or when i try to drop the policy. Is there is any method to resolve this issue.
If only Row level security will solve this issue , give me some example
i had an issue last week with enabling and disabling a column in a tabular view. and i have it working now.... when the page loads i disable the column in the 25 rows being displayed.
the problem i now have is when i use the pagination to move through the rows the columns are all now enabled.
so what i am trying to do is this:
i have a tabular view showing 25 rows at a time. there is one column with a code and one with a description. when the code is changed, via a popup LOV, the description is changed based on the valus in a lookup table in the database.
i need the description column disabled, so the user cannot simply just type in the column. but when the page is submitted, the column needs to be enabled so i have modified the sumbit button to respond to a dynamic action which runs some enabling code then submits the page.
SO....
this all seems to be working except for when you use the pagination, the description column is all enabled.
is there a way of running some javascript when the pagination is refreshed? some better way of doing it?
I have a table containing hundreds of columns and I would like to be able to qualify my select statements so that only those columns containing a value are returned. Something like:
Select (non null columns) from tablename where columnX = 'whatever'
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
"CORE10.2.0.5.0Production"
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
See attached file for creation script and data load.Each staff member is required to complete at least one task every three years. The source table contains an EID (aka User ID) and a date column for each task with a date of when the task was completed. If a task has never been started/completed the date value is "NULL".
If a row looks like this:
EID,DATE01,DATE02,DATE03,DATE04,DATE05,DATE06,DATE07,DATE08,DATE09,DATE10,DATE11,DATE12,DATE13
68,NULL,11/10/2009,5/3/2010,4/22/2012,NULL,NULL,4/14/2010,NULL,NULL,NULL,NULL,4/14/2010,4/14/2010
The the result set would look like this:
EID,MaxDate,Within_Last_3_Years
68,4/22/2012,'YES'
The result set will show the EID, date of latest task completed and if the task was completed within the last 3 years from given date (for example June 30, 2012).
Below is the query that I have:
Quote:
select a.contact, b.db_name
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b,
where a.DB_ID=b.DB_ID
and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%');
How can I add two more columns into the select statement and the two columns are in the tables that were in the sub query..I would like to have something like this:
Quote:
select a.contact, b.db_name, COLUM1, COLUMN2
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b, mgmt_targets t, mgmt_current_severity s
where a.DB_ID=b.DB_ID
and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%');
I am using some UPS shipping software that imports shipping information based on an order number that the user enters and we are having performance problems. In the software's configuration you choose one database object (namely a table or view) to get the information from. I have created myView that selects from multiple tables, lookup tables, etc. I must use a view because the software only has the ability to add a " and <importKeyThatIHaveDefined> = <valueEnteredByUser>". For instance " and order_number = 123". It cannot join multiple tables, etc. The address_field1 info is the same for each record with the same order_number so I can just get the first row (or any row with order_number = <enteredOrderNumber> for that matter).
When I first looked at this it was created as something like this:
select order_number, line_number, order_number || '' || line_number as ol, address_field1 from order;Keep in mind that there are more than just this table and more fields selected. They are all joined correctly (for lookup values etc.).
The concatenation was determined to be the bottleneck in performance. ol was defined as the import key to be used and the users were just entering the order number plus a 1 to get ONLY the first line number. Each order (usually) has more than one line and even the line number is not necessarily unique for each order. For instance, order 123 may have line 1 twice and line 2 once.
select order_number, line_number, address_field1 from order where rownum = 1;
The software appended the " and order_number = 123" as expected but because rownum = 1 had already been executed (before the and order_number = 123" the view only returned one row even WITHOUT the order_number = 123.
I need to return only ONE row for a given order number EVEN IF there are multiple records with the same order_number and line_number. Using "where order_number = <enteredOrderNumber> and line_number = 1" is not necessarily unique so that won't work.If I can't make it work this way and can't use the rownum = 1 what options do I have?
I am using Oracle Database 9i. I have created a view as follows:
CREATE OR REPLACE FORCE VIEW ipl.abc_test AS
SELECT '10' dsgn, TRIM (b.oc1) code, b.grp, b.POLICY, b.premcode,
b.receipt, b.recptdt, b.PLAN, b.term, b.paymode, b.sumass, b.datcom,b.duedt, b.instl, b.lprem, b.rprem, b.sprem, b.extprem, b.totprem,b.oc10, b.oc9, b.oc8, b.oc7, b.oc6, b.oc5, b.oc4, b.oc3, b.oc2,b.oc1, b.bill_oc1 billno, a.fprdate riskdt,
NVL (a.bmon, TO_CHAR (a.fprdate, 'YYYYMM')) bmon, a.entryopt,
a.extprem3 suplext
[Code]...
View created properly, But when i select from this view this giving message missing right parenthesis. For information the select statement of the view gives multiple rows. Where is the problem.
I've read so many different pages on this topic but I can't seem to get my query the way it needs to be. Here's the query:
select admitnbr, lastname||', '||firstname||' '||finitial, hphone, mobile, wphone, med_rec, dob
from patients join schedule using (key_patien)
join adtmirro using (key_patien)
where appt_state = 'ON HOLD'
Because patients in my database can have multiple appointments "on hold" there are duplicates in the results. I only need 1 record per patient in order to forward this information into an automated dialer to contact that patient. I do NOT want to call the patient over and over again. Once will suffice. I'm trying to make a distinction on the column 'med_rec'. One row per 'med_rec' will be awesome but I can't find a way to create a distinct on that column.
I have a table which stores Employees and their Phone numbers. Each employee can have multiple numbers e.g.
Employee, Number
Adam, 123
Adam, 456
John, 123
John, 456
I am trying to write a select statement that will split the rows into columns and group by each employee e.g.
Employee, Number1, Number 2
Adam, 123, 456
John, 123, 456
