SQL & PL/SQL :: Handle Customer Specific Objects In Oracle?
Nov 25, 2012
the more customers we have for our software solution the more individual oracle objects (Tables, Packages, Functions, etc.) we have in our scheme. Right now we separate these by giving them identifying names like "X_CUSTOMER1_TABLENAME" e.g. (I know ..... )
This is not very practical when keeping our reference clean and when deploying/syncing our reference with a customer db: One customer would receive the objects of other customers on a deploy.
Is there a common solution to this problem? We were thinking about having a separate scheme for each customer. That way we would have our standard, untouched scheme with the basic functionality and the customer schemes with the individual content.
To make it a bit more concrete: We have around 100 basic tables that make the most of the content/functionality of the software. Each customer might have between 1 -5 additional tables with "custom" data that is used in conjunction with the standard objects in individual packages, functions etc. The installations will be made on our customers systems. So I have in mind to have schemes 0001, 0002, etc for each customer IN OUR REFERENCE. But we would then deploy only the scheme for that certain user when installing on their system. So for example for customer 0001 I would deploy the STANDARD and 0001 scheme
View 6 Replies
ADVERTISEMENT
Dec 7, 2011
I'm trying to write a procedure that displays customerID, customer name, product name, and the total quantity of products the customer purchased, and the total amount the customer paid.Here's the relevant Schema tables:
CREATE TABLE Product (
Product_ID NUMBER(6) NOT NULL,
Description VARCHAR2(30),
Product_Code CHAR(4));
[code]....
Now I'm trying to wrap the above query in procedure code. I believe that I need a cursor, but I don't know what kind of cursor variable to store the result of the SELECT statement in because the query selects columns from several different tables, and I'm not sure how to terminate the FOR loop (but I think probably I can use the EXIT WHEN cursor%NOTFOUND;Here's the procedure code I have written thus far:
CREATE OR REPLACE PROCEDURE find_customer_statistics IS
DECLARE
TYPE cust_stats IS REF CURSOR; weak ref cursor declaration
SELECT sales_order.customer_id, customer.name, product.description, SUM(line_item.quantity),
SUM(line_item.subtotal)
FROM sales_order, customer, product, line_item
WHERE customer.customer_id = sales_order.customer_id
AND line_item.order_id = sales_order.order_id
[code]....
View 7 Replies
View Related
May 29, 2012
I'd like to know if it is possible to track DML actions issued on a specific table by a specific user, for example , i tried :
AUDIT SELECT on SCOTT.DEPT by HR by ACCESS;
I get an error, where is my syntax error ?
i want to know if it's possible to do it without trigger ?
View 7 Replies
View Related
Apr 11, 2011
I have a database which consists of various orders and various field.
I have a variable called createddatetime . I want that whenever i should run the database it should display records from
Yesterday 06:00:00 am to Current Date 05:59:59 am
Now to implement this i tried to put this syntax
and to_char(Createddatetime,'dd/mm/yyyy HH24:mi:ss') between 'sysdate-1 06:00:00' and 'sysdate 05:59:59'
But nothing comes up
where as definitely there are records between times because when i do and Createddatetime between sysdate-1 and sysdate I see valid records coming up.
View 3 Replies
View Related
Apr 23, 2012
We want to provide data dump from 3 tables Like A,B,C.
In C we have more than 3 million records.
What is the best way to move this data from our DB to customer DB.
Customer is running on MySql
View 12 Replies
View Related
Jan 23, 2013
I`m migrating a mysql 5.0 database to Oracle 11g. I used Oracle's Sql developer and with a workaround i could convert tables, constraints, indexes, some triggers and created sequences.
My question is, what happen with stored procedures and functions, they didn't pass. I've been searching and i found here hey have to be rewritten. Is there any way to convert those objects ? I can't afford to do it manually because the mysql database has almost 400 of them and they aren't small.
View 2 Replies
View Related
Apr 4, 2013
I'm using the below data base version
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.
I have created a pl sql type as object with five attributes as below.Now im trying to insert only 3 attributes with the attribute names ( As like insert into statement with column names).
Create type address_test as object (
Streetno varchar2(20),
Locality varchar2(20),
City varchar2(20),
Pin varchar2(7),
Country varchar2(20))
[code]....
Is that possible to use attribute names in the insert statement of column object type ?
View 9 Replies
View Related
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
Mar 28, 2013
I'm doing online business of selling my manufacturing product to my customer through internet. It is so much easy way but with this there are some unwanted threads that might be disturb business working area. I want to keep my customer online data from hacker but have no strong software who minimize or protect them hackers.
View 1 Replies
View Related
Apr 25, 2010
I am currently doing a project where i need to write a stored procedure which will be doing the following-
i)it will retrieve multiple columns from multiple tables in a single database(through join) based on certain conditions
II)then it will store the entire data in a certain field(File_data) of staging table
inside file_data a header and a trailer will be present with the records.also the field values will be pipe separated and a new record will start in a new line.
So,the data inside the file_data of staging table will look like this-
H|v1000
transdate|ordnmb|deposite_amt|order_status....
12-nov-09|123456|23.8|C...
4-dec-07|234567|67.7|R...
..........
7-jan-04|567890|54.7|x.....
T|234(record count)
i did this formatting using java, but my project leader wants me to do the formatting using SP,and wants me to use staging table.
View 7 Replies
View Related
Nov 12, 2013
How to select a substring in oracle up to a more than one specific characterfor ex : 121.051^NP: FAMILY PRACTICE ( trim the values before ^ ) 121.051^*NP: FAMILY PRACTICE (trim the value before *). with below function I can only get rid of ^ , I want both the specific characters ^ and ^* to be removed at the same time.
SUBSTR(p.phys_sub_grp_2_desc,INSTR(p.phys_sub_grp_2_desc, '^') +1)
View 7 Replies
View Related
Sep 30, 2010
I am building an information service that manages Suppliers. The suppliers are used by our billing system, tender system and sales system. Though 60% of the attributes of supplier are unique to each system, there are still 40% attributes of Supplier that are shared across the systems.
My objective is to build a flexible system, so that change to one individual system's data, should not impact other systems. For example, if i need to make certain tables offline for upgrading them, it should not impact rest of the systems that need supplier information. What is the best way of achieving this? Should all the different context specific attributes live in one schema, but deployed on different table spaces? Also, the read and update may happen more for one set of attributes than the other. How should i logically represent them via one model, but deploy them in such a fashion that they can evolve independently?
View 3 Replies
View Related
Jul 30, 2012
On our production servers we have simple function with result_cache, like this:
create or replace function f_rc(p_id number) return number result_cache
is
ret number;
begin
select t.val into ret from rc_table t where t.id=p_id;
return ret;
exception
when no_data_found then
return null;
end;
/And its results frequently invalidates without any changes in table or function. I found only 2 cases when oracle invalidates result_cache results without any changes in table:
1. "select for update" from this table with commit;
2. deletion of unrelated rows from parent table if there is unindexed foreign key with "on delete cascade".
I test it on 11.2.0.1, 11.2.0.3, on solaris x64 and windows. Test cases:
[URL]......
But none of them can be the cause of our situation: we have no unindexed fk, and even if i lock all rows with "select for update", it still does not stop invalidating. In what other cases this happens? Am I right that the oracle does not track any changes, but the captures of the locks and "commits"?
View 2 Replies
View Related
Oct 29, 2008
wish to do a database on dvd rental in oracle following: for the ER diagram i have indetified the entities as : branch , emp, customer,dvd, rental...
1) set the one - many relationship in the above data
2) identify the attributes for customer,dvd and rental tables and thus normalise them as well.
View 2 Replies
View Related
Nov 19, 2010
i WANTED TO SIMPLIFY THIS CODE SO THAT I NEED NOT DO ALL THESE manipulations but still get the result of
UNI_CUG_SITE,
Create Table CUG_SITE compress nologging As
Select C_Key, S_Key From A
group by C_Key, S_Key
/
commit
/
--Creating a table for all Single Sited Customer ---
[code]........
View 5 Replies
View Related
Nov 3, 2010
i'm working on procedure to send invoices to customer thru mails.
i'm using UTL_Mail package to send html content in a mail , it's working if i'm using my company mail exchange BUT if i put "xxxx@hotmail.com" or "xxxx@yahoo.com" it doesn't work and raise
error :ORA-29279: SMTP permanent error: 550 5.7.1 Unable to relay for xxxxx@hotmail.com
View 10 Replies
View Related
Dec 20, 2012
have a column cstomer_ts from table customer, which contain records like:
"cstomer account"||"PRIVATE"||"foundation"
"cstomer account"||"PRIVATE"||"foundation of money"
"cstomer account"||"PRIVATE"||"Moneycost"now i want to fetch the third record from the column cstomer_ts which starts with 'Foundation'
i mean i want to fetch only 3rd postion frm the column cstomer_ts and tht should starts with 'Foundation'
View 9 Replies
View Related
Jun 6, 2011
it is possible to create Oracle objects(Views) using Conditional compilation?
View 5 Replies
View Related
Oct 8, 2010
I'm converting a SQL Server db to Oracle 10g. So far it's going pretty well. However, I've hit a significant performance snag trying to run queries against the converted view whose SQL is below. In SQL Server it runs pretty quickly, 10-15 secs. This query presently returns about 1.7 million records. code below, and assuming I've done nothing to optimize the database or the objects involved,
BS
------------------
CREATE VIEW DST_TMP_VIEW_ACCT_XACTN_CRN AS
SELECT CUSTACCNUM,
FUNDID,
[code]...
View 2 Replies
View Related
Jun 26, 2013
How to Pick / Extract the java class files from the database.? We have not maintained the latest codes in the oracle application server where java class code is residing.
All the Java Classes are available only in database. So we need to pick the latest java class code from production environment. In TOAD we tried but all class objects are listing at the left side but we are unable to take the code. So how can we take the latest codes(java classes) from the Production Database as a backup.
View 1 Replies
View Related
May 26, 2010
How to Print Customer Copy / Office Copy in rports 6i for example i want to print two copies of any report i want that on first copy of report shows CUSTOMER COPY and second copy of shows OFFICE COPY and report may be on multiple pages.
View 1 Replies
View Related
Feb 16, 2012
I am student of Oracle programming(SQL).Has a question on MIN and MAX functions:
- "Write a SELECT statement that returns one row for each customer with four columns--the customer's first and last name, the date of the customer's most recent order, and the date of the customer's oldest order and the difference between the two dates. Sort the result set so the customers who have been ordering with the company the longest appear first. Use most recent order date as a tie-breaker for the sort."
where orders and order_details,customers are 3 different.this is my query ...but its not working.
SELECT DISTINCT customers.customer_first_name||' '||customers.customer_last_name AS Customer_name,MAX(order_date) AS "Most recent order", MIN(order_date) AS "Oldest order"
FROM customers
INNER JOIN orders
ON customers.customer_id=orders.customer_id
LEFT JOIN order_details
ON orders.order_id=order_details.order_id
LEFT JOIN items
ON order_details.item_id=items.item_id
ORDER BY Customer_name
View 6 Replies
View Related
Nov 15, 2010
How similar is PL/SQL to SQL? I'm trying to retrieve all data from a customer table and print out each customer information one by one:
DECLARE
CURSOR all_customer IS
SELECT *
FROM customer;
BEGIN
DBMS_OUTPUT.PUT_LINE(all_customer);
END;
but I really don't know the correct syntax. The Powerpoint slides the professor gave is not useful at all.
View 13 Replies
View Related
Sep 13, 2013
how to write this in sql command lineDisplay CUSTOMER number, name, rep, balance, and credit limit for all CUSTOMERs of Rep 20.
In addition, display this information for CUSTOMERs of rep 65 who have a $10,000 credit limit. Arrange the output by credit limit descending within rep ascending. no more than two decimal digits
View 3 Replies
View Related
Aug 1, 2012
I'm having some difficulty securing my Oracle APEX + Oracle HTTP Server environment.
There are currently around 20 Applications running on the APEX Server, and I'd like to publish 2 of them Externally for users.
I have added a certificate to Oracle Wallet Manager to secure the OHS, but I can access all the Applications on the APEX Server by just changing the Application ID suffix.
How can allow only two applications to be accessible via the internet?
View 11 Replies
View Related
Jan 18, 2013
I was asked if it was possible to restrict which users / or client IP's connect to my Oracle 11.2 database. I guess I could just shutdown the listener and have me and one other DBA connect to it via SSH / LOCALHOST but I was wondering if there was a more DBA specific way to restrict client connections to just two specific IP's over the Oracle listening port?
View 5 Replies
View Related
Jul 22, 2013
I have an application connected to Oracle 11g that sends its own querys to the db based on what the user is clickng on. The applicaiton is connected via one user id and I was wondering, is there a way that I can capture the tiem each query starts, the sql itself, and the amount of time it took to fetch the data?
View 7 Replies
View Related
Nov 17, 2011
I have created two types and a list of the first type:
create type type1;
/
create type type1_list as table of ref type1;
/
create type type2;
/
I have now just created the two types as follows:
create type type1 as object(
id# number
);
/
create type type2 as object(
attribute1 type1_list,
MEMBER FUNCTION function1 RETURN NUMBER
);
/
Ok, I've created the tables (I don't know if it's necessary to point out my problem)
CREATE TABLE type1_table OF type1;
/
CREATE TABLE type2_table OF type2
NESTED TABLE attribute1 STORE AS nested_type1_list_table;
/
And what I wanted to do now is to implement the member function1 and check something of the attributes of type1 in the list of attribute1... And that's where my question occurs, how does it work, I can't figure it out. I tried something like this:
Quote:
create or replace
TYPE BODY type2 AS
MEMBER FUNCTION function1 RETURN NUMBER AS
[Code]....
But I don't get the right way, it doesn't work
View 4 Replies
View Related
Feb 13, 2013
I am trying to find the unix process for one of my application in the database but I am unable to view the same. To simulate, I did the following.
1. My database runs on different server.
2. I invoked "sqlplus" from another unix box to login to the database.
3. I found that the process id (ps -ef |grep sqlplus).
4. When I execute the below mentioned query it does not display the process id that I am looking for. But the osuser, username, program and machine details are correct. How can I know the process details from the database?
SELECT SYS.GV_$SESSION.OSUSER, SYS.GV_$SESSION.USERNAME, SYS.GV_$PROCESS.SPID,
SYS.GV_$SESSION.MACHINE, SYS.GV_$SESSION.PROGRAM,
SYS.GV_$PROCESS.PROGRAM ,SYS.GV_$SESSION.SQL_ID
FROM
SYS.GV_$PROCESS, SYS.GV_$SESSION
WHERE
SYS.GV_$PROCESS.ADDR=SYS.GV_$SESSION.PADDR and SYS.GV_$SESSION.USERNAME='TEST'
and SYS.GV_$SESSION.MACHINE like '%hostname%'
View 3 Replies
View Related
Mar 24, 2008
I have a stored procedure that does a "select name into v_name" SQL statement, which works fine. The only problem is when the query finds no data (the procedure will error because there is no value to put into the variable). Now i have a work around to this by running the query first with a count statement (which will always have results) and then if it is not equal to 0, then i will run the select into.
My question is, is there a better way to handle this kind of issue?
View 1 Replies
View Related
