Droping Oracle Table

[etldeveloper2050]

Hi,
I am trying to drop a table using routine. My Table name is ERR$_EMPLOYEE but RunSQL routine is taking table name as ERR i.e. its taking $ as end of line. PFB the code:

SQL: Drop table ERR$_EMPLOYEE
Call DSU.RunSQL(iCheckStat, I.DB_NAME, I.DB_USER, I.DB_PWD, SQL )

I am not able to find RunSQL routine to check if its using $ as end of line. Please suggest me some way to drop a table which contains special characters through routine.

Thanks.

[tkbharani]

Did you try using Double quotes. Else pass it in a variable and then use it by calling it.

[etldeveloper2050]

Did you try using Double quotes. Else pass it in a variable and then use it by calling it.

Yeah i passed it as parameter.

PFB the log:
Display: DROP TABLE ERR$_EMPLOYEE;
--->It displays the SQL properly
Output: DROP TABLE ERR
*
ERROR at line 1:
ORA-00942: table or view does not exist
-->Routine is taking $ as end of line. thus trying to drop table ERR instead of ERR$_EMPLOYEE.

[chulett]

The "DSU" prefix means it is a user written BASIC routine, so the only one that can fix or trouble shoot it is you and yours. Check the Routines\Custom branch of the repository from the Manager for starters.

[etldeveloper2050]

The "DSU" prefix means it is a user written BASIC routine, so the only one that can fix or trouble shoot it is you and yours. Check the Routines\Custom branch of the repository from the Manager ...

Yeah its a user routine.. problem is i have not coded that and i dont know also where it is? i tried to find it but no success....

[ray.wurlod]

Code: Select all

SELECT * FROM DS_ROUTINES WHERE @ID = 'RunSQL';

[etldeveloper2050]

Code: Select all

SELECT * FROM DS_ROUTINES WHERE @ID = 'RunSQL';

...

Thanks wurlod.. i got the routine. Its executing the SQL query using Dsexecute.
Call DSExecute("UNIX", command, Output, RetCode)

SQL query passed properly to this routine but the DSExecute function is taking "$" as end of line. Please suggest what can be done to enable d routine to read it as ERR$_EMPLOYEE instead of ERR.

[ray.wurlod]

Who knows? You see, none of us can see the routine code.

[etldeveloper2050]

Who knows? You see, none of us can see the routine code. ...

... can you suggest me where can i get more info. on DSExecute.

I want to know how we can pass tablename with $ to DSExecute so that it considers "$" also part of query.
For Ex:
Below code, i am assigning database details and sql query "DROP TABLE ERR$_EMPLOYEE" into the variable command.

Call DSExecute("UNIX", command, Output, RetCode)

[ray.wurlod]

DROP TABLE is not a UNIX command. You'd have to wrap that in an sqlplus script or similar.

[chulett]

Have you tried escaping it? Put a back-slash before the dollar sign.

[etldeveloper2050]

Have you tried escaping it? Put a back-slash before the dollar sign. ...

Yahooo......... back-slash thing is working... table is getting dropped. can you please explain me the logic behind backslash... is it something like it will look for similar tables or it will tell DSExecute that "$" is part of the table name? why i am asking is i should not end up with dropping others table...

anywayz, Thanks alot everyone specially chullet and ray for your help.

[chulett]

The latter. It's a normal convention when working with metacharacters and you escape them so they become a literal instead of being interpreted by the shell/program/parser/whatever.

Please mark the thread as Resolved using the big button at the top of the screen.

[etldeveloper2050]

The latter. It's a normal convention when working with metacharacters and you escape them so they become a literal instead of being interpreted by the shell/program/parser/whatever.

Please mark the thread as Resolved using the big button at the top of the screen.

Ok. Thanks Chullet.