Intégration de scripts oracle dans SAS

[mathilde_1180]

Bonjour,

Je souhaite exécuter une requête oracle en utilisant des variables oracle pour des soucis de performance.

Code SAS d'origine (les dates sont des macro variables SAS) :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
proc sql;
connect TO oracle(user=xxx orapw=xxx path=xxx);
CREATE TABLE TMP_CALENDAR AS 
SELECT * FROM connection TO oracle ( 
SELECT 
CAL.CDDATE, 
upper(TYPE.SHORTNAME) AS TYPE 
FROM 
CALENDAR_DATE CAL, 
CALENDAR TYPE 
WHERE 
CAL.CALENDAR = TYPE.TID 
AND ( CAL.VPCREATIONDATE <= to_date('25JAN2012:09:24:16','DDMONYYYY:HH24:MI:SS') ) 
AND ( CAL.VPLASTUPDATE IS NULL OR CAL.VPLASTUPDATE >= to_date('25JAN2012:09:24:16','DDMONYYYY:HH24:MI:SS') ) 
AND ( CAL.VPSTARTDATE IS NULL OR CAL.VPSTARTDATE <= to_date('15Nov2011:00:00:00','DDMONYYYY:HH24:MI:SS') ) 
AND ( CAL.VPENDDATE IS NULL OR to_date('15Nov2011:00:00:00','DDMONYYYY:HH24:MI:SS') < CAL.VPENDDATE)
AND ( TYPE.VPCREATIONDATE <= to_date('25JAN2012:09:24:16','DDMONYYYY:HH24:MI:SS') ) 
AND ( TYPE.VPLASTUPDATE IS NULL OR TYPE.VPLASTUPDATE >= to_date('25JAN2012:09:24:16','DDMONYYYY:HH24:MI:SS') ) 
AND ( TYPE.VPSTARTDATE IS NULL OR TYPE.VPSTARTDATE <= to_date('15Nov2011:00:00:00','DDMONYYYY:HH24:MI:SS') ) 
AND ( TYPE.VPENDDATE IS NULL OR to_date('15Nov2011:00:00:00','DDMONYYYY:HH24:MI:SS') < TYPE.VPENDDATE)
);
disconnect FROM oracle;
quit;

Code oracle à intégrer dans SAS (les dates deviennent des variables oracle) :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
VARIABLE date1 VARCHAR2(20);
VARIABLE date2 VARCHAR2(20);
exec :date1 :=  '25JAN2012:09:24:16' ;    
exec :date2 :=  '15Nov2011:00:00:00' ;
SELECT 
CAL.CDDATE, 
upper(TYPE.SHORTNAME) AS TYPE 
FROM 
CALENDAR_DATE CAL, 
CALENDAR TYPE 
WHERE 
CAL.CALENDAR = TYPE.TID 
AND ( CAL.VPCREATIONDATE <= to_date(:date1,'DDMONYYYY:HH24:MI:SS') ) 
AND ( CAL.VPLASTUPDATE IS NULL OR CAL.VPLASTUPDATE >= to_date(:date1,'DDMONYYYY:HH24:MI:SS') ) 
AND ( CAL.VPSTARTDATE IS NULL OR CAL.VPSTARTDATE <= to_date(:date2,'DDMONYYYY:HH24:MI:SS') ) 
AND ( CAL.VPENDDATE IS NULL OR to_date(:date2,'DDMONYYYY:HH24:MI:SS') < CAL.VPENDDATE)
AND ( TYPE.VPCREATIONDATE <= to_date(:date1,'DDMONYYYY:HH24:MI:SS') ) 
AND ( TYPE.VPLASTUPDATE IS NULL OR TYPE.VPLASTUPDATE >= to_date(:date1,'DDMONYYYY:HH24:MI:SS') ) 
AND ( TYPE.VPSTARTDATE IS NULL OR TYPE.VPSTARTDATE <= to_date(:date2,'DDMONYYYY:HH24:MI:SS') ) 
AND ( TYPE.VPENDDATE IS NULL OR to_date(:date2,'DDMONYYYY:HH24:MI:SS') < TYPE.VPENDDATE)

Merci d'avance pour votre aide.

Cordialement,
Mathilde.

[datametric]

Quelle est la question ?

[mathilde_1180]

la question est :
Comment intégrer le code Oracle dans une proc sql de sas ?
J'ai essayé avec execute() by oracle ; mais ça ne marche pas :

Code :

ERROR: ORACLE execute error: ORA-00900: invalid SQL statement.

Merci d'avance.
Mathilde.

[bahraoui]

http://support.sas.com/techsup/technote/ts566d.pdf
Tu as essayer de faire une requête simple?
Pour essayer de savoir si ce sont les dates qui pose le problème.

Code :

1
2
3
4
5
6
7
8
9
10
 
proc sql;
connect TO oracle(user=xxx orapw=xxx path=xxx);
CREATE TABLE TMP_CALENDAR AS
SELECT * FROM connection TO oracle (
	SELECT count(*)
	FROM CALENDAR_DATE CAL
);
disconnect FROM oracle;
quit;

[mathilde_1180]

