Stored procedure - Facing error while executing Call statement

General
Enthusiast

Stored procedure - Facing error while executing Call statement

Hi,

I have createda procedure and it was success but while execuing the call procedure statement by passing the parameter values I am facing an error stating Failure 3706:syntax error expected GLOBAL TEMPORARY.

I have not defined any temporary tables insie the PRO.

Please help

Thanks 

Madhu

6 REPLIES

Re: Stored procedure - Facing error while executing Call statement

Hi,

I have created a belo procedure for table baackup, But I am getting the error'3706 syntax error expected global temporary'

call SAI_OPERATIONS.CHC_PRD_TABLE_BACKUP_1('CHC','CHC_TB','BKP');

Please help on me on this....

Thanks in advance..

CREATE PROCEDURE SAI_OPERATIONS.CHC_PRD_TABLE_BACKUP_1(

IN DATABASENAME VARCHAR(20),

IN TABLENAME1 VARCHAR(20),

in TABLENAME1_bkp VARCHAR(20)

)

BEGIN

DECLARE P_DB_COUNT INTEGER;

DECLARE P_TABLE_COUNT INTEGER;

Declare MESSAGE Varchar(100);

DECLARE SqlTxt VARCHAR(500);

SELECT COUNT(*) INTO :P_DB_COUNT

FROM   DBC.DATABASES

WHERE  UPPER(DatabaseName) = UPPER(:DatabaseName);

If   P_DB_COUNT =  0 THEN

     SET MESSAGE = 'Database ' || DATABASENAME|| 'does not exists!';

 else   

SELECT COUNT(*) INTO :P_TABLE_COUNT

FROM   DBC.DATABASES

WHERE  UPPER(TableName1) = UPPER(:TableName1);

end if;

if P_TABLE_COUNT=0 THEN

SET MESSAGE = 'TABLENAME' ||TABLENAME1|| ' doesnot exist!';

else

SET SqlTxt = (' CREATE TABLE'   || DATABASENAME ||'.'|| TABLENAME1_bkp || 'AS' || DATABASENAME ||'.'||TABLENAME1||  'WITH DATA and STATS' );

---SET SqlTxt = (' CREATE TABLE'   || :DATABASENAME ||'.'|| :TABLENAME1_bkp || 'AS' || :DATABASENAME ||'.'||:TABLENAME1||  'WITH DATA and STATS' );

CALL DBC.SysExecSQL(:SqlTxt);

--CALL DBC.SysExecSQL (' CREATE TABLE'   || :DATABASENAME ||'.'|| :TABLENAME1_bkp || 'AS' || :DATABASENAME ||'.'||:TABLENAME1||  'WITH DATA and STATS' );

end if;

END

Senior Apprentice

Re: Stored procedure - Facing error while executing Call statement

You need to add some spaces, currently you try to execute this

CREATE TABLEdb.tb_bkpASdb.tbWITH DATA and STATS

Re: Stored procedure - Facing error while executing Call statement

Hi,

I am not able call this procedure with parameteres, "Failure 3706:syntax error expected GLOBAL TEMPORARY."

Just give me How call this procedure....?

Regards

Mohammad

Senior Apprentice

Re: Stored procedure - Facing error while executing Call statement

Hi Mohammad,

you need to fix the source code before you can call this procedure :-)

As I already wrote: your SET statement results in an invalid CREATE TABLE statement in SqlTxt.

Re: Stored procedure - Facing error while executing Call statement

Hi Dieter,

Could you please give me the correct statement with calling parameters values.

Regards

Mohammad

Enthusiast

Re: Stored procedure - Facing error while executing Call statement

SET SqlTxt = (' CREATE TABLE'   || DATABASENAME ||'.'|| TABLENAME1_bkp || 'AS' || DATABASENAME ||'.'||TABLENAME1||  ' WITH DATA and STATS' );