Using Variable- Oracle

Question

In the above code, I am giving schemaname as input and using that input it should connect to the database. But In this case the value i entered is not taken by the schemaname. This is how the out put and the error is:

declare schemaname varchar2(20);
exec :schemaname := XYZ;
BEGIN

end;

Error report -
ORA-06550: line 2, column 6:
PLS-00103: Encountered the symbol "" when expecting one of the following:

   constant exception <an identifier>
   <a double-quoted delimited-identifier> table long double ref
   char time timestamp interval date binary national character
   nchar
ORA-06550: line 4, column 1:
PLS-00103: Encountered the symbol "CONNECT" when expecting one of the following:

Could any one suggest how to make it work using spool

Alex Poole · Accepted Answer · 2016-09-14 14:01:08Z

2

the code between declare and end is PL/SQL. Commands like CONNECT or SPOOL are SQL*Plus commands. You cannot use SQL*Plus commands in a PL/SQL block.

In your case you don't need PL/SQL at all:

Create a script with following content

connect &1
spool C:\ABC
@c:\ABC
spool off;

and run it

@your_script_name

BTW: there is no reason to run script c:\ABC while you are spooling into it. What exactly do you want to achieve?

edited Sep 14, 2016 at 14:01

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

answered Sep 14, 2016 at 13:51

Petr Pribyl

3,6151 gold badge22 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Poole Over a year ago

The spool will default to C:\ABC.LST as no suffix was given; the @ will default to C:\ABC.SQL. So they aren't the same file. I'd prefer to see at least the spool name in full though to avoid confusion...

Alex Poole · Accepted Answer · 2016-09-14 13:58:31Z

exec[ute] is SQL*Plus and SQL Developer (and maybe other clients) shorthand for an anonymous block. It is a client command, it is not part of PL/SQL. You are trying to use it inside a PL/SQL declare section, where it is not valid or recognised.

If you want a client bind variable you need the var[iable] command:

var schemaname varchar2(20);
exec :schemaname := '&1';

BEGIN
...

Notice the single quotes around &1, as it's being assigned to a string variable.

But you can't connect inside a PL/SQL block either, and you can't use a bind variable for the connection.

connect :schemaname

will prompt for a password (even if you defined it's value as user/passwd) and try to connect as a user lieterally called :schemaname.

You can use a substituion variable, but you don't really need to define a new one; as you seem to be passing the credentials in, you can do:

connect &1

(without surrounding quotes)

Collectives™ on Stack Overflow

Using Variable- Oracle

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related