Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 578
  • Last Modified:

Can you help me resolve a ORA-01006?

hi, i get a ORA-01006: bind variable does not exist  ..... error with this, from a packaged proc, can you please help me with this...

EXECUTE IMMEDIATE
            'select  oldest_backup_time,newest_backup_time  into :lv_oldest, :lv_newest from '
            || lv_dbname
            || '.'
            || 'rc_backup_set_summary'
            || '@'
            || 'LINK_ABCDEF '
            USING lv_oldest,lv_newest,lv_dbname;
0
Rao_S
Asked:
Rao_S
  • 3
  • 2
  • 2
  • +1
1 Solution
 
MikeOM_DBACommented:
Missing colon, here: ...   || lv_dbname
0
 
flow01Commented:
You are passing 3 variables (using ,,)  and defined 2 in your query :lv.

You can't pass however the databaselink name as a bind variable, so you must include it in the query text.

Try
lv_dbname := ' 'LINK_ABCDEF ';
EXECUTE IMMEDIATE
            'select  oldest_backup_time,newest_backup_time  into :lv_oldest, :lv_newest from '
            || lv_dbname
            || '.'
            || 'rc_backup_set_summary'
            || '@'
            || lv_dbname
            USING lv_oldest,lv_newest;
0
 
flow01Commented:
Is the schema name same as dblink ?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MikeOM_DBACommented:
Ooops, miss-read the code.

Actually it should be:
EXECUTE IMMEDIATE
       'SELECT  oldest_backup_time, newest_backup_time 
          FROM '|| lv_dbname  || '.rc_backup_set_summary@LINK_ABCDEF '
          INTO lv_oldest, lv_newest; 

Open in new window

0
 
HuaMinChenBusiness AnalystCommented:
Try
EXECUTE IMMEDIATE
            'select  oldest_backup_time,newest_backup_time  into :lv_oldest, :lv_newest from '
            || lv_dbname
            || '.'
            || 'rc_backup_set_summary'
            || '@'
            || 'LINK_ABCDEF '
            USING lv_oldest,lv_newest;

Open in new window

0
 
MikeOM_DBACommented:
1) Your query does not have and conditions and therefore will none of the posted variations will work unless the table has ONE row.

2) In the EXECUTE IMMEDIATE statement, the "USING" part is to supply bind variable values to the query and the "INTO" part is to receive results from the query.

3) Other than variables, in order to supply information that builds the query and which is contained in variables (like schema and/or table name and/or database link. etc...), you must CONCATENATE these variables to the query  text.
0
 
Rao_SAuthor Commented:
hi flow, the schema name is not same as the link name, i tried to use the link name as a variable, but your script did not work...
hi mikeom_dba, i tried your scipt, and it worked thank you...
0
 
Rao_SAuthor Commented:
thank you..
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now