PL/SQL :: How To Make SQL*Plus Count Comment And Blank Line Numbers

Jul 3, 2013

 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?

View 5 Replies


ADVERTISEMENT

Loader Skip Blank Line?

Mar 21, 2012

I am running Oracle 9 and using sql loader to import text file into table. Can sql loader skips the record which blank line or carriage return? Do I need to set up with options?

View 1 Replies View Related

SQL & PL/SQL :: UTL_TCP Returning Blank Line?

Feb 8, 2010

I am trying to use utl_tcp to connect, through TCP/IP, to a 3rd party program PC Miler. When I use telnet, PC Miler works without any issue. However, when I use utl_tcp, beginning from the second PC Miler API call, when I do a utl_tcp.get_line to get the result, the first line that return is always blank (with 1 white space). Here is my program.

---------------------------------------------------
DECLARE
c utl_tcp.connection;
ret_val PLS_INTEGER;
v_data VARCHAR2(4000);
BEGIN
c := utl_tcp.open_connection('mars', 8145);

[code]....

Note that the 1st and 3rd API call is the same and should give me the same results. The 2nd API is supposed to give me 1 line back. At the end of each output, the server will end will the word "Ready". I use that to indicate when to exit the loop.

If I disconnect and reconnect after each API call, each output result will be correct because each API call will become the "1st" call since connection - like this program:

DECLARE
c utl_tcp.connection;
ret_val PLS_INTEGER;
v_data VARCHAR2(4000);
BEGIN
c := utl_tcp.open_connection('mars', 8145);

[code]....

If there is something wrong with PC Miler, why would it work when I telnet through a Windows Command prompt? If it is not PC Miler and utl_tcp, what else can be wrong?

View 8 Replies View Related

Oracle Reports Blank Line In Output

Feb 21, 2008

i have a problem with oracle reports 6i. the output has 1000s of lines. in my output first to 999th line is fine..bt then 1000th line is blank and again the data continues to be shown from 1001th line till 2000. and again i see a blank. how can i get rid of these blank lines very 1000th line.

View 3 Replies View Related

Server Utilities :: Sql Loader Skip Blank Line?

Mar 20, 2012

I am running Oracle 9 and using sql loader to import text file into table. Can sql loader skips the record which blank line or carriage return? Do I need to set up with options?

View 1 Replies View Related

Forms :: Blank Line Getting Generated On Save Button

May 31, 2013

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.

View 1 Replies View Related

SQL & PL/SQL :: Count Blank Space In Sentences

Jul 3, 2012

Is There any way to count how many space in the sentences..Like this the sentences : " I AM BOY".

There is 2 space in the sentences.How can i find out using oracle query.

View 7 Replies View Related

Remove Count In First Line

Nov 27, 2009

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.

View 2 Replies View Related

Forms :: Get Count Of Line From Client_Text_Io.Get_Line

Mar 14, 2012

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]....

View 1 Replies View Related

Count Number Of Lines In Multi Line String

Aug 2, 2007

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?

View 2 Replies View Related

Client Tools :: Change Separator Line Of Compute SUM Or Count?

Jul 9, 2013

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.

View 3 Replies View Related

Client Tools :: Comment On Column With And Symbol?

Jul 31, 2012

I wanted to Comment on Column with & symbol. correct the syntax

Syntax
SQL> comment on column scott.emp.empname is ' Jerry&Tom. ' ;

ERROR
Enter value for t:
SP2-0546: User requested Interrupt or EOF detected.

View 5 Replies View Related

SQL & PL/SQL :: Extracting Numbers And Few Portion Of Text From A String Containing Dates / Characters And Numbers

Jul 13, 2011

I have a table test with column containing dates, characters and numbers. I have to extract the number part and the three characters before the number . My data looks like :

TEST
ID DATA
1 3/12/2007
2 0
3 3/8/2010 ABC 217
4 NONE
5 COLM XYZ 469 6/8/2011
6 LMN 209

My expected results should look like :

ID DATA
1
2
3 ABC 217
4
5 XYZ 469
6 LMN 209

View 7 Replies View Related

Spatial :: Convert Multi-line To Simple Line

Feb 13, 2013

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 :)

View 4 Replies View Related

Inserting Blank Data

Jul 17, 2009

I have a table in oracle sql developer showing years, IDs and attendance etc I'm wanting to create a report based on this table but have encountered a problem with the years.

The years currently run from 2006 to 2009 and the problem is that some of the IDs are only present for say 2007 and 2008, thus leaving nothing for 2006 and 09. This in turn creates problems in the report.

