Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Posts
    1

    Post Unanswered: stored procedures + temptables ?

    is it possible to use stored procedures with temptables?

    heres a piece of my script:

    CREATE OR REPLACE PROCEDURE FRDMMGR.SPANALYZER (p_PD_LINK IN VARCHAR2)
    AS
    BEGIN

    EXECUTE IMMEDIATE ('CREATE TABLE #tables_temp (TABLE_NAME VARCHAR2 (40), TABLE_STORAGE VARCHAR2 (255))');
    EXECUTE IMMEDIATE ('CREATE TABLE #tables_compare (TABLE_NAME VARCHAR2 (40),
    TABLE_STORAGE VARCHAR2 (255))');
    EXECUTE IMMEDIATE ('CREATE TABLE #columns_1 (COLUMN_NAME VARCHAR2 (40),
    TABLE_NAME VARCHAR2 (40), COLUMN_LENGTH NUMBER (10), COLUMN_PRECISION NUMBER (10),
    COLUMN_SCALE NUMBER (10))');
    EXECUTE IMMEDIATE ('CREATE TABLE #columns_2 (COLUMN_NAME VARCHAR2 (40),
    TABLE_NAME VARCHAR2 (40), COLUMN_LENGTH NUMBER (10), COLUMN_PRECISION NUMBER (10),
    COLUMN_SCALE NUMBER (10))');
    EXECUTE IMMEDIATE ('CREATE TABLE #col_compare (COLUMN_NAME VARCHAR2 (40),
    TABLE_NAME VARCHAR2 (40), COLUMNS VARCHAR2 (50), COLUMN_LENGTH VARCHAR2 (20),
    COLUMN_PRECISION VARCHAR2 (20), COLUMN_SCALE VARCHAR2 (20))');


    it gives me an error at line one saying 'invalid character', but i dont see anything wrong with the syntax... any ideas? thanks

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: stored procedures + temptables ?

    #tablename is a sql-server notation. You also had an extra ) at the end of each line

    Try

    execute immediate 'Create Global Temporary Table tables_temp(TABLE_NAME VARCHAR2 (40), TABLE_STORAGE VARCHAR2 (255))';


    It is often a good idea when you getting errors to copy the code out of the execute immediate and run it stand alone. this will show you if your embedded code has errors.

    Tony


    Originally posted by shugarhi
    is it possible to use stored procedures with temptables?

    heres a piece of my script:

    CREATE OR REPLACE PROCEDURE FRDMMGR.SPANALYZER (p_PD_LINK IN VARCHAR2)
    AS
    BEGIN

    EXECUTE IMMEDIATE ('CREATE TABLE #tables_temp (TABLE_NAME VARCHAR2 (40), TABLE_STORAGE VARCHAR2 (255))');
    EXECUTE IMMEDIATE ('CREATE TABLE #tables_compare (TABLE_NAME VARCHAR2 (40),
    TABLE_STORAGE VARCHAR2 (255))');
    EXECUTE IMMEDIATE ('CREATE TABLE #columns_1 (COLUMN_NAME VARCHAR2 (40),
    TABLE_NAME VARCHAR2 (40), COLUMN_LENGTH NUMBER (10), COLUMN_PRECISION NUMBER (10),
    COLUMN_SCALE NUMBER (10))');
    EXECUTE IMMEDIATE ('CREATE TABLE #columns_2 (COLUMN_NAME VARCHAR2 (40),
    TABLE_NAME VARCHAR2 (40), COLUMN_LENGTH NUMBER (10), COLUMN_PRECISION NUMBER (10),
    COLUMN_SCALE NUMBER (10))');
    EXECUTE IMMEDIATE ('CREATE TABLE #col_compare (COLUMN_NAME VARCHAR2 (40),
    TABLE_NAME VARCHAR2 (40), COLUMNS VARCHAR2 (50), COLUMN_LENGTH VARCHAR2 (20),
    COLUMN_PRECISION VARCHAR2 (20), COLUMN_SCALE VARCHAR2 (20))');


    it gives me an error at line one saying 'invalid character', but i dont see anything wrong with the syntax... any ideas? thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •