CODEWITHSUNDEEP
sqloracle-database
Akira
Akira

Reputation: 2870

Why I can not use my variable in WHERE statement?

I declare a variable user_name so that other people can change it to run my script on their laptop. Here C##AKIRA is my account I created in SQL Plus.

DECLARE
    user_name    varchar2(20);
BEGIN
    user_name := 'C##AKIRA';
END;

When I run

SELECT
    table_name
FROM
    all_tables
WHERE
    owner = user_name

Oracle returns an error ORA-00904: "USER_NAME": invalid identifier. On the other hand, it works well when I run

SELECT
    table_name
FROM
    all_tables
WHERE
    owner = 'C##AKIRA'

Could you please elaborate on this issue?

-- Declare a variable called 'user_name'
DECLARE
    user_name    varchar2(20);
BEGIN
    user_name := 'C##AKIRA';
END;

-- Drop a table of it already exists
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE works_in';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
            RAISE;
        END IF;
END;

-- Create a table called `works_in`
CREATE TABLE works_in (
    coded       NUMBER(2),
    codew       NUMBER(2),
    datewi      DATE,
    hourcount   NUMBER(2),
    PRIMARY KEY (coded, codew, datewi),
    FOREIGN KEY (coded) REFERENCES doctors (coded),
    FOREIGN KEY (codew) REFERENCES wards (codew)
);

SELECT
    table_name
FROM
    all_tables
WHERE
    owner = user_name

Upvotes: 0

Views: 57

Answers (1)

Rene Arteaga
Rene Arteaga

Reputation: 354

The scope of the variable user_name apply only for :

DECLARE
 user_name    varchar2(20);
BEGIN
 user_name := 'C##AKIRA';
END;

You must declare the variable only inside pls method you will use.

Like this:

-- Declare a variable called 'user_name'
DECLARE
    user_name    varchar2(20);
    v_table_name varchar2(100);
BEGIN
    user_name := 'C##AKIRA';

 SELECT
 table_name
 into v_table_name 
 FROM
 all_tables
 WHERE
 owner = user_name
 and rownum = 1
 ;
END;

Upvotes: 1

Related Questions