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'
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]....
Say, a table has existing columns of A, B, D, and E.Now I want to add a new column C. But I want this column appear between B and D. I found that the newly added column always goes to the end of the table, i.e., A, B, D, E, C, instead of, A, B, C, D, E.
View 6 Replies View RelatedHow to add a new column to specified position in a existing table.I have using the oracle database 10g.This below code is not working in oracle 10 g
example:
ALTER TABLE EMPLOYEE ADD DEPT NUMBER FIRST:
ALTER TABLE EMPLOYEE ADD DEPT NUMBER AFTER JOB:
how to get name of coloumn and table name which is being modified in trigger
View 8 Replies View RelatedI need to fetch the data from a particular column and display it as a row of data.
For example,
Table
Col1 col2 col3
1 6 10
1 7 11
1 8 12
2 6 18
2 7 null
2 8 19
Data should be diplayed using col 2 as partition as below
col1 col3 col3 col3
1 10 11 12
2 18 null 19
Note: I have also referred to this link [URL]....
i am fetching one record based on date but not able to fetch the data.
in my table pdate column is there with date datatype and value is in that is:15-OCT-12 so i am fetching empid based on dates.
select empid from masterprocessdailydata where pdate=to_date('15/OCT/12','dd/MM/yy'); but not getting the empid.
i am running one query which is here
INSERT INTO shiftsample (Empid, PPDate, Inpunch, outpunch)( SELECT Emp_ID, PDate, In_Punch, Out_Punch FROM ProcessDailyData WHERE PDate = to_char(2012-10-15,'yyyy-MM-dd') AND Emp_ID = '00000001' );
in this query pdate has timestamp datatype and in shift sample ppdate column has date type. so i am not able to insert value from process daily data table.
getting this error.
SQL Error: ORA-01481: invalid number format model
01481. 00000 - "invalid number format model"
*Cause: The user is attempting to either convert a number to a string
via TO_CHAR or a string to a number via TO_NUMBER and has
supplied an invalid number format model parameter.
I have the following piece of
CREATE OR REPLACE PROCEDURE COMP_RECORDS
IS
l_query VARCHAR2 (10000) := '';
CURSOR TBL1
IS
SELECT TABLE_NAME, COLUMN_NAME FROM COLS_TO_COMP WHERE TABLE_NAME='ACE_HIST';
TBL1_REC APP.COLS_TO_COMP%rowtype;
[Code]..
However I am getting an ORA-00923 exception with message as "FROM keyword not found where expected". know if I can/cannot use a cursor to fetch column names for a table?
i have a proc that is taking p_serial_number refsursor as in parameter. the structure of p_serial_number is required to be
mfg_prod_cdchar (3 byte)
mfg_prod_seq_no char (6 byte)
How do I need to define this ref cursor ? and when I use it in my Procedure how do I fetch the column values ?
I have created tabular form. I have set LOV for one column. Here my requirement is when i select the value from lov then it will fetch that value into page item.
I am using apex 4.1
FRM-40501: ORACLE error: unable to reserve record for update or delete.
ORA-24374: define not done before fetch or execute and fetch
My master-detail form has single canvas. For both blocks, master and detail, two tables joined together in each. One table to be updated, second table has some info for reference (query only).
I am getting these errors when in detail block the item from LOV is selected for existing record. This does not happen for new record inserted in detail block.
create or replace PROCEDURE newprocedur(outname OUT VARCHAR2,outroll OUT NUMBER) AS
CURSOR c1 IS
select Name,Rollno,Section from emp;
BEGIN
Open c1;
fetch c1 into outname,outroll;
Here out of 3 columns in cursor is it possible to fetch only two columns using FETCH like i did above?
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
From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?
View 2 Replies View RelatedI 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.
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 Relatedwish 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.
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]........
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
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
I want to split the time frame into 3 hours interval as my requirement is that the number of application submitted between 12.00 am in morning to 12 pm in night and between that time interval i have to calculate in every 3 hours the no of application coming to DB
12:00am to 3:00am
3:01am to 6:00 am
6:01am to 9:00am
9:01am to 12:00pm
12:01pm to 3:00pm
3:01pm to 6:00 pm
6:01pm to 9:00pm
9:01pm to 11:59pm
A form have a frame in the name of 'Frame5'. I need to change the title of the frame.
I Had done like Set_item_property('blkname.Frame5',title,'newtitle');. The coding is compiled properly even i had received the error when im running like 'Unable to resolve references to item blkname.frame5'.
We are using oracle forms 10g, in formsweb.cfg file i gave separeteFrame=true and it works fine also like IE & applet window is opening. But User wants to hide the IE in backegroud and they want only applet window, so using VBScript we are hiding the IE and now only applet window apears this also fine.
I am running the report and displaying the report using web.show_document, report is running fine and displaying in IE with PDF format. But the problem is if i press the run report button report is opening and displaying behind the applet window not infront of applet window and there is no focus on the report IE also. How to display the report output(IE with PDF format) infront of the applet window and current focus on the report window(IE with PDF).
Note: If i use separateFrame=false then report is displaying infront of the IE screen and focus is on the report IE.
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 RelatedI 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
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.
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
I have included a calender control in my form and its working fine.Now, my problem is how can I automatically place the calender next to the text-item (for example, "block.invdate")if the push button is pressed to call a calender.
View 1 Replies View RelatedI have created tab canvases in the tab canvas there is three tab pages.and i have been used one datablock(use the datablock wizard) all the table column(items) is displayed only in the single tab pages--frames.for example
datablock--items_1.
tabcanvases--tabpages_1--frames_1--display all datablock item.
--tabpages_2--frames_2--none to display.
--tabpages_3--frames_3--none to display.
so i want to display datablock in all the three frames.