Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Can you help me resolve a ORA-01006?

Posted on 2013-12-12
8
Medium Priority
?
562 Views
Last Modified: 2013-12-16
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
Comment
Question by:Rao_S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39715377
Missing colon, here: ...   || lv_dbname
0
 
LVL 20

Expert Comment

by:flow01
ID: 39715378
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
 
LVL 20

Expert Comment

by:flow01
ID: 39715382
Is the schema name same as dblink ?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 2000 total points
ID: 39715390
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
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 39718364
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39720064
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
 

Author Comment

by:Rao_S
ID: 39721331
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
 

Author Closing Comment

by:Rao_S
ID: 39721361
thank you..
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question