Submit PL/SQL from SAS/ACCESS

[DCL]

Hi,

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,
Dilip

[LinusH]

Sounds like a strange requirement. What is the business case for this requirement?

/Linus

[DCL]

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.

[SASKiwi]

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.

[DCL]

Here is the sas code I had submitted

And the error I get is "ERROR: ORACLE execute error : ORA-00900: invalid SQL statement

[DCL]

Hi,

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....);

proc sql....

execute("&plsql.");

..

[SASKiwi · Accepted solution]

In that case this SAS note will be of interest:

http://support.sas.com/kb/42/930.html

What version of SAS are you running.

[DCL]

Hi SASKiwi,

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!

Dilip

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.

[Tom]

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?

[DCL]

Hi Tom,

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.

Thanks,
Dilip

[boschy]

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.