Re: URGENT! HELP! Variable Substitution in PL/SQL

> KYLai <laiky_at_hkstar.com> wrote in article
 <531t08$l9t_at_capella.hkstar.com>...
>
> Hello!
>
> I would like to write a procedure to check the existence of an
> element in the table(
> input parameter : table name, field name, field value;
> output parameter : True - record found
> False - record not-found);
>
> This procedure will be called by forms.
>
>
> Can anybody advise me on the followings:
>
> - how can I define the datatype of the input field?
> it may be varies, may be VARCHAR2/NUMBER...
> how can I make use of %type if table_name & field_name are
> only known at run time?

Use packages on the server. Within a package, you can have functions or procedures of the same name but with different number or type of arguments.
Create a function for each datatype that you may require e.g.

  FUNCTION Record_Found (TableName IN VARCHAR2, FieldName IN VARCHAR2,

                FieldValue IN VARCHAR2) RETURN INTEGER;

  FUNCTION Record_Found (TableName IN VARCHAR2, FieldName IN VARCHAR2,
                FieldValue IN DATE) RETURN INTEGER;

  FUNCTION Record_Found (TableName IN VARCHAR2, FieldName IN VARCHAR2,
                FieldValue IN NUMBER) RETURN INTEGER;

 

> - how can I substitute the table_name of the SELECT statement
> in declaring the CURSOR?
>
> : I can't use NAME_IN function in SELECT statement
 

> : I can do the following under <<SQL*PLUS>>
> define WORK_TABLE="EMP"
> select &&WORK_TABLE.DEPTNO from &&WORK_TABLE;
>
> I cannot use && under <<PL/SQL>>
>

There is a package DBMS_SQL that allows you to dynamically create your SQL statements as needed. So within each of the packaged functions that you have now created, you build the SQL statement to search the table and column for the value. Use %SQLNOTFOUND to determine if your function should return True or false.

-- 
Slainte mhath
Stephen Lappin