how to get the count from Client_Text_Io.Get_Line??
For Example :
Client_Text_Io.Get_Line(vOutFile,voutput);
vBaris := voutput + 1;
If vBaris = 3 then
v_reg := SUBSTR (voutput, 1, 3);
tanggal := SUBSTR (voutput,1, 4);
v_outlet := SUBSTR (voutput, INSTR (voutput, ',', 4) + 1, 3);
v_refer := SUBSTR (voutput, INSTR (voutput, ',', 6) + 1, 4);
[code]....
My query is suppose to capture the student's number, date of birth, and number of students which that students mentors. I am suppose to show only the 5 oldest students
SELECT s.std_num, s.birth_date, COUNT(s2.mentor_num)
From Student s left outer join Student s2
on s2.mentor_num=s.std_num
GROUP BY s.std_num, s.birth_date
ORDER BY s.birth_date ASC;
This function works but the problem is in displaying the five records because whenever I apply the rownum<=5 function the counter returns incorrect results. I tried writing another query to fix it but I could not dispay the counter anymore
SELECT ROWNUM as SENIOR, s.std_num, s.birth_date, COUNT(s2.mentor_num)
FROM (SELECT s1.std_num, s1.birth_date, COUNT
(e2.super_id)
From Student s left outer join Student s2
on s2.mentor_num=s1.std_num
GROUP BY s1.std_num, s1.birth_date
ORDER BY s1.birth_date ASC) S
WHERE row_num<=5;
This code only works if I remove the count in the first line but I need to display the number in my result.
I have a string like this:
s_list varchar2(234) :=
'asdasd
asfsdf
dsfsdfs
dfsdfs';
How can I find the number of lines in this string? I tried using
INSTR('s_list', '
', 1, 1)
but it gives 0.
Is there any inbuilt function/proc SQL or PL/SQL which can do this?
ParameterParameter valueOracle versionEnterprise Edition Release 11.2.0.1.0 - 64bitOSLinux Fedora Core 17 (X86_64)
is there any way to force SQL*Plus to provide the actual line number in the source file whenever there is an error? I often put at the first line of my script SET SQLBLANKLINES ON so that I may be able to put several consecutive blank lines lines in my code (I do this sometimes, when I find it to be appropriate in order to make my code more readable or to group a sequence of instructions which I believe make their logical link more understandable for the reader) Now the problem is that SQL*Plus ignores these blank lines and whenever there is an error, the line number provided in the error message, doesn't correspond to the actual line number in the source file but it seems to be the last non blank line in the file. Consider the following example:
SET SQLBLANKLINES ON;DECLARE var PLS_INTEGER := 10;BEGIN var := 20END;/ In the above code at line 9
(by counting also the blank lines) there is an error (no semicolon at the end of var := 20) but when I run the script inSQL*Plus here is the error message that I get
SQL> @myscript.sql;END;*ERROR at line 8:ORA-06550: line 8, column 1:PLS-00103: Encountered the symbol "END"
when expecting one of the following:* & = - + ; < / > at in is mod remainder not rem<an exponent (**)> <> or != or ~= >= <= <> and or like like2like4 likec between || multiset member submultisetThe symbol ";" was substituted for "END" to continue.SQL> So as you can see the error message indicate that the error was found at the line 8 whereas in the file it is really at the line 9 As long as the number of lines in the script is limited, this may not be a problem and one can find rapidly the actual line number in the code raising the error but for a code including hundreds (or even thousands) of lines and with many comments and blank lines, finding the actual line number given by SQL*Plus error message becomes tricky. Is there any way to make SQ*LPlus trace correctly the line numbers as they appear in the source file?
Is that possible to modify the ***** and ----- in the TEXT report?
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
============= ========== ========================= ==========
20 MK_MAN Hartstein 13000
************* ********** ----------
sum 13000
From above lines, you can see ****** and ----- which are above sum&13000.
Is that possible to change them as '='?
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
============= ========== ========================= ==========
20 MK_MAN Hartstein 13000
============= ========== ==========
sum 13000
"set underline =" is only shot the headers.
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
i am trying to read the file, for which i m using the following:
l_utlfile_hdr := utl_file.fopen(p_dir,'header_evnt.txt', 'R');
utl_file.get_line(l_utlfile_hdr, l_hdr_evnt);
it gets these three characters at the start of the line
l_hdr_evnt = 
So i saved header_evnt.txt as UTF-8, and used utl_file.fopen_nchar, utl_file.get_line_nchar, which got rid of first two characters, but still has ¿. How to get rid of that?
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
We are processing spatial data from another source to display in our GIS environment. The data is a set of multi lines. The gtype is 2006. A typical geometry looks like:
SDO_GTYPE 2006
SDO_SRID 31370
SDO_POINT.X NULL
SDO_POINT.Y NULL
SDO_POINT.Z NULL
SDO_ELEM_INFO (1,2,1, 7,2,1)
SDO_ORDINATES (105094.84, 195084.96,
105094.54, 195080.22,
105094.84, 195084.96,
105094.84, 195084.96,
105094.68, 195082.47 )
Now, this is not an actual multiline... it's just encoded as a multi line, but if you look at the coordinates you'll see that the end point of the first line is the same as the beginning of the second line (105094.84, 195084.96).
A better way to encode this geometry would be:
SDO_GTYPE 2002 <---
SDO_SRID 31370
SDO_POINT.X NULL
SDO_POINT.Y NULL
SDO_POINT.Z NULL
SDO_ELEM_INFO (1,2,1)
SDO_ORDINATES (105094.84, 195084.96,
105094.54, 195080.22,
105094.84, 195084.96, <---
105094.68, 195082.47 )
Is there a standard function in Oracle that does this conversion for me? Or do I have to write my own :)
how to display tab pages in line level?
View 1 Replies View RelatedHow to find the line number along with package name or procedure name (for ex. in Oracle 10g DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ) where exactly error is coming in forms.There is plsql code written and while executing the code, ORA-01722: invalid number error is coming. For easy tracing the error, any way is there ?
I searched in Google before posting in this forum and found that ON-ERROR trigger if we write DBMS_ERROR_TEXT it will display the ling number along with package or procedure name. But it is not working and this is only giving me error name no line number.
how can i create line graph on oracle 11g forms
View 1 Replies View RelatedWe have Forms 10g for our application.
When i raise a exception in a procedure / trigger, i want to show the line number where the exception happened. is there any builtin to show that.
For eg
Exception when others
message('Error in this procedure');
raise;
I want the above exception to raise with line number also. How do i do it. I need some built in procedure in forms.
I am working on Oracle forms 6i. I am getting a problem when I am trying to save the value in the form.
Well the scenario is like this:
I have got a multi record block. In that block there are two items jobno and inoivce no. Both the items have an LOV attached to them giving jobnos and invoice nos respectively.
This is on the '*ORIGINAL_BLOCK*'.
The problem perists with the jobno field. In the LOV for jobno, the 'column mapping properties' property, the return item section has original_block.jobno as the value. The code to fetch the details about the jobno (like vendor no,vendor site,invoice no etc) has been written on the When-Validate-Item trigger. I am also updating the WHO colums(like created by,created date,last upated by, last update date) on the Pre-Insert trigger at the block level. This whole thing of fetching the records is working fine. But when I simply press the save button an extra blank line gets generated on the form as well as at back end with jobno like this:
Sr.noJOBNO vendor_no vendor_name invoice_noinvoice_date
1 123 37456 abc 4564565 26-APR-2013 --- correct entry
2 123 8574 xyz 645656 26-JAN-2013 --- correct entry
3 123 --- --- --- --- unrequired blank entry
A same jobno can have multiple number of invoices. So the lines getting generated are - number of lines + 1(unrequired entry).As in the above scenario there are only 2 invoices for the same job and a third unrequired entry getting generated. If there are 3 invoices I get 4 lines (3 required + 1 unrequired blank entry).
So to solve this issue I added a button 'Add JOB'. I took another '*NEW_BLOCK*',designed another canvas and and added an item on it containing a dummy jobno field and attached an LOV to that item. Then the same When-Validate-Trigger to that item fetching the details. so when I press the button 'Add JOB' using show_canvas and go_item(dummy_job_item) I navigate to that new canvas, select the jobno there from LOV, fetch the details. Now I wrote execute_query on KEY-EXIT of the NEW_BLOCK so that whatever are the fetched details, they would go on the ORIGINAL_BLOCK.jobno and respective details in resp fields. But this did not work.So I wrote execute_query on the When-New-Block-Instance of the ORIGINAL_BLOCK and it worked.
But now what is happening is when I open the form once again after closing the form, all the back end data is appearing on the form i.e. when I open a new form session every time. But I want the form to be clear with no entries displayed whenever I open the form. I understand the question is lengthy and tricky.
How can we design a form with header level and line level. here i need to place a button in header section.when we press that button, line level values should be populate.
View 1 Replies View RelatedI am working in form 6i, EBS 11i. I have below requirement to achieve.
I have header data block A, line control block B. My requirement is, i know that in master detail block, if i press F11 in header block, it will clear the line block, like this, in my case if i press F11 in header data block A, i want to clear the line block B. Is there any way we can achieve this.
I have two blocks.In header 2 columns are there same columns in line how to populate default values in line level based on header?
View 1 Replies View RelatedAfter running my report I generate into the file by delimited type and then I save as it by XLS extension.The problem is each row of this excel file has header repeatedly!
View 4 Replies View RelatedI have a list block of two fields item_name, item_price. User can enter as many item`s as she/he wants. But, before saving the record system should calculate the total and give error if sum is grater then or less than 100.
View 2 Replies View RelatedWhy showing at the status bar "3 records applied and saved" instated of "2 records applied and saved". 2 means 1 for Header and 1 for Detail. How I change status bar.
View 7 Replies View Relatedhow to count how many items present in a particular data block in oracle forms 10g.whether it is a text_item or display_item or list_item etc is there any method to do this.
i have written this when-button pressed but the problem is how to get the next item name in the block
DECLARE
V_COUNT NUMBER:=0;
V_FIRSTITEM VARCHAR2(100):='';
BEGIN
GO_BLOCK('BLOCK_ITEMCOUNT');
V_FIRSTITEM := GET_BLOCK_PROPERTY('BLOCK_ITEMCOUNT', FIRST_ITEM);
LOOP
[code]....
i have a master detail form, In Master block we have one field cheque amount and in Detail block we have field receiveable amount invoice wise. if company paid us a cheque amount we will enter this amount in Master block field Cheque amount and in detail block there will be invoice wise receivable amounts. i want to distribute the cheque amount in detail block invoice wise for example
Cheque amount in master block = 291
Invoice wise receiveable amount is as follows
Invoice No , receivable amount , Received amount
10, 196 , 0
20 , 95 , 0
30 , 54 , 0
Result should be as follows:
Invoice No , receivable amount , Received amount
10, 196, 196
20 , 95 , 95
30 , 54, 0
Received amount field should be distributed according to the receivable amount when recevied amount = cheque amount then remaining will be 0.
When we execute select count(*) from table_name it returns the number of rows.
What does count(1) do? What does 1 signifies over here? Is this same as count(*) as it gives the same result on execution?
difference between count(1) and count(*). As i know count(*) will give number of rows irrespective of null and count(1) will not count the null.My Oracle version is 10 g.
SQL> select * from t1;
A B C
---------- -------------------- --------------------
1 2 3
2
5
SQL> select rownum,a.* from t1 a;
ROWNUM A B C
---------- ---------- -------------------- --------------------
1 1 2 3
2 2
3 5
4
[code]....
i want to count how many check boxes are chechked and if the check box is unchecked the count will decrease as soon as checkbox is check or uncheck.the number of chech boxes are depentdent on record set that are fetched from database.
View 2 Replies View RelatedI have a table
Line_num Amount
1 10
2 20
3 30
create table Test (line_num number, amount number);
insert into test values(1,10);
insert into test values(2,20);
insert into test values(3,30);
I need to get the line number which has a maximum amount.
I have the folloiwng two queries:
Query_1: select count(*) yy from table1;
Query_2: select count(*) zz from table2;
I need to compute the following:
var:=(yy/zz)*100
How can I achieve this in a single query?
I'm using this code, and it performs fine, but I'm wondering if there is a more elegant way to do it--maybe with "ROLLBACK". Basically (as you can see) I need to get a normal count for each group but also for each group take a percentage of the total count (so all groups pct adds up to 100 (oh yeah, don't test for zero below, but just a test... )
select
c.Event,
c.code,
count(1) as calls,
total.total_count,
count(1) / total.total_count * 100 as pct_of_total
from
table1 c
[Code]....
[Edit MC: add code tags, do it yourself next time]
how to get more lines into one line per customer.
For instance:
CREATE TABLE XXX_MAPE_CC
(
accounting_month_key number,
customer_key VARCHAR2(18 BYTE),
total_amount VARCHAR2(29 BYTE)
)
insert into XXX_MAPE_CC
values (201205, 313774201, '15,03')
[code]...
And I would like get every total_amount to individual columns by accounting_month_key
--------------------------------------------------------------------------
customer_key month_201205 month_201206 month_201207
----------------------------------------------------------------------------
313774201 15,03 10,03 13,10
----------------------------------------------------------------------------
I tried writing a decode statement but it doesn't work as expected. The data I'm working has 4 different brand names with an associated code. I tried using decode to get the 4 brands to be on one line like this: Description, Inventory_Item, Product_Line, Product_Type, Brand_1, Brand_2, Brand_3, Brand_4..I still get 4 lines with the Brands displayed like a stair steps.
What do I have to do to get the data on one line? My code is as follows:
SELECT sgk_invfg_organization_items.description,
sgk_invfg_organization_items.inventory_item,
sgk_invfg_organization_items.product_line,
sgk_invfg_organization_items.product_type,
Decode(sgk_item_master_launch_v.sgk_model_item, 20,
sgk_item_master_launch_v.sgk_model_number) brand_1,
Decode (sgk_item_master_launch_v.sgk_model_item, 30,
[code]...
I am trying to get a line of best fit for a set of data in oracle
TERM Count
TERM_080178
TERM_080258
TERM_08030
TERM_080482
TERM_08050
TERM_08060
TERM_080732
TERM_08080
TERM_090192
[code]...
I want run sql connection and command in one line , something like this ...
sqlplus "/as sysdba" 'select * from dual'
is it possible to do it in one command ?
