CODEWITHSUNDEEP
oracle-databaseplsql
agiro
agiro

Reputation: 2080

Input table and column name in Oracle SQL

I want to complete a task that says: Search in the user given table, in the user given column the user given value using explicite cursor. This means I have to use the & operator to input the table name, column name and value. If I found the given object, give a varchar found else not found. How to input the table name, column name and the value (that is of correct type of course)? I tried it but failed miserably:

declare
@Mytable varchar2;
@Mycolumn varchar2;
Myvalue @Mycolumn%TYPE;
oneLine @Mytable%ROWTYPE;
found varchar2;
cursor kurzor is select * from @Mytable;
begin
open kurzor;
loop
fetch kurzor into oneLine;
if oneLine.@Mycolumn = Myvalue then
found='found';
end if;
exit when kurzor%NOTFOUND;
end loop;
close kurzor;
end;
/

Upvotes: 0

Views: 404

Answers (1)

AlexSmet
AlexSmet

Reputation: 2161

You don't need to describe variables for table name, column name and etc. in section DECLARE. Just use substitution variables.

DECLARE 
    CURSOR cur IS  SELECT &myColumn FROM  &myTable;
    currentVal VARCHAR2(4000);
    isFound    VARCHAR2(10):= 'not found';  
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO currentVal;
        EXIT WHEN cur%NOTFOUND;

        IF currentVal = &myValue THEN
            isFound:= 'found';
            EXIT; 
        END IF;
    END LOOP;

    CLOSE cur;
    dbms_output.put_line(isFound);
END;

Upvotes: 1

Related Questions