BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DCL
Obsidian | Level 7 DCL
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

10 REPLIES 10
LinusH
Tourmaline | Level 20

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

/Linus

Data never sleeps
DCL
Obsidian | Level 7 DCL
Obsidian | Level 7

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
PROC Star

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
Obsidian | Level 7 DCL
Obsidian | Level 7

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

DCL
Obsidian | Level 7 DCL
Obsidian | Level 7

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
PROC Star

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
Obsidian | Level 7 DCL
Obsidian | Level 7

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.

Tom
Super User Tom
Super User

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
Obsidian | Level 7 DCL
Obsidian | Level 7

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
Fluorite | Level 6

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.

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 10 replies
  • 19964 views
  • 3 likes
  • 5 in conversation