PL/SQL UNION ERROR : PLEASE HELP

PL/SQL UNION ERROR : PLEASE HELP

Post by crog.. » Sun, 31 Dec 1899 09:00:00



Hello everybody,

I hope someone will help me on this one :

I have a PL/SQL stored procedure that tries to do something like that :

BEGIN
...
        INSERT into table1(a, b)
        select T.x , T.y from
        ((SELECT m1 as x , m2 as y from M)
        UNION
        (SELECT n1 as x , n2 as y from N)) T;
...
END;

When I test the SQL code through sqlplus, it works fine (the select
part)
When I try to run it, it sends me an exception ORA-01745: invalid
host/bind variable name

The only time it worked was when I did some testing using only varchar2
type fields

I don't find any documentation on this behaviour.

If you have any clue, I would heavily apreciate.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

PL/SQL UNION ERROR : PLEASE HELP

Post by fumi » Sun, 31 Dec 1899 09:00:00



> Hello everybody,

> I hope someone will help me on this one :

> I have a PL/SQL stored procedure that tries to do something like that :

> BEGIN
> ...
> INSERT into table1(a, b)
> select T.x , T.y from
> ((SELECT m1 as x , m2 as y from M)
> UNION
> (SELECT n1 as x , n2 as y from N)) T;
> ...
> END;

> When I test the SQL code through sqlplus, it works fine (the select
> part)
> When I try to run it, it sends me an exception ORA-01745: invalid
> host/bind variable name

> The only time it worked was when I did some testing using only varchar2
> type fields

The quotation code is syntactic OK, and does not raise this error.
It's probable that you typed needless colons.

 
 
 

PL/SQL UNION ERROR : PLEASE HELP

Post by Jonathan Lewi » Sun, 31 Dec 1899 09:00:00


It's could be a version dependent error.

PL/SQL is often a little bit behind in
the SQL that it can handle (try using
CUBE and ROLLUP inside Pl/SQL
in 8.1).

I don't remember version numbers but
there was a time when an in-line view
would work in SQL*Plus but not in PL/SQL.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk



>> Hello everybody,

>> I hope someone will help me on this one :

>> I have a PL/SQL stored procedure that tries to do something like that :

>> BEGIN
>> ...
>> INSERT into table1(a, b)
>> select T.x , T.y from
>> ((SELECT m1 as x , m2 as y from M)
>> UNION
>> (SELECT n1 as x , n2 as y from N)) T;
>> ...
>> END;

>> When I test the SQL code through sqlplus, it works fine (the select
>> part)
>> When I try to run it, it sends me an exception ORA-01745: invalid
>> host/bind variable name

>> The only time it worked was when I did some testing using only varchar2
>> type fields

>The quotation code is syntactic OK, and does not raise this error.
>It's probable that you typed needless colons.