using variables and Functons in Dynamic SQL

[rdharmar]

I have declared a procedure, function "con_event_dt" and a variable "v_subm_dt_id" in the package specification.

In the Package body I am trying to execute the following query:

v_str_sql:= SELECT 'card_cd, con_event_dt(v_subm_dt_id,card.card_wip_id_cd) as card_subm_dt '|| 'FROM card_wip c_wip ' ||
', card card '||
', concept_detl cd '

The function "con_event_dt" is declared in the specification and actual details are in the body.
--------------------------------------------------------
I am getting an error when I execute the procedure
"invalid column name", this happens only when I use the function and variable in the select statement and obviously this is where the problem is.

You help will be greatly appreciated. Thanks.

 

[rdharmar]

Some more details to the above problem:

1.The where clause is dynamic depending on the input parameter.
2.cur_ConceptMatrix is declared as REF CURSOR in the specification.
3. Using Oracle version 8.1.7

the string is built as below:
v_str_sql := v_str_sql || v_from || v_where;

Opening the cursor:
OPEN cur_ConceptMatrix FOR v_str_sql;

 

[rdharmar]

I have solved the problem.

1. I had to prefix the function name with the package Name eventhough it was declared in the specification. I am not sure why.

2. the variable inside the function had to be outside the quotes.
e.g. PKG_REPORTS.con_event_dt(' ||card_subm_dt_id ||',card.card_cd) as card_subm_dt '

If any one can add reasons to this it will help. Thanks.

 

[sem]

This behaviour is completely predictable, just learn the basics of pl/sql (namely resolving names, parsing/binding/executing). Breifly, any dynamic sql is executed within it's own separate environment, thus variables defined within the scope of calling module are not visible. Sql engine itself may see only database-wide objects, e.g. tables, views, procedures, packages.

Regards, Dima

http://seminihin.narod.ru