What i would like to do is in the table, for each ID that is missing any years, is to insert a single row with just the ID and year so that in the report it will show a blank instead of nothing at all. So for the above example i would have numbers for 2007 and 2008 and for 2006 and 2009 there would just be a space.

View 6 Replies View Related

SQL & PL/SQL :: Blank Record Pickup

May 25, 2010

I want to retrive record from tables which dont have person name

if i fire following query it gving 2 records one is correct and one is blank

select
distinct hp.party_name,
hppcv.PERSON_NAME
from hz_person_profiles_cpui_v hppcv,
AS_LEAD_CONTACTS_ALL ALCA,
hz_org_contacts_cpui_v hoccv,

[Code]..

what is problem?

View 8 Replies View Related

SQL & PL/SQL :: Blank Mail From SMTP

Mar 31, 2012

One more query need to be resolved.

1) I am getting a mail with only the SUBJECT through SMTP.

********* ALERT ********* Tablespace Alert Mail Send from DBA Team. ******** ALERT *********

2) This is from a procedure called 'TBLSPACE_MON_EMAIL'.

CREATE OR replace PROCEDURE "Tblspace_mon_email"
IS
v_smtp_server VARCHAR2(100) := '10.80.23.24';
v_smtp_server_port NUMBER(2) := 25;
v_crlf VARCHAR2(2) := Chr(13)
|| Chr(10);
[code].......

3) It was scheduled as below

What is the problem in this code.

View 18 Replies View Related

Reports & Discoverer :: Output Is Blank

Jun 2, 2010

I am trying to run Cash receipt report in reports builder. But the output is always blank. It is not even giving any fields created in the layout. This report was developed by some other developer, and there is no documentation available.

I am able to run the query in TOAD and I am getting genuine rows.

View 5 Replies View Related

SQL & PL/SQL :: External Table And Blank Strings

Sep 23, 2011

I am importing some data using an external table, but the file on which the external table is built has some rows where a certain column is populated with two empty space characters.

CREATE OR REPLACE DIRECTORY xtern_data_dir AS 'C:/...';

