01-24-2012 02:12 AM
I have a requirement to submit Oracle PL/SQL, create Oracle stored processes... via SAS.
I tried the PROC SQL pass through facility but was not successful.
Is there any other means to do this?
Thanks in advance,
01-24-2012 03:46 AM
We have developed a SAS macro that creates our Applications Oracle Database by executing the individual SQL scripts(DDL+DML statements) that are stored in a repository.Using the PROC SQL pass through facility, we are able to execute all the DDL/DML statements.
We have few stored procedures in our database and want to integrate the creation of this in our automated Database build macro.
Please let me know if you have further questions.
01-24-2012 03:19 PM
It would be helpful if you could supply some sample code of what you have tried.
Have you used the EXECUTE pass-thru statement in your tests? I know you can use this to run stored procedures. I have done this myself but have not tried to create them. In theory there should be nothing to prevent it.
01-25-2012 04:01 AM
I just tried another options:
1. I removed the single quote inside the execute() and all the semi-colons from the stored procedure and submitted it via PROC SQL execute. SAS worked and the SP was created in Oracle database. But, the stored procedure threw errors since it needs the semi-colon that I had removed.
So, I think it is the semi-colon which causes the compiler to fail.
2. and when I tried to assign the whole stored procedure code to a macro variable after quoting the text and then ran the sas code, it failed "ORA-00972: Identifier is too long.
%let plsql= %quote(create or replace....);
01-27-2012 02:20 AM
Thanks a lot! I have been testing this in the SAS old version 9.1.3. I tried this today in SAS9.2 and it worked.
Thanks to all who have spent time on this!
P.S - I have been performing my testing with the old sas version(9.1.3) because my new EG client SAS EG 4.2 had got corrupted.
01-25-2012 08:01 PM
Did you try protecting the semi-colons that are intended for passthrough with %STR() so that SAS does not see them special characters?
I do not know PL/SQL syntax but why does BEGIN statement not need a semi-colon?
01-27-2012 02:24 AM
I had tried the option to mask the semi colon (%nrbquote), but that did not work. Because the masking function was passed as it is in the creation of the stored procedure and the stored procedure threw errors as it is didnot understand the masking function.
As, I had replied to SASKiwis post, now the issue is fixed. It was the problem with SAS9.1.3.
01-24-2012 06:57 PM
This is tricky, but not impossible.
As mentioned, use Oracle SQL pass-thru via the EXECUTE ( ) option. What you might need for PL/SQL statements are the BEGIN and END in the correct syntax, e.g. with the semi-colons in the right places.
If the PL/SQL statements run in an Oracle session (SQL-Plus, TOAD) then they should run in EXECUTE pass-thru. Perhaps check the SAS/ACCESS Engine for Oracle manual for more information. I thought I had an example of PL/SQL statements, but I'm only running INSERTs or calling a procedure.
You should not need to put the PL/SQL into a procedure for SAS, but it's another approach to explore.