Constraints - Cannot Enter Multiple Lines Of Data?
Apr 8, 2008
I have some tables (below), but I'm having trouble entering multiple lines of data.Here are the tables I have created.
Quote: CUSTOMER table
SQL> create table customer
2 (customer_no char(6) not null,
3 name varchar2(30) not null,
4 address varchar2(50) not null,
5 credit_limit number(6,2),
6 constraint customer_customer_no_pk primary key (customer_no));
Table created.
SALESMAN table
SQL> create table salesman
2 (salesman_id char(8),
3 name varchar2(20) not null,
4 address varchar2(50) not null,
5 emaill_address varchar2(30),
6 constraint salesman_salesman_id_pk primary key (salesman_id));
Table created.
ITEM table
SQL> create table item
2 (ISBN char(13) not null,
3 title varchar2(30) not null,
4 price number(4,2) not null,
5 constraint item_ISBN_pk primary key (ISBN));
Table created.
INVOICE table
SQL> create table invoice
2 (invoice_no char(1) not null,
3 invoice_date date not null,
4 salesman_id char(8),
[code]...
Table created.
DELIVERY table
SQL> create table delivery
2 (invoice_no char(1) not null,
3 ISBN char(13) not null,
[code]...
Table created. I can enter data into the customer, salesman and item table without any problems. However, I need to enter multiple lines of data using the same invoice_no (which is 1). The data is generally the same, except for 2 things need changing but I keep getting the following error...
As you see, I only need the Customer one time followed by part, amount and address - separated hyphen - comma, next part, amount, address and so on ...I've tried the PIVOT way and STRAGG (wm_concat) but all failed so far.The main problem is that when one customer fits more than one condition, ora throws: ORA-01427: single-row subquery returns more than one row
When referencing a procedure during a trigger, can I split the parameters across multiple lines? Similar to a backslash in perl? I've written a simple send mail procedure and it works well, though the parameter list is large and I'd like to be able to format the code for readability, i.e.:
BEGIN send_mail('from@domain.com', 'to@domain.com, ??? 'Subject', 'Message'); END;
What would I replace ??? with to extend the procedure to the next line?
I know this sounds like a very elementary question, but I've yet to figure it out via queries on these forums or Google. Perhaps I'm not choosing the right words.
When creating the procedure, i was able to use || to extend the utl_smtp function parameters, but I get an error when using the same syntax during trigger creation.
why my code wont work. Am trying to create a constraint so that the only data that can be entered into the size field is a number between 4 and 15.
CREATE TABLE accommodation (chalet_no NUMBER (3) PRIMARY KEY, chalet_name VARCHAR2(20) NOT NULL, size NUMBER CHECK (size >3) AND CHECK (size <16), priceperweek NUMBER (4,2) NOT NULL);
formatting the mail message sent using utl_mail, i have created one table and it has around 17 rows inside as sample and it may increase as well, my present email format is in single line..find below the code i have written along with test case, you may substitute your emailid for test, especially my main focus is on the column mrk , where all the mrk are comming in one line but i want to be arranged in a line not more thant 10 rows, if it exceeds it should go to next line.
CREATE TABLE FAB_LOG ( MRK VARCHAR2(30)) INSERT ALL INTO FAB_LOG VALUES ('1018017M-6001') INTO FAB_LOG VALUES ('1018017M-6002') INTO FAB_LOG VALUES ('1018017M-6003') INTO FAB_LOG VALUES ('1018017M-6004') INTO FAB_LOG VALUES ('1018017M-6005') INTO FAB_LOG VALUES ('1018017M-6006') [code]....
I want to create a store procedure to copy data from a source tables(which may not have any constraints defined) to a table which has primary key, foreign key and unique key constraints.
Any records which are rejected due to these constraints not being satisfied need to go another table.
Once the initial data load is done, these procedures need to be automated(through cron) to do the future incremental uploads in the same manner.
I have requirement of loading a part of the flat file that contains many headers and lines info. The program has to load the lines whose header recrd_type is 05 using SQL*LOADER.
eg of flat file.
Header trans_code comp date rec_type ------------------------------------------------------------ 8 12800002 0729201005 transcode_line acct date refrence ------------------------------------------------------------ 4424604001002738307272010 24427330207710017569675 4424604001002738307272010 24427330207710017569675
I developed a custom Form using Form Builder 6i. This Form has selection criteria (data to be entered in a couple of fields) and a "Find" Button, which queries the data (based on the selection criteria) and populates all fields on the Form.
This Form has a couple of "Required" fields in its search criteria section. When data is not populated in any of the Required fields and "Find" button is clicked upon, Form raises an exception and pops up a message indicating the user 'to enter data in mandatory fields'. However, this message keeps popping up and would not stop.
So, I'm not able to proceed with querying data on my custom Form. I had to kill the session and reconnect to front end to overcome this issue.
I want to delete data from say 100 tables without disable the constraints.
Tables having foreign key.. which is giving error if I select table randomly to delete.
I want to know the query which will give me the sequence of all tables based upon dependencies, so that If i delete the data on that sequence, it wont give me child record exists error.
In Oracle XE when I run PL/SQL in the SQL Commands window I will sometimes get a "Not found" error. It further reads, "The requested URL /apex/wwv_ flow.show was not found on this server". I found that if I remove all of the indentations from the lines of the PL/SQL code everything will work fine. Talk about a screwy problem. So, if you have that problem just take all of your PL/SQL code and slam it up against the left margin.
I want added one new column in the below report which will shows only the items purchased on Cash basis but when i am running the query the column not showing any data.
i wanna copy data from a table to another one (same structure, only difference is that the second table is partitioned). the origin table consists of about millions of lines so i made an insert :
insert into table2 select * from table1. the insertion doesn't end correctly : TOAD crashes, any line was inserted in table2 : select * from ...etc.
However, the file system becomes full. i would if there is a way to purge something like cache ..., are these lines inserted somewhere : temporary table...,
I would like to write a query on USER_SOURCE that can display the number of code lines for each procedure/function in a package. Is it possible to write such a query? Maybe by using analytical functions?
for example in the following example i would like to count the lines between
"PROCEDURE proc1 IS" and "END proc1;" and between "PROCEDURE proc2 IS" and "END proc2;" SQL> select text from user_source where name='PKG_TEST' and type='PACKAGE BODY';
TEXT -------------------------------------------------- PACKAGE BODY PKG_TEST IS /************************************************* ****/ PROCEDURE proc1 IS BEGIN update t1 set EDITION_NAME = 'AAAAAAA'; commit; END proc1; [code]....
i need a chart with 4 lines and 2 y axis. 2 lines on the first y axis, 2 lines on the other y axis.First i tried to create 4 series and "multiple y axis", then i get the first line at one y axis, and all the other lines at the second y axis.
After that i have seen that it is possible to return more than one value within one series, so i create 2 series with 2 values each - i thought that then i will get the first 2 values at one y axis and the other 2 lines of the second serie at the second y axis... but sadly it is not like i expected - i get the same result as with 4 series.is it really necessary to create a custom xml for that?
here is my example: [URL] as you can see the blue and the red line are on different axis.the query of one serie looks like that (it gives the values of a day and the day before), the queries are ok.
select null link, to_char(a.ts,'DD.MM HH24:MI') label, sum(a.kw*100/12) over ( partition by trunc(a.ts) order by a.ts rows between unbounded preceding and current row ) "KWh", sum(b.kw*100/12) over ( partition by trunc(b.ts) order by b.ts rows between
I am spooling to a text file some output for a client. The file has 4 queries in it, one creates a header row, another a comment row, another the data rows and finally a trailer.
Code looks something like this:
/* Custom Extract Project: Plan Data Extract Product: EOWin 4.02 - Oracle db Use: Script to create above extract and spool results to text file Input Parameters: &1 Path and name of output file */
I´m monitoring a 11g database with OEM. I have a couple of questions regarding the Average Active Sessions chart:
- What does the line 'CPU core' means? The DB is running in a virtual server with 8 CPUs. However, the line 'CPU core' is in 1. Does it mean that Oracle is just using 1 CPU? - What represents the '99th percentile' line? The chart shows several sessions above that line, something is not working well, isn't it?
Customer sales order lines are fulfilled by IR-ISO. When line progresses, it is set to have Internal Req Requested status. A scheduled program runs amd IR are created. However, sales order line status does not change to Internal Req Open. It remains in Internal Req Requested status.
As a work around, workflow is rewound to 'Check ATR Qty' which is customized activity and then status changes to Internal Req Open. What could be the reason that workflow is not setting the status to Internal Req Open?
I have three database fields on my form , field_1 , field_2 and field_3 , user will enter the data in field_1 , and sometime he may not enter in field_2 ,
I want to bring the value of field_1 as it is into field_3 if he does not enter anything in field_2, if he enters the data in field_2 , then field_1 will get concatenated with field_2 and gets copied into field_3.Is there a way to do this automatically at form level using a trigger when-validate-item.In short this should be done after user presses the enter key on field_2.
create table ot_Sale_code_head ( sale_code varchar2(12),code1 varchar2(6),code2 varchar(6),code3 varchar2(12)); insert into ot_Sale_code_head ('A','01.01',NULL,NULL); --inserted record in form will be 'A','01.01',NULL,'01.01' insert into ot_Sale_code_head ('B','01.01','PS-P',NULL); --inserted record in form will be 'B','01.01','PS-P','01.01.PS-P'
The code written in PL SQL.I am using '&' operator to enter the values at run time in nested case statements.But when I chosen a value for case statement even though it is asking all the values which are not part of the selected case statement.I need only the values related to my choice in case statement.
choice1:= &CHOICE_NO1; CASE(choice1) WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('** ENTER YOUR CHOICE TO PERFORM THE OPERATION: **'); DBMS_OUTPUT.PUT_LINE('** (1)INSERTION (2)DELETION (3) UPDATION **'); [code]....
that is my exact code.when I enter value 1 for choice1 and 2 for choice2 It should ask the values only for student_ scorecard. delete_ record (& STUDENTID); &studentid. But it is asking all the functions (student_scorecard.existing_marks(&ID,&SUBJECTNAME));(student_ scorecard. std_ major_ name (&STD_ID)) and procedures.I dont want that.
I have 2 text fields as search criteria and a push button to search. When the user enter any on first field (or in any text field) and press ENTER key, I want to execute the search.
I tried the key-next-item trigger, but it's firing when I press TAB key.