Global variable in oracle

Question

In Sql developer I need to store the variable and use it later how to do that?

   VARIABLE liab NUMBER;
    exec select nvl(LIAB_ID,-1)into liab FROM GETM_LIAB_CUST WHERE CUSTOMER_NO = (select   customer_id from apps_master where ACCOUNT_NUMBER='&C' AND BRANCH_CODE = '&B') ;

SPOOL c:\query.SQL
select 'SELECT * FROM GETB_UTILS WHERE LIAB_ID =liab ;' FROM DUAL;
spool off

Tried

column liabid new_value liab noprint
select nvl(LIAB_ID,-1) liabid FROM GETM_LIAB_CUST WHERE CUSTOMER_NO = (select customer_id from apps_master where ACCOUNT_NUMBER='&C' AND BRANCH_CODE = '&B') ;

It doesnt work .Please help me

What exactly does 'it doesn't work' mean? Error message, missing/wrong output, exploding fireworks... ? — Frank Schmitt, Jul 02 '14 at 10:29
Hi Frank -- ORA-06550: line 2, column 34: PLS-00201: identifier 'LIAB' must be declared ORA-06550: line 2, column 39: PL/SQL: ORA-00904: : invalid identifier ORA-06550: line 2, column 7: PL/SQL: SQL Statement ignored — user3737084, Jul 02 '14 at 10:33
Please don't put additional information in comments. Edit your question instead (using the "Edit" button). — Frank Schmitt, Jul 02 '14 at 12:23

Alex Poole · Accepted Answer · 2014-07-02T11:49:48.160

VARIABLE declares a bind variable, so you need to prefix it with a colon when you refer to it:

VARIABLE liab NUMBER;
exec select nvl(LIAB_ID,-1) into :liab FROM ...

SPOOL c:\query.SQL
select 'SELECT * FROM GETB_UTILS WHERE LIAB_ID = ' || :liab || ';' FROM DUAL;
spool off

You could also do this with a substitution variable instead of a bind variable:

column liab new_value g_liab
select nvl(LIAB_ID,-1) as liab FROM ...

SPOOL c:\query.SQL
select 'SELECT * FROM GETB_UTILS WHERE LIAB_ID = &g_liab;' FROM DUAL;
spool off

The column x new_value y syntax defines a substitution variable &y whose value is set from a column called x in a subsequent query.

In both cases SQL Developer will put both the header and the command you ran into the spool file, so you'd see this:

> select 'SELECT * FROM GETB_UTILS WHERE LIAB_ID = ' || :liab || ';' from dual
'SELECT*FROMGETB_UTILSWHERELIAB_ID='||:LIAB||';'                                 
----------------------------------------------------------------------------------
SELECT * FROM GETB_UTILS WHERE LIAB_ID = 1;                                        

> spool off

You can add set heading off and set echo off, but if you run it directly from an SQL Worksheet you'd still end up with:

> select 'SELECT * FROM GETB_UTILS WHERE LIAB_ID = ' || :liab || ';' from dual
SELECT * FROM GETB_UTILS WHERE LIAB_ID = 1;

To get rid of the echo'd command you'd need to save this as a script and then run that from another worksheet, as was the case with your previous question. So if you had a script called liab.sql containing:

VARIABLE liab NUMBER;
set feedback off
exec select nvl(LIAB_ID,-1) into :liab FROM ...

set echo off
set heading off
SPOOL c:\query.SQL
select 'SELECT * FROM GETB_UTILS WHERE LIAB_ID = ' || :liab || ';' FROM DUAL;
spool off

... and then ran that with

@liab.sql

... the query.sql file would contain just:

SELECT * FROM GETB_UTILS WHERE LIAB_ID = 1;

@user3737084 - as in your previous question you need to run it as a script to allow `set echo off` to take effect, as it behaves the same as `set termout`. I've updated the answer to show the difference. — Alex Poole, Jul 02 '14 at 11:50

Global variable in oracle

1 Answers1