oui le premier code marche (même avec les dates).
Ce que je n'arrive pas à faire c'est utiliser des variables oracle au lieu des variables sas dans la requete.

[bahraoui]

Pourquoi tu veux utiliser des variables oracle?
Tu as pensé à utiliser des macro variables SAS.

[bahraoui]

Le fait d'utiliser une macro variable SAS ne dégradera pas les performances.

[mathilde_1180]

Si justement, ça dégrade les performance au niveau de Oracle. Nous avons fait des tests et on gagne 80% de temps en utilisant des bind variables dans Oracle au lieu d'utiliser des constantes. En fait, à l'origine, on utilisait des macro variables sas mais elles sont interprétées par SAS avant que la requête soit envoyée à Oracle, donc c'est comme si on utilisait des constantes pour oracle.

[datametric]

le mieux serait d'appeler une procédure stockée Oracle qui contient les dates en paramètres.

[s_a_m]

Envoyé par mathilde_1180

Si justement, ça dégrade les performance au niveau de Oracle. Nous avons fait des tests et on gagne 80% de temps en utilisant des bind variables dans Oracle au lieu d'utiliser des constantes. En fait, à l'origine, on utilisait des macro variables sas mais elles sont interprétées par SAS avant que la requête soit envoyée à Oracle, donc c'est comme si on utilisait des constantes pour oracle.

Hello;
Les Bind variables sont des objets Oracles que SAS ne connait pas.

[xav2229]

je n'arrive pas a croire que un bind oracle marche mieux qu'un to_date

rien ne t'empeche de stocker dans ta macro-variable un to_date:

Code :

1
2
 
%let madate = to_date('18/03/2012', 'DD/MM/YYYY');

X

[s_a_m]

to_date est une fonction et les binde variables sont des applications utilisées pour optimiser les traitements , en gros c'est pour éviter que le moteur Oracle fait un parse de la requette pour chercher le bon plan d'execution à chaque exécution de la requête et c'est ce qui permet d'optimiser les traitements.

[xav2229]

oui pour du PL/SQL on gagne mais une pauvre requête cela ne doit pas être cela qui impacte les temps de traitements.

sauf si la requête est rejouée très très souvent, alors là je rejoindrais plutôt la réponse procédure stockée.

[s_a_m]

OUI OUI ... procédure stocke , package ou autre choses... avec la commande execute.
Mais comment rappatrier les résultats de ces procédures stockées Oracle dans SAS?

[xav2229]

OK pour le retour...

mais j'aimerais savoir quelle est la procédure de test qui a permis de conclure que :

on gagne 80% de temps en utilisant des bind variables dans Oracle

surtout par rapport aux to_date mentionnés, tu mets que :

en gros c'est pour éviter que le moteur Oracle fait un parse de la requette pour chercher le bon plan d'execution à chaque exécution de la requête et c'est ce qui permet d'optimiser les traitements.

je penche donc plutot du coté du design de la base, que l'optimiser est perdu a cause de mauvaises statistiques.

il est déconseillé de ne plus utiliser les requêtes qui forcent l'optimiser (hints) (depuis 10g je crois)

X

[datametric]

Mais comment rappatrier les résultats de ces procédures stockées Oracle dans SAS?

Ca c'est simple :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
proc sql;
 
connect TO odbc AS dblink ("driver=SQL Server; server=chezMoi; database=Maison; owner=DBO;");
 
      CREATE TABLE test1 AS
 
      SELECT *
 
FROM connection TO dblink
 
      (execute CheckQuantityUnits 'H2', 208, 2010, 2009 );
 
disconnect FROM dblink;
 
quit;

[s_a_m]

Envoyé par xav2229

OK pour le retour...

mais j'aimerais savoir quelle est la procédure de test qui a permis de conclure que :


surtout par rapport aux to_date mentionnés, tu mets que :

X

Je ne parlais pas de to_date mais de l'utilité de l'utilisation de variables BIND.

Envoyé par xav2229

je penche donc plutot du coté du design de la base, que l'optimiser est perdu a cause de mauvaises statistiques.

il est déconseillé de ne plus utiliser les requêtes qui forcent l'optimiser (hints) (depuis 10g je crois)

X

Je crois que cette question il faut plutôt la poser à mathilde_1180. qui évoque la problèmatoque des bind et leurs utilisations... Tout depend de leurs contexte...
Et oui Oracle déconseille d'utiliser les BIND (à lire sur leurs site).

[s_a_m]

Envoyé par datametric

Ca c'est simple :

Code :

Les procédures que je developpe en générale alimentent ou traitent plusieurs tables ( dans la même procédure). Penses-tu que je pourrai utiliser le code que tu proposes dans ce cas ?

[datametric]

Tu as vu dans mon exemple que je récupère le résultat de la requête. Tu ne sais donc pas que ma procédure fait une jointure de x tables.
Donc si ta procédure fait des INSERT INTO et que tu renvoies un résultat ( un RC ) je pense que SAS peut l'exécuter.
L'idée est quand même d'exécuter une procédure pour rapatrier quelque chose. Pour une autre utilisation il faut tester.

[s_a_m]

PK donc une procédure? une proc sql pass trouth + PL ou transact /SQL ne devra pas suffire ?