Forum Programmation.autre Oracle Pro*c

Error at line 203, column 1 in file grant_role.pc
PCC-S-02010, found end-of-file while scanning string literal
Error at line 0, column 0 in file grant_role.pc
PCC-F-02102, Fatal error while doing C preprocessing

PCC-S-02010: found end-of-file while scanning string literal
Cause: A string in a SQL statement, which should be delimited by single quotation marks, was not terminated properly.
Action: Check that all strings are delimited.

#include stdio.h;


#include string.h;


#include sqlca.h;


#include stdlib.h;


#include sqlda.h;


#include sqlcpr.h;





EXEC SQL BEGIN DECLARE SECTION;





char      db_username[40];


char      db_passwd[40];


char      uid[81];





char      CreateRole[255];


char      GrantCreateSession[255];


char      GrantCreateTable[255];


char      GrantCreateView[255];


char      GrantCreateTrigger[255];


char      GrantUpdate[255];


char      GrantInsert[255];


char      GrantSelect[255];


char      GrantAlter[255];


char      GrantRoleToUser[255];


char      SetDefaultRole[255];








char     owner_name[40];


char     quoted_owner_name[42];


char     role_name[80];


char     table_fqn[255];








sql_cursor  table_cursor;


struct


{


    char  used_table[50];


}table_record;





EXEC SQL END DECLARE SECTION;





void sql_error(msg)


   char *msg;


{


   char err_msg[512];


   size_t buf_len, msg_len;





   EXEC SQL WHENEVER SQLERROR CONTINUE;





   printf("\n%s\n", msg);





/* Call sqlglm() to get the complete text of the


* error message.


*/


   buf_len = sizeof (err_msg);


   sqlglm(err_msg, &buf_len, &msg_len);


   printf("%.*s\n", msg_len, err_msg);





   EXEC SQL ROLLBACK RELEASE;


   exit(EXIT_FAILURE);


}





void main()


{  


/*Get db passwd*/  


    sprintf(db_passwd,"%c",'0');


       printf("\nEnter sys db password (0 to quit): ");


       gets(db_passwd);


       if(!strcmp(db_passwd,"0"))


           exit (0);


       


/*Get owner name and put it between quotes*/  


    sprintf(owner_name,"%c",'0');  


       printf("\nEnter schema owner name (0 to quit): ");


       gets(owner_name);


       if(!strcmp(owner_name,"0"))


           exit (0);


       sprintf(quoted_owner_name,"'%s'",owner_name);


 





/*Get role name*/            


    sprintf(role_name,"%c",'0');


       printf("\nEnter role name (0 to quit): ");


       gets(role_name);


       if(!strcmp(role_name,"0"))


           exit (0);      


   


/* Connect to ORACLE. */


 


   sprintf(db_passwd,"%s",'sys');


 


   sprintf(uid,"%s/%s",db_username,db_passwd);


 


   EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--");





   EXEC SQL CONNECT :uid IN SYSDBA MODE;


   printf("\nConnected to ORACLE as user: %s\n", db_username);





   EXEC SQL DECLARE ALL_TABLES TABLE (TABLE_NAME VARCHAR2(30) , OWNER VARCHAR2(30));








/*retrieve table list in a cursor predefined structure*/


  EXEC SQL DECLARE table_cursor CURSOR FOR


   SELECT TABLE_NAME FROM ALL_TABLES  


   WHERE owner = :quoted_owner_name;





  EXEC SQL OPEN table_cursor;





/*Create role*/


//DDL statements can't be specified with host variables in it.


 


   sprintf(CreateRole, "EXEC SQL CREATE ROLE %s", role_name);


   EXEC SQL PREPARE q FROM :CreateRole;


   EXEC SQL EXECUTE q;





/*Grant privileges to role*/  


   sprintf(GrantCreateSession, "EXEC SQL GRANT CREATE SESSION TO %s", role_name);


   EXEC SQL PREPARE q FROM :GrantCreateSession;


   EXEC SQL EXECUTE q;





   sprintf(GrantCreateTable, "EXEC SQL GRANT CREATE TABLE TO %s", role_name);


   EXEC SQL PREPARE q FROM :GrantCreateTable;


   EXEC SQL EXECUTE q;





   sprintf(GrantCreateView, "EXEC SQL GRANT CREATE VIEW TO %s", role_name);


   EXEC SQL PREPARE q FROM :GrantCreateView;


   EXEC SQL EXECUTE q;





   sprintf(GrantCreateTrigger, "EXEC SQL GRANT CREATE TRIGGER TO %s", role_name);


   EXEC SQL PREPARE q FROM :GrantCreateTrigger;


   EXEC SQL EXECUTE q;





   for (;;)


   {


        EXEC SQL FETCH :table_cursor


            INTO :table_record;


   


        sprintf(table_fqn, "%s.%s", owner_name, table_record);





   


        sprintf(GrantUpdate, "EXEC SQL GRANT UPDATE ON %s",table_fqn ," TO %s", role_name);


        EXEC SQL PREPARE q FROM :GrantUpdate;


        EXEC SQL EXECUTE q;


   


        sprintf(GrantInsert, "EXEC SQL GRANT INSERT ON %s",table_fqn ," TO %s", role_name);


        EXEC SQL PREPARE q FROM :GrantInsert;


        EXEC SQL EXECUTE q;


   


        sprintf(GrantSelect, "EXEC SQL GRANT SELECT ON %s",table_fqn ," TO %s", role_name);


        EXEC SQL PREPARE q FROM :GrantSelect;


        EXEC SQL EXECUTE q;


   


        sprintf(GrantAlter, "EXEC SQL GRANT Alter ON %s",table_fqn ," TO %s", role_name);


        EXEC SQL PREPARE q FROM :GrantAlter;


        EXEC SQL EXECUTE q;


    }


 


   EXEC SQL CLOSE :table_cursor;





/*Role granting*/


   sprintf(GrantRoleToUser, "EXEC SQL GRANT %s",role_name ," TO %s", owner_name);


   EXEC SQL PREPARE q FROM :GrantRoleToUser;


   EXEC SQL EXECUTE q;


 


   sprintf(SetDefaultRole, "EXEC SQL ALTER USER %s",owner_name," DEFAULT ROLE %s", role_name);


   EXEC SQL PREPARE q FROM :SetDefaultRole;


   EXEC SQL EXECUTE q;





   printf("\nArrivederci.\n\n");





   EXEC SQL COMMIT WORK RELEASE;


   exit(EXIT_SUCCESS);


}