Skip to Main Content
  • Questions
  • dynamically binding with user defined types

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Javier.

Asked: July 21, 2017 - 8:11 am UTC

Last updated: July 22, 2017 - 1:26 am UTC

Version: oracle 11g

Viewed 1000+ times

You Asked

I'm trying to use dynamicaly sql with user defined types. I have a big struct with many attributes, and I'd like factorize the code to use dynamically the user defined types.

I've a table with the model of the struct...

I try several things, ie:

DECLARE

TYPE blk_esta IS RECORD (
codi_mone VARCHAR2(1),
codi_situ VARCHAR2(1),
situ_pig VARCHAR2(1),
IMPORTE NUMBER(11,2)
);

name:= 'codi_mone'; -- Name of the first field, I'd obtained from DB.
bloq_esta blk_esta;

BEGIN

-- First test
v_sql:='SELECT ''E'' bloq_esta.'||name||' from dual';
execute immediate (v_sql);

-- out: ORA-00905: missing keyword

-- Second test
v_sql:='bloq_esta.:b1:=''E''';
execute immediate (v_sql) using in out bloq_esta;

-- out:PLS-00457: expressions have to be of SQL types

END;

Any idea?

instead of that solution, I thought that I could also traverse the attributes of a user type... like an array... blk_esta.(1)... ?¿?

and Connor said...

There are various limitations when using PL/SQL types from the SQL interface. They are *different* engines. For example, there is no Boolean datatype in SQL but there is in PL/SQL.

What people typically do is have SQL object types defined. For example, instead of:

TYPE blk_esta IS RECORD (
codi_mone VARCHAR2(1),
codi_situ VARCHAR2(1),
situ_pig VARCHAR2(1),
IMPORTE NUMBER(11,2) 
);


we would do something like

create or replace type TYPE blk_esta as object (
codi_mone VARCHAR2(1),
codi_situ VARCHAR2(1),
situ_pig VARCHAR2(1),
IMPORTE NUMBER(11,2) 
);


which *can* be referred to via the SQL engine.

Many of these restrictions are lifted in 12c, where things are more seamless:

http://docs.oracle.com/database/121/LNPLS/release_changes.htm#GUID-57E439FB-B196-46CB-857C-0ADAB32D9EA0


Rating

  (1 rating)

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

Comments

waiting for 12C version

Javier Torijano, July 24, 2017 - 6:11 am UTC

Hello, thanks for your answer, I hope that we can do it with 12c Versin.

In the other hand I tried before with objects and we obtain the same result.
create or replace TYPE blk_esta_object as object (
codi_mone VARCHAR2(1),
codi_situ VARCHAR2(1),
situ_pig VARCHAR2(1),
IMPORTE NUMBER(11,2)
);
/
declare
bloq_esta blk_esta_object;
name varchar2(150) := 'codi_mone';
sentencia varchar2(2000);

begin
sentencia:='select ''E'' into bloq_esta.'|| name ||' from dual';

execute immediate(sentencia);

end;

output: Error report -
ORA-00905: missing keyword
ORA-06512: at line 9
00905. 00000 - "missing keyword"

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