Execute Immediate Syntax

I want to place a column value into a variable.


Here's the Syntax I used

execute immediate

' Select ' || col_name || '
From tbl_name
where ' || col_name || ' = :bind_var'
into return_val
using key_val;


This next version compiles but is useless

execute immediate

' Select ' || col_name || '
From tbl_name
where ' || col_name || ' = :bind_var'
using key_val;


The things I have looked at seem to have the same syntax.

URL

URL

URL


This one uses a return keyword

URL

would this work?



I have seen the idea that the into is in the wrong place

execute immediate
' Select ' || col_name || ' into ' || ret_val || '
From tbl_name
where ' || col_name || ' = :bind_var'
using key_val;

Yet the reply said to use the syntax i first used since it was more efficient. Also I saw discussion of placing a semi colon at the end makes it a plsql block, could this be the issue?That version was not running either.


I am thinking this is a version problem. I know that version earlier than 8.1.5 need the DBMS_SQl package. However I believe that I am using a later version since I have queries that use bind variables. And ones that use concatenation to dynamically choose the table. But can't get this one that used both to work. This is my last idea of using all bind variables

execute immediate
' Select :bind_col_name1
From tbl_name
where :bind_col_name2 = :bind_var'
into return_val
using col_name, col_name, key_val;

Here I am guessing bind variables have to be unique if not it could be



execute immediate
' Select :bind_col_name
From tbl_name
where :bind_col_name = :bind_var'
into return_val
using col_name, key_val;


Any help at all would be appreciated. I was using concatention in the first version since I was dealing with varray elements yet I could place their values in regular variables to use this last version.