Duda Procedimiento Oracle

[Keyser]

Hola,

Quiero construir una query dentro de un procedimiento en función a unos parámetros que me lleguen, ejecutarla y devolver el conjunto de resultados que me dé la select, ya que luego los tendré que mostrar vía web.

¿Lo más fácil sería construir una insert de los datos en una tabla temporal y luego utilizar un cursor para devolver estos datos? Ando un poco perdido porque soy nuevo en esto. ¿Conocéis de alguna buena práctica para resolver esto?

Mil gracias :)

[matanga]

Hola,

Te dejo procedimiento, que en resumen, incluye todos los ejemplos que creo vas a necesitar.

1. Creo el procedimiento, que toma como parametro el nombre de la tabla a la que tiene que consultar, y por parametro de salida devuelve el conjunto de resultados

Código:

oracle@A9I> 
  1  create or replace procedure p1 (table_name varchar2, c out sys_refcursor)
  2  as
  3  begin
  4  open c for 'select * from ' || table_name;
  5* end;

Procedimiento creado.

2. Ejecuto el procedimiento, el formato del ejemplo tiene que ver con el SQL*Plus, la llamada puede variar si se hace desde algun Driver o Provider.

Código:

oracle@A9I> variable c refcursor
oracle@A9I> exec p1('DUAL',:c);

Procedimiento PL/SQL terminado correctamente.

3. Finalmente, muestro por pantalla el conjunto de resultados de la consulta.

Código:

oracle@A9I> print c

D
-
X

Transcurrido: 00:00:00.00

Saludos

[Michelc]

Muy buen dato matanga, nosotros utilizamos un paquete que se creó cuando migramos de sql a oracle, no conocía el sys_refcursor.

Gracias.

[Keyser]

Gracias matanga por la respuesta lo primero. Lo segundo es que no sé si lo que quiero hacer se puede. En mi procedimiento he de construir la query primero en función de varios parámetros, ejemplo:

[...............viene codigo................]
IF INSTR(condR,'%') <> 0 THEN
Item := RTRIM(LTRIM(SUBSTR(condR, 1, INSTR(condR,',')-1)));
condR := RTRIM(LTRIM(SUBSTR(condR, INSTR(condR,',') + 1, LENGTH(condR))));
sqlR := 'r.lru like ''' || Item || '';
WHILE LENGTH(condR) > 1 LOOP
Item := RTRIM(LTRIM(SUBSTR(condR, 1, INSTR(condR,',')-1)));
condR := RTRIM(LTRIM(SUBSTR(condR, INSTR(condR,',')+1 , LENGTH(condR))));
IF LENGTH(Item) > 0 THEN
sqlR := sqlR || ''' or r.lru like ''' || UPPER(Item) || '';
sql20 := sql20 || ''' or nombre like ''' || UPPER(Item) || '';
END IF;
END LOOP;
s_sql := s_sql || ' and (' || sqlR || ''')';
ELSE
[................sigue más código...............]

Con lo cual según lo que explicas, tras construir mi query después de muchas vueltas, la instrucción que debería utilizar sería...

OPEN o_remCursor FOR s_sql;

(s_sql es mi varchar donde tendré la query final)

Lo que necesito es, una vez tengo en una variable (s_sql en el ejemplo anterior) la consulta que me devolverá todos los datos que he de mostrar, ejecutarla para que el sys_refcursor que va como parámetro de salida, me vaya devolviendo las filas de tal forma que pueda manejar cada columna de éstas e ir construyendo la tabla, en resumen.

En definitiva, ¿se puede hacer así?

Gracias por todo :)

Iniciado por matanga

Hola,

Te dejo procedimiento, que en resumen, incluye todos los ejemplos que creo vas a necesitar.

1. Creo el procedimiento, que toma como parametro el nombre de la tabla a la que tiene que consultar, y por parametro de salida devuelve el conjunto de resultados

Código:

oracle@A9I> 
  1  create or replace procedure p1 (table_name varchar2, c out sys_refcursor)
  2  as
  3  begin
  4  open c for 'select * from ' || table_name;
  5* end;

Procedimiento creado.

2. Ejecuto el procedimiento, el formato del ejemplo tiene que ver con el SQL*Plus, la llamada puede variar si se hace desde algun Driver o Provider.

Código:

oracle@A9I> variable c refcursor
oracle@A9I> exec p1('DUAL',:c);

Procedimiento PL/SQL terminado correctamente.

3. Finalmente, muestro por pantalla el conjunto de resultados de la consulta.

Código:

oracle@A9I> print c

D
-
X

Transcurrido: 00:00:00.00

Saludos

[matanga]

Hola,

...me vaya devolviendo las filas de tal forma que pueda manejar cada columna de éstas e ir construyendo la tabla...

¿Que tabla?, si lo que vas a hacer con la consulta dentro de la variable s_sql es llenar una tabla temporal, entonces no necesitas el cursor, con un insert select lo puedes lograr, por ejemplo

Código:

execute immediate 'insert into t1 select campo1, campo2, etc from tabla1';

o bien

execute immediate s_sql;

Donde t1 seria tu tabla temporal y s_sql contiene la consulta dinamica.

Saludos

[Keyser]

Perdón, perdón... no me he explicado. La tabla es una de html que construye mi aplicación web en función del recordset que devolvería este procedimiento. Es por ello que necesito el cursor como parámetro de salida (que luego utilizaré en una página .asp a modo de recordset con el que relleno la tabla de la que hablaba). En principio es factible la instrucción "OPEN o_remCursor FOR s_sql;" de la que hablaba?

Gracias!

Iniciado por matanga

Hola,



¿Que tabla?, si lo que vas a hacer con la consulta dentro de la variable s_sql es llenar una tabla temporal, entonces no necesitas el cursor, con un insert select lo puedes lograr, por ejemplo

Código:

execute immediate 'insert into t1 select campo1, campo2, etc from tabla1';

o bien

execute immediate s_sql;

Donde t1 seria tu tabla temporal y s_sql contiene la consulta dinamica.

Saludos

[matanga]

Hola,

En principio es factible la instrucción "OPEN o_remCursor FOR s_sql;" de la que hablaba?

Sip, esa es la idea.

Saludos

[Keyser]

Pues muchas gracias, ya te diré cómo quedó la cosa cuando la acabe. Gracias de nuevo :)

Iniciado por matanga

Hola,

Sip, esa es la idea.

Saludos