CODEWITHSUNDEEP
sqloracle-databasedynamicplsql
Sunny
Sunny

Reputation: 43

How to fetch column name in a table in dynamic sql through an array

I get an error

ORA-00904: "GLOBAL_VARIABLE_ARR": invalid identifier ORA-06512: at line 51.

It has something to with the global_variable_rec.where_clause.

Can somebody help me solve this? The code follows. It's suppose to return the distribution_id value from global_variable_arr('GLOBAL_DISTRIBUTION_ID'). But it is not.

CREATE OR REPLACE Procedure updateGlobalvariables ( transmittal_id IN NUMBER,      distribution_id IN NUMBER )

IS

sql_data        VARCHAR2(500);    --Holds select statment resulting value
query_string    VARCHAR2(500);    --Holds query/sql
i               VARCHAR2(75);     --Used for looping through the array
--transmittal_id  NUMBER(3) := 321; 
--distribution_id NUMBER(3) := 123;

--place holder for LETTER_GLOBAL_VARIABLE.TABLE_NAME
table_name VARCHAR2(30);

--Array for the Global Variables
TYPE global_variable_table_arr IS TABLE OF VARCHAR2(500) INDEX BY VARCHAR2(30);
global_variable_arr global_variable_table_arr;

--Cursor to hold the global variables
CURSOR global_variable_cur IS 
SELECT * FROM LETTER_GLOBAL_VARIABLES 
WHERE TABLE_NAME IS NOT NULL AND WHERE_CLAUSE IS NOT NULL;

--Record for the cursor to hold the data from the cursor.
global_variable_rec global_variable_cur%rowtype; 

BEGIN  
--Filling the array with(Element) the value from Distribution ID
global_variable_arr('GLOBAL_DISTRIBUTION_ID') := distribution_id;
dbms_output.put_line('GLOBAL_DISTRIBUTION_ID := '||      global_variable_arr('GLOBAL_DISTRIBUTION_ID') );

--Filling the array with(Element) the value from Transmittal ID
global_variable_arr('GLOBAL_TRANSMITTAL_ID') := transmittal_id;
dbms_output.put_line('GLOBAL_TRANSMITTAL_ID := '||     global_variable_arr('GLOBAL_TRANSMITTAL_ID') );

OPEN global_variable_cur;
FETCH global_variable_cur INTO global_variable_rec;
LOOP

    FETCH global_variable_cur INTO global_variable_rec;
    EXIT WHEN global_variable_cur%NOTFOUND;

    --Display table name, column name,global variable and the where clause (All from the LETTER_GLOBAL_VARIABLE table)
    dbms_output.put_line('Table name is: '|| global_variable_rec.table_name ||', column name is: '|| global_variable_rec.column_name ||', global variable name is: '|| global_variable_rec.global_variable || ', where clause is: ' || global_variable_rec.where_clause);

    --This the query that will grab the value(s) from the table(s). It is stored inside a variable
    query_string:= 'SELECT '||global_variable_rec.column_name|| '  FROM '|| global_variable_rec.table_name ||' WHERE '|| global_variable_rec.where_clause;

/* the where_clause contains: DISTRIBUTION_ID=global_variable_arr('GLOBAL_DISTRIBUTION_ID') */

    --Displays the SQL Query (query_string)
    dbms_output.put_line('SQL QUERY IS: ' || query_string);

    --This Executes the Query(SQL statement) inside the query_string variable
    --and saves the result(Value) into the sql_data variable.
    EXECUTE IMMEDIATE query_string INTO sql_data;

    --Display the value inside the variable sql_data
    dbms_output.put_line('SQL data IS: ' || sql_data);

    --Fill in the array with the value(Elements) from the sql_data variable.
    global_variable_arr(global_variable_rec.global_variable) := sql_data;

END LOOP;

CLOSE global_variable_cur;

i := global_variable_arr.FIRST;  -- Get first element of array

WHILE i IS NOT NULL LOOP

    --Displaying the global variable and its value
    dbms_output.put_line('Global Varaible name : ' || i || ' Value : ' || global_variable_arr(i));
    i := global_variable_arr.NEXT(i);  -- Get next element of array

END LOOP;

END;
--EXECUTE updateGlobalvariables;

This is my out put:

GLOBAL_DISTRIBUTION_ID := 123 GLOBAL_TRANSMITTAL_ID := 321 Table name is: DISTRIBUTION, column name is: PERSON_ID, global variable name is: Global_person_id, where clause is: DISTRIBUTION_ID=global_variable_arr('GLOBAL_DISTRIBUTION_ID') SQL QUERY IS: SELECT PERSON_ID FROM DISTRIBUTION WHERE DISTRIBUTION_ID=global_variable_arr('GLOBAL_DISTRIBUTION_ID')

Upvotes: 0

Views: 1904

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67802

There is no way that dynamic SQL can see the content of a variable if you don't explicitely give this variable to the SQL engine.

Consider dynamic SQL as a standalone engine completely isolated from your procedure. It is as if you opened a SQL*Plus terminal inside your procedure and you typed your query.

Obviously the following would fail in a terminal:

SELECT PERSON_ID 
  FROM DISTRIBUTION 
 WHERE DISTRIBUTION_ID=global_variable_arr('GLOBAL_DISTRIBUTION_ID')

Why would it fail? Because there is no object named global_variable_arr in your database, thus the invalid identifier error.

When you use dynamic SQL you have to be very specific and manually feed the variables to the engine. You have to do this yourself, as if you were using java or C#, or really any other language that is not static PL/SQL (which is why you should stick with static SQL at first and as long as possible).

Your query should be: 

SELECT PERSON_ID FROM DISTRIBUTION WHERE DISTRIBUTION_ID = :VARIABLE_A

And you would call it with: 

EXECUTE IMMEDIATE query_string 
             INTO sql_data 
            USING global_variable_arr('GLOBAL_DISTRIBUTION_ID');

Upvotes: 1

Related Questions