Architecting, installing and maintaining your SAS environment

Submit PL/SQL from SAS/ACCESS

Accepted Solution Solved
Reply
Contributor DCL
Contributor
Posts: 21
Accepted Solution

Submit PL/SQL from SAS/ACCESS

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


Accepted Solutions
Solution
‎01-25-2012 05:08 PM
Super User
Posts: 2,330

Submit PL/SQL from SAS/ACCESS

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.

View solution in original post


All Replies
Respected Advisor
Posts: 4,457

Submit PL/SQL from SAS/ACCESS

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

/Linus

Data never sleeps
Contributor DCL
Contributor
Posts: 21

Submit PL/SQL from SAS/ACCESS

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.

Super User
Posts: 2,330

Submit PL/SQL from SAS/ACCESS

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.

Contributor DCL
Contributor
Posts: 21

Submit PL/SQL from SAS/ACCESS

Here is the sas code I had submitted

sascode.jpg

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



Contributor DCL
Contributor
Posts: 21

Submit PL/SQL from SAS/ACCESS

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

..

Solution
‎01-25-2012 05:08 PM
Super User
Posts: 2,330

Submit PL/SQL from SAS/ACCESS

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.

Contributor DCL
Contributor
Posts: 21

Submit PL/SQL from SAS/ACCESS

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! Smiley Happy

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.

Esteemed Advisor
Esteemed Advisor
Posts: 5,286

Submit PL/SQL from SAS/ACCESS

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?

Highlighted
Contributor DCL
Contributor
Posts: 21

Submit PL/SQL from SAS/ACCESS

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

Post a Question
Discussion Stats
  • 10 replies
  • 6903 views
  • 3 likes
  • 5 in conversation