0

I want to create a function in oracle using vb.net in many databases using loop. I am using this query

CREATE OR REPLACE FUNCTION promo_code_fun(store_id IN VARCHAR2,ws_id IN VARCHAR2,trn_id IN VARCHAR2,business_dt IN VARCHAR2,line_no IN integer,item_cd IN VARCHAR2,creation_time timestamp)

the problem is it is creating function but with errors.. in oracle it is automatically adding a schema name before the function name. I have passed schema name through vb code. but it creates function with errors.

CREATE OR REPLACE FUNCTION " & """" & SchemaName & """" & ".promo_code_fun(store_id IN VARCHAR2,ws_id IN VARCHAR2,trn_id IN VARCHAR2,business_dt IN VARCHAR2,line_no IN integer,item_cd IN VARCHAR2,creation_time timestamp)

if i am copying this code in toad editor and running it manually it creates the function with no errors..

CREATE OR REPLACE FUNCTION "23914".promo_code_fun(store_id IN VARCHAR2,ws_id IN VARCHAR2,trn_id IN VARCHAR2,business_dt IN VARCHAR2,line_no IN integer,item_cd IN VARCHAR2,creation_time timestamp)

while running manually w/o double quotes it is giving error.

a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843
Basmeh Awad
  • 3
  • 2

1 Answers1

0

From the docs:

Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.

Hence if you want to use a schema name of 23914 is must be encloded in double-quotes.

David Aldridge
  • 50,423
  • 8
  • 67
  • 94