SUBSTR ignored

[Laura Birch]

Hello. I'm new to PL/SQL Developer (v 10.0.5.1710); I previously used PL/SQL Worksheet. I find that I am unable to do simple data manipulation, such as a SUBSTR function; it is simply ignored. I'm using the Command Window. Also, the field width in the output/Dialog window does not correspond with the actual table field length. Is there something in setup that I am missing? I really miss Worksheet! Thanks for your help!

 

[Maxmix]

Hello Laura,
I don't use Command Windows often, but is seems everything works
(had to search in the documentation about COLWIDTH and its default value):

SQL>
set echo on

select rpad('asdf',200,'g') mytext, substr(rpad('asdf',200,'g'), 1, 15) mytextpart from dual;
MYTEXT                                                                           MYTEXTPART
-------------------------------------------------------------------------------- ---------------
asdfgggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg asdfggggggggggg

set colwidth 200

select rpad('asdf',200,'g') mytext, substr(rpad('asdf',200,'g'), 1, 15) mytextpart from dual;
MYTEXT                                                                                                                                                                                                   MYTEXTPART
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------
asdfgggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg asdfggggggggggg

 

[Laura Birch]

Thanks for your input, Maxim. Just to clarify, here is an example of the issue I am experiencing:

A table has a field that is defined as 60 characters...VARCHAR2(60). In PL/SQL Worksheet, I was able to use the following command to pull a portion of that field (ie: the first 30 characters):

SQL> select SUBSTR(last_name,1,30) from nametable where ID = 123456789;
SUBSTR(last_name,1,30)
--------------------------------------------------------------------------------
Smith

The result of the query in Developer (Command window) is to output the last name with MORE than the 30 characters desired, and MORE than the table/field's 60 characters; it's outputing 81 charaters. Where is this coming from? Is there a default setting that I need to adjust? Is there a different function (besides SUBSTR) that I should be using to manipulate how each field appears in the output? Is there a SET command that I have to run each time I run similar queries that will allow me to at least output accurate table/field widths?

I can get the results I expect from Query Reporter (using SUBSTR), but I can only run one query at a time. I need to be able to run scripts that include many queries together, and I need to be able to manipulate the output for each query/field.

Guidance to a rookie Developer user is most welcomed!

 

[Laura Birch]

Also, I need to be able to manipulate each field individually. By using SET COLWIDTH, all subsequent columns are defaulted to the specified single column width; for example, I need to be able to pull 30 characters of the last name and 20 characters of the first name, all in one query. How is this accomplished in Developer?

 

[mike]

I recommend using the SQL window. It will make a nice grid from the output and you can export it to Excel and all kinds of stuff.

If you need to use the command window, it is working for me. I suspect that the substr is working, but the width of the column is longer than you like. You can limit all of them, or you can limit one column. This is how:

-- set maximum column width to 20
SET COLWIDTH 20

-- set myname column to 8 characters
COL myname FORMAT a8

SELECT full_name
       ,SUBSTR(full_name, 1, 5) myname
FROM   per_people_f ppf
WHERE  person_id = 1234;

 

[Maxmix]

substr is adequate in most cases.

it's outputing 81 charaters

Do you mean you see non-whitespace?
What is it then? Garbage? Chunks of adjacent columns' values?

I would like to look at your output!

 

[Laura Birch]

Hello, Maxim.

The "outputing 81 characters" refers to the column width. If I specify SUBSTR(xxx,1,30), I expect to get a column that is 30 characters wide. Instead, I'm getting a column width that is wider than the actual table column; there appears to be a default set somewhere, and I don't know how to change/override it.

Hello, Mike.

I started out with the SQL window, but it allows only one query at a time in a single window/tab; I moved to the Command window because it allows me to run a series of queries together and SCRIPT the output to a text file. It is just not formatting well...the columns are way too wide, making the output difficult to read/use.

I tried the commands you suggested, but my output did not change; it appears to be ignoring the commands. I keep wondering if there is something in my setup/preferences that is causing this issue. I thought this would be a simple question that would have a simple answer.

It is unfortunate that something that used to be so simple and basic (in SQL Worksheet) is now complicated and complex (in Developer). But I guess that is "progress."

I do appreciate your efforts in trying to help me, Maxim and Mike! THANK YOU! I will contact Allround Automations to see if there is something I can change in my setup.

 

[mike]

It does sound like a support issue if these commands are ignored.

If I'm understanding what you want, you can turn the "Auto Select Statement" preference on under the SQL Window preferences. Then, if you end each SQL statement with a semi-colon, it will execute just the SQL statement that the cursor is on. If you highlight the whole window and execute, it will execute all statements on the page.

 

[Maxmix]

I see.
My last guess ;-)
By chance, don't your strings contain TAB characters?
In such case, output could appear visually wider (notice however, how the header is as narrow as it should be):

SQL> select LPAD('A',3,CHR(9)) as t9 from dual;
T9
---
		A