CODEWITHSUNDEEP
sqlstringoracle-databasestored-proceduresplsql
sunsilk85
sunsilk85

Reputation: 21

Oracle stored procedure with parameter

I am fairly new to Oracle SQL. I am trying to create a stored procedure which takes 2 parameters. I am getting errors when I save below. Any idea ?

CREATE OR REPLACE PROCEDURE SWAP_VIEWS 
(
  SchemaName NVARCHAR,
  TableName NVARCHAR
) AS 
BEGIN
  DECLARE SQLstring NVARCHAR :=
      'ALTER VIEW  AS POL.V_' + TableName + ' as SELECT * FROM ' + SchemaName + '.' + TableName
  EXEC SQLstring;
END SWAP_VIEWS ;

Upvotes: 2

Views: 27009

Answers (3)

GMB
GMB

Reputation: 222462

You seem to be mixing SQL Server and Oracle syntax here:

  • Oracle uses standard operator || for string concatenation, not +

  • Oracle wants EXECUTE IMMEDIATE instead of EXEC

  • you need CREATE OR REPLACE VIEW

  • NVARCHAR2 should be preferred to NVARCHAR

Note that you don't need an intermediate variable assignment, you can concatenate the query string and execute it at once.

Consider:

CREATE OR REPLACE PROCEDURE SWAP_VIEWS (
  pSchemaName NVARCHAR2,
  pTableName  NVARCHAR2
) AS 
BEGIN
EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW  AS POL.V_' 
    || pTableName + ' as SELECT * FROM ' 
    || pSchemaName || '.' || pTableName;
END;
/

Upvotes: 3

Sujitmohanty30
Sujitmohanty30

Reputation: 3316

I have taken your procedure and modified like below, see if it works. Additionally i want to say, you can explicitly do exception handling in the procedure to capture any error during the execution and also creating view with '*' has some disadvantages. Please chek

CREATE OR REPLACE PROCEDURE SWAP_VIEWS 
(
  p_schema_name IN NVARCHAR2,
  p_table_name  IN NVARCHAR2
)
IS
    lo_sql_string NVARCHAR2(4000);
    lo_view_name NVARCHAR2(30);
    lo_table_name NVARCHAR2(30);
BEGIN
    -- I just prefer local variables for convenience to avoid more complex string in actual one and also for debugging purpose
    lo_view_name := 'POL.V_'||p_table_name;
    lo_table_name := p_schema_name||'.'||p_table_name;
    lo_sql_string:= 'create or replace view '||lo_view_name||
                               ' as '||
                               'select * from '||lo_table_name;
  
    EXECUTE IMMEDIATE lo_sql_string;
END SWAP_VIEWS;
/

Upvotes: 0

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

Here the version corrected

CREATE OR REPLACE PROCEDURE SWAP_VIEWS 
(
  SchemaName NVARCHAR,
  TableName NVARCHAR
) AS 
SQLstring varchar2(4000);
BEGIN

 SQLstring := 'CREATE OR REPLACE FORCE VIEW POL.V_' || TableName || ' as SELECT * FROM ' || SchemaName || '.' || TableName ' ;
 EXECUTE IMMEDIATE SQLstring;
  
END SWAP_VIEWS ;
/
  • The concatenation character is |
  • The variable SQLString must be declared
  • The way to execute a variable is EXECUTE IMMEDIATE
  • You can use CREATE OR REPLACE FORCE VIEW instead of ALTER VIEW

Upvotes: 2

Related Questions