Passing Variables for IN statment

[jhchan]

Hi all,

I was writing a script (to be called by another) that would pass
variables to be used in an IN statement. Something like this:

DEFINE &1 -- variable for IN statement
DEFINE &2 -- table name

SELECT distinct item1, item2
FROM &2 -- table name
WHERE item3 IN (&1)

When I run this from the SQL command prompt (@ test.sql "'FD','FDC"
my_table) I get a couple error messages but it still runs and
completes the task. The error message looks like this:
SP2-0553: Illegal variable name "'FD','FDC'".
SP2-0136: DEFINE requires an equal sign (=)

But when I call it from another script it refuses to run and bombs.
Can anyone help me out with this?
I'm pretty new at all this and a lot of what I learned was from a long
time ago so my memory's a bit foggy with terminology and techniques.

[Rob Wolfe]

for "define" and substitution variable information in general

http://download.oracle.com/docs/cd/B14117_01/server.101/b12170/ch13.htm#i2697507
http://download.oracle.com/docs/cd/B14117_01/server.101/b12170/ch6.htm#sthref998
http://download.oracle.com/docs/cd/B14117_01/server.101/b12170/ch6.htm#sthref1009

A resource that is all to often overlooked is http://tahiti.oracle.com/

all oracle documentation is online there.

And yes .. this is just a polite version of RTFM. I wouldnt normally
do it but it probably took me less time to find the answer to your
question there than it did for you to type in your post.

basically ... you dont ever do something like "DEFINE &1" The
documentation explains it better than i do though (complete with
examples) so ....

and yes .. i really DO hope this helps

Rob

[jhchan]

Thanks. I'll do some reading. But I'm not sure that's answering
exactly what I need my programs to do.

Program 1 will do a loop that will call program 2.

Prog1:

DECLARE species = "'H','HM','HW'"

For i in 1..6 Loop -- i will be the leading species value
@ prog2.sql &species i
END Loop;


Prog 2:

DEFINE &1 -- take input from prog 1 for species value
DEFINE &2 -- take input from prog 2 for primary, secondary, tertiary,
etc to 6 species

UPDATE table
-- do stuff in here
blah blah blah
;


I'm definitely a newbie in CompSci stuff and I'm not an acronym person
so RTFM might as well be martian to me. But any help
that anyone can provide is much appreciated. I didn't find much in
terms of examples on the web other than the most simple
where they count from 1 to 6 and spit out a line using
dbms_output.put_line and I need much more than that.

On Feb 6, 11:46 am, Rob Wolfe <wolfe....@gmail.com> wrote:
> for "define" and substitution variable information in general
>

> http://download.oracle.com/docs/cd/B14117_01/server.101/b12170/ch13.h...http://download.oracle.com/docs/cd/B14117_01/server.101/b12170/ch6.ht...http://download.oracle.com/docs/cd/B14117_01/server.101/b12170/ch6.ht...

> > time ago so my memory's a bit foggy with terminology and techniques.- Hide quoted text -
>
> - Show quoted text -

[Rob Wolfe]

I am going to suggest that you probably are taking the wrong approach
to solving the problem in the first place.

There isnt really any reason (and it is the wtong thing to do ) to
break what you are trying to do into two complete scripts. You might
want to read the section in the PL/SQL users guide on subprograms
(functions and procedures) because that is what you REALLY want to be
doing ...

I would actually kind of surprised that the following fragment would
run ....

For i in 1..6 Loop -- i will be the leading species value
@ prog2.sql &species i
END Loop;

Don't get SQLPLUS and PL/SQL confused. They are very different and I
think that you are conflating the syntaxes of the two.

You mention that you are not a CS person. I would suggest that if that
is the case you stay as far away from using SQLPLUS and PL/SQL as you
can. You would probably be far better off, if you need to learn a
scripting language, to learn Ruby or Python. You can easily access
Oracle (or any other data source) data from them and they are far more
flexible and friendly.

RTFM .. means "Read The F'ing Manual". If you arent a programmer to
start with ... I would say that you should change the manual you were
going to read :-)

On Feb 6, 4:41 pm, jhchan <jhcha...@gmail.com> wrote:
> Thanks. I'll do some reading. But I'm not sure that's answering
> exactly what I need my programs to do.
>
> Program 1 will do a loop that will call program 2.
>
> Prog1:
>
> DECLARE species = "'H','HM','HW'"
>
> For i in 1..6 Loop   -- i will be the leading species value
>    @ prog2.sql &species i
> END Loop;
>
> Prog 2:
>
> DEFINE &1  -- take input from prog 1 for species value
> DEFINE &2  -- take input from prog 2 for primary, secondary, tertiary,
> etc to 6 species
>
> UPDATE table
>  -- do stuff in here
> blah blah blah
> ;
>
> I'm definitely a newbie in CompSci stuff and I'm not an acronym person
> so RTFM might as well be martian to me. But any help
> that anyone can provide is much appreciated. I didn't find much in
> terms of examples on the web other than the most simple
> where they count from 1 to 6 and spit out a line using
> dbms_output.put_line and I need much more than that.
>
> On Feb 6, 11:46 am, Rob Wolfe <wolfe....@gmail.com> wrote:
>
>
>
> > for "define" and substitution variable information in general
>

> >http://download.oracle.com/docs/cd/B14117_01/server.101/b12170/ch13.h......

> > - Show quoted text -- Hide quoted text -

[Michael Moore]

RTFM means "Kindly refer to the Manual"

[Michael Moore]

I think you need to understand that
@ prog2.sql &species i

is an INCLUDE

In other words, the content of @ prog2.sql is inserted at that location BEFORE any attempt is made to run the script.

this is what you are actually running:

Prog1:

DECLARE species = "'H','HM','HW'"

For i in 1..6 Loop   -- i will be the leading species value

  DEFINE &1  -- take input from prog 1 for species value
DEFINE &2  -- take input from prog 2 for primary, secondary, tertiary,
etc to 6 species

UPDATE table
 -- do stuff in here
blah blah blah
;

END Loop;

DEFINE &1  -- take input from prog 1 for species value
DEFINE &2  -- take input from prog 2 for primary, secondary, tertiary,
etc to 6 species

UPDATE table
 -- do stuff in here
blah blah blah
;

to demonstrate:

part1.sql is a text file that says:
select *

part2 is a text file that says:
into abc from dual;

now I run this script
SQL> declare
  2  abc varchar2(5);
  3  begin
  4  @c:\sql\part1.sql
  5  @c:\sql\part2.sql
  6  end;
  7  /

PL/SQL procedure successfully completed.

the script interpreter does not call part1.sql process it and then call part2.sql and process it.Instead, it inserts part1 and part 2 before it ever runs. So what really runs is ...

SQL> declare
  2  abc varchar2(5);
  3  begin
  4  select *
  5  into abc from dual;
  6  end;
  7  /

Get it?

[jhchan]

Thanks Michael. That makes sense now and makes the previous post make
sense to me as well.

I guess I'll try to include it all in one big PL/SQL script and have a
procedure(?) that gets run several times with the loop.

> the script interpreter does *not *call part1.sql process it and then call

> part2.sql and process it.Instead, it inserts part1 and part 2 before it ever
> runs. So what really runs is ...
>
> SQL> declare
>   2  abc varchar2(5);
>   3  begin
>   4  select *
>   5  into abc from dual;
>   6  end;
>   7  /
>
> Get it?
>

> On Feb 6, 2008 3:34 PM, Michael Moore <michaeljmo...@gmail.com> wrote:
>
>
>
> > RTFM means "Kindly refer to the Manual"
>

> > On Feb 6, 2008 1:41 PM, jhchan <jhcha...@gmail.com> wrote:
>
> > > Thanks. I'll do some reading. But I'm not sure that's answering
> > > exactly what I need my programs to do.
>
> > > Program 1 will do a loop that will call program 2.
>
> > > Prog1:
>
> > > DECLARE species = "'H','HM','HW'"
>
> > > For i in 1..6 Loop   -- i will be the leading species value
> > >   @ prog2.sql &species i
> > > END Loop;
>
> > > Prog 2:
>
> > > DEFINE &1  -- take input from prog 1 for species value
> > > DEFINE &2  -- take input from prog 2 for primary, secondary, tertiary,
> > > etc to 6 species
>
> > > UPDATE table
> > >  -- do stuff in here
> > > blah blah blah
> > > ;
>
> > > I'm definitely a newbie in CompSci stuff and I'm not an acronym person
> > > so RTFM might as well be martian to me. But any help
> > > that anyone can provide is much appreciated. I didn't find much in
> > > terms of examples on the web other than the most simple
> > > where they count from 1 to 6 and spit out a line using
> > > dbms_output.put_line and I need much more than that.
>
> > > On Feb 6, 11:46 am, Rob Wolfe <wolfe....@gmail.com> wrote:
> > > > for "define" and substitution variable information in general
>

> > >http://download.oracle.com/docs/cd/B14117_01/server.101/b12170/ch13.h....
> > > ..

> > > > - Show quoted text -- Hide quoted text -