CREATE TABLE ET_RPDMMA1_PEDI_MSTR (
GCN_SEQNO NUMBER(6),
PDM_MNAGE NUMBER(4),
PDM_MXAGE NUMBER(4),
PDM_MND NUMBER(18,6),
[code].......

This is an except of what's in the external text file. The full text file has been attached.

000011|0030|....|000000.000000| |000000.000000| |0002
000011|0365|....|000000.000000| |000000.000000| |0002
000011|0730|....|000000.000000| |000000.000000| |0002
^
blank spaces may be |
causing error----------

Here is the error message I am receiving. I believe this is caused by the blank fields in the data.

INSERT INTO RPDMMA1_PEDI_MSTR (GCN_SEQNO,....
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52

This is difficult to work with because the external table function does not appear to be even reading the file so it's not like I can convert the data as I'm loading into the internal database table. What are some approaches I can use to get Oracle to accept these blank columns and either populate them with blank spaces or set them to null?

View 12 Replies View Related

SQL & PL/SQL :: Mark Repeating Values As Blank

Jun 1, 2010

I have a result set with the following structure

Column 1 Column 2 Column 3 Column 4
--------- --------- --------- ---------
A1 B1 10 50
A1 B1 20 50
A1 B2 30 40

But i want to restructure this result like below,

Column 1 Column 2 Column 3 Column 4
--------- --------- --------- ---------
A1 B1 10 50
20
B2 30 40

I am just trying to change the repeating values on a certain combination as blank.

View 5 Replies View Related

SQL & PL/SQL :: Oracle MAX Date - BLANK Row Returned?

Aug 2, 2013

If running the following query:

select s.effective_date
from SECURITYDBO.DERIVATIVES s
where s.security_alias=100014320 and s.src_intfc_inst=0;

There are no rows returned.However, if I am running the other query:

select MAX(s.effective_date)
from SECURITYDBO.DERIVATIVES s
where s.security_alias=100014320 and s.src_intfc_inst=0;

There is a BLANK row returned.

View 11 Replies View Related

Leaving Connection String Parameter Blank

Sep 4, 2007

I have an oracle 10 xe database. I have a user named lotions. I am trying to connect to this database using Erwin Data Modeler. I entered the user name and password and first tried leaving the connection string parameter blank. If I do so I get an ora-01041. If I put 'xe' in this parameter it says "this parameter is incorrect".

View 1 Replies View Related

Number Field Coming As Blank But Length 1

Aug 12, 2013

column with datatype Number contains length 1.Its not null.Tried to trim then also length is 1 ....How to find what is the character there..?

View 1 Replies View Related

Forms :: Webutil 1.0.6 In 10g Opens Blank Page

Jul 8, 2009

I have developed a form which has webutil objects in it, and it is working fine on my local machine. When I deploy my form to my application server it opens up a blank page. I have configured the webutils in the app server as per the document and made entries into formsweb.cfg regarding frmwebutil.jar,jacob.jar. Does the dev suite need to be installed in the server for the webutils to run properly?

Also I watched my java console as I opened my form and I am pasting the contents below:
-----------------
Loading frmwebutil.jar from JAR cache
Loading jacob.jar from JAR cache
proxyHost=null
proxyPort=0
connectMode=HTTP, native.
Forms Applet version is : 10.1.2.3
[code]...

View 14 Replies View Related

SQL & PL/SQL :: Remove Blank Spaces In The Query Output

May 6, 2010

I write a Query to get Deptno wise Employee Names as fallows..

SQL> select max(decode(d.deptno,10,e.ename)) dept10,
2 max(decode(d.deptno,20,e.ename)) dept20,
3 max(decode(d.deptno,30,e.ename)) dept30
4 from emp e,
5 dept d
6 where e.deptno = d.deptno

[code]....

But i don't want to include the blank(null) spaces in the query output.

The Required output should be like this..

DEPT10 DEPT20 DEPT30
------ ------ ------
CLARKADAMSALLEN
KINGFORDBLAKE
MILLERJONESJAMES
SCOTTMARTIN
SMITH TURNER
WARD

View 5 Replies View Related

OEM 11g - Monitored SQL Executions Click ID Shows Blank Screen?

Jun 10, 2011

I am trying to investigate my execution plans against my DB; and was informed i can check current status of SQL scripts using:

Performance tab > SQL Monitoring > Monitored SQL Executions

I click on SQL ID i want to investigate and comes back with blank screen instead of the overview tab.

See Oracle URL below for screenshot

Figure 6-8 Monitored SQL Execution Details Page

[URL]

View 2 Replies View Related

Application Express :: Upgrading To 4.2 On XE 11.2  / Blank Login-Page

Oct 24, 2012

I installed APEX 4.2 over XE 11.2

The Installation process succeeded.I Used the Installation Guide -> and followed the steps from chapter 3...My problem after the Apex-Upgrade ->

The page URL.... is blank, or say it better, ... there is nothing visible on that page (no Login-Form)The original GetStarted page from XE Database -> URL.... shows an Error Error Error processing request.ORA-01403: no data found

I can post the sourcecode from the "blank" HTML-Page...

View 20 Replies View Related

Application Express Listener :: Page Is Blank And Freezed

Jan 15, 2013

Problem is that page is blank and freezed. After another enter in URL page is shown normally.What I find out is that "freeze" happened only in Apex design time ... never in runtime of any of mine app.

Mine conf:
Apex 4.2.1 on
Apex Listener standalone 2.0.0.354.17.06
Oracle 11.2.03 x64
Windows 2008 SE R2 x64

C:UsersDamirV>java -version
java version "1.6.0_34"
Java(TM) SE Runtime Environment (build 1.6.0_34-b04)
Java HotSpot(TM) 64-Bit Server VM (build 20.9-b04, mixed mode)error log:
D:UsersdamirvDesktopApexApex-Misc>set ORACLE_HOME=c:oracleproduct11.2.0dbhome_1
 [code].....

View 4 Replies View Related

Enterprise Manager :: Blank Content Instead Of Database Performance And Activity?

Feb 4, 2010

I've installed Grid Control (aka OMS) 10.2.0.5 and try to look at the Database Performance. But instead of information I receive headers and blank picture in the place where usually all charts are shown. As a matter of fact it looks like the page tries to access that data source, but fails in that and shows just a symbol of picture that was not loaded and all menus from that page.

View 2 Replies View Related

Client Tools :: How To Eliminate Blank Lines In Between Queries In Output

Apr 28, 2010

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
*/

[code]....

and the output looks like this:

HDR,04272010,Plan Data

CMT,Plan Num,Plan ID,Plan Name,Shares Allocated

DAT,1,01,Plan 01,99999999
DAT,2,02,Plan 02,99999999
DAT,3,03,Plan 03,99999999
DAT,4,04,Plan 04,99999999
FTR,4

but the client and I want the output to look like this with no blank lines in between the queries:

HDR,04272010,Plan Data
CMT,Plan Num,Plan ID,Plan Name,Shares Allocated
DAT,1,01,Plan 01,99999999
DAT,2,02,Plan 02,99999999
DAT,3,03,Plan 03,99999999
DAT,4,04,Plan 04,99999999
FTR,4

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved