L'obtention de résultats dans un ensemble de résultats de dynamique SQL dans Oracle

Cette question est similaire à un couple d'autres, j'ai trouvé sur StackOverflow, mais les différences sont assez importants pour moi, pour justifier une nouvelle question, si elle est ici:

Je souhaite obtenir un ensemble de résultats de dynamique SQL dans Oracle et ensuite de les afficher sur un ensemble de résultats dans un SqlDeveloper-comme outil, comme si j'avais exécuté l'instruction SQL dynamique directement. C'est simple, SQL Server, afin d'être concret, voici un exemple à partir de SQL Server qui renvoie un jeu de résultats dans SQL Server Management Studio ou de la Requête Explorer:

EXEC sp_executesql N'select * from countries'

Ou plus correctement:

DECLARE @stmt nvarchar(100)
SET @stmt = N'select * from countries'
EXEC sp_executesql @stmt

La question "Comment retourner un jeu de résultats /curseur à partir d'un Oracle PL/SQL anonyme bloc qui exécute SQL Dynamique?" adresses de la première moitié du problème, de l'exécution du SQL dynamique dans un curseur. La question "Comment faire de l'Oracle de la procédure de retour des ensembles de résultats" fournit une réponse similaire. La recherche sur le Web a révélé de nombreuses variations sur le même thème, tous les aborder seulement la première moitié de ma question. J'ai trouvé ce post expliquant comment le faire dans SqlDeveloper, mais qui utilise un peu de la fonctionnalité de SqlDeveloper. Je suis en fait à l'aide d'une requête personnalisée de l'outil, donc j'ai besoin de la solution de l'auto-contenues dans le code SQL. Cette coutume outil de requête de la même façon n'a pas la capacité pour afficher le résultat de l'impression (dbms_output.put_line) déclarations; il affiche uniquement les ensembles de résultats. Voici encore encore une avenue possible à l'aide de 'exécution immédiate...bulk collect", mais cet exemple encore rend les résultats avec une boucle de dbms_output.put_line consolidés. Ce lien tente d'aborder le sujet, mais la question ne sera jamais obtenu de réponse non plus.

En supposant que cela est possible, je vais ajouter une condition: je voudrais le faire sans avoir à définir une fonction ou une procédure (en raison du nombre limité DB autorisations). C'est, je voudrais exécuter un auto-contenue PL/SQL bloc contenant le SQL dynamique et retour dans l'ensemble des résultats SqlDeveloper ou un outil similaire.

Donc, pour résumer:

Je veux exécuter un arbitraire instruction SQL (d'où le SQL dynamique).
La plate-forme Oracle.
La solution doit être une PL/SQL bloc avec pas de procédures ou de fonctions.
La sortie doit être produite canonique ensemble de résultats; aucune des instructions d'impression.
La sortie doit rendre que dans l'ensemble des résultats SqlDeveloper sans l'aide de toute SqlDeveloper des fonctionnalités spéciales.

Des suggestions?

InformationsquelleAutor Michael Sorens | 2010-04-05

0

Vous semblez demander un morceau de code PL/SQL qui va faire une requête arbitraire de retour ensemble de résultats indéterminés quant à la structure et à l'avant/restructurer' ensemble des résultats d'une certaine façon telle qu'il puisse facilement être rendus par certains "custom outil graphique".

Si oui, regarde dans le DBMS_SQL pour SQL dynamique. Il a un DESCRIBE_COLUMNS procédure qui retourne les colonnes du dynamisme de l'instruction SELECT. Les étapes que vous avez besoin sont,
1. Analyser la déclaration
2. Décrire le jeu de résultats (les noms de colonnes et les types de données)
3. Récupérer chaque ligne et pour chaque colonne, appelez le type de données dépendante de la fonction de renvoyer cette valeur dans une variable locale
4. Place de ces variables locales dans une structure définie pour revenir à l'appel de l'environnement (par exemple la cohérence des noms de colonne [comme col_1, col_2] probablement tous de VARCHAR2)
Comme alternative, vous pouvez essayer de la construction de la requête dans un XMLFOREST déclaration, et d'analyser les résultats de l'XML.

Ajouté :
Contrairement à SQL Server, Oracle PL/SQL sera pas "naturellement" de retour d'un seul ensemble de résultats. Il peut ouvrir un ou plusieurs ref curseurs et de les transmettre au client. Il devient alors de la responsabilité du client pour récupérer les dossiers et les colonnes de ceux ref curseurs. Si votre client ne veut pas/ne peut pas gérer cela, alors vous ne pouvez pas utiliser un PL/SQL appel.
Un stockées fonction peut renvoyer une pré-défini type de collection, qui peut vous permettre de faire quelque chose comme "select * from table(func_name('select * from pays'))". Cependant, la fonction ne peut pas faire DML (mise à jour/supprimer/insérer/fusion) parce qu'il souffle loin de tout concept de la cohérence de cette requête. En Plus de la structure retourné est fixé de sorte que
```
select * from table(func_name('select * from countries'))
```
doivent retourner le même ensemble de colonnes (noms de colonnes et les types de données) comme
```
select * from table(func_name('select * from persons'))
```
Il est possible, à l'aide de DBMS_SQL ou XMLFOREST, par exemple une fonction pour faire une requête dynamique et de le restructurer dans un ensemble pré-défini de colonnes (col_1, col_2, etc), de sorte qu'il peut être retourné de manière cohérente. Mais je ne vois pas ce que le point de il serait.
- Merci pour l'entrée, Gary. Je ne suis pas familier avec soit DBMS_SQL ou XMLFOREST j'ai donc lu un peu sur les deux. Peut-être que je manque quelque chose, mais je ne crois pas soit la technique de ce que je demande. Concernant votre DBMS_SQL suggestion je vois 2 problèmes. Tout d'abord, il semble nécessaire de faire progresser les connaissances de ce qui est dans la cible de la déclaration. Deuxièmement, il ne fait pas de moi un simple jeu de résultats comme le Serveur SQL exemple. Une précision: oui, je suis en utilisant un custom interface graphique de l'outil, mais la solution doit tout d'abord travailler dans SqlDeveloper en utilisant simplement le code PL/SQL, et je ne vois pas comment la technique que vous mentionnez ne peut le faire.
InformationsquelleAutor Gary Myers

Essayez ces.

DECLARE
  TYPE EmpCurTyp  IS REF CURSOR;
  v_emp_cursor    EmpCurTyp;
  emp_record      employees%ROWTYPE;
  v_stmt_str      VARCHAR2(200);
  v_e_job         employees.job%TYPE;
BEGIN
  -- Dynamic SQL statement with placeholder:
  v_stmt_str := 'SELECT * FROM employees WHERE job_id = :j';

  -- Open cursor & specify bind argument in USING clause:
  OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';

  -- Fetch rows from result set one at a time:
  LOOP
    FETCH v_emp_cursor INTO emp_record;
    EXIT WHEN v_emp_cursor%NOTFOUND;
  END LOOP;

  -- Close cursor:
  CLOSE v_emp_cursor;
END;


declare
  v_rc    sys_refcursor;
begin
   v_rc := get_dept_emps(10);  -- This returns an open cursor
   dbms_output.put_line('Rows: '||v_rc%ROWCOUNT);
   close v_rc;
end;

Trouver plus d'exemples ici. http://forums.oracle.com/forums/thread.jspa?threadID=886365&tstart=0

Merci, mais cette solution proposée viole point 4 dans mon résumé (pas d'instructions d'impression) et le point 5. Les deux sont nécessaires parce que je suis en utilisant, comme indiqué, une coutume outil graphique pour voir mes résultats (penser que c'est un très basique SqlDeveloper app).

InformationsquelleAutor Kuberchaun

0

En CRAPAUD lors de l'exécution du script ci-dessous, vous serez invité à indiquer le type de v_result. À partir de la liste de sélection des types sélectionnez curseur, les résultats sont ensuite affichés dans le Crapaud de la grille de données (la feuille de calcul excel comme résultat). Cela dit, lorsque l'on travaille avec des curseurs comme résultats, vous devriez toujours écrire deux programmes (le client et le serveur). Dans ce cas, 'CRAPAUD' sera le client.
```
DECLARE
   v_result      sys_refcursor;
   v_dynamic_sql   VARCHAR2 (4000);
BEGIN
   v_dynamic_sql := 'SELECT * FROM user_objects where ' || ' 1 = 1';

   OPEN :v_result FOR (v_dynamic_sql);
END;
```
Il peut y avoir un mécanisme similaire dans Oracle SQL Developer pour demander la liaison.
- Il y a un mécanisme similaire, comme je l'ai indiqué avec un lien dans ma question, mais cette proposition viole point 5 dans mon résumé (aucune SqlDeveloper des fonctionnalités-le bloc de code doit être complètement autonome).
InformationsquelleAutor Brian

La chose la plus proche que je pouvais penser est de créer une dynamique à la vue pour laquelle une autorisation est nécessaire. Cela nécessitera certainement à l'aide de PL/SQL bloc et une requête SQL n'existe pas de procédure/fonction. Mais, de toute requête dynamique peut être converti et affichés à partir de la Grille de résultats que ça va être exécuter en tant qu'une requête select.

DEFINE view_name = 'my_results_view';
SET FEEDBACK OFF
SET ECHO OFF
DECLARE
  l_view_name VARCHAR2(40)     := '&view_name';
  l_query     VARCHAR2(4000)   := 'SELECT 1+level as id,
                                  ''TEXT''||level as text  FROM DUAL ';
  l_where_clause VARCHAR2(4000):= 
                           ' WHERE TRUNC(1.0) =  1 CONNECT BY LEVEL < 10';
BEGIN
     EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW '
                       || l_view_name
                       || ' AS '
                       || l_query
                       || l_where_clause;
END;
/
 select * from &view_name;

L'obtention de résultats dans un ensemble de résultats de dynamique SQL dans Oracle

InformationsquelleAutor Kaushik Nayak

Vous devez vous connecter pour publier un commentaire.