How can i use variables in select statement?

Posted on 2013-10-14
Medium Priority
Last Modified: 2013-10-14
hi, i have to use variables in select statement including a constant value, table name, and where clause, like so..
execute immediate 'INSERT INTO table1 USING SELECT '
                                ||', '
                                ||'col1, col2, col3 FROM '
                                ||'WHERE TEMPLATE_NAME LIKE '
                                ||CHR (39)
                                ||CHR (39);
Question by:Rao_S
  • 3

Author Comment

ID: 39570635
with the above statement i get a error:
Error: Error in loading:table1-933ORA-00933: SQL command not properly ended
LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 39570641
           'INSERT INTO table1 USING SELECT :source, col1, col2, col3 FROM '
        || lv_tablename
        || ' WHERE TEMPLATE_NAME LIKE :likevalue'
        USING lv_source, lv_likevalue;

the error was because you were missing a space between the tablename and the WHERE clause in the concatenation.

But, you should use bind variable (with the colon) and the USING clause for the variables.

Note, you can't bind the object names, those must be literals within the concatenated sql

Author Comment

ID: 39570710
thank you sdstuber, it worked!
i have to add another cursor and pass mutiple 'lv_likevalue', let me test that too...

Author Closing Comment

ID: 39572028
thank you sdstuber, works correctly!!

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

624 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