SQL & PL/SQL :: User Is Creating Original Item Code Without Mm
Mar 28, 2012
i have a field in one table called item_name based on which i am creating new item name as duplicate with new given length, for eg there is one item_code which has length of 12000mm i am going and replacing the string using instr function and replace function based on criteria that where i will find mm it will extract a portion and replace the new length as 6300mm.But its failing because some time user is creating the original item code without mm, is there a way.
My aim is to display the missing itemnames for each itemcode.
For each ITEM_CODE there should be four items i.e Pen,Pencil,Scale and Marker If any itemcode missed any of the itemnames those records should display in the output.
So the output should be like this
101 Marker Because the item Marker is missed. 102 Pencil Because the items Pencil and Marker are missed. 102 Marker Because the items Pencil and Marker are missed.
For itemcode 103 no records should be displayed because it having all four itemnames.
I have a strange problem when creating a view in user from another user
I have a user called "Cash_tst"
its syntax creation is
-- Create the user create user CASH_TST identified by "" default tablespace CASH temporary tablespace TEMP profile DEFAULT quota unlimited on cash; -- Grant/Revoke object privileges grant connect to CASH_TST; grant dba to CASH_TST; grant resource to CASH_TST;
-- Grant/Revoke system privileges grant create any view to CASH_TST; grant unlimited tablespace to CASH_TST;
I want to create a view
CREATE VIEW TAMER AS SELECT * FROM [b]AROFL[/b].RA_CUSTOMER_TRX_LINES_ALL_BEFO
"AROFL" is another user on the same database when try to create the view "tamer" i got message of "insufficent privilege" although i granted "create any view" to the user "cash_tst"
In an Application Procedure, I'd like to reference some items that are local to the current page. Obviously, hard-coding like the following won't work unless it happens to be on Page 25.
:f105_blah := :p25_blah;
So how can I write this line of PL/SQL code so that the "25" is automatically set to the number of the current page?
Need a code which prevents the User from entering more than 30 characters in a field. Although the variable can accept more than 30 characters. My requirement is to validate the field by restricting up to 30 characters.
I created a form that would allow a user to create another user.i tried creating a user in forms 6i using the following code on the when button pressed:
BEGIN forms_ddl('create user'||:user_name||'identified by'||:pasword); commit_form; END;
I am trying to create a new user who will be able to do a delete from all of the tables that only I MYSELF created. I created my user and granted access but realized I may not have done it right..
is it simply SQL>grant delete on <TABLESPACE> to <new user>; ? or do i need to specify the grant the delete on my tablespace to new user?
i want to create a group xyz and add some users to xyz group and want to grant/revoke permissions to xyz. So that all the users present in that group will have the same permissions as of the xyz group. so that instead of giving the permissions to users individually i can give it at a time.
We are using oracle forms 6i as front end and oracle 10g as back end.When we are creating a new user, the password should be stored in back end table in encrypted format.
SQL Plus version Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.1.0 Production Forms Version : 6i Reports Version: 6i O/S : Microsoft Windows Xp professional Version 2002 Service Pack 3
With reference to the above version details here is the requirement. How do I restrict user on key next item trigger i.e I want the cursor to move to next field only when user presses "ENTER" button on the key board any other key like mouse should not allow user to move to the next field.
That is I have created the User Defined Data Type as following. CREATE OR REPLACE TYPE Bit_Type AS OBJECT(Bit NUMBER(1,0));
After completing this creation of new UDT, I am trying to create the table with this UDT as follows, CREATE OR REPLACE TABLE Sample_Bit ( RegID Bit_Type);
I received an Error Message like: SQL Error: ORA-22913: must specify table name for nested table column or attribute 22913. 00000 - "must specify table name for nested table column or attribute" *Cause: The storage clause is not specified for a nested table column or attribute. *Action: Specify the nested table storage clause for the nested table column or attribute.
I have been asked to improve the speed of our extract feeds to another team. It was using an excel file as csv created with C# reading the csv into a global temporary table and a procedure to insert rows from global table to main table one at a time.
Around 1 million rows That was taking around 25 min. After trying sqlldr and an intermediate table I managed to reduce timing to 15 sec with sqlldr and Direct Path Load. I tried all that in one of our own DEV databases. Now we want to try the approach in one of DEV databases belong to downstream group (the one we feed data). They are rather reluctant to give me access to their DEV database! I have asked them to create a new login and grant object creation (DDL) to this user.They keep saying that they are prepared to give me a role but NOT schema owner. What is the best way of enabling me to create tables somewhere and test loading times with sqlldr.
I need to do full DDL (table/proc creation) and DML on a certain schema. So my question: What is the best way of doing so.. Do I need a schema created for user to do this workI they already have a general purpose schema, can they create a role to have full DDL: rights in that schema?
My problem consists in setting dynamically a data block related to the item chosen by the user.So I ask if it is possible, in forms 10g, to base dynamically a data block with different tables having different columns.
I need to create a forgot password page in which there will be two text box named user name and email id and user will provide his usename and email id in those text box. so apex will check that the provided name and email id are there in the data base and if email id corresponds to right user name then a mail will be send to that email Id containing auto generated password.
I have a table EMPLOYEE with columns employee_id and status. I have a requirement that when an employee status is getting changed, then even its linked employee's status also should be changed to the same status value. For this, I need to handle the updating of linked employee's status using a trigger.
Since we get mutating trigger issue when try to update the originating table, I am trying to go with the approach of "in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers. The first is an AFTER row trigger that updates a temporary table, and the second an AFTER statement trigger that updates the original table with the values from the temporary table".
But however I am still facing the same issue.
Test case:
CREATE TABLE EMPLOYEE ( EMPLOYEE_ID VARCHAR2(1), STATUS NUMBER(9) );
INSERT INTO EMPLOYEE VALUES ('A',1); INSERT INTO EMPLOYEE VALUES ('B',1); commit; [code]....
Also, any alternate options (rather than using 2 triggers with temp table).
i am not able to rename a column for original table, once i create a new one with Create table as select i can rename the new table column, but not the original one
SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 14 13:10:10 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options
SQL> show user USER is "WEBSYSTEMD6" SQL> alter table cbe rename column tnum to tnum_old; alter table cbe rename column tnum to tnum_old * ERROR at line 1: ORA-00900: invalid SQL statement
SQL> create table cbe_test as select * from cbe;
Table created.
SQL> alter table cbe_test rename column tnum to tnum_old;
I have no knowledge about Barcode. The problem is an issue of Loyalty Cards of a Hotel and Restaurant to various customers and then these cards will be presented by the customers time to time in the Hotel as well as Restaurant. The Owner of the Hotel and Restaurant wants to generate separate barcode for each card and when this card will be presented then the bar code reader will readout the code and the system will calculate the amount of discount/rebate. Because if the data entry operator enter the code of the card through key board the it will be a chance of leakage or misuse of that card.
I have to compare my SVN source code (packages, views etc) with the production code in the database like views etc (actually we are not sure that what we have in the svn is the final version of production code, we have objects created in the production database, but we don't have latest scripts for that. we have to deploy the svn code in the UNIX box).
So here the comparison is between the OS files and the database objects.
I thought I would get scripts of all the packages, views etc from the production database by using DBMS_METADATA or some utility and save the code in OS files then compare one svn file with OS file manually by using some comparison tools e.g toad provide one comparison tool.
I have 20 or so tables, partitioned by range, indexed etc...soon these tables will be gone and i have to recreate them with the same definitions. I have to write a procedure(or script, it's up to me) which must:
1- check if table A exist in the scheme 2- if not create the table as the original ddl.
for 1 i've used smth like ------------------------- SELECT count(*) INTO a FROM user_objects WHERE object_name='A';
[code]....
so my question is how and what is the cleanest and fastest way to do this.? it's a lot DDLs,
