Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DBMS_SQL -HELP....

Re: DBMS_SQL -HELP....

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Mon, 08 Feb 1999 22:37:48 GMT
Message-ID: <36bf650b.31808588@inet16.us.oracle.com>


On Mon, 08 Feb 1999 21:45:25 GMT, gayatribalaji_at_my-dejanews.com wrote:

>Hi,

You seem to be missing one semicolon and have a colon instead of a semicolon in another place.

>
>I tries to run a PL/SQl block which creates a table.
>DECLARE
> v_cursor_id number;
> v_createtableString varchar2(500);
> v_numrows integer;
>BEGIN
> v_cursor_id := DBMS_SQL.OPEN_CURSOR;
> v_createtableString := 'CREATE TABLE Mytable(NAME varchar2(20),MYNUM
>number(2))'

            ^^^
Missing semicolon

> DBMS_SQL.PARSE(v_cursor_id,v_createtableString,DBMS_SQL.V7.3);
> v_numrows := DBMS_SQL.EXECUTE(v_cursor_id);

Close your cursor here

>
>EXCEPTION
> WHEN OTHERS THEN
> IF SQLCODE != -955 THEN
> RAISE;
> ELSE
> DBMS_OUTPUT.PUTLINE('TABLE ALREADY EXSISTS');
> END IF:

                      ^^^

Should be a semicolon

> DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
>END;
>/
>
>It gives me a error message BIND VARIABLE "DBMS_SQL" not declared.
>Can anyone help me in this...

Make sure the whoever runs this has 'create table' granted directly to them and not though a role. They will need that since roles are not enabled in procedures and functions.

Hope this helps.

chris.

>
>Thanks,
>gayatri
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.



Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Feb 08 1999 - 16:37:48 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US