Create Table In Oracle Procedures

[Ken Watts]

Hello, I am new to all of this. I have basic SQL skills. I am trying to create a table using a procedure in Oracle Developer but I can't seem to get the table to create. I get no errors.

create or replace
PROCEDURE SKELETON2
Is
str long;
s_ERR VARCHAR2(500);
BEGIN
STR := 'CREATE TABLE ken_blah2 AS
SELECT sbscrp_id,
MONTH
FROM ken_test
where rownum < 10
';
execute immediate str;
s_ERR := 'SUCCESS';
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
s_ERR := ' ERROR: ' || SQLERRM;
END;

[Saii]

Hello, I am new to all of this. I have basic SQL skills. I am trying to create a table using a procedure in Oracle Developer but I can't seem to get the table to create. I get no errors.

create or replace
PROCEDURE SKELETON2
Is
str long;
s_ERR VARCHAR2(500);
BEGIN
STR := 'CREATE TABLE ken_blah2 AS
SELECT sbscrp_id,
MONTH
FROM ken_test
where rownum < 10
';
execute immediate str;
s_ERR := 'SUCCESS';
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
s_ERR := ' ERROR: ' || SQLERRM;
END;

Display the error to screen using dbms_output in exception block.
Do you have privilege to create tables in the schema?

[Ken Watts]

Display the error to screen using dbms_output in exception block.
Do you have privilege to create tables in the schema?

I do not get an error. It simply does not create the table with the data. yes I have create table priviledges. Thanks

[Ken Watts]

Did I do this correctly?

create or replace
PROCEDURE SKELETON
aS
s_ERR VARCHAR2(500);
STR LONG;
BEGIN
STR := '
CREATE TABLE ken_blah AS
SELECT sbscrp_id,
MONTH,
rankm,
cnt,
1 +
(SELECT COUNT(*)
FROM ken_test temp
WHERE temp.rankm < ken_test.rankm
AND temp.sbscrp_id = ken_test.sbscrp_id
AND kent_test.ROWNUM < 10)
rankmm
FROM ken_test
ORDER BY sbscrp_id,
chg DESC
';
execute immediate str;
s_ERR := 'SUCCESS';
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
s_ERR := ' ERROR: ' || SQLERRM;
dbms_output.enable(1000000);
END;

[Ken Watts]

Sorry, different query but same issue.

[debasisdas]

does the second table exists in the same schema and/or u have select previleges on the table.

[debasisdas]

add this to the before END

u can find out the error

dbms_output.put_line(s_err);