CODEWITHSUNDEEP
plsqloracle11g
FEST
FEST

Reputation: 883

Pl/SQL declaration of table variables and setting options

I want to create an anonymous block where I do the following:

set session set current_schema = PTSI;
DECLARE
var_cur_proj structure.structure_code%TYPE;
var_cur_task structure.structure_code%TYPE;
TYPE tbl_result AS OBJECT
(
  projeto_id structure.structure_code%TYPE,
  projeto structure.description%TYPE,
  tarefa_id structure.structure_code%TYPE,
  tarefa structure.description%TYPE,
  recurso_id structure.structure_code%TYPE,
  recurso structure.description%TYPE,
  perfil_id structure.structure_code%TYPE,
  perfil structure.description%TYPE,
  rate_id structure.structure_code%TYPE,
  rate_value rate_change_point_res.rate_value%TYPE,
  dia date,
  horas_reportadas NUMBER(38,2),
  horas_integradas_realizadas NUMBER(38,2),
  horas_integradas_remanescentes NUMBER(38,2),
  horas_planeadas NUMBER(38,2)
);
TYPE table_resul IS TABLE OF tbl_resul;
resultado table_result;
BEGIN

END;
/

This is giving me an error... I'm following this site for creating the table variable (http://ss64.com/oraplsql/declaretbl.html) seeing that it did not worked, I tried to create the table variable like I'm used to create tables to be returned by functions (create the object then the table).

Can anyone help me please?

Upvotes: 0

Views: 420

Answers (1)

Shaun Peterson
Shaun Peterson

Reputation: 1790

Rather than creating as object you should create this as a record then declare the table from the record, and as mentioned by Justin above you must have at least a null; between the Begin and End tags. Please see amended code below. And http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/record_definition.htm for more details.

alter session set current_schema = PTSI;

DECLARE
  var_cur_proj structure.structure_code%TYPE;
  var_cur_task structure.structure_code%TYPE;
  TYPE rec_result IS RECORD
  (
    projeto_id structure.structure_code%TYPE,
    projeto structure.description%TYPE,
    tarefa_id structure.structure_code%TYPE,
    tarefa structure.description%TYPE,
    recurso_id structure.structure_code%TYPE,
    recurso structure.description%TYPE,
    perfil_id structure.structure_code%TYPE,
    perfil structure.description%TYPE,
    rate_id structure.structure_code%TYPE,
    rate_value rate_change_point_res.rate_value%TYPE,
    dia date,
    horas_reportadas NUMBER(38,2),
    horas_integradas_realizadas NUMBER(38,2),
    horas_integradas_remanescentes NUMBER(38,2),
    horas_planeadas NUMBER(38,2)
  );
  TYPE table_result IS TABLE OF rec_result;
  resultado table_result;
BEGIN
  null;
END;
/

Hope this helps

Upvotes: 3

Related Questions