I'am an SQL rookie and just started my Apprenticeship in the CMD Team of my Company. It's my first time working with Oracle SQL-dev and I never coded Functions/Procedures in SQL before So my Sensei gave me a task to solve for myself which is:

To create a stored procedure which generates views for all of my Synonyms in the current Scheme. If executed again it should Replace the current views of those Synonyms.

As second part of the task I should also add a function to DROP all the views i don't have a Synonym for

Yes I already discussed with her if it is useful to create views this way or not

The first thing i found out that it is not possible to create views from a stored procedure the traditional way and that i have to use a workaround method with EXEC() for example

my Sensei gave me a code Snippet to begin with:

FOR KO IN(SELECT * FROM all_synonyms WHERE OWNER = 'CMD_SANDBOX')

She told me i have to fill Variables with the Names of the Synonyms .. so far so good it makes sense because i have to generate unique names for the views as well as the procedure to know which is the current synonym to create a view of

CREATE OR REPLACE PROCEDURE CREATE_VIEWS AS 
 DECLARE @viewCommand varchar(1000)
 DECLARE @viewName    varchar(75)
 DECLARE @synonymName varchar(75)

 SET @viewCommand = 'CREATE OR REPLACE VIEW'   @viewName   'AS SELECT * FROM ' 
  @synonymName 
 

BEGIN
 FOR KO IN(SELECT * FROM all_synonyms WHERE OWNER = 'CMD_SANDBOX')

  SET @synonymName = <Pointer on Synonym>
  SET @viewName =  'v_'   @synonymName
  EXEC(@viewCommand)

 END LOOP KO; 

END CREATE_VIEWS

Long story short... My questions are: How do I Point to a Certain Object without using its specific name to fill my @synonymName ? Is the For Loop header already complete ? I kinda don't get how it works in SQL

How do you pros research this stuff? I feel pretty confident in queries but as it comes to specific things like pointing to objects or similar it is pretty hard to find out.

CodePudding user response：

Your syntax is invalid in Oracle as:

• there are multiple DECLAREs but only a single BEGIN/END block;
• @ is not valid in (unquoted) variable names;
• is the numeric addition operator and does not concatenate strings. To concatenate strings you want to use the || operator;
• assignment in PL/SQL does not require SET and requires := instead of =; and
• you want EXECUTE IMMEDIATE and not EXEC.

If you fix all that then there are still issues as VIEW' @viewName 'AS should have spaces between the VIEW and AS keywords and the identifier.

The syntax looks more like SQL Server than Oracle.

For Oracle, you want:

CREATE OR REPLACE PROCEDURE CREATE_VIEWS
AS 
BEGIN
  FOR KO IN(
    SELECT synonym_name
    FROM   all_synonyms
    WHERE  OWNER = 'CMD_SANDBOX'
  )
  LOOP
    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW v_' || ko.synonym_name
                   || ' AS SELECT * FROM ' || ko.synonym_name;
  END LOOP;
END CREATE_VIEWS;
/