Sql Plus

[JaedenRuiner]

Can PL/SQL Developer to SQL Plus commands?

Alot of my coworkers use SQL plus through a CRT terminal, and thus they use some differing commands, but all of those commands are accepted by SQL plus and Oracle.

example:

accept MyVar char prompt 'Enter Value';

  select from mytable where mytable.column1 = &MyVar;

the accept line is kicked out as an invalid SQL statement.
Can't i set up PLSql Developer to recognize SQL Plus commands or something?

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[mike]

Use the command window for a SQL Plus like session (File/New/Command Window).

The dialog tab is like SQL Plus. The editor tab is good for SQL Plus scripts that you edit/save/run.

 

[JaedenRuiner]

Cool. I'm glad there is a way, but now i'm having some trouble using the Command Window method to run my SQL files. The very same files I've written in a SQL Window won't run right through the command window.
I have like:

Drop table mytable;
create table mytable parallel as (
  select data, id
  from othertable
  where somerestriction = 'y'
);
commit;

From the Editor window when i run this it sort of elminates the semicolons, and fails miserably, but when i run it from a SQL Window it works fine.

What's the difference between the two so that I can write my sql to work with the command window view.

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[mike]

By "sort of eliminates the semicolons", do you mean that it runs all SQL statements and errors at the drop statement?

If that is what you mean - That is supposed to happen. The command window runs scripts and will run all SQL statements from the top to the bottom.

Try modifying it like this:

-- Continue if the table doesn't exist...
WHENEVER SQLERROR CONTINUE
Drop table mytable;
-- re-set script to fail if errors occur...
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

create table mytable parallel as (
  select data, id
  from othertable
  where somerestriction = 'y'
);
commit;

The SQL window is better at select statements where you want the output placed in the nice grid. It can also automatically select the SQL statement that your cursor is placed on if you have your preferences set up that way.

Mike

 

[Gustavo]

Or you could try

Drop table mytable
/
create table mytable parallel as (
  select data, id
  from othertable
  where somerestriction = 'y')
/
commit -- you don't need this commit for ddl
/

This should work in both tools.

 

[JaedenRuiner]

Cool.

I've found that one of my main problems was formatting, because i'd have blank lines between the create table and select statements and the command was interpreting that as an error.

create table mytable parallel as (

  Select * from othertable

);

IN the SQL Window this looks easier to read, but in the command window it has issues. *chuckle*

On another note, though, I've noticed something odd with the Command Window behavior, in the use of the "echo" command. Many of my coworkers use SQL*Plus through telnet access, and thus they use alot of the SQL*Plus commands in their code. One in particular is rather helpful and yet PL/SQL Developer seems not to like it.

SET TIMING ON;
SET PAGESIZE 50000;
SET LINESIZE 120;

!echo Cleaning up table...

drop table mytable;

!echo Creating Base Table...

create table mytable parallel as (
  select col1, col2, col3, col4
  from table1 inner join table2 using(col1)
  where col2 in (value1, value2)
);
commit;

!echo Done.

Now in SQL*Plus this would basically output the text to the right of the echo and thus you would see the limited output of each statement from the SQL*Plus (like elapsed time and how many rows were inserted, etc) but would also tell you other information that was helpful.
PL/SQL Developer doesn't like the command at all, and I am forced to "SET ECHO ON" in my scripts in order to see what it's doing.

Is there a way to achieve this same effect, whether with the ECHO command or some other command specific to PL/SQL Developer?

 

[ScottMattes]

The !echo is doing a host command to your unix system, it isn't a sqlplus command

 

[ScottMattes]

'prompt' is the sqlplus command

 

[JaedenRuiner]

Awesome.. You rock!

 

[aotte]

For your blank lines problem, the SQL*Plus manual says:

SET SQLBL[ANKLINES] {ON | OFF}
Controls whether SQL*Plus puts blank lines within a SQL command or script. ON interprets blank lines and new lines as part of a SQL command or script. OFF, the default value, does not allow blank lines or new lines in a SQL command or script or script.

It has been added to the command window in 2003 and works likes a charm, but somehow never made it into the manual. (Marco, any chance it makes the manual in 2007?)

Check out page 46 or section 7.3 of the manual for all other SQL*Plus commands supported by the Command window. It is one of the features of PL/SQL Developer I have come to love.

 

[William Robertson]

A lot of my old SQL*Plus scripts put all the SET commands in one string:

set pause off pause >> lines 111 pages 999

The command window needs these to be in separate lines:

set pause off
set pause >>
set lines 111
set pages 999

Once I'd realised that & updated my scripts I found almost all of them worked like a charm. (Just a tip in case you're having problems.)