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
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 */
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.
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...
DECLARE JOBSFILE UTL_FILE.FILE_TYPE; -- TAKE ALL JOB TITLES FROM JOBS CURSOR JOBSCUR IS SELECT * -- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID FROM TARGET_UCM ; [code].......
this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.
create table emp1 (empno number,deptname varchar2(30),deptno number,sal number); insert into emp1 values (1,'Bank',10,1000); insert into emp1 values (1,'Finance',20,400); insert into emp1 values (2,'Finance',20,4000); insert into emp1 values (3,'Account',30,3000); commit; select * from emp1;
actual output :
empno deptname deptno sal 1 Bank 10 1000 1 Finane 20 400 2 Finane 20 4000 3 Account 30 3000
Expected output :
empno deptname deptno sal 1 Bank 10 1000 2 Finane 20 4000 3 Account 30 3000
I am looking a output like above one. if any empno belongs to deptname Bank then give priority to that values else go to other dept like Finance but empno 1 should return only one row.i.e Bank dept only.
I have to return only one row based on dept values for one empno. how to do in sql or plsql?
I've written a SP and it refuses to work with the CONCAT function as documented. >>Yes I have searched the web and other locales to no avail. Create Procedure
`SP_CODE`(IN search_term VarChar(200))begin if search_term is not null then begin
Select b.s_term, a.O_Number, a.S_CODE, a.LONG_DESC From JKC a INNER JOIN search_terms b ON a.S_CODE = b.S_CODE Where b.s_term like CONCAT ('%', search_term, '%'); end;end;
The editor believes that the problem is at CONCAT('%', search_term, '%');
insert all into concat_test (empno,empname,description) values ('1','pratik','want') into concat_test (empno,empname,description) values ('1','pratik','to') into concat_test (empno,empname,description) values ('1','pratik','concat') select * from dual;
I am getting numeric or value error when concatenating sql script..
Here is the sample, I have in the stored proc.. firstpart:_ works fine if I comment the secondpart:_. (I modified the query because I am posting in the public forums)
V_SAMPLEQUERY VARCHAR2(2000);
firstpart:_
V_SAMPLEQUERY := ' SELECT AB.ABCDID AS ABCDID, CD.MEMBERID AS MEMBERID,
[Code]..
secondpart:_ ***ERROR PART***
V_SAMPLEQUERY := V_SAMPLEQUERY || ' ORDER BY AB.USER'; -- Here I am getting error saving ORA-06502 - numeric or value error
Want to understand difference between Concat function and "||" operator. I am getting the same result for both. Below is the test case for your reference.
Select 'H '||' S' From Dual; --Output H S
Select Concat('H ',' S') A From Dual; --Output H S
Select Length('H '||' S') A From Dual; --Output 6
Select Length(Concat('H ',' S')) A From Dual; --Output 6
Im having mail content column of type long Raw in a table.i just want concat or append a value in that column when i tried it shows error "illegal use of long type".how to append value to it. value will be string type
I'm trying to concatenate a local phone number field. The LDAP system only has the last 5 digits but for the directory database we need all 7 digits.I've tried every combination I can think of to get the concatenation to work but every combination results in just the first two digits being imported, e.g.,
LOCAL_NUM "'20'||:local_num",
results in just 20 being imported. Every iteration I've tried that didn't result in an error imported only the 20 and ignored the ||. I've also tried calling the CONCAT directly, e.g.,
LOCAL_NUM "CONCAT('20', :local_num)",
result is the same.The problem seems to be that the loader is ignoring the concatenate statement all together. I've tried the statements outside of the loader via sqlplus with expected result so I'm confused as to why it's not working within the loader.
declare a clob; b varchar2; c clob; begin c:=a||b; end;
When execute above code in form runtime, I get error ora-32767.
"ORA-29287: invalid maximum line size Cause: An invalid maximum line size value was specified. Action: Correct the maximum line size to be in the range [1, 32767]."
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.
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 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 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´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?
Currently I have a requirement where I need to create 2 more output rows using each result row.
In my requirement I am populating charges table with types of charges, on each line item of charges, I need to apply 2 types of taxes and populate it along with the charge line item. I will be storing charges in table charges and the 2 taxes to be applied in taxes table respectively. For each row of charges, i need to apply these 2 taxes present in taxes table resulting in 3 rows output.
