I've been asked to evaluate whether Oracle PL/SQL could be used to perform a re-design of a piece of Fortran code that our organisation has used for years.

This code interacts with our database and cycles through a list of newly registered web users, providing their details to a helpdesk team. The code then asks the helpdesk team if they are happy with the user details, through a Y/N? If Y, the code then proceeds and asks the helpdesk team, again through a series of Y/N statements if certain other statements are true. At the end of every Y/N, there is a write to a row on one or more database tables.

Once it finishes these enquiries with the first user, the code then loops and repeats for the next user in the list.

I know Oracle PL/SQL allows the use in scripts of input variables through & and &&, but I don't know if the use of these input variable forms is allowed within functions and procedures?

Can someone clarify please for me if & and && can be used within PL/SQL code such as functions and procedures?

If not, that's fine - I'll just then have to approach the design process differently.

Thanks

CodePudding user response：

No, that won't work in stored procedures. PL/SQL runs on the database server and isn't supposed to interact with users. You can pass values via its parameters (arguments), but that's it.

But, if you're on Oracle, consider using its Application Express (evaluate it on apex.oracle.com, it is free to register and use). In a matter of several clicks (well, not really, but suppose it is so), you can develop a web application which would then be used by users. You'd create a progress list navigation which would lead you from one page to another; you'd ask questions and on each press on the "Next page" button you'd store their answer into the database.

So, if you do know some (PL/)SQL, you might do that rather easily.

Illustration: