0

Is it possible to pass the column of the current row as an object like variable so i can extract some informations from that in a function? The function can be inside a package

For example

Select myfunction(column1) from dual;

And then in the function get the the information of this column like an object

Function myfunction(objCol object)
Return something
Is
... declaration 
Begin
  schema := objCol.schema
  table := objCol.table_name
 ... 
  do some other things
  return something 
End

Something like that?

Moribundus
  • 39
  • 9

1 Answers1

1

Oracle is not object-oriented by default and it will not pass a normal column as an object enhanced with metadata about itself. Without creating custom object types and variables and/or tables, columns and nested tables that use those custom object types, all the values you pass will be scalars. So, with a normal table you'd have to pass in not only the column value, but any attributes about that column you need (schema, table name, column name, etc.):

CREATE OR REPLACE FUNCTION myfunction (owner IN varchar2, table_name IN varchar2, column_name IN varchar2, column_value IN varchar2)
  RETURN something
AS

BEGIN
  -- do something with owner, table_Name, column_name and column_value
  RETURN something
END;

/

SELECT myfunction('MYSCHEMA','MYTABLE','MYCOLUMN',mycolumn)
  FROM myschema.mytable

Also, Oracle is hard-typed, so if you want a generic function you'll need to create overloads (multiple definitions in a package with the same function name) for different possible datatypes of my_column/column_value. One for strings, one for numbers, one for dates, etc..

Paul W
  • 5,507
  • 2
  • 2
  • 13
  • Yeah that is the way how i do it at the moment, schema table, column and value as arguments, disappointed... thanks for clarification – Moribundus Jul 23 '23 at 09:06
  • Can I ask what your function is going to do? Unless it's special requery-style LONG or LOB-over-dblink handling I can't think of a purpose for passing in this column-level metadata. – Paul W Jul 23 '23 at 10:55
  • i have a table which stored a value for some table columns per schema and i want that myfunction select the value and return it and i dont want to give schema, table, column name as arguments... – Moribundus Jul 23 '23 at 21:26
  • Why use a function? Why not just select the column from the table and be done with it? – Paul W Jul 23 '23 at 22:57
  • ok i think i don't have it make it clear enough... i have a table on another(for example t_operation): schema_name, table_name, column_name, operation_class(the value i want to get) the user that will run the function should not have access to this table but to the function, the function now query the table and get the operation class for schema_name, table_name, column_name and then depending on operationclass do different things with the column value – Moribundus Jul 24 '23 at 12:34
  • I think it'd be better to make a clear choice between SQL and function-based retrieval. If you choose SQL, your application is table-structure aware and should have privs to select from your tables directly (this is the normal approach). If you choose function-based, then your app should not be table-structure aware, or even aware of table names. In either case, there should be no need to pass in metadata into your functions. If you go with the non-SQL approach, register your attributes in a custom dictionary that your functions can use internally and app asks only for a registered attribute. – Paul W Jul 24 '23 at 12:50