Skip to Main Content
  • Questions
  • how to get the variable value through the variable name

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jerry.

Asked: February 24, 2016 - 2:22 am UTC

Last updated: February 25, 2016 - 11:15 am UTC

Version: oracle 10g

Viewed 1000+ times

You Asked

hi tom,
I have a question , how to get the variable value through the variable name.

ex:
vi_a pls_integer := 100;
vc_b varachr2(4) = ‘vi_a’ ;

how to get the variable value 100 through Vc_b ('vc_a')

thanks very much.


and Connor said...

You could use an associative array for this:

SQL> set serverout on
SQL> declare
  2    type vc_list is table of varchar(30) index by varchar2(30);
  3
  4    l_values vc_list;
  5
  6    l_lookup varchar2(10);
  7  begin
  8    l_values('varA') := 'Hello';
  9    l_values('varB') := 'There';
 10
 11    l_lookup := 'varA';
 12    dbms_output.put_line(l_values(l_lookup));
 13
 14    l_lookup := 'varB';
 15    dbms_output.put_line(l_values(l_lookup));
 16  end;
 17  /
Hello
There

PL/SQL procedure successfully completed.


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

sorry, this answer is not my want

Jerry Liu, February 24, 2016 - 6:34 am UTC

thanks, but , sorry, this answer isn't the answer I'm looking for . maybe my question is not very clear.

the true requirements like this:

CREATE TABLE T_TEST
(
V1 VARCHAR2(1) NOT NULL,
V2 VARCHAR2(1) NOT NULL
)

create or replace procedure p_test (pi_v1 in varchar2,
pi_v2 in varchar2
) is
begin

FOR L IN (
SELECT COLUMN_NAME, NULLABLE
FROM SYS.USER_TAB_COLUMNS T1, SYS.USER_TAB_COMMENTS T2
WHERE T1.TABLE_NAME=UPPER('T_TEST') AND T1.TABLE_NAME = T2.table_name AND T1.NULLABLE = 'N') LOOP
IF PKG_PARAMUTL.ASSERTNULL(
'PI_'||L.COLUMN_NAME --here I want use this param name to get param value, so I can write a general check program
)

THEN
HERE WILL DO SOMETHING
END IF;

END LOOP;

end;
Connor McDonald
February 25, 2016 - 6:08 am UTC

The dynamic referencing you want does not exist in PL/SQL.

That's why I suggested the indirect referencing scheme, ie,

create or replace procedure p_test (
  p_parm_vals in array_as_per_before
) is 
begin 

FOR L IN ( 
  SELECT COLUMN_NAME, NULLABLE 
  FROM SYS.USER_TAB_COLUMNS T1, SYS.USER_TAB_COMMENTS T2 
  WHERE T1.TABLE_NAME=UPPER('T_TEST') AND T1.TABLE_NAME = T2.table_name AND T1.NULLABLE = 'N') LOOP 

  IF PKG_PARAMUTL.ASSERTNULL( 
    p_parm_vals(L.COLUMN_NAME)
  ) 

THEN 
  HERE WILL DO SOMETHING 
END IF; 


Hope this helps

some supplement

Jerry Liu, February 24, 2016 - 6:41 am UTC

create or replace package PKG_PARAMUTL is

-- Author : LIUGR
-- Created : 2016/2/22 15:31:47
-- Purpose : 参数设置可用到的一些公共基础

-- Public type declarations
--type <TypeName> is <Datatype>;
NULL_EXCEPTION EXCEPTION;

/**//*将名称与用于触发器中的错误号码关联起来*/
---RAISE_APPLICATION_ERROR error_number -20000 到 -20999
PRAGMA EXCEPTION_INIT(NULL_EXCEPTION, -20991);


-- Public constant declarations
--<ConstantName> constant <Datatype> := <Value>;

-- Public variable declarations
--<VariableName> <Datatype>;

-- Public function and procedure declarations
--function <FunctionName>(<Parameter> <Datatype>) return <Datatype>;

FUNCTION ASSERTNULL(PI_PARAMVALUE IN ANYDATA) RETURN BOOLEAN;

FUNCTION getanydata(v_data IN anyData) RETURN VARCHAR2;

end PKG_PARAMUTL;


create or replace package body PKG_PARAMUTL is

-- Private type declarations
--type <TypeName> is <Datatype>;

-- Private constant declarations
--<ConstantName> constant <Datatype> := <Value>;

-- Private variable declarations
--<VariableName> <Datatype>;

-- Function and procedure implementations
FUNCTION getanydata(v_data IN anyData)
RETURN VARCHAR2 IS
v_num NUMBER;
v_date DATE;
v_re_data VARCHAR2(4000);
BEGIN
CASE v_data.gettypeName
WHEN 'SYS.NUMBER' THEN
IF (v_data.getNumber(v_num) = dbms_types.success) THEN
v_re_data := v_num;
END IF;
WHEN 'SYS.DATE' THEN
IF (v_data.getDate(v_date) = dbms_types.success) THEN
v_re_data := to_char(v_date,'yyyy-mm-dd hh24:mi:ss');
END IF;
WHEN 'SYS.VARCHAR2' THEN
IF (v_data.getVarchar2(v_re_data) = dbms_types.success) THEN
NULL;
END IF;
ELSE
v_re_data := 'not number or date or varchar2';
END CASE;

RETURN v_re_data;
END getanydata;

FUNCTION ASSERTNULL(PI_PARAMVALUE IN ANYDATA) RETURN BOOLEAN
is
begin
return getanydata(PI_PARAMVALUE) is null ;
end ASSERTNULL;


begin
-- Initialization
--<Statement>;
NULL;
end PKG_PARAMUTL;

thanks for your supplement

Jerry Liu, February 25, 2016 - 6:45 am UTC

Thanks for your supplement, this is very helpful .
I feel depressed that The dynamic referencing does not exist in PL/SQL.


Connor McDonald
February 25, 2016 - 11:15 am UTC

Sorry. You could always raise an enhancement request via Oracle Support